I have this query where I want the field to return the value as "Number" data type. Fields in calculation are both "Number" data type. BUT, for some reason when I run the query, the field gives me a text data type. Therefore, it's giving me a type mismatch when I need to do a subtotal.


Need help!!!

Type mismatches can have other meanings.

Check all your entries. Numerical fields

1 - must not have spaces or any non-numeric characters

2 - may have only decimal (.) and/or commas (,) in valid places (this is also dependant on your locale)

3 - may have a valid currency symbol if they are currency type

4 - are almost always right-justified in the field.

I have a suspicion, though, that your data is fine. If it is, please copy and post the query's SQL here so we can take a look at it.

Sam

SELECT [tblAgeBucketing - 1].Region, [tblAgeBucketing - 1].VendorName, [tblAgeBucketing - 1].VendorID, [tblAgeBucketing - 1].InvoiceNum, [tblAgeBucketing - 1].InvoiceDate, [tblAgeBucketing - 1].InvoiceAmount, [tblAgeBucketing - 1].AP_PaidDate, [tblAgeBucketing - 1].AP_PaidAmt, [tblAgeBucketing - 1].ReferenceNum, [tblAgeBucketing - 1].[Validate/Approval], [tblAgeBucketing - 1].DueDate, [tblAgeBucketing - 1].DaysFromInvDate, IIf([tblAgeBucketing - 1]![DaysFromInvDate]<31,[InvoiceAmount],"") AS [0-30], IIf(([tblAgeBucketing - 1]![DaysFromInvDate]<61) And ([tblAgeBucketing - 1]![DaysFromInvDate]>30),[InvoiceAmount],"") AS [31-60], IIf(([tblAgeBucketing - 1]![DaysFromInvDate]<91) And ([tblAgeBucketing - 1]![DaysFromInvDate]>60),[InvoiceAmount],"") AS [61-90], IIf(([tblAgeBucketing - 1]![DaysFromInvDate]<121) And ([tblAgeBucketing - 1]![DaysFromInvDate]>90),[InvoiceAmount],"") AS [91-120], IIf(([tblAgeBucketing - 1]![DaysFromInvDate]>120),[InvoiceAmount],"") AS [120+]
FROM [tblAgeBucketing - 1]
GROUP BY [tblAgeBucketing - 1].Region, [tblAgeBucketing - 1].VendorName, [tblAgeBucketing - 1].VendorID, [tblAgeBucketing - 1].InvoiceNum, [tblAgeBucketing - 1].InvoiceDate, [tblAgeBucketing - 1].InvoiceAmount, [tblAgeBucketing - 1].AP_PaidDate, [tblAgeBucketing - 1].AP_PaidAmt, [tblAgeBucketing - 1].ReferenceNum, [tblAgeBucketing - 1].[Validate/Approval], [tblAgeBucketing - 1].DueDate, [tblAgeBucketing - 1].DaysFromInvDate, IIf([tblAgeBucketing - 1]![DaysFromInvDate]<31,[InvoiceAmount],""), IIf(([tblAgeBucketing - 1]![DaysFromInvDate]<61) And ([tblAgeBucketing - 1]![DaysFromInvDate]>30),[InvoiceAmount],""), IIf(([tblAgeBucketing - 1]![DaysFromInvDate]<91) And ([tblAgeBucketing - 1]![DaysFromInvDate]>60),[InvoiceAmount],""), IIf(([tblAgeBucketing - 1]![DaysFromInvDate]<121) And ([tblAgeBucketing - 1]![DaysFromInvDate]>90),[InvoiceAmount],""), IIf(([tblAgeBucketing - 1]![DaysFromInvDate]>120),[InvoiceAmount],"");

OK, there's the problem, probably. All your iif() statements end inappropriately. I'll just show you one; they're all the same error. A disclaimer: there may be other errors, but this one's obvious.

IIf([tblAgeBucketing - 1]![DaysFromInvDate]<31,[InvoiceAmount],"")

change to

IIf([tblAgeBucketing - 1]![DaysFromInvDate]<31,[InvoiceAmount],0)

That's what's probably changing the data to text. As I said in my previous post, non-numeric characters aren't allowed in numeric fields, including empty ones.

You can always suppress the 0's in the report; you can't, though, in the query.

Sam

Yup.. great.. that fixed it
Thank you.

The simplest way to avoid this error, in the future, is to simply omit the final Argument in the Nz Function ("" or 0, in this example.) When this Argument is omitted, the Access Gnomes will use the appropriate one, based on the Datatype of the Field in question.

Linq ;0)>

Glad to be of help.

Sam