Query Search Form - Searching Between Two Dates

[ryanmce92]

Hi Folks,

I have a search audit trail form, all the fields seem to be working perfectly to search for except for dates.

I have a query which is run when a button is pressed and uses the following code in each of the criteria fields in the query:
For Username:

 Like "*" & [Forms]![Search Audit Trail]![Username] & "*"

To find the source table:

 Like "*" & [Forms]![Search Audit Trail]![SourceTable] & "*"

And so on

I would like to know what criteria I would have to enter in order for me to be able to search between two fields whilst still allowing the fields to be blank if they weren't entered. I have tried:

 Between "*" & [Forms]![Search Audit Trail]![DateFrom] and [Forms]![Search Audit Trail]![DateTo] & "*"

But this doesn't seem to be working, any help would be greatly appreciated.

[CJ_London]

* are only used for text values

dates need to be formatted US style - mm/dd/yyyy or confirmed as a date, using say cdate

myfield <=cdate(nz([Forms]![Search Audit Trail]![DateTo],"31/12/3000")) and myfield>=cdate(nz([Forms]![Search Audit Trail]![DateFrom],"01/01/1900"))

[ryanmce92]

* are only used for text values

dates need to be formatted US style - mm/dd/yyyy or confirmed as a date, using say cdate

myfield <=cdate(nz([Forms]![Search Audit Trail]![DateTo],"31/12/3000")) and myfield>=cdate(nz([Forms]![Search Audit Trail]![DateFrom],"01/01/1900"))

Is there any way to have a wildcard symbol in there or does the 31/12/3000 and 01/01/1900 count as wildcards?.

[CJ_London]

no wildcard for dates (or numbers), although you can convert to text e.g.

like "*" & format(mydate,"yyyymmdd") & "*"


Be aware that dates are actually numbers, formatted to look like dates. If you format as above, then you convert the number to a text. If you use the format property on a form or report control, the number stays a number, but is displayed as text.

The same is true for numbers with thousand separators, set decimal places, currency symbols etc.

Also, although many use it, it is indicative of bad database design. Putting a * at the beginning of your search string means that access (or any other db for that matter - some don't even allow it) cannot use indexing so has to use sequential searching (check each record one at a time) which for small amounts of data is not an issue but for larger amounts will have an incredible effect on performance - without the initial * and the field indexed - it probably takes a second to query 100,000 records, with an initial * this could be a couple of minutes. So you have with a couple of keystrokes in your code, made your db very slow and inefficient.

People tend to use the initial * so they can find 'Andrew Smith' by searching for 'Smith'. This is indicative of a bad db design since if this is a regular requirement, the field should be split into two fields firstname and lastname.
Done properly, most users will know the beginning of what they are looking for, but I don't include * at the beginning or end of a search string in code - I train users to type '*smith' or 'Andrew*' if they need to. This gives them much more flexibility and no coding for yourself because it means they can look for 'A*Smith' for example (and 'A*Smith' will use indexing).

I would modify your criteria to

WHERE (isnull([Forms]![Search Audit Trail]![Username]) Or myfield Like [Forms]![Search Audit Trail]![Username])

WHERE myfield like nz([Forms]![Search Audit Trail]![Username],myfield)

[IrogSinta]

I train users to type '*smith' or 'Andrew*'

I believe Ajax meant 'a*smith' or 'Andrew*'

[CJ_London]

no I didn't, I was demonstrating the use of the * wildchar

*smith returns all values which end in smith
Andrew* returns all values that being with Andrew
A*Smith returns all values that start with A and end with smith
*smith* returns all values which has smith in it somewhere
Andrew*smith returns all Andrew smiths with or without a middle name or initial

Of these, the two marked in red will be the slowest queries by an order of magnitude compared with the others for any large dataset (and assuming the field is indexed) - because they cannot use the index.

My point isn't that you shouldn't use an initial *, just not built in as guaranteed way to make your db inefficient.

[IrogSinta]

I thought you meant you train users to type a*smith to take advantage of indexing.
:-)

[CJ_London]

hmm, no - I train them in how to use wildchars - part of which includes the effect on performance