Re: multiple OR criteria

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Based on the SQL, your table is more like a spreadsheet than a relational
database table. When you have "repeating fields" (e.g., "Discrepancy_1_1",
"Discrepancy_2_1", ... "Initials_1_1", "Initials_2_1", ...), you have
committed spreadsheet on Access.

This design is pretty much what you'd use with a spreadsheet application,
but look ahead to the maintenance nightmare involved ... If there is any
change to the number of "discrepancy" or "initials" fields, you have to
change the table definition, change any related SQL statements, change any
related queries, change any related forms/reports, change any related
macros, change any related code ...

Consider turning off Access and taking up paper/pencil to sketch out the
entities and relationships. Count this as a "pay now or pay later". Sure,
you have a (temporary) solution for your current data structure ... and you
will pay later!

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Chuck216" <cigar216@xxxxxxxxxxx> wrote in message
news:2B0D2D72-2ED9-43A3-88B3-D96815EB5575@xxxxxxxxxxxxxxxx
Jeff

Here is the SQL it's rather long.


SELECT tblValleyDaily.ID AS tblValleyDaily_ID, tblValleyDaily.InspDate,
tblValleyDaily.Ride AS tblValleyDaily_Ride, tblValleyDaily.Complete,
tblValleyDaily.Discrepancy_1_1, tblValleyDaily.Initials_1_1,
tblValleyDaily.Discrepancy_2_1, tblValleyDaily.Initials_2_1,
tblValleyDaily.Discrepancy_2_2, tblValleyDaily.Initials_2_2,
tblValleyDaily.Discrepancy_2_3, tblValleyDaily.Initials_2_3,
tblValleyDaily.Discrepancy_2_4, tblValleyDaily.Initials_2_4,
tblValleyDaily.Discrepancy_2_5, tblValleyDaily.Initials_2_5,
tblValleyDaily.Discrepancy_2_6, tblValleyDaily.Initials_2_6,
tblValleyDaily.Discrepancy_2_7, tblValleyDaily.Initials_2_7,
tblValleyDaily.Discrepancy_2_8, tblValleyDaily.Initials_2_8,
tblValleyDaily.Discrepancy_2_9, tblValleyDaily.Initials_2_9,
tblValleyDaily.Discrepancy_3_1, tblValleyDaily.Initials_3_1,
tblValleyDaily.Discrepancy_3_2, tblValleyDaily.Initials_3_2,
tblValleyDaily.Discrepancy_3_3, tblValleyDaily.Initials_3_3,
tblValleyDaily.Discrepancy_3_4, tblValleyDaily.Initials_3_4,
tblValleyDaily.Discrepancy_3_5, tblValleyDaily.Initials_3_5,
tblValleyDaily.Discrepancy_3_6, tblValleyDaily.Initials_3_6,
tblValleyDaily.Discrepancy_4_1, tblValleyDaily.Initials_4_1,
tblValleyDaily.Discrepancy_4_2, tblValleyDaily.Initials_4_2,
tblValleyDaily.Discrepancy_4_3, tblValleyDaily.Initials_4_3,
tblValleyDaily.Discrepancy_4_4, tblValleyDaily.Initials_4_4,
tblValleyDaily.Discrepancy_4_5, tblValleyDaily.Initials_4_5,
tblValleyDaily.Discrepancy_5_1, tblValleyDaily.Initials_5_1,
tblValleyDaily.Discrepancy_5_2, tblValleyDaily.Initials_5_2,
tblValleyDaily.Discrepancy_5_3, tblValleyDaily.Initials_5_3,
tblValleyDaily.Discrepancy_5_4, tblValleyDaily.Initials_5_4,
tblValleyDaily.Discrepancy_5_5, tblValleyDaily.Initials_5_5,
tblValleyDaily.Discrepancy_5_6, tblValleyDaily.Initials_5_6,
tblValleyDaily.Discrepancy_5_7, tblValleyDaily.Initials_5_7,
tblValleyDaily.Discrepancy_5_8, tblValleyDaily.Initials_5_8,
tblValleyDaily.Discrepancy_5_9, tblValleyDaily.Initials_5_9,
tblValleyDaily.Discrepancy_5_10, tblValleyDaily.Initials_5_10,
tblValleyDaily.Discrepancy_5_11, tblValleyDaily.Initials_5_11,
tblValleyDaily.Discrepancy_5_12, tblValleyDaily.Initials_5_12,
tblValleyDaily.Discrepancy_5_13, tblValleyDaily.Initials_5_13,
tblValleyDaily.Discrepancy_5_14, tblValleyDaily.Initials_5_14,
tblValleyDaily.Discrepancy_5_15, tblValleyDaily.Initials_5_15,
tblValleyDaily.Discrepancy_6_1, tblValleyDaily.Initials_6_1,
tblValleyDaily.Discrepancy_6_2, tblValleyDaily.Initials_6_2,
tblValleyDaily.Discrepancy_6_3, tblValleyDaily.Initials_6_3,
tblValleyDaily.Discrepancy_6_4, tblValleyDaily.Initials_6_4,
tblValleyDaily.Discrepancy_6_5, tblValleyDaily.Initials_6_5,
tblValleyDaily.Discrepancy_6_6, tblValleyDaily.Initials_6_6,
tblValleyDaily.Discrepancy_6_7, tblValleyDaily.Initials_6_7,
tblValleyDaily.Discrepancy_7_1, tblValleyDaily.Initials_7_1,
tblValleyDaily.Discrepancy_7_2, tblValleyDaily.Initials_7_2,
tblValleyDaily.Discrepancy_7_3, tblValleyDaily.Initials_7_3,
tblValleyDaily.Discrepancy_7_4, tblValleyDaily.Initials_7_4,
tblValleyDaily.Discrepancy_7_5, tblValleyDaily.Initials_7_5,
tblValleyDaily.Discrepancy_7_6, tblValleyDaily.Initials_7_6,
tblValleyDaily.Discrepancy_8_1, tblValleyDaily.Initials_8_1,
tblValleyDaily.Discrepancy_8_2, tblValleyDaily.Initials_8_2,
tblValleyDaily.Discrepancy_8_3, tblValleyDaily.Initials_8_3,
tblValleyDaily.Discrepancy_9_1, tblValleyDaily.Initials_9_1,
tblValleyDaily.Discrepancy_9_2, tblValleyDaily.Initials_9_2,
tblValleyDaily.Discrepancy_9_3, tblValleyDaily.Initials_9_3,
tblValleyDaily.Discrepancy_9_4, tblValleyDaily.Initials_9_4,
tblValleyDaily.Discrepancy_9_5, tblValleyDaily.Initials_9_5,
tblValleyDaily.Discrepancy_9_6, tblValleyDaily.Initials_9_6,
tblValleyDaily.Discrepancy_9_7, tblValleyDaily.Initials_9_7,
tblValleyDaily.Discrepancy_9_8, tblValleyDaily.Initials_9_8,
tblValleyDaily.Discrepancy_10_1, tblValleyDaily.Initials_10_1,
tblValleyDaily.Discrepancy_10_2, tblValleyDaily.Initials_10_2,
tblValleyDaily.Discrepancy_10_3, tblValleyDaily.Initials_10_3,
tblValleyDaily.Discrepancy_10_4, tblValleyDaily.Initials_10_4,
tblValleyDaily.Discrepancy_10_5, tblValleyDaily.Initials_10_5,
tblValleyDaily.Discrepancy_10_6, tblValleyDaily.Initials_10_6,
tblValleyDaily.Discrepancy_10_7, tblValleyDaily.Initials_10_7,
tblValleyDaily.Discrepancy_10_8, tblValleyDaily.Initials_10_8,
tblValleyDaily.Discrepancy_10_9, tblValleyDaily.Initials_10_9,
tblValleyDaily.Discrepancy_11_1, tblValleyDaily.Initials_11_1,
tblValleyDaily.Discrepancy_12_1, tblValleyDaily.Initials_12_1,
tblValleyDaily.Discrepancy_12_2, tblValleyDaily.Initials_12_2,
tblValleyDaily.Discrepancy_12_3, tblValleyDaily.Initials_12_3,
tblValleyDaily.Discrepancy_12_4, tblValleyDaily.Initials_12_4,
tblValleyDaily.Discrepancy_12_5, tblValleyDaily.Initials_12_5,
tblValleyDaily.Discrepancy_12_6, tblValleyDaily.Initials_12_6,
tblValleyDaily.Discrepancy_12_7, tblValleyDaily.Initials_12_7,
tblValleyDaily.Discrepancy_12_8, tblValleyDaily.Initials_12_8,
tblValleyDaily.Discrepancy_12_9, tblValleyDaily.Initials_12_9,
tblValleyDaily.Discrepancy_12_10, tblValleyDaily.Initials_12_10,
tblValleyDaily.Discrepancy_12_11, tblValleyDaily.Initials_12_11,
tblValleyDaily.Discrepancy_12_12, tblValleyDaily.Initials_12_12,
tblValleyDaily.Discrepancy_13_1, tblValleyDaily.Initials_13_1,
tblValleyDaily.Discrepancy_13_2, tblValleyDaily.Initials_13_2,
tblValleyDaily.Discrepancy_13_3, tblValleyDaily.Initials_13_3,
tblValleyDaily.Discrepancy_14_1, tblValleyDaily.Initials_14_1,
tblSigDaily.ID
AS tblSigDaily_ID, tblSigDaily.Name1, tblSigDaily.Signature1,
tblSigDaily.Sig_1_Date, tblSigDaily.Name2, tblSigDaily.Signature2,
tblSigDaily.Sig_2_Date, tblSigDaily.Name3, tblSigDaily.Signature3,
tblSigDaily.Sig_3_Date, tblSigDaily.Name4, tblSigDaily.Signature4,
tblSigDaily.Sig_4_Date, tblSigDaily.Ride AS tblSigDaily_Ride,
tblSigDaily.Link
FROM tblValleyDaily INNER JOIN tblSigDaily ON tblValleyDaily.ID =
tblSigDaily.Link
WHERE (((tblValleyDaily.Ride)=[Forms]![frmBlazingFuryMenu]![Ride] Or
(tblValleyDaily.Ride)=[Forms]![frmTornadoMenu]![Ride] Or
(tblValleyDaily.Ride)=[Forms]![frmSlidewinderMenu]![Ride] Or
(tblValleyDaily.Ride)=[Forms]![frmDaredevilMenu]![Ride])) OR
((([Forms]![frmTennesseeTornadoDailyMenu]![Ride]) Is Null));








