IsNull problem in query

[excelHk]

I can’t design my query to work. I think it’s because MeetingLocation is an optional field in my table and the query doesn’t show the empty Meeting Location values. I want to design a query that needs to show the IsNull MeetingLocation values in between dates. How do I get IsNull values to show up in the dates that I want?



See below:


Table Fields:
MeetingLocation (optional), MeetingDate(mandatory), PersonID (mandatory)

Search Form:
-MeetingLocation
-From Date
-To Date
-PersonID


------


The user then enters some values in the search form...

Logic of my query:
- If meeting location and dates are blank then show all values in table.
- If only From Date and To Date have search form values then show all records in table between From Date and To Date.
- If only Meeting Location has search form value then show all records in table in that Meeting Location.
- If all the search form has values then show all records in that table in Meeting Location between From Date and To Date.

 

[ByteMyzer]

You might try a query like the following (substitute highlighted text with actual table/field/form names):

SELECT [b][i]MyTable[/i][/b].*
FROM [b][i]MyTable[/i][/b]
WHERE Nz([Forms]![[b][i]MyForm[/i][/b]]![[b][i]MeetingLocation[/i][/b]], [b][i]MyTable[/i][/b].[b][i]MeetingLocation[/i][/b]) = [b][i]MyTable[/i][/b].[b][i]MeetingLocation[/i][/b]
AND Iif(IsDate([Forms]![[b][i]MyForm[/i][/b]]![[b][i]From Date[/i][/b]])=True,
        [Forms]![[b][i]MyForm[/i][/b]]![[b][i]From Date[/i][/b]],
        [b][i]MyTable[/i][/b].[b][i]MeetingDate[/i][/b]) <= [b][i]MyTable[/i][/b].[b][i]MeetingDate[/i][/b]
AND Iif(IsDate([Forms]![[b][i]MyForm[/i][/b]]![[b][i]To Date[/i][/b]])=True,
        [Forms]![[b][i]MyForm[/i][/b]]![[b][i]To Date[/i][/b]],
        [b][i]MyTable[/i][/b].[b][i]MeetingDate[/i][/b]) >= [b][i]MyTable[/i][/b].[b][i]MeetingDate[/i][/b]
AND Nz([Forms]![[b][i]MyForm[/i][/b]]![[b][i]PersonID[/i][/b]], [b][i]MyTable[/i][/b].[b][i]PersonID[/i][/b]) = [b][i]MyTable[/i][/b].[b][i]PersonID[/i][/b];