Query starts with a number

[Guest]

I have an Intranet page which pull data from an Access 2000 database.

I have created links and queries to display substances which begin with A to
Z. But I am having problems with substances which start with a number ie 0
to 9.

Here is the query:

strSQL = "SELECT Table1.* FROM Table1 WHERE Table1.substance LIKE '0%' ORDER
BY Table1.ID DESC;"

Any help with this is much appreciated.

I am using Access 2000 and Windows XP.

 

[Guest]

So what IS the problem?

 

[Guest]

The query will only display substances beginning with 0 as it is. I need the
query to display substances beginning with 0 and/or 1 and/or 2 and/or 3
and/or 4 and/or 5 and/or 6 and/or 7 and/or 8 and/or 9.

 

[Guest]

If your query has "LIKE '0%' " then it will only select those with 0? I also
see the "%" sign. Is the column simply a division formatted as "fixed" or are
you multiplying by 100. I would have thought your "like" statement would be
affected depending on your formatting. I think you may need a wildcard
character (asterisk). Just a thought.

What do you mean by "and/or" ??? What do the numbers look like or are they
combinations of numbers and text?

 

[Guest]

Sorry, there won't be text if you are using a percentage. duh!

 

[Guest]

Thanks scubadiver

The column is simply a text field that has text and numbers entered, they
are substances eg '3 in 1 oil' or 'de-icer' or 'anti-freeze'. The % sign acts
as a wild card so 'a%' would return anything starting with an 'a'. The
asterisk doesn't work. I am not dividing or multiplying.

Tha and/or means I want the query to return any substance startrting with 0
or starting with 1 or starting with 2 etc

 

[Guest]

My solution would be to split the field so you isolate the number (assuming
it will always appear first in the text).

expr1: left([fieldname],1)

I think!

 

[Guest]

I have found the solution:

strSQL = "SELECT Table1.* FROM Table1 WHERE Table1.substance LIKE '0%' or
Table1.substance LIKE '1%' or Table1.substance LIKE '2%' or Table1.substance
LIKE '3%' or Table1.substance LIKE '4%' or Table1.substance LIKE '5%' or
Table1.substance LIKE '6%' or Table1.substance LIKE '7%' or Table1.substance
LIKE '8%' or Table1.substance LIKE ‘9%' ORDER BY Table1.ID DESC;"

Thanks for your help.

 

[John Spencer]

Try the following

Substance Like '[0-9]%'

or if that doesn't work then this should

Substance Like '[0123456789]%'
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..