Help with Update Query - VBA error

[jwrnana]

Objective: I am attempting to update records weekly (every Wednesday)
through Tuesday (yesterday) with my weekly essett number IF the payment
date falls between my weekly date range. This should calculate the start
date to enter essett date, and modify all appropriate records with correct
information.

An individual, Sam, replied and advised to create a command button on my
form calling it cmdEnterEssett and add the following VBA.

He gave me the following VBA to use, but I when building I am getting the
error message - "expected end of statement" and the last "d" is highlighted.


Private sub cmdEnterEssett_OnClick()

Dim StartDate As Date

StartDate = DateAdd("d",-7,Date())
'this gives last week Wednesday

DoCmd.RunSQL "UPDATE tblSalesData SET [Essett #]=[Enter Essett Number],
[Essett Date] = #"_
& Date() & "# WHERE [Payment Date] Between #" & StartDate & _
"# And DateAdd("d",6,StartDate) & "#;"

End Sub

I would reply to the same post; however, I cannot locate it. It was Tuesday
1-24-06.
Thank you in advance.

JR

 

[George Nicholson]

Try changing

"# And DateAdd("d",6,StartDate) & "#;"

to
"# And #" & DateAdd("d",6,StartDate) & "#;"

"Expected end of statement" in this case means that there was an odd number
of quotes used to build the SQL string rather than the expected pairs of
quotes. Since there were an odd number of quotes, the compiler can't find
the final "closing" quote, so there is no required "end of statement" as far
as it can tell.

Also: you might make sure that each line continuation character (i.e., _)
has a space before it. Not sure if your 1st one does. It's probably ok, but
worth a quick look.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Objective: I am attempting to update records weekly (every Wednesday)
through Tuesday (yesterday) with my weekly essett number IF the payment
date falls between my weekly date range. This should calculate the start
date to enter essett date, and modify all appropriate records with correct
information.

An individual, Sam, replied and advised to create a command button on my
form calling it cmdEnterEssett and add the following VBA.

He gave me the following VBA to use, but I when building I am getting the
error message - "expected end of statement" and the last "d" is
highlighted.


Private sub cmdEnterEssett_OnClick()

Dim StartDate As Date

StartDate = DateAdd("d",-7,Date())
'this gives last week Wednesday

DoCmd.RunSQL "UPDATE tblSalesData SET [Essett #]=[Enter Essett Number],
[Essett Date] = #"_
& Date() & "# WHERE [Payment Date] Between #" & StartDate & _
"# And DateAdd("d",6,StartDate) & "#;"

End Sub

I would reply to the same post; however, I cannot locate it. It was
Tuesday
1-24-06.
Thank you in advance.

JR

 

[jwrnana]

Thank you. That worked!

One more question -- How do I have the computer automatically assign the
next number to my query; i.e. Essett # is 899, next time it is 900, and so
on; without having to manually enter number. I am using the update query to
insert the Essett # to my payment form. I really do not want to rely on
keypunch people to insert the number. Should I create another form or
report or???

Thank you for your assistance.

JR

Try changing

"# And DateAdd("d",6,StartDate) & "#;"

to
"# And #" & DateAdd("d",6,StartDate) & "#;"

"Expected end of statement" in this case means that there was an odd number
of quotes used to build the SQL string rather than the expected pairs of
quotes. Since there were an odd number of quotes, the compiler can't find
the final "closing" quote, so there is no required "end of statement" as far
as it can tell.

Also: you might make sure that each line continuation character (i.e., _)
has a space before it. Not sure if your 1st one does. It's probably ok, but
worth a quick look.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Objective: I am attempting to update records weekly (every Wednesday)
through Tuesday (yesterday) with my weekly essett number IF the payment
date falls between my weekly date range. This should calculate the start
date to enter essett date, and modify all appropriate records with correct
information.

An individual, Sam, replied and advised to create a command button on my
form calling it cmdEnterEssett and add the following VBA.

He gave me the following VBA to use, but I when building I am getting the
error message - "expected end of statement" and the last "d" is
highlighted.


Private sub cmdEnterEssett_OnClick()

Dim StartDate As Date

StartDate = DateAdd("d",-7,Date())
'this gives last week Wednesday

DoCmd.RunSQL "UPDATE tblSalesData SET [Essett #]=[Enter Essett Number],
[Essett Date] = #"_
& Date() & "# WHERE [Payment Date] Between #" & StartDate & _
"# And DateAdd("d",6,StartDate) & "#;"

End Sub

I would reply to the same post; however, I cannot locate it. It was
Tuesday
1-24-06.
Thank you in advance.

JR