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?

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
graidan
  • 151
  • 2
  • 11
  • 2
    Watch out for locale problems with dates. It is nearly always best to format dates to year, month, day to avoid ambiguity. – Fionnuala Apr 05 '12 at 00:07

2 Answers2

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
Radek
  • 1,054
  • 7
  • 16
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.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • 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 Apr 04 '12 at 18:11
  • Hard code a date to make sure your syntax is correct first, then proceed from there. [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) – D'Arcy Rittich Apr 04 '12 at 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 Apr 04 '12 at 18:25
  • Even with a hardcoded date, and the table.* deleted, I still get syntax errors. – graidan Apr 04 '12 at 18:41
  • It was the _ before the table name that was causing the syntax error. – graidan Apr 04 '12 at 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 Apr 04 '12 at 19:36