multiple OR criteria

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
 
C

Chuck216

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...
 
J

Jeff Boyce

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
 
V

vanderghast

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
 
C

Chuck216

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));
 
J

John Spencer MVP

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...
 
C

Chuck216

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...
 
J

Jeff Boyce

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Dcount (multiple criteria) 3
Multiple Parameter Crosstab query filter by form 1
Criteria in a Querie 8
Parameters query 1
Form queries from multiple tables 4
IIf Criteria Question in a Query 4
How To Specify No Criteria 2
criteria for date field using iif() 1

Top