Can't execute delete query in VBA - need help!!

[edsac64]

I've been stuck on one small item for awhile and it's driviing me nuts. In short, I want to run a delete query in VBA to delete records containing a billing month that's greater that a precalculated current month. The source data from another system has the dates in text format, shown as (YYYY/MM).

Here's the statement:

mySQL = "DELETE * FROM [tblBillingData]" _
& "WHERE (((tblBillingData.[Billing Month]) > PreCalcCurrentMonth));"

DoCmd.RunSQL mySQL

All variables are declared properly and I can see the PreCalcCurrentMonth when I step through the code, but when it runs the SQL statement, I'm presented with an Enter Parameter input box for the PreCalcCurrentMonth variable.

Any suggestions??

Thanks,

Ed

 

[MarkK]

Your line continuation doesn't leave a space between the tablename and the word WHERE, so SQL can't parse your statement.

A tip when your SQL fails is print the value of the variable mySQL in the immediate pane like ...

? mySQL

... and you'll see ...

DELETE * FROM [tblBillingData]WHERE (((tblBillingData.[Billing Month]) > PreCalcCurrentMonth));

... and your mistakes are easier to spot. If that still doesn't help, copy that SQL in the immediate pane and paste it into the SQL of a new query in the design grid and run it, and you'll get error messages directly from the SQL parser.

 

[edsac64]

Hi,

Thanks, I fixed that issue and it's still giving me the pop up box Enter Parameter Value for the PreCalcCurrentMonth variable.

Here's the SQL now, copied straight from the immediate window:

DELETE * FROM [tblUnbilled_PreCutoff_CleanData_Temp] WHERE (((tblUnbilled_PreCutoff_CleanData_Temp.[Billing Month]) > crntCBSBillingCycle));

I've attached a quick pic of the parameter box that's popping up

Would it be better to run through the records individually?

Thanks again!

 

[MarkK]

When SQL can't find a field in a referenced table, it considers it to be a parameter. If you don't supply a value for the parameter, it prompts you.

So the problem is that the field PreCalcCurrentMonth cannot be found in the table tblBillingData. Check your spelling. Or is that a parameter?

 

[edsac64]

Thanks lagbolt. I ran across another suggestion.

First, you need to refer to your variable as a variable outside the string, otherwise, Access VBA thinks its a string.
Second, you need to surround date paramaters with the # sign.

This is what ended up working:

mySQL = "DELETE * FROM [tblBillingData]" _
& "WHERE (((tblBillingData.[Billing Month]) > #" & PreCalcCurrentMonth & "#));"

Thanks again for your help!

 

[spikepl]

The source data from another system has the dates in text format, shown as (YYYY/MM).

Now be careful with all this. Just because it happened to run correctly with some date or other, it doesn't mean you are done. What does PreCalcCurrentMonth contain exactly?

If PreCalcCurrrentMonth also is a string, where date is formatted as YYYY/MM then you are good to go, but then no ## are necessary.

 

[edsac64]

Thanks for the heads up on this.

About an hour later: I'm so glad you pointed out that I wouldn't need the # marks. When I left them in and the code ran, it deleted everything from the table. Fortunately, I always create backup tables.

I removed the # marks and it ran as expected. I am, however, going to keep a close eye on this.

thanks again!