Quick SQL Problem

[fabby1]

Can anybody sus out what is wrong with this

Set qdfTemp = dbsCurrent.CreateQueryDef("")




With qdfTemp
.SQL = "SELECT T.ITIME, T.LINE, T.ISLAND, T.TAGVALUE-Nz(V.TAGVALUE,0) AS Total " _
     & "FROM TMP_DAILY_TABLE AS T INNER JOIN TMP_DAILY_TABLE AS V ON (T.DISPLAYNAME=V.DISPLAYNAME) AND (Format(T.IDATE,""yyyymmddhhnn"")=Format((V.IDATE+#12/30/1899 0:15:0#),""yyyymmddhhnn"")) " _
     & "WHERE T.DISPLAYNAME like " & G_Desk & ""
End With

Set rstTemp = qdfTemp.OpenRecordset

With rstTemp
   .MoveLast
   .MoveFirst
End With

I get the error

Run time error '3075'

Syntax error (missing operator) in query expression 'T.DISPLAYNAME like Dest 41'.

Thanks a bunmch

P

 

[PHV]

Replace this:
& "WHERE T.DISPLAYNAME like " & G_Desk & ""
By this:
& "WHERE T.DISPLAYNAME like '" & G_Desk & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fabby1]

Mr Fix it strikes again

Thanks

P

 

[Lammers]

Something for long term might be to change it into parameterized query.

Instead of:
& "WHERE T.DISPLAYNAME like '" & G_Desk & "'"

try:
& "WHERE T.DISPLAYNAME like [sG_Desk]"

To set the parameter [sG_Desk]
you might then do:
Dim param1 As DAO.Parameter
Set param1 = qdfTemp.Parameters!sG_Desk
param1 = G_Desk

This way you won't have to remember to check for quotes inside G_Desk

 

[Lammers]

Opps you would need something like this at the beginning of the query string:

sql = "PARAMETERS sG_Desk Text ( 255 );