Searching multiple fields in a query for the same number. 1

[testkitt2]

Hello to All
I have a query that has a wild card to search for particular numbers in a database. The thing is that there
are 7 fields that may have the number that I'm looking for.

For example... Lets say that you give each tire on a truck a number and the truck has 10 tire positions.

The query is set with a simple [enter tire number] on the query so the user can enter the tire number that they are looking for. But the tire could be in any one of the 10 different positions. Therefore I need to
set up some type of query that will allow me to search for a tire number no matter what position it is in.
If I use a wild card on more than one field I get a blank result on the query.

any suggestions are greatly appreciated.
I could also copy the query code in SQL and post it.

I know this is probably easy for most of you.
But anyway thanks for just reading this
JZ
Testkitt2


Testkitt2

 

[gol4]

looks like a design issue see database normalization to make your life much easier. In the mean time did you try the or operator
select * from table where field1 = tirenumber or field2 = tire number or field3= tire number etc...


the way to do this in the QBE grid is to add criteria on the first line of the first field then drop down 1 line and add it to field2 down a line ..field 3 etc...

 

[PHV]

WHERE [enter tire number] In ([tire pos 1],[tire pos 2],...,[tire pos 10])

Anyway, have a look here:

http://r937.com/relational.html

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

 

[testkitt2]

Thanks gol4 and PHV
for your suggestions.. I give them both a try at work and will post results.

Good day
JZ

Testkitt2

 

[testkitt2]

Hey Thanks guys
Thanks gol4, PVH

This is what I tried and seems to be working....

SELECT [Fleet Number], [Right Front], [Left Front], [Right Rear Outer], [Right Rear Inner], [Left Rear Inner], [Left Rear Outer], [Right Rear Rear Outer], [Right Rear Rear Inner], [Left Rear Rear Inner], [Left Rear Rear Outer], *
FROM tblTirePosition
WHERE [enter tire number] In ([Right Front],[Left Front],[Right Rear Outer],[Right Rear Inner],[Left Rear Inner],[Left Rear Outer],[Right Rear Rear Outer],[Right Rear Rear Inner],[Left Rear Rear Inner],[Left Rear Rear Outer]);

Remember a truck has 12 tire positions and once a tire is branded with a number it could land up in any position on a truck. The thing is once it catches a flat it may even be put back into inventory or on another truck all together.

Now I have this query attached to a form that has a design of a truck with text boxes to represent each tire position. Is it possible that when the query runs and finds the "number" branded on the tire that the focus go to that text box and have like "conditional formatting" change the background or and number color to highlight the field found.

If you guys have some time...pls assist...

Thank you
JZ

Testkitt2

 

[gol4]

once the record is found just loop thru your textboxes and if there is a match color it

assumes you textboxes are named pos1,pos2 etc.
code similar to this

dim x as integer
if me.recordsetclone.recordcount>0 then
for x = 1 to 12
if me("pos"& x)= me![enter tire number]then 
me("pos"& x).forecolor= vbred
else
me("pos"& x).forecolor= vbblack
end if
next

you still should normalize this database

 

[testkitt2]

Thanks Gol4
I'll give it a try...and will post back..
I was out a few days...thus checking this site late..

Thanks again
JZ

Testkitt2

 

[testkitt2]

Hello to all
Gol4
I tried your code and then tried to modify it..Not working.....maybe I'm using it wrong,...
Note: my text boxes are named just the way it shows in my previous post..look at the post for 8 Aug 05 21:45

I will keep trying...
Thanks
JZ

Testkitt2