In the example below I am trying just to match for today and I know that
there is a matching value in the db.
Generally, I find that the single biggest problem in debugging is my unwarranted assumptions.
I use PHP to get the current date via
$today= date("d/m/y",time());
In Access SQL, the required format for dates in a WHERE clause is the US format, which would be m/d/y. BTW, your expression gives the same result as date("d/m/y"). The default setting for the second argument is the current system time.
If I use
.....WHERE s.Date=#$today#
That's the correct format. Access SQL encloses dates in pound signs, not apostrophes.
Then I get no error but nothing matches either!!
Which is probably the correct response, since your date criteria is mis-formatted.
In the Access DB the date is set as a Date/text field in a short date format
It's either a Date/Time field or a Text field. It's not both. If I recall correctly, formatting only affects the display in a Date column. All dates in Date columns are stored the same way, regardless of display formatting. If it's a Text column, then it's stored as it's formatted.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak