Searching between dates from form (1 Viewer)

[cocowomble]

Hi all.

Apologies if there is a really simple solution.

I have a query and need it to filter between to dates that are entered into a form.

the dates are long date fields (dd/dd/dd hh/mm/ss)

I've used the following search criteria

Between #" & [forms]![frmSearch]![txtTimefrom] & "# And #" & [forms]![frmSearch]![txtTimeTo] & "#

but clearly im missing something because when I go and save the query it comes up with the following error "the expression you entered has an invalid date value".

Been trying loads of different expressions but nothing seem to work

PLEASE PLEASE HELP.

thanks in advance

 

[plog]

Do it using mathematical symbols:

>= [forms]![frmSearch]![txtTimefrom] And <=[forms]![frmSearch]![txtTimeTo]

 

[Minty]

Provided the controls on your form are set to date format then

Between [Forms]![frmReporting]![txtFromDate] And [Forms]![frmReporting]![txtToDate]
Definitely works as I've pasted it from one of my forms.

If you have DateTime fields then this is the better solution if the time portion of the date field is set;

>=[Forms]![frmReporting]![txtFromDate] And <(DateAdd("d",1,([Forms]![frmReporting]![txtToDate])))

edit: (Between is more efficient apparently but I've always used the second approach)