Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quering only for numbers (Access 2000)

    I have a query where I'm looking for all text in a field starting with a number. I've tried Val((Left([Assignment],1))) and then in criteria between 1 and 9 but it runs the query and after 3 seconds the whole table have errors on.

    Thanks for the help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quering only for numbers (Access 2000)

    Try this as criteria for the Assignment field itself:
    <pre>Like "[0-9]*"
    </pre>

    The [0-9] means "any character in the range from 0 to 9".

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quering only for numbers (Access 2000)

    This one still has the original criteria. You don't seriously expect me to actually read the entire SQL, do you? It's almost 7,000 characters long.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quering only for numbers (Access 2000)

    The calculations in the query are ridiculously convoluted; some elements have no discernible function (such as IIf(InStr([Text],"T0")>0,1,1), repeated 12 times), and there is a lot of redundance. The SQL in the attached text file should do the same in less than 60% of the characters.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quering only for numbers (Access 2000)

    Almost 7,000 characters long SQL statement moved to attachment.

    I've tried it and it still gives me the error after 3 seconds. The complete sql statement is attached.

    This is my attempt. What I'm trying to do is check for specific text inside the assignment field.

    Thanks for any ideas
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quering only for numbers (Access 2000)

    Hans in the field I'm checking not only the "2004" but for which month the billing is and who is the source. I've got on my report a section that summaries this data as option buttons for quick (long code) viewing. I could have the field as "2004 M01 M02 T1 T2" M01 being month 1 and T1 being the source.

    Any ideas would be great

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quering only for numbers (Access 2000)

    Sorry, what is the question exactly?

Posting Permissions

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