access query misses data criteria

[Joe Farage]

This is the code that i put into the SQL screen.
the data is as follows
BS 1 = 80
BS 2 = 101
BS 3 = 20
BS 4 = 100

and the query asks me for the Blood Sugar Level.
If i use numbers below 80, or 100 and up it works correctly. but if i put in 83,87,95,99, etc between 80 and 100 it shows 0 results even though it should still show the results from above.
what am i missing?

SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Paternal Last Name], [Contact Info].[Maternal Last Name], [Contact Info].DOB, BS.[BS 1], BS.[BS 2], BS.[BS 3], BS.[BS 4]
FROM BS INNER JOIN [Contact Info] ON ([Contact Info].ID = BS.ID) AND (BS.ID = [Contact Info].ID) AND (BS.ID = [Contact Info].ID)
WHERE 
(((BS.[BS 1])>=[Blood Sugar Level])) OR (((BS.[BS 2])>=[Blood Sugar Level])) OR (((BS.[BS 3])>=[Blood Sugar Level])) OR (((BS.[BS 4])>=[Blood Sugar Level]))

[TheSmileyCoder]

Hi Joe

I have tried looking at it, and can't find any obvious issues. Have you tried placing a parenthesis around the 4 or statements (The where criteria)?

Also why do you join the tables 3 times? I would think 1 time should suffice just fine :P

[Joe Farage]

smiley,
thanks for the insight and oops...i cheated and used the query builder to build it for the beginning part so it for some reason joined them 3 times.

but i figured it out. i had the field property set to "text" instead of "number" for all those fields, so it was reading 83 as higher than 100 because the first digit is higher...

[TheSmileyCoder]

Ah, good you could spot that. I read and reread your sql before my first post and couldn't find anything wrong, besides the triple join.

[NeoPa]

Joe Farage:
thanks for the insight and oops...i cheated and used the query builder to build it for the beginning part so it for some reason joined them 3 times.

Actually, using the Query Builder is an intelligent way to proceed and start learning SQL.

That said, the Query Builder isn't stupid either, so if you have the JOIN set up with three identical pairs of fields compared (as you have) then you're doing something wrong there. That may be in your table design (look particularly at how relationships are set up), or it may be in your use of the Query Builder itself. Either way, such mistakes may go unnoticed for a while, but they should never be considered unimportant. It's almost guaranteed that they will come back and bite you on the bottom. I strongly recommend you find and fix.

Best wishes.

[TheSmileyCoder]

I use the query builder myself to START 95% of my SQL statements. Then I might switch to "pure" sql view, copy the SQL syntax into my VBA editor and make small modifications (Like parsing variables into the SQL).

[NeoPa]

Sure. I do too for the more involved queries. I'm not so stupid I want to do all that detailed work with my fingers ;-)