DoCmd.RunSql with a variable

[ngooo]

Hi Folks,

We have a database where documents are due based on Order dates. We prefer all starting points to be on a Friday.


First part, I check for the day it was entered, and store the new order date as FloatOrderDate. That part works no problem as i have check the output via message box.

I am having an issue with running the docmd.runsql, as I am trying to update a table based on that stored date- FloatOrderDate. How do i put that FloatOrderDate into the RunSQL?

Dim formatorderdate As StringDim FloatOrderDate As String


formatorderdate = Format(Forms!newvdlform!OrderDate, "dddd")
    If formatorderdate = "monday" Then
    FloatOrderDate = Forms!newvdlform!OrderDate + 4
    ElseIf formatorderdate = "tuesday" Then
    FloatOrderDate = Forms!newvdlform!OrderDate + 3
    ElseIf formatorderdate = "wednesday" Then
   FloatOrderDate = Forms!newvdlform!OrderDate + 2
    ElseIf formatorderdate = "thursday" Then
    FloatOrderDate = Forms!newvdlform!OrderDate + 1
    ElseIf formatorderdate = "friday" Then
    FloatOrderDate = Forms!newvdlform!OrderDate
End If
DoCmd.RunSQL "UPDATE WeeksTable SET WeeksTable.ConvertedDate = FloatOrderDate+7*[WEEKSTABLE].[weeksvalue] WHERE WeeksTable.datetype = 'aro' "
End Sub

[pbaldy]

You have to concatenate it into the string, using appropriate delimiters. For a date:

SET WeeksTable.ConvertedDate = #" & FloatOrderDate & "# +7*[WEEKSTABLE].[weeksvalue] WHERE

[andy49]

Why not use

Dim MySQL as string
MySQL = “update... and rest of sql startement”

Debug.print MySQL

And look for your sqlstatement in the immediate window.

I’d say your date variable needs delimiters. #......#

[ngooo]

You have to concatenate it into the string, using appropriate delimiters. For a date:

SET WeeksTable.ConvertedDate = #" & FloatOrderDate & "# +7*[WEEKSTABLE].[weeksvalue] WHERE

works perfectly. The quick help is greatly appreciated.

I am always running into trouble with delimiters. Is there a good site to read up on delimiters.
Thanks a bunch.

[pbaldy]

Jason has a faq here:

http://www.baldyweb.com/BuildSQL.htm

[ngooo]

Jason has a faq here:

http://www.baldyweb.com/BuildSQL.htm

Really useful. I was going to bother you folks with another one, as I was trying to apply the date delimiter above on another one but after reading the faq, discovered dates/numbers/strings are handled differently.

Thanks a bunch.

[pbaldy]

Glad it helped and welcome to the site by the way!