Results 1 to 7 of 7
  1. #1
    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!!!

  2. #2
    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

  3. #3
    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],"");

  4. #4
    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

  5. #5
    Join Date
    Dec 2012
    Posts
    3

    Smile

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

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,526
    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

  7. #7
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •