vba deletes all records and ignores where condition?

[MikeL]

strSQL = "DELETE * From [tEST] WHERE [Pay_End_Dt] > 10/1/2020"

 

[MikeL]

Got it ..changed date format to #10/1/2020#

 

[Micron]

Interesting. Can only imagine that the date being passed is null or an empty string, which would explain it. However, I would have expected a syntax error instead.

 

[Joe4]

Interesting. Can only imagine that the date being passed is null or an empty string, which would explain it. However, I would have expected a syntax error instead.

I don't think so. I think it is doing a valid mathematical computation (just not the one you want).
10/1/2020 would read as 10 divided by 1, which would be 10, and then divided by 2020, which would be: 0.00495.
Since Excel and Access store dates as the number of days since 1/0/1900, that would be a time on that date.
So any date after 1/0/1900 wou;d evaluate as TRUE, and be returned.

 

[MikeL]

I don't think so. I think it is doing a valid mathematical computation (just not the one you want).
10/1/2020 would read as 10 divided by 1, which would be 10, and then divided by 2020, which would be: 0.00495.
Since Excel and Access store dates as the number of days since 1/0/1900, that would be a time on that date.
So any date after 1/0/1900 wou;d evaluate as TRUE, and be returned.

This seems to be working for me as I have been able to isolate records related to each quarter in a year using between ,and, > , < operators

 

[Joe4]

This seems to be working for me as I have been able to isolate records related to each quarter in a year using between ,and, > , < operators

Yes, it works properly when you use the date qualifiers of "#".
I was responding to Micron's reply, explaining the logic why it was selecting/deleting all the records when you don't use the date qualifiers (your original question).