Re: form provides and/or params to query
- From: John Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 26 Nov 2005 13:59:18 -0700
On Sat, 26 Nov 2005 12:35:01 -0800, "cpsaltis"
<cpsaltis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>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.
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]
.
- Prev by Date: Re: creating recordset with only one entry per user
- Next by Date: Re: Another Date Error in linked tables query
- Previous by thread: Re: creating recordset with only one entry per user
- Next by thread: Re: Select precise words in an Access Query
- Index(es):
Relevant Pages
|
