Like not returning results on date format

[pennycarhire]

Hi, if someone can help it would be greatly appreciated.

I have a query which pulls up results based on the value of a text box. All works well except one field has a DOB (date) and if I search say for " /1976" I get no results, however search /76 and all the people with that year of birth show then show up. The date is stored in the dd/mm/yyyy format. Any ideas's.

I'm using the like [forms]![clients]![searchfield] in the criteria of the query to bring up results.

:banghead:

 

[MarkK]

Dates are stored as numbers not as formatted text, and LIKE does a string comparison, so the fact that it matches "/76" is actually pretty good, and I would consider just using that.

But if you want all the flexibility of using LIKE on date data formatted as strings, you need to write a query that converts the date to a string in the format you want . . .

SELECT Format([MyDate], "mm/dd/yyyy") As MyStringDate
FROM MyTable

. . . and then use LIKE to find string matches on the MyStringDate field. Then you can match "/1976"

Hope this helps,

 

[Galaxiom]

A string comparison would be relatively slow because the conversion would need to be appied to each record before the criteria was applied. It won't matter unless you have a large number of records.

Better performance with:

SELECT whatever
FROM MyTable
WHERE datefield BETWEEN DateSerial(1976,1,1) AND DateSerial(1976,12,31)

 

[pennycarhire]

Another thought then, is there a way of changing /nnnn
Where nnnn are numbers only to /nn in the text box before the query is opened?
i.e. if somebody puts in the searchbox 18/01/1976 it is automatically reformatted to 18/01/76

 

[MarkK]

But if somebody supplies a complete date there's no need to do a partial match using LIKE, and the whole problem of this thread is circumvented.

 

[Galaxiom]

Better to have StartDate and EndDate textboxes.

 

[pennycarhire]

Hi, I was thinking of a user entering /1976 or even 09/1976, then a code spotting a / followed by 4 numbers and re writing it to a /76 . Don't think that is possible though? I'll have a play with matching the string and see if I can get that to play. Thank you for your helps so far

 

[Galaxiom]

Hi, I was thinking of a user entering /1976 or even 09/1976, then a code spotting a / followed by 4 numbers and re writing it to a /76 . Don't think that is possible though? I'll have a play with matching the string and see if I can get that to play. Thank you for your helps so far

Anything is possible but you would be better off using start and end dates. The database engine will process it hundreds of times more efficiently than partially matching as a string.

 

[spikepl]

"*/*/*76" will work
"*/*/1976" will work
"*/1976" check it out

All of the above ought to be illegal in that it exploits Access' sometimes overdone proclivity to be "user-friendly" and converts each testet date into a string for comparison. If you have many many thousands of posts it'll be slow, otherwise it makes no difference. Efficiency or not.

 

[pennycarhire]

*/1976 doesn't work, the rest do though so Thank you!
Before I go with that -
Rather than tell every user if they want to search for a date or year to miss out the 19 bit say of 1976 is there a way in code for to delete the parts

So if 4 numbers appear after a / then delete the two numbers?

 

[spikepl]

Try also *76

"*" is a pretty common wildcard character.

Training people to use /76 when you mean /1976 is IMHO not a good idea. Provide a short "How to " text, at least in the help text in the Status Bar text or the ControlTip text