Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Query Criteria Tip

    Some of the databases I work with include "text" fields that contain only numbers -- an old guru once taught me that everything is "text" unless you need to perform mathematical calculations on it. These numbers are zero-filled, so that when sorted they will be listed in the correct order ("0004" before "0031", instead of "31" before "4"). When it became necessary to build criteria on these fields into queries, the prompts would read something like 'What Product? "0000"'. This would tell the user that the number had to be entered including leading zeroes.

    An alternative to this would be to enter the following in the criteria field:

    <font color=blue>Format([What Product?],"0000")</font color=blue> [img]/w3timages/icons/yep.gif[/img]

    This allows the user to enter "4" at the prompt, and have Access interpret it as "0004" and return the correct records. Users no longer have to be concerned with whether the "number" is actually a "text" item, or if the query results come back incorrect because the leading zeroes were incorrectly input.

    (This works for Access 2000; I don't know if this would be workable in previous versions of Access.)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Criteria Tip

    The other way to do it is to create a calculated field in the query grid that converts the text "number" to a real number so that when the user enters a 4, that will be compared to an expression like Val([ProductNumber]).
    Charlotte

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria Tip

    Now this relates exactly to my situation; I'm trying to sum a text field. Somehow I just couldn't get the query to provide a "number" equivalent to the actual text.

Posting Permissions

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