Solved Criteria Between two Dates Optional and not Mandatory

[Sarah.M]

Hi, Plz note, My VBA is blocked by IT for security reason (Cybersecurity).
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
------------------------------------------------
I want to make query creteria between two dates based on txtboxes in form, but the criteria is not working properly if I add date in [txtFrom]
I did this but it does not filter the from
Like "*" & [Forms]![MeetF]![txtFrom] & "*" And Like "*" & [Forms]![MeetF]![txtTo] & "*"

Between Like "*" & [Forms]![MeetF]![txtFrom] & "*" And Like "*" & [Forms]![MeetF]![txtTo] & "*"

Between "*" & [Forms]![MeetF]![txtFrom] & "*" And "*" & [Forms]![MeetF]![txtTo] & "*"

I did this it works, but entring data is mandatory
Between [Forms]![MeetF]![txtFrom] And [Forms]![MeetF]![txtTo]

I want all data to be visible, but search textbox by date is optional and not mandatory
Please help me

Sampel Attached

 

[Josef P.]

A date is not a string.

Maybe that's what you're looking for:

...
WHERE
    ( MeetDate >= [Forms]![MeetF]![txtFrom]  OR [Forms]![MeetF]![txtFrom] IS NULL )
    and
    ( MeetDate <= [Forms]![MeetF]![txtTo] OR [Forms]![MeetF]![txtTo] IS NULL )

or (if MeetDate is not null):

...
WHERE
    MeetDate >= Nz([Forms]![MeetF]![txtFrom], #1900-01-01#)
    and
    MeetDate <= Nz([Forms]![MeetF]![txtTo], #2999-12-31# )

This variant is better for index.

 

[XPS35]

Or ............
In the criteria of the function you can put:

Between Nz([Forms]![MeetF]![txtFrom],DMin("Meetdate","MeetT")) And Nz([Forms]![MeetF]![txtTo],DMax("Meetdate","MeetT"))

 

[Sarah.M]

Or ............
In the criteria of the function you can put:

Between Nz([Forms]![MeetF]![txtFrom],DMin("Meetdate","MeetT")) And Nz([Forms]![MeetF]![txtTo],DMax("Meetdate","MeetT"))

Smart!

My way to remove filter

But I encountered a problem when I remove the filter I get an error message

 

[XPS35]

Try Null instead of ""

 

[XPS35]

Or ............

Remove the button and make the field(s) empty and click "search".

 

[Sarah.M]

A date is not a string.

Maybe that's what you're looking for:

...
WHERE
    ( MeetDate >= [Forms]![MeetF]![txtFrom]  OR [Forms]![MeetF]![txtFrom] IS NULL )
    and
    ( MeetDate <= [Forms]![MeetF]![txtTo] OR [Forms]![MeetF]![txtTo] IS NULL )

or (if MeetDate is not null):

...
WHERE
    MeetDate >= Nz([Forms]![MeetF]![txtFrom], #1900-01-01#)
    and
    MeetDate <= Nz([Forms]![MeetF]![txtTo], #2999-12-31# )

This variant is better for index.

WoW! both ways are works

 

[Sarah.M]

Thank you guys!

 

[Sarah.M]

Hi, I have question realted to this threads

My question is What about if I have a query has [SecID] and the criteria refer to 1 combo box [Forms]![MeetF]![CombSecNum] how can I let the query to show all the Recored if the [Forms]![MeetF]![CombSecNum] is Null or ""?

I do the following way but, if I pick for example Sec1 it is works, but if i[Forms]![MeetF]![CombSecNum] is Null all recored gone , I want if the ComboBox is null or "" show all recored
Nz([Forms]![MeetF]![CombSecNum],"*")
Nz([Forms]![MeetF]![CombSecNum],Is Null)
Nz([Forms]![MeetF]![CombSecNum],IsNull(Forms]![MeetF]![CombSecNum]))

Help me Plz
Sample Attached

 

[Josef P.]

Same principle as shown in #2:

SELECT MeetT.*
FROM MeetT
WHERE MeetT.SecID=[Forms]![MeetF]![CombSecNum] Or  [Forms]![MeetF]![CombSecNum]  is null;

 

[Sarah.M]

Same principle as shown in #2:

SELECT MeetT.*
FROM MeetT
WHERE MeetT.SecID=[Forms]![MeetF]![CombSecNum] Or  [Forms]![MeetF]![CombSecNum]  is null;

Awesome!
Do you have another method with NZ easy to write?

 

[arnelgp]

you can also use:

Where IIf(Trim([Forms]![MeetF]![CombSecNum] & "")="",[SecID],[Forms]![MeetF]![CombSecNum])

 

[Sarah.M]

you can also use:

Where IIf(Trim([Forms]![MeetF]![CombSecNum] & "")="",[SecID],[Forms]![MeetF]![CombSecNum])

Awesome!
Trim will remove space
Thanks

 

[Sarah.M]

Thank you guys