Syntax error is making me crazy 1

[annsolomon]

Dim CurrentCycleDate As Date
CurrentCycleDate = DLookup("CycleDate", "tblCurrentCycleDate&quot


DoCmd.RunSQL "Delete [Tables]![tblHistory].[CycleDate] _
& WHERE tblHistory.CycleDate= #" & CurrentCycleDate _
& "# from tblHistory;"

I just can't seem to get a handle on properly writing SQL statements in a VBA module. Can someone help? I've searched the forums, played with single and double quotes, but just can't seem to get this right. Ann

 

[Rick39]

Is CurrentCycleDate a form field? If so, then

DoCmd.RunSQL "Delete [Tables]![tblHistory].[CycleDate] _
& WHERE tblHistory.CycleDate= #" & Me!CurrentCycleDate _
& "# from tblHistory;"

or Me!CurrentCycleDate.Value

 

[swampBoogie]

DoCmd.RunSQL "Delete from tblHistory " _
& WHERE CycleDate= #" & me.CurrentCycleDate & "#"

 

[annsolomon]

No, CurrentCycleDate is a variable that I use the hold the value from tblHistory.CycleDate and it's a date/time field. Ann

 

[mikevh]

Problem is you have line-continuation chars and
ampersands inside quotes. They belong outside.
Also don't get the "[Tables]![tblHistory].[CycleDate]"
part. You're going to delete any record that matches
your criteria, so why not just "* from tblHistory"?
Suggest you try it like this:

dim strSQL as String

strSQL = "DELETE * FROM tblHistory " _
"WHERE tblHistory.CycleDate= #" & CurrentCycleDate & _
"# from tblHistory;"

doCmd.RunSQL(strSQL)

 

[swampBoogie]

Missed a "

DoCmd.RunSQL "Delete from tblHistory " _
& "WHERE CycleDate= #" & CurrentCycleDate & "#"

 

[Tranman]

Ann,
You really can't delete fields. You have to delete rows. It may be that you are really wanting to set the date to zero like:

"Update tblhistory set tblHistory.CycleDate = 0 where tblHistory.CycleDate = #" & CurrentCycleDate "#;"

If you *are* really trying to delete rows, I agree with swampBoogie.

The best way I've found to write these queries is to write something close to what you want to do in the query window, then go into SQL view and copy the text and paste it into the VBA module window.

Tranman

 

[mikevh]

Whoops -- I left out an ampersand before the line
continuation character on the first line

strSQL = "DELETE * FROM tblHistory " & _
"WHERE tblHistory.CycleDate= #" & CurrentCycleDate & _
"# from tblHistory;"

 

[annsolomon]

I appreciate all of your help but I'll have to come back tomorrow. It's been a day from hell. Our Ohio division needs a redesign (2 weeks ago), the CFO decided to finally approve my Policy & Procedure about checking out laptops and put it out today saying I needed to deploy the redesigned database that meshes with the policy today, and the quarterly match that we give union employees bombed out in my application (this is the first time we tried it in the new database). The final straw was when I asked y'all for help today the darned site went down. I lost it and decided to go out for drinks. Okay, so I feel better (sorta) but I'll have to work all weekend to take care of this stuff 'cause I'm scheduled for A+ hardware training next week. Not my forte or desire but you gotta do what you gotta do.

Thanks for letting me blow off steam. I hope you'll be there for me tomorrow when I'm ready to get back to reality.

Ann

 

[annsolomon]

Tranman, I know I have to delete rows and I do copy the SQL view into the VBA window but I still have trouble figuring out where I need to put quotation marks in the VBA code.

Mikevh, thanks for point out my errant ampersands and line continuation characters. I ended up putting this code on one line but it still bottoms out:

DoCmd.RunSQL "Delete * from tblHistory WHERE CycleDate = #" & CurrentCycleDate & "# from tblHistory;"

I get: Error #3075 Syntax error (missing operator) in query expression 'CycleDate = #04/04/2003# from tblHistory'.

I just can't see what's wrong.

By way of background, every fourth quarter the employer contributes to our union employees' 401(k). The normal payroll cycle is processed, then clerks to data entry to add the quarterly employer match, then we reprocess the payroll cycle. I need to delete the current payroll cycle from tblHistory before reprocessing the cycle so I don't end up with duplicate records in tblHistory for our nonunion employees.

I really appreciate all your help and hope y'all can see what I'm doing wrong here. Ann

 

[r937]

>> DoCmd.RunSQL "Delete * from tblHistory
>> WHERE CycleDate = #" & CurrentCycleDate & "#
>> from tblHistory;"
>>
>> I get: Error #3075 Syntax error (missing operator)
>> in query expression 'CycleDate = #04/04/2003#
>> from tblHistory'.
>>
>> I just can't see what's wrong.

off the top of my head, it's the extra FROM clause that's causing the error

rudy

 

[annsolomon]

Thanks Rudy! The extra from clause was my problem. Now my code runs perfectly! I thank you and our Benefits Administrator thanks you.

Thanks to all of you for being there to help! I don't know how I ever survived before discovering this site. Ann

 

[janeatwork]

Hi r937,

Love it. A * 4 u

Jane