"Jeff Boyce" wrote:

Please post the SQL statement of the query.

Is there a chance you've used the query's parameter property to "name"
all
those criteria?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Chuck216" <cigar216@xxxxxxxxxxx> wrote in message
news:E3C49A85-C837-4B01-A49C-E630DB13BC00@xxxxxxxxxxxxxxxx
I have several forms that I need to use the same query (one at a time)
each
form has a field with the same name on it that I am using to pass
criteria
to
the query. I have set up multiple OR criteria in the query looking for
the
field on the forms. My question is since I only have one form open at
any
one
time is there a way to stop the query from prompting for the criteria
from
the other forms which are not open and just accept the criteria from
the
open
form.

Thanks in advance for any help with the problem.
Chuck






.

Relevant Pages

  • Re: SQL Syntax Error
    ... match all the criteria you input. ... SQL view and never open it in the query grid view. ... The syntax for the "where" statement is incorrect becuase of this. ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... "Jeff Boyce" wrote: ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ...
    (microsoft.public.access.formscoding)
  • Re: Adjusting SQL in Query
    ... from the RESPEL table which satisfy the criteria in the PRICELIST Table. ... RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST = ... Subject: Adjusting SQL in Query ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ... Dim strWhere As String ...
    (microsoft.public.access.formscoding)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... The way I'd debug it would be to start w/ one OR clause in the criteria. ... Run the query & see if it works for that criteria. ... I followed your suggestion of pasting the sql, ...
    (microsoft.public.access.queries)