Solved

Compile Error: Syntax Error In Date In Query Expression

Posted on 2009-04-15
5
676 Views
Last Modified: 2013-11-27
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?
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

Open in new window

0
Comment
Question by:siva_iaf
  • 3
  • 2
5 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24148175
If your table or field names have anything other than digits, letters, and underscores, you must wrap them in
brackets.. For example,

      SQL1 = "SELECT TICKET#, ACTION FROM Jetbase"

should be

      SQL1 = "SELECT [TICKET#], ACTION FROM Jetbase"

For future reference, using anything other than digits, letters, and underscores in table or field names is a
Very Bad Idea and should be avoided like the plague.

:)

0
 

Author Closing Comment

by:siva_iaf
ID: 31570449
Great - worked like magic - ill remember the exception now
0
 

Author Comment

by:siva_iaf
ID: 24148318
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?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24148353
CurrentDb.Execute "UPDATE compare Set [Ticket#] = " & rs1![Ticket#]

Or, if Ticket# is text...

CurrentDb.Execute "UPDATE compare Set [Ticket#] = '" & rs1![Ticket#] & "'"
0
 

Author Comment

by:siva_iaf
ID: 24148815
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now