Power By 专否社区

Access/VBA and SQL WHERE clause format issue

Go To StackoverFlow.com

0

I have read a huge pile of problems and solutions, and I just can't figure out what I'm doing wrong.

BounceDate = DateValue(txtBounceDate.Value)
bncSql = "DELETE _BounceMaster.* FROM _BounceMaster" & _
    " WHERE _BounceMaster.DateCheck >= #" & BounceDate & "#;"
DoCmd.RunSQL bncSql

_BounceMaster.DateCheck is in Date/Time format, which I think may be the issue, but I can't figure out what different format it should be in, or how to get there. As best as I can tell, BounceDate is correct - even using CDate didn't make a idfference. I have gotten both data mismatch errors, and currently, with code as above, I am getting syntax errors. What am I doing wrong?

2012-04-04 18:01
by graidan
Watch out for locale problems with dates. It is nearly always best to format dates to year, month, day to avoid ambiguity - Fionnuala 2012-04-05 00:07

0

It should be

DELETE FROM _BounceMaster

not

DELETE _BounceMaster.* FROM _BounceMaster

You should be using parameterized queries, as your code is subject to SQL injection attack.

2012-04-04 18:03
by RedFilter
According to my references, either way is perfectly valid - the table.* is optional before FROM. As to parameterized queries - I'm new enough at this that I don't know what that means - graidan 2012-04-04 18:11
Hard code a date to make sure your syntax is correct first, then proceed from there. SQL InjectionRedFilter 2012-04-04 18:15
D'oh! I hadn't thought about hard coding a date. It's not working still, but maybe I'll figure out how to fix it from there. I'm not worried about SQL injection as this database is internal only, and will be protected in other ways - graidan 2012-04-04 18:25
Even with a hardcoded date, and the table.* deleted, I still get syntax errors - graidan 2012-04-04 18:41
It was the _ before the table name that was causing the syntax error - graidan 2012-04-04 18:51
The BounceDate variable was set in the wrong place. I moved it to the procedure, instead of the module, and it worked fine. I don't know why that made a difference, but it worked, so I'm happy - graidan 2012-04-04 19:36

1

I suppose the problem comes from date formatting. The BounceDate variable is DateTime type, so when you concatenate with string type variable, VBA automatically casts DateTime variable into String type using date format from your regional settings.

As I correctly remember, SQL interpreter from MS Access feels comfortable only with mm/dd/yyyy date format, so please try this:

BounceDate = DateValue(txtBounceDate.Value)
bncSql = "DELETE _BounceMaster.* FROM _BounceMaster" & _
    " WHERE _BounceMaster.DateCheck >= #" & Format(BounceDate, "mm/dd/yyyy") & "#;"
DoCmd.RunSQL bncSql
2012-04-05 19:45
by Radek
It will also accept date literals in yyyy-mm-dd format - HansUp 2012-04-05 20:31
Ads