I thought I was geting the hang of this SQL stuff

[Malcy]

Hi
Just when I thought I was getting somewhere I leave it for a few days, come back, and feel I am back at square one!
I have put the following bit of code behind a form and it won't work.

 strSQL = "UPDATE tblScriptSupply SET tblScriptSupply.strSupTxt = ""STOPPED"", tblScriptSupply.bSupStop = -1 " & _
             "WHERE (((tblTmpSessStop.lngStopCnt =" & Me.txtStopCnt & ") AND (tblScriptSupply.dtmSupDate > #" & Me.txtStopD * "#)));"
    cnn.Execute strSQL

I strongly suspect I have some of my quotation marks or ampersands in the wrong sequence. I have tried playing around them but it has deteriorated into random effort. Could someone please point me in the right direction?
txtStopD is a date and txtStopCnt is a long integer.
Thanks in anticipation!
Best wishes

Malcy

 

[FoFa]

strSQL = "UPDATE tblScriptSupply SET tblScriptSupply.strSupTxt = ""STOPPED"", tblScriptSupply.bSupStop = -1 " & _
"WHERE (((tblTmpSessStop.lngStopCnt =" & Me.txtStopCnt & ") AND (tblScriptSupply.dtmSupDate > #" & Me.txtStopD * "#)));"

Use single quotes for strings inside your double quotes. Example, ""STOPPED"" would become "'STOPPED'" (double quote for the VBA string, single quote STOPPED single quote double quote) so if you viewed this portion of SQL it would look something like SET tblScriptSupply.strSupTxt = 'STOPPED'
in the VBA variable

 

[Malcy]

Thanks for the quick response FoFa
I changed to "'STOPPED'" but it still jumps straight to the error line when I run it as step into. I tried just running it straight to see what the message box said and it just says there is a syntax error in the UPDATE statement, but I thought that looked OK to my untutored eye.
Is your eyesight clearer than mine?
Best wishes

 

[Kodo]

For actual strings
'STOPPED'

for variables that are strings

'"&MyString&"'

for variables that are numeric

"&MyNumber&"

 

[Malcy]

Thanks Kodo
Am I right in thinking that if the variable is a date you use
#"&MyDate&"#

I have now modified the code to

    strSQL = "UPDATE tblScriptSupply SET tblScriptSupply.strSupTxt =  'STOPPED', tblScriptSupply.bSupStop = -1 " & _
             "WHERE (((tblTmpSessStop.lngStopCnt = " & Me.txtStopCnt & ") AND (tblScriptSupply.dtmSupDate > #" & Me.txtStopD & "#)));"
    cnn.Execute strSQL

When I run it now I get no value given for one or more paramaters so I have got the UPDATE part right now, just need to sort the WHERE part!
I used your suggestion to cover the Me.txtStopCnt value which is a number but I may have the date wrong I suspect.
Help very much appreciated!

 

[Kodo]

do a debug.print on you variables to make sure that they are populated.

 

[Jon K]

Your SQL string contains two table names: tblScriptSupply and tblTmpSessStop.

If they are actually the same table, you can simplify the SQL string to:-

strSQL = "UPDATE tblScriptSupply SET strSupTxt =  'STOPPED', bSupStop = -1" & _
         " WHERE lngStopCnt = " & Me.txtStopCnt & _
         " AND dtmSupDate>#" & Me.txtStopD & "#;"

Note
The # sign works only on systems with US date format (m/d/yyyy).

On systems with other date formats, you may need to use the Format() function before you can properly compare dates using >, that is:-

strSQL = "UPDATE tblScriptSupply SET strSupTxt =  'STOPPED', bSupStop = -1" & _
         " WHERE lngStopCnt = " & Me.txtStopCnt & _
         " AND Format(dtmSupDate,'yyyymmdd')>'" & Format(CDate(Me.txtStopD), "yyyymmdd") & "';"

 

[Malcy]

Thanks guys
Jon K's suggestions got me there but had to use the date format options.
One day I will understand!!
Help very much appreciated
Best wishes

 

[ray147]

I am having the same problem and can't resolve it...am getting Data Type Mismatch Error...have the following:

Private Sub Form_Load()

date1 = Date - 7

DoCmd.RunSQL "UPDATE Bookings SET ArchivedFails=Yes WHERE (Status='Failed Booking' AND Archive <> Yes AND Format(PlanDespDate, 'DD/MM/YYYY') < '#" & Format(date1, "DD/MM/YYYY") & "#')"

End Sub


Thanks!

 

[Summerwind]

I got extremely p'd off with dates and their formatting a long time ago.

I now use the DateSerial function when coding for dates. Its a couple of extra lines but I don't get all of the hassle with them any more. Now I can concentrate on all of the other rubbish that I write that stops my code working.

Another thing I do to cut down on the confusion is to have a constant global variable that I call QUOTE that consists of nothing more than "" When using string parameters I use this as it then makes life far simpler when debugging.