Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006

    Unanswered: Access query treating text as number

    I am using Access 97.
    I have a query that pulls in a field from a table. The field is defined as text, 255 characters. The data that populated the field is actually numbers (0001, 0050, 0300, etc...) and when I look at the data in table view that is how the data is displayed.
    However, when I run a query that links this table with another table, the results of the query do not include the leading zeros (1, 50, 300, etc...).
    I am very new to Access and do not understand why this would happen. I see some threads that could possibly help me with my problem (like change the parameters of the field in the query) but I really need step by step instructions.

    Thank you for any help you can give me.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Look up the Format function (i.e formatting numbers). In your query you can put in the blank column of a field row an expression which is something like MyNumber: Format([FieldNamehere],"#,#0.0") or something like that (not sure on the exact format of the "#,#0.0" or maybe try "###0", etc.). There's different ways you can format the field. There's also other ways to do this. This is one way to get the numbers to display the way you want in the query. Looking up the help on Format of numbers will give you options on how you want to display the number. Someone else will probably show you the right format on the way you want it displayed but hopefully this leads you in the right direction. Also, since it's a text field, you may also need to do some kind of convert function with the format function (look up in the help converting text to numbers). I can't think off of the top of my head what that would be. Sorry, I'll try to get you more info if you don't get some other responses but the idea is that you can use expressions in a query to change the way you want certain fields displayed. You could also consider changing the text field to an integer data type in the table itself (unless you also store alpha characters in the same field). As an integer data type you can still change the way you want it displayed (i.e. with leading 0's).
    Last edited by pkstormy; 09-09-06 at 20:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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