form provides and/or params to query

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

Guest

I've been trying to wrap my head around this one and am stuck.

I have a table with WorkDate and ShiftNum fields. I have a form that prompts
for the criteria. Query AND works fine. Now the fun part. I want to enter
just a date get the results for all shifts (or the shift with no date for all
dates). I entered these on seperate lines (in design view) to get the OR
which works when I leave one field empty however when I enter both a date and
shift I still get the OR's results included. When I enter both params I only
want the AND.

Any help would be appreciated.
 
I've been trying to wrap my head around this one and am stuck.

I have a table with WorkDate and ShiftNum fields. I have a form that prompts
for the criteria. Query AND works fine. Now the fun part. I want to enter
just a date get the results for all shifts (or the shift with no date for all
dates). I entered these on seperate lines (in design view) to get the OR
which works when I leave one field empty however when I enter both a date and
shift I still get the OR's results included. When I enter both params I only
want the AND.

Any help would be appreciated.
Click to expand...

The criteria need parentheses. It may be easier to see in SQL view;
the SQL WHERE clause would resemble

WHERE ([WorkDate] = [Forms]![YourForm]![txtWorkdate] OR
[Forms]![YourForm]![txtWorkdate] IS NULL)
AND
([ShiftNum] = [Forms]![YourForm]![txtShift] OR
[Forms]![YourForm]![txtShift] IS NULL)

When you go back to the design grid, Access will probably make a
hard-to-understand hash of this (putting the form references into
calculated fields and going to four OR lines) but it should still
work.

John W. Vinson[MVP]
 
John,

Thanks, I had found a note something like that yesterday and tried it but
when I went back to design view is basically put it back. I must have typed
it wrong.

Your suggestion works just fine. Thx.

John Vinson said:
I've been trying to wrap my head around this one and am stuck.

I have a table with WorkDate and ShiftNum fields. I have a form that prompts
for the criteria. Query AND works fine. Now the fun part. I want to enter
just a date get the results for all shifts (or the shift with no date for all
dates). I entered these on seperate lines (in design view) to get the OR
which works when I leave one field empty however when I enter both a date and
shift I still get the OR's results included. When I enter both params I only
want the AND.

Any help would be appreciated.
Click to expand...

The criteria need parentheses. It may be easier to see in SQL view;
the SQL WHERE clause would resemble

WHERE ([WorkDate] = [Forms]![YourForm]![txtWorkdate] OR
[Forms]![YourForm]![txtWorkdate] IS NULL)
AND
([ShiftNum] = [Forms]![YourForm]![txtShift] OR
[Forms]![YourForm]![txtShift] IS NULL)

When you go back to the design grid, Access will probably make a
hard-to-understand hash of this (putting the form references into
calculated fields and going to four OR lines) but it should still
work.

John W. Vinson[MVP]
Click to expand...
 
I figured out where I went wrong yesterday. I had entered the Null
statements on the criteria line instead of in SQL view. Access didn't hash it
out the same.

Thanks again.

cpsaltis said:
John,

Thanks, I had found a note something like that yesterday and tried it but
when I went back to design view is basically put it back. I must have typed
it wrong.

Your suggestion works just fine. Thx.

John Vinson said:
I've been trying to wrap my head around this one and am stuck.

I have a table with WorkDate and ShiftNum fields. I have a form that prompts
for the criteria. Query AND works fine. Now the fun part. I want to enter
just a date get the results for all shifts (or the shift with no date for all
dates). I entered these on seperate lines (in design view) to get the OR
which works when I leave one field empty however when I enter both a date and
shift I still get the OR's results included. When I enter both params I only
want the AND.

Any help would be appreciated.
Click to expand...

The criteria need parentheses. It may be easier to see in SQL view;
the SQL WHERE clause would resemble

WHERE ([WorkDate] = [Forms]![YourForm]![txtWorkdate] OR
[Forms]![YourForm]![txtWorkdate] IS NULL)
AND
([ShiftNum] = [Forms]![YourForm]![txtShift] OR
[Forms]![YourForm]![txtShift] IS NULL)

When you go back to the design grid, Access will probably make a
hard-to-understand hash of this (putting the form references into
calculated fields and going to four OR lines) but it should still
work.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top