[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Compile Error: Syntax Error In Date In Query Expression

Posted on 2009-04-15
5
Medium Priority
?
704 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 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 93

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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