simple query

[bembden]

I am not a programmer, however I manage a medium database. It has an access front end connected to an sql back end.
I have been asked to export a work order table for specific systems for the analysis group. The asset record determines what systems to export. I need the abc systems only. In the criteria if I put "abc" I get no results. "abc*" gets me the abc system as well as the abcl/abcm/abch systems.
The abc system is the only set of systems with 3 alpha characters in the name. All other systems have 4 alpha characters.
Any help is appreciated.

Thanks

 

[byurow]

Is there a space after abc? Have you tried "abc "?



Brenda

 

[bembden]

Thanks for the reply Brenda

A space returns no records. The asset is identified as
abc100.00
abc101.00
abc102.00

With each asset having a number of records (abc100.00/.01/.02 etc)

Bernie

 

[Golom]

Maybe something like

'abc[0-9][0-9][0-9].'

which should give you 'abc' followed by three digits and a period.

 

[bembden]

Thanks Golom.


That does not seem to work either

Bernie

 

[byurow]

Try:

Like "ABC1*" Or Like "ABC2*" or Like "ABC3*" or Like "ABC4*"


Brenda

 

[PWise]

where asset is like "abc*" and IsNumeric(mid(asset,3,1))

 

[PWise]

sorry s/b

where asset is like "abc*" and IsNumeric(mid(asset,4,1))

 

[bembden]

Try:

Like "ABC1*" Or Like "ABC2*" or Like "ABC3*" or Like "ABC4*"


This will work if I ran 3 queries. The systems start at 100 and go to 300.
Is there a way to join them together?

Like abc 1 to 3?


Bernie

 

[byurow]

You can run them as one query:

Where Asset Like "ABC1*" Or Asset Like "ABC2* Or Asset Like "ABC3*"


Brenda

 

[bembden]

You can run them as one query:

Where Asset Like "ABC1*" Or Asset Like "ABC2* Or Asset Like "ABC3*"

Thanks byurow

I believe that will work.


Thanks everyone

 

[PHV]

Where Asset Like "ABC1*" Or Asset Like "ABC2* Or Asset Like "ABC3*"
A simpler way:

WHERE Asset Like 'ABC[123]*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886