VBA - SQL Help required (1 Viewer)

[rammudali]

Hi all,
I have a small prob which i am not able to fix.

while executing this code the following error message is give

Private Sub Command5_Click()
Dim asql As String
DoCmd.SetWarnings (off)
DoCmd.OpenQuery ("aqryEmpsch")
DoCmd.OpenQuery ("aqrynassign")
DoCmd.OpenQuery ("aqryleaveadd")
Dim week, wk As String
nu = 1
Do While nu < 55
week = "week" & nu
wk = "wk” & nu
MsgBox (week)
MsgBox (wk)

asql = "UPDATE TblEmpSch INNER JOIN tnassign ON TblEmpSch.EMPNO = tnassign.token SET TblEmpSch." & "'" & week & "'" & " = [TNASSIGN].[" & "'" & wk & "'" & "]"
asql = asql + " WHERE (((Tnassign." & "'" & wk & "'" & ")!= 'na' ));"
DoCmd.RunSQL asql
Nu = nu +1
Loop
End sub

error message

Run-time error 3075: syntax error (missing operator) in query expression '(((tnassign.'wk1') != 'na'))'.



i tried in may ways but in vain

when i use the following sql , data is getting updated.


asql = "UPDATE TblEmpSch INNER JOIN tnassign ON TblEmpSch.EMPNO = tnassign.token SET TblEmpSch.week1 = [tnassign].[wk1]where (((tnassign.wk1)<>'na'));"
DoCmd.RunSQL asql

thanks and regards in advance

 

[Uncle Gizmo]

What you can do is just before: DoCmd.RunSQL asql

Add a message box like this: MsgBox “ >>> “ & asql

When the message box pops up you can use the control C, “Ctrl C” (the keyboard copy command) on the message box and this will extract the SQL statement from the message box. You will now be able to compare the SQL exacted from the message box with your original SQL statement hopefully you will be able to spot the difference. You could also post it here for further comment.

 

[vbaInet]

As an alternative to the Msgbox suggested by Uncle Giz for this purpose, you can use Debug.Print if you know where the Immediate Window is.

asql = "UPDATE TblEmpSch INNER JOIN tnassign ON TblEmpSch.EMPNO = tnassign.token " & _
       "SET TblEmpSch." & week & " = [TNASSIGN].[" & wk & "] " & _
       "WHERE Tnassign." & wk & " [COLOR=Red]<>[/COLOR] 'na';"

Try the above. I noticed you used != which in other languages is the equivalent to Not Equal to. In VBA it's <>

By the way, having fields like week1, week2, ... etc indicates that the table is not normalized.

 

[philben]

Hi,

could you try this (without simple quote !):

asql = "UPDATE TblEmpSch INNER JOIN tnassign ON TblEmpSch.EMPNO = tnassign.token SET [COLOR=red]TblEmpSch." & week &[/COLOR] " = TNASSIGN[COLOR=red]." & wk[/COLOR]
asql = asql + " WHERE ((([COLOR=red]Tnassign." & wk & [/COLOR]")!= 'na' ));"

Philippe

 

[rammudali]

What you can do is just before: DoCmd.RunSQL asql

Add a message box like this: MsgBox “ >>> “ & asql

When the message box pops up you can use the control C, “Ctrl C” (the keyboard copy command) on the message box and this will extract the SQL statement from the message box. You will now be able to compare the SQL exacted from the message box with your original SQL statement hopefully you will be able to spot the difference. You could also post it here for further comment.

hi uncle gizmo

thanks


---------------------------
Microsoft Office Access
---------------------------
>>>UPDATE TblEmpSch INNER JOIN tnassign ON TblEmpSch.EMPNO = tnassign.token SET TblEmpSch.'week1' = [TNASSIGN].['wk1'] WHERE (((Tnassign.'wk1')!= 'na' ));


now i changed the code to

---------------------------
Microsoft Office Access
---------------------------
>>>UPDATE TblEmpSch INNER JOIN tnassign ON TblEmpSch.EMPNO = tnassign.token SET TblEmpSch.week1 = [TNASSIGN].[wk1] WHERE tnassign.wk1 <> 'na';

now i got that since these variables are only column heading Single quote is not required.

thanks, thanks, thanks

 

[rammudali]

As an alternative to the Msgbox suggested by Uncle Giz for this purpose, you can use Debug.Print if you know where the Immediate Window is.

asql = "UPDATE TblEmpSch INNER JOIN tnassign ON TblEmpSch.EMPNO = tnassign.token " & _
       "SET TblEmpSch." & week & " = [TNASSIGN].[" & wk & "] " & _
       "WHERE Tnassign." & wk & " [COLOR=red]<>[/COLOR] 'na';"

Try the above. I noticed you used != which in other languages is the equivalent to Not Equal to. In VBA it's <>

By the way, having fields like week1, week2, ... etc indicates that the table is not normalized.

hi vbainet

thank you very much

 

[rammudali]

hi philben

thank you very much