Query w/ 8 "WHERE" Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Query with 8 (eight) WHERE vaiues.

It doesn't ALWAYS work. Sometimes it will
find the record, & other times it won't.

As I analyze it, I found an instance where an "A"
in the table did NOT find the record if "a" was entered
in the unbound textbox as one of the WHERE conditions.

Has anyone encountered a similar situation???

TIA - Bob
 
Bob

A bit tough to diagnose without a look at the actual SQL statement... or the
data...

A little like "I have a car with 4 wheels. Sometimes it works. Sometimes I
press a pedal. What's wrong with it?" <G>

Jeff Boyce
<Access MVP>
 
Jeff - It's a simple 9-field Table, where the PK is assigned by a Function.
The PK value is the "many" in another table which has about 90 fields
because there is heavy analysis based on those 90 values.

Of the 8 fields as "WHERE", they are a complex combination of values as a
"formula" to get to the other 90 observations. Those 8 fields are 7 text &
one date.

Does this help?

TIA - Bob
 
Bob

That describes your data a bit (actual examples would help even more).

The SQL-statement (query) you are using sounds like it isn't working --
please post that, too.

By the way, a table with 90 fields is highly unusual in a well-normalized
design -- is there a chance this data/system has "inherited" a spreadsheet?
If your data design is not well-normalized, you (and Access) will have to
work a lot harder to do things that Access is designed to do with normalized
data.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff - Here's the Query...

SELECT TheData.DataID, TheData.DateG, TheData.ModelG, TheData.ColorG,
TheData.ShopG, TheData.PlantG, TheData.BoothG, TheData.TrailG, TheData.HitG
FROM TheData
WHERE (((TheData.DateG)=Forms!frmMain!GDate) And
((TheData.ModelG)=Forms!frmMain!GModel) And
((TheData.ColorG)=Forms!frmMain!GColor) And
((TheData.ShopG)=Forms!frmMain!GShop) And
((TheData.PlantG)=Forms!frmMain!GPlant) And
((TheData.BoothG)=Forms!frmMain!GBooth) And
((TheData.TrailG)=Forms!frmMain!GTrail) And
((TheData.HitG)=Forms!frmMain!GHit));

Of the "WHERE"s - "DateG" is a Date field, the other 7 are All Text fields.

IF I conclude I can't "trust" it, I'll use a series of "Drill Down" Queries
to reach my goal of determining BOF or not. I use that to decide how to
proceed..to display data (all this is on an unbound Form - Tab Control), or
to inform the User that NO record exists, & ask if they want to add one.

The 90 fields. My Client "lives" off Excel sheets, but decided the App
would be better in Access (I agree). I don't like 90 fields either, but it
appears it is
"appropriate" here. I will have NO performance issues.

TIA - Bob
 
Bob

Date criteria need delimiters, something like:

"WHERE (((TheData.DateG) = #" & Forms!frmMain!GDate) & "# And ..."

and I don't see any Text delimiters on the text fields, like:

" ... TheData.ModelG = '" & Forms!frmMain!GModel) & "' And ..."

Is the SQL statement an exact copy, or re-typed?

Good luck

Jeff Boyce
<Access MVP>
 
Jeff - That's from the Query-By-Example that Access gives.

I wrote the same Query as a string in a module & while trying
to Debug - Compile, it wouldn't "identify" an unbound textbox
on the Form.

That's why I went to a "QueryDef".

Are there any "known limitations" to using "x number"
of WHERE conditions in a Query?

TIA - Bob
 
Bob

I'm not aware of any reason why 8 WHERE clauses would be too many ... but
are you getting an actual value entered into every control on the form?

I'm not recalling any I've seen/heard, but don't decide based on that.

I'm not "intimately" familiar with the KB (Knowledge Base) articles, but can
recommend you visit the Microsoft site and search. And/or visit Google.com
and try some searches there.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff - YES, a value is entered in ALL textboxes.
I can even run the Query manually, & enter
a value for each parameter - it still will fail
some.

Since it appears this particular Query doesn't work
all the time, I'll do a series of Drill-Down Queries.
I'll be in trouble if I can't identify a "non-BOF" w/ just
one WHERE condition.

Thanks for listening to me.

Bob
 
Back
Top