How to format date values in query criteria to search for short date

[MilaK]

How to correctly format date values in query criteria? Thanks

>=Format (#[Forms]![frm_search_comments]![txtStartDate]#, "Short Date") And <= Format (#[Forms]![frm_search_comments]![txtEndDate]#, "Short Date")

 

[isladogs]

Wrap dates in # delimiters with #" at the start and "# at the end.

Either use vbShortDate or spell it out e.g. "mm/dd/yyyy"
"Short Date is wrong

So the first part is:

>=#" & Format([Forms]![frm_search_comments]![txtStartDate], "mm/dd/yyyy")  & "#

Personally I would replace >= and <= with Between ... And ... though others will disagree with that

 

[MilaK]

When I type this as criteria for one field I get a message that the expression might be typed incorrectly or too complex.

>="#" & Format([Forms]![frm_search_comments]![txtStartDate],"mm/dd/yyyy") & "#" And <="#" & Format([Forms]![frm_search_comments]![txtEndDate],"mm/dd/yyyy") & "#"

it didn't let me type #". I had to include both quotes.

thanks

 

[Minty]

if the criteria are stored in the query you don't need the delimiters or format at all - access will deal automatically with it.

If you are trying to deal with the fact you have datetime fields to compare, but are inputting a date only then add a day to the txtEndDate

>=[Forms]![frm_search_comments]![txtStartDate]And < DateAdd("d",1 [Forms]![frm_search_comments]![txtEndDate])

 

[MilaK]

Sorry, but Access tells me the syntax is invalid.

 

[Minty]

Well it shouldn't, I've just copied and pasted this from a report selector I use

>=[Forms]![ManagerReportSelector]![txDateFrom] And <DateAdd("d",1,[Forms]![ManagerReportSelector]![txtDateTo])

 

[MilaK]

I get "expression you've entered contains invalid syntax. You may have entered operand with operation."

>=[Forms]![frm_search_comments]![txtStartDate]And < DateAdd("d",1 [Forms]![frm_search_comments]![txtEndDate])

 

[Minty]

There should be a space between the ] And , but other than that I can't see anything else wrong.

 

[MilaK]

That didn't make a difference it still throws the same error if I try to save the query. I have Access 2016. thanks

 

[Minty]

Can you post up a sample db? Doesn't need a lot in it, just some sample data and the form and the query.

 

[isladogs]

Going back to my first reply, I thought you were asking about VBA code rather a query. Should have read your post more carefully. Sorry about that

 

[isladogs]

Also, I see you've gone from <= DateAdd ... to <DateAdd ... for some reason.
You also lost a comma ... ((in RED below)

Have you tried using :

 Between [Forms]![frm_search_comments]![txtStartDate] And DateAdd("d", 1[COLOR="Red"],[/COLOR] [Forms]![frm_search_comments]![txtEndDate])

 

[MilaK]

Please see the attached db.

I was hoping to use "Load Form" sub which used the query and is much faster than filtering the form but passing the parameters between dates is not working.

Thanks

 

[almahmood]

Use this in your VBA code.

If IsDate(Me.txtStartDate) = True Then
Criteria = Criteria & "([CreatedDate] >= #" & [Forms]![frm_search_comments]![txtStartDate] & "#) AND "
End If


If IsDate(Me.txtEndDate) = True Then
Criteria = Criteria & "(CDate(Int(NZ([CreatedDate],0))) <= #" & [Forms]![frm_search_comments]![txtEndDate] & "#) AND "
End If

This should work.

 

[Minty]

The filtering in your test db is working for me. I tend to put default dates in for the current month on search forms like this.

You can't really load the form pre-filtered, as you wouldn't have dates selected, unless you where going to show the current month as suggested. You could then reset the sub form record source.

You have caused some confusion by saying that this is a query criteria, but are then using the form filter which requires a different syntax.

I've used your test database and this

>=[Forms]![frm_search_comments]![txtStartDate] And <DateAdd("d",1,[Forms]![frm_search_comments]![txtEndDate])

works without an error, as criteria for the Created date.

I noticed you have declared all the parameters, which is an unusual step, but not an unwelcome one as you could used a parameterised SQl query def to set up the sub form, if your dataset gets large.

 

[MilaK]

What will be faster once I have lot's of data in tables, filtering the form or using the query? Thanks

 

[Minty]

Normally restricting the dataset to just what you need will always be quicker on the form. I believe (though happy to be proved wrong) with a filter all the records are still in the underlying dataset.