Adding correct charachter For Numeric

[DMT Dave]

Hi Guy's, what is the correct character needed for the highlighted please ?

I know
String Variables are "' & _


Date Variables are #" _
Numeric don't normally have closing characters are declaring

My debug window shows correct result but i have syntax wrong here ?

Dim sSQL As String, sDriver As StringDim iRoute As Integer
Dim dtDate As Date


iRoute = Me.RouteNo
dtDate = Me.DelDate
sDriver = Me.Driver


Select Case iRoute


Case Is = 0


Exit Sub


Case Else


sSQL = "UPDATE tblRoutePlan SET tblRoutePlan.RouteNo = " & iRoute & " & " _
            & "WHERE (((tblRoutePlan.DelDate)= #" & dtDate & "#) " _
            & "AND ((tblRoutePlan.Driver) = '" & sDriver & "'));"
    
    Debug.Print sSQL


DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
DoCmd.SetWarnings True


If Me.Dirty Then Me.Dirty = False


Forms!frmRouteEdit!frmEditRouteWeekDS.Requery




End Select

[Welshgasman]

And yet you do not show the output from the debug.print ?
I always put a space at the start of any continuation lines, so I do not have to scroll to the right all the time to see if one is there?

Try

sSQL = "UPDATE tblRoutePlan SET tblRoutePlan.RouteNo = " & iRoute  _
            & " WHERE (((tblRoutePlan.DelDate)= #" & dtDate & "#) " _
            & " AND ((tblRoutePlan.Driver) = '" & sDriver & "' & "));"

Edited in Notepad.
Plus your date needs to be in mm/dd/yyyy or yyyy-mm-dd format?

[DMT Dave]

Thanks WGM, the output had data in that i could have wiped out before posting but all criteria's looked correct so was easier to confirm data correct

So what i have picked up on is a different and potentially better method having spaces at the start of continuous lines

also numeric variables don't have closing parameters and see that you have removed in your edit

Thanks, will adjust and update

[DMT Dave]

Thanks WGM fixed

Dim sSQL As String, sDriver As StringDim iRoute As Integer
Dim dtDate As Date


iRoute = Me.RouteNo
dtDate = Format(Me.DelDate, "mm/dd/yyyy")
sDriver = Me.Driver


Select Case iRoute


Case Is = 0


Exit Sub


Case Else


sSQL = "UPDATE tblRoutePlan SET tblRoutePlan.RouteNo = " & iRoute _
            & " WHERE (((tblRoutePlan.DelDate)= #" & dtDate & "#)" _
            & " AND ((tblRoutePlan.Driver) = '" & sDriver & "'));"
    
    Debug.Print sSQL


DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
DoCmd.SetWarnings True


If Me.Dirty Then Me.Dirty = False


Forms!frmEditRoute!frmEditRouteWeekDS.Requery




End Select

[Welshgasman]

TBH I hardly ever use the continuation character, preferring to use strSQL = srtSQL & whatever

[Welshgasman]

You do NOT run the sql until you get the syntax correct, so comment it out until you reckon your sql is correct. No big deal for selects, but essential for update/delete queries.
I would always stat with a Select until I have the correct data, before changing to an update query.

[DMT Dave]

Hi WGM, when testing for results in immediate window, i always use exit sub after debug.print to halt procedure, that is when i had correct info in immediate window then removed exit sub to continue