Query Criteria Statement?

[mdaniele]

That was kinda freaky, I'll repost.

I have a query with a LIKE "*" & [ ] & "*" statement in the DESC criteria field.
Could someone help me with syntax for another criteria for the query's "SUB" field that will retrieve records based on the value in the table's SUB field of the active record I'm working on?.

...i.e. I'm working in row 894 of a table, and the value in the SUB field is "220", and the value in the DESC field is "Motor - 220 Volts - 2 HP".
Currently, when I run my query, I specify "2 HP" to satisfy the query's LIKE parameter, and all records with "2 HP" appear.

What I'm looking for is the the criteria statement in the query's SUB that will automatically use the value from the table's SUB in whatever record I'm on and retrieve only the records in SUB "220" and "2 HP" as part of the description.

Thanks for any and all suggestions

MoD



...and when Satan lividly demanded to know why the old man did not exhibit any fear whatsoever of him, the 82 year old man calmly replied "why hell, you ain't so bad, I've been married to your sister for over 50 years"

 

[jrbarnett]

Hi,

Try

Like "*220*2 HP*"

will pick up any which have 220 ahead of 2 HP, you can reverse the string to retrieve it the other way around.

Alternatively you could use:
Field like "*220*" or field Like "*2 HP*"

to pick up both in either order.

John

 

[jazzz]

Not sure what you are asking but people here are always asking about books to help them along. I highly recommend 'SQL Queries for Mere Mortals' by Michale J. Hernandez and John L. Viescas. Excellent book to help one figure out query building.

Life's a journey enjoy the ride...

jazzz

 

[mdaniele]

Sorry that I did not make the question clear enough.
I don't want the query SUB criteria to be a specific value, but rather to reference whatever value is in the SUB field in the active record when I run the query.
So, if I happen to be on a record that has "180" in the SUB field, I want the query to automatically use 180 as the SUB criteria value, if 199 is in the active record, the query will use 199, (in the same way a filter works)






...and when Satan lividly demanded to know why the old man did not exhibit any fear whatsoever of him, the 82 year old man calmly replied "why hell, you ain't so bad, I've been married to your sister for over 50 years"

 

[jrbarnett]

Try setting it to:

= "'" & Me!Sub & "'"

Replace sub with the name of the control that holds the value.

John

 

[mdaniele]

Problem is that I'm not using a form, am just working with raw data in data tables. I already use a query that displays a subset of all the records based on the LIKE parameter in the DESCRIPTION field, and want to be able to add a criteria expression into the SUB field that will auto filter based on the value of the SUB field of the record I'm on in the data table.

...and when Satan lividly demanded to know why the old man did not exhibit any fear whatsoever of him, the 82 year old man calmly replied "why hell, you ain't so bad, I've been married to your sister for over 50 years"