QBE Grid Query: Search for specific date (1 Viewer)

[LEXCERM]

Hi all,

Currently, I have a query which is set-up for searching on multiple fields on a form.

I am having an issue with finding a specific date within a month. For example, the query field is as follows:-

Field: myDate: Day([ID_Date])
Criteria: Like "*" & [Forms]![frm_DataMart]![cmbDate] & "*"

Problem being, if someone wants to search for records created on the 4th of each month, the data returned is for 4th, 14th, 24th and this is obviously caused by the "like".

Removing the "like" would resolve the problem, but if the user does not want to search on this field, then no records are returned.

I've tried adding an "Or" or "Is Null", but still no luck. All the other fields also have a "like" inclusion.

Thanks in advance for your suggestions.

 

[Grumm]

I think the problem are the "*".
In Access, that is a wildcard. So basically you are now searching for all the records that contains "4".
So the question you have to ask is what do you want when you search for "4" ?
if you want only they 4th day, use

Like "4"

.
If you want the find everything that starts with 4 use

Like "4*"

.
I hope this will help you in founding the right criteria you need.

 

[LEXCERM]

Hi Grumm and thanks for the feedback.

Yes, I am aware of the "like" capabilities, however, if the particular field is left blank, then no records are returned at all.

For example, I have tried this:

Like [Forms]![frm_DataMart]![cmbDate] Or Is Null

... but no records are returned.

The problem I am encountering is if the user does not want to query on this field.

Regards.

 

[Grumm]

Is it correct to asume that the user put his search value in [Forms]![frm_DataMart]![cmbDate] ?
If that is the case, why not test if he entered a value in it ?
Maybe something like this can do the trick :

If isEmpty([Forms]![frm_DataMart]![cmbDate]) Then
sLike = ""
Else sLike = "Like " & [Forms]![frm_DataMart]![cmbDate]
End If

This way will allow you to add multiple search terms in 1 query.

 

[LEXCERM]

Hi Grumm,

Your last reply got me thinking. Did a little research and came up with this solution which I place in the criteria part of the query:-

Like IIf(IsNull([Forms]![frm_DataMart]![cmbDate]),"*",[Forms]![frm_DataMart]![cmbDate])

This seems to work!

Thanks again for your time. Much appreciated.