siva_iaf
asked on
Compile Error: Syntax Error In Date In Query Expression
Hi - Iam writing a VBA code that will acheive the following
Compare the value of an attribute in 2 tables in a database, and update another table with a value depending on if the value of the attributes is the same in the 2 tables or not
However, on compiling, iam getting the error
Syntax Error In Date In Query Expression 'TICKET#'
Can someone please advise what is wrong in my code?
Compare the value of an attribute in 2 tables in a database, and update another table with a value depending on if the value of the attributes is the same in the 2 tables or not
However, on compiling, iam getting the error
Syntax Error In Date In Query Expression 'TICKET#'
Can someone please advise what is wrong in my code?
Public Function F2bCheck()
Dim db As DAO.Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim SQL1 As String
Dim SQL2 As String
Dim Action1 As String
Dim Action2 As String
Set db = CurrentDb
SQL1 = "SELECT TICKET#, ACTION FROM Jetbase"
Set rs1 = db.OpenRecordset(SQL1)
Do Until rs1.EOF
SQL2 = "select Ticket#, Buy_Sell from obs_for_comparsion where ticket# = " & rs1!Ticket# & ""
Set rs2 = db.OpenRecordset(SQL2)
Action1 = rs1!Action
Action2 = rs2!Buy_Sell
CurrentDb.Execute "update compare Set [Ticket#] = rs1!Ticket#"
If Action1 = Action2 Then
CurrentDb.Execute "Update Compare Set [Action_Check] = 0 where [Ticket#] = " & rs2!Match_No & ""
ElseIf Action1 <> Action2 Then
CurrentDb.Execute "Update Compare Set [Action_Check] = 1 where [Ticket#] = " & rs2!Match_No & ""
End If
rs1.MoveNext
Loop
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi - Another one on the same code
Iam using the UPDATE statement to write the result of my comparison to another table called "Compare"
But the table is empty
I thought my code will write the ticket# from rs1 the corresponding result in the Action column
but looks like it is not - i get an error as below
Too few parameters - Expected 1
This is on the line
CurrentDb.Execute "UPDATE compare Set [Ticket#] = rs1![Ticket#]"
Any ideas please?
Iam using the UPDATE statement to write the result of my comparison to another table called "Compare"
But the table is empty
I thought my code will write the ticket# from rs1 the corresponding result in the Action column
but looks like it is not - i get an error as below
Too few parameters - Expected 1
This is on the line
CurrentDb.Execute "UPDATE compare Set [Ticket#] = rs1![Ticket#]"
Any ideas please?
CurrentDb.Execute "UPDATE compare Set [Ticket#] = " & rs1![Ticket#]
Or, if Ticket# is text...
CurrentDb.Execute "UPDATE compare Set [Ticket#] = '" & rs1![Ticket#] & "'"
Or, if Ticket# is text...
CurrentDb.Execute "UPDATE compare Set [Ticket#] = '" & rs1![Ticket#] & "'"
ASKER
That works perfectly.... and i ran into some more trouble
problem with the following statement
CurrentDb.Execute "Update Compare Set [Action_Check] = 1 where [Ticket#] = " & rs1![TICKET#] & ""
Same error - too few parameeters - sorry to be a pest
Please advise
problem with the following statement
CurrentDb.Execute "Update Compare Set [Action_Check] = 1 where [Ticket#] = " & rs1![TICKET#] & ""
Same error - too few parameeters - sorry to be a pest
Please advise
ASKER