Like "*" and Date Range in a query

[blues763]

Hi,

I have built a search form based on a bound to a query where I have Like criterias to search data by Departments, Groups, Names etc...

For e.g to search by Surname - I have --

Like "*" & [forms]![frm_Search]![Surname] & "*"

This works great and returns Surname data that looks like what is entered in the text box on forms.

Now I want to do similar thing in order to search data between 2 dates plus if I leave Date form text box blank it should return all data.

Its 2010 therefore using date picker in 2 text boxes (Start Date and End Date)

Please help

Regards.....

 

[pr2-eugin]

Hello blues763, are the two date being used on the same field using the BETWEEN operator? If so you could use the Nz function. Something like

SELECT theTable.someFields FROM theTable 
WHERE ((theTable.dateField BETWEEN Nz(Forms!yourFormName!startDate, #01/01/1800#) AND Nz(Forms!yourFormName!startDate, #01/01/2900#))
Or (theTable.someSurName Like "*" & Forms!yourFormName!startDate & "*"));

 

[blues763]

Hi mate

Thanks for a quick reply

My search Form is based on a query not table.

I could do with a like criteria with in the query or code as well as long it runs the query.

Yes 2 dates being used on the same field...

 

[blues763]

Sorry mate... Ignore my previous reply.
I just managed to use the nz function with between parametre on my query and it works....

Thanks for you help...
Regards...

 

[pr2-eugin]

No probs ! Good luck