If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Converting Data Type from Text to Number

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
Question Converting Data Type from Text to Number

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!!!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
Sql

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],"");
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
Quote:
Originally Posted by prgrmmr View Post
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
Smile

Yup.. great.. that fixed it
Thank you.
Reply With Quote
  #6 (permalink)  
Old
Moderator
 
Join Date: Jun 2005
Location: Richmond, Virginia USA
Posts: 2,474
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)>
__________________
Hope this helps!

The Devil's in the Details!!

All posts/responses based on Access 2003/2007
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
Quote:
Originally Posted by prgrmmr View Post
Yup.. great.. that fixed it
Thank you.
Glad to be of help.

Sam
Reply With Quote
Reply

Tags
access, data type, query

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On