multiple OR criteria

  • Thread starter Thread starter Chuck216
  • Start date Start date
C

Chuck216

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
 
Karl

it still prompts for the criteria from the forms which are not open

KARL DEWEY said:
[forms]![YourForm]![TextBox] OR [forms]![YourForm]![TextBox] Is Null

Chuck216 said:
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
Click to expand...
Click to expand...
 
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
 
You can, but it is not necessary without work.

First, have a public function, in a standard module (not behind form,
report, not a class) which take an argument (the form name) and return true
or false accordingly to the fact that the form is open. Kind of:

Public Function IsFormOpen( x As name) As boolean
IsFormOpen= (acObjStateOpen=SysCmd(acSysCmdGetObjectState, acForm,
x))
End Function


(well, that is the basic idea, at least).

Next, in your query, instead of:

fieldName = FORMS!FormNameHere!ControlNameHere


use:

iif( IsFormOpen("FormNameHere"), fieldName =
FORMS!FormNameHere!ControlNameHere, true)



for each of these parameters. Note that you need to use the User Interface
(not CurrentDb) to execute your query, since CurrentDb is not able to solve
the parameter with the syntax FORMS!FormName!ControlName.




Vanderghast, Access MVP
 
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));
 
The easiest way might be to set up a VBA function that will test for which
forms is open and return the value based on which form is open.

Paste the sample function into an existing or new VBA module. If you paste
this into a new module save the module with a name OTHER THAN fGetRideValue

Public Function fGetRideValue()

If CurrentProject.AllForms("frmBlazingFuryMenu").IsLoaded THEN
fGetRide=[Forms]![frmBlazingFuryMenu]![Ride]
ElseIf CurrentProject.AllForms("frmTornadoMenu").IsLoaded THEN
fGetRide=[Forms]![frmTornadoMenu]![Ride]
ElseIf CurrentProject.AllForms("frmSlidewinderMenu").IsLoaded THEN
fGetRide=[Forms]![frmSlidewinderMenu]![Ride]
ElseIf CurrentProject.AllForms("frmDaredevilMenu").IsLoaded THEN
fGetRide=[Forms]![frmDaredevilMenu]![Ride]
ElseIf CurrentProject.AllForms("frmTennesseeTornadoDailyMenu").IsLoaded THEN
fGetRide=[Forms]![frmTennesseeTornadoDailyMenu]![Ride]
Else
fGetRide=Null
End If
End Function

Then in the query change the where clause to
WHERE tblValleyDaily.Ride = fGetRideValue()

AND if you want to you can add the following to the where clause to IGNORE the
criteria if none of the forms is open OR if the value returned by the control
on the open form is null
OR fGetRideValue() is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
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 said:
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
Click to expand...
Click to expand...
 
That did it!!

Thank You



John Spencer MVP said:
The easiest way might be to set up a VBA function that will test for which
forms is open and return the value based on which form is open.

Paste the sample function into an existing or new VBA module. If you paste
this into a new module save the module with a name OTHER THAN fGetRideValue

Public Function fGetRideValue()

If CurrentProject.AllForms("frmBlazingFuryMenu").IsLoaded THEN
fGetRide=[Forms]![frmBlazingFuryMenu]![Ride]
ElseIf CurrentProject.AllForms("frmTornadoMenu").IsLoaded THEN
fGetRide=[Forms]![frmTornadoMenu]![Ride]
ElseIf CurrentProject.AllForms("frmSlidewinderMenu").IsLoaded THEN
fGetRide=[Forms]![frmSlidewinderMenu]![Ride]
ElseIf CurrentProject.AllForms("frmDaredevilMenu").IsLoaded THEN
fGetRide=[Forms]![frmDaredevilMenu]![Ride]
ElseIf CurrentProject.AllForms("frmTennesseeTornadoDailyMenu").IsLoaded THEN
fGetRide=[Forms]![frmTennesseeTornadoDailyMenu]![Ride]
Else
fGetRide=Null
End If
End Function

Then in the query change the where clause to
WHERE tblValleyDaily.Ride = fGetRideValue()

AND if you want to you can add the following to the where clause to IGNORE the
criteria if none of the forms is open OR if the value returned by the control
on the open form is null
OR fGetRideValue() is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top