Finding a certain number

[jasonsas]

HI,
I've created a query, and in this query i need to be able to search for numbers that end in '90'. Eg, I am querying a field that has all types of numbers, usually about 5 digits long. I need to return only the numbers that end in 90. Is this possible?

Thanks

 

[willir]

Hmmmm

I guess the first question is why is it neccessary to find a "90" at the end. Normally, if you know that you are going to parse a "number", then you should define the field as a string. I use this rule of thumb - if I am going to count / add or perform other mathmatical calculations, use the appropriate number field. If the "number" is not to be used in a calculation, then it may be better to use a string field - example, phone numbers, street numbers work much better when treating then as strings.

We have to convert the number to a string and run a comparison....

SELECT Y.*, Right(Format([Y.ItemDefPrice],"#,#00"),2) AS Test
FROM YourTable as Y
WHERE Right(Format([Y.ItemDefPrice],"#,#00"),2) = "90";

Richard

 

[Craig0201]

Richard,

Again, we agree on the string thing but....

Your SQL is a bit unneccessarily complex.

SELECT Y.*, CStr([Y.ItemDefPrice]) AS Test
FROM YourTable as Y
WHERE CStr([Y.ItemDefPrice]) LIKE '*90'

does the same job.

 

[hymn]

have you tried Like *90 in the critera of the query

Hope this helps
Hymn

 

[MichaelRed]

Most SQLers' oppose wild card queries with the wild card as the first character. Since I have generally followed their advice, I can't say fo sho, but the consesus is this takes a lot longer to run. Perhaps on smallish recordsets is is not material, but why devolve into bad practice? After all, some day a "REAL" job might come along ...

And, ther is an alternative:

CalcFld: (Mod([fldName], 90) = 0)

Should (IF i got the args in the correct order, return TRUE for the desired records and FALSE for all others. A conditional on the field would then be a reasonable selection criteria.

MichaelRed
mlred@verizon.net

 

[hymn]

MichaelRed
thanks for the sarcastic comments

Hope this helps
Hymn

 

[EBGreen]

Wouldn't using Mod 100 in the query essentially return the last 2 digits?

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Craig0201]

Michael,

Your Mod will return rubbish.

Mod(360,90) returns 0. The record containing 360 is not required. Perhaps a little more thought into answering the question in hand rather than being a clever dick, may be in order? Also, evidence of the claim might be helpful.

TomThumb is quite correct. Mod([fldName], 100) = 90 would work if you wished to go that way.

Craig

 

[MichaelRed]

my we ARE testy today:

acused of scarscam for what i THINK is an accurate statement

Told the posting is GARBAGE, and advised that "I" need to Check the Facts - evev when i have already noted that i may not have gotten it correct ...

alas

thin skins?

MyNum2 = 1234567899
MyNum1 = 1234567890

? MyNum1 mod 90
0
? ((MyNum1 mod 90) = 0)
True
True

? MyNum2 mod 90
9
? ((MyNum2 mod 90) = 0)
False



MichaelRed
mlred@verizon.net

 

[PHV]

Try something like this:
WHERE (MyNumm Mod 100)=90

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KornGeek]

MichaelRed,
I had never heard the advice not to use wildcard queries where the wildcard is the first character. In a couple of my apps (which have yet to hit truly large datasets), I use these.

My users are allowed to enter criteria (a string), and the query returns all records where the criteria string appears anywhere within the field. For instance, if the user entered "x", the query would use "Like '*x*'".

What would be a better way to do this?

 

[MichaelRed]

KornGeek,

I know of no better approach, if the object is truly anypart of the string. Perhaps some research in the SQL fora would yield some insight, as thoes folks must need to deal withh the issue in a meaniungful environment.





MichaelRed
mlred@verizon.net

 

[cgarts]

Could something this simple work?

SELECT Tblx.Num90
FROM Tblx
WHERE ((Right([Num90],2)=90))

Best,

C

 

[MichaelRed]

A variation. converting the 'value' to a string would.

 ... right(CStr([Num90]), 2) = 90)

or some minor variation thereof.

On the otherhand, what makes this more 'simple' than the modulo approach?



MichaelRed
mlred@verizon.net

 

[formerTexan]

I think simple wins the day.

Access's numeric abilities don't make it the fastest kid on the block and using the mod operator seems to carry some computational baggage.

Cheers,
Bill

 

[Craig0201]

Michael,

Your MOD is indeed incorrect. This had nothing to do with argument order and everything to do with the value of the arguments.

?((1234567890 mod 90)=0) is true as 1234567890 = 13717421 * 90
?((123456790 mod 90)=0) is false as 123456790 = 1371742 1/9 * 90
?123456790 mod 90 returns 10.

?((1234567890 mod 100)=90) will return true
?((123456790 mod 100)=90) will also return true

KornGeek,

Michael is actually correct with regards to wildcards at the front of strings but chooses to condescend rather than educate.

This is because the database engine can't use the indexes on the field when the wildcard is at the beginning of the string.

This quote relates to Informix but probably relates to JET as well.

"Look out for the WHERE predicates which may not be able to use indexes. These include: OR, LIKE or MATCHES with a wildcard at the beginning (e.g.; MATCHES *NOUR), functions (e.g.; MONTH, DAY, LENGTH), negative expressions ( e.g.; != NOUR), non initial substring searches (e.g.; postcode[4,5]>10). "

Craig

 

[jasonsas]

Thanks so much for all the replies.

Cgarts solution seemed to be the easiest to implement.

Thanks