• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

Access VBA Syntax error Update query between 2 dates

Getting a Syntax error. I am trying to update a field based on the date on a form.  Forms!frmRateTool!CurrentDate  or Me.CurrentDate (Not sure which one to use).  Not sure how to write the "Between line.

Dim db As DAO.Database
 Dim qdfFuel As DAO.QueryDef
 Dim strSQLFuel As String
 Dim strDb As String
 
 strDb = "C:\Users\Austin\Documents\Access Development\RateTool.accdb"
 
  strSQLFuel = "UPDATE (tblFedExServices INNER JOIN tblFuel " & _
    "ON tblFedExServices.FuelServiceID = tblFuel.FuelServiceID) INNER JOIN tblRate " & _
    "ON (tblFuel.OriginCountryID = tblRate.OriginCountryID) " & _
    "AND (tblFedExServices.ServiceID = tblRate.ServiceID) SET tblRate.FuelPercentage = tblFuel.[FuelPercentage] " & _
    "WHERE " & Chr(34) & Me.CurrentDate & Chr(34) & _
    "Between # & [tblFuel].[StartDate] & # And # & [tblFuel].[EndDate] & #;"
       
    Set db = OpenDatabase(strDb)
   
    Set qdfFuel = db.CreateQueryDef("", strSQLFuel)
    qdfFuel.Execute
0
ExpressMan1
Asked:
ExpressMan1
  • 2
1 Solution
 
IrogSintaCommented:
Change this line:
"Between [tblFuel].[StartDate] And [tblFuel].[EndDate];"

Ron
0
 
Gustav BrockCIOCommented:
This is how:

strSQLFuel = "UPDATE (tblFedExServices INNER JOIN tblFuel " & _
     "ON tblFedExServices.FuelServiceID = tblFuel.FuelServiceID) INNER JOIN tblRate " & _
     "ON (tblFuel.OriginCountryID = tblRate.OriginCountryID) " & _
     "AND (tblFedExServices.ServiceID = tblRate.ServiceID) " & _
     "SET tblRate.FuelPercentage = tblFuel.[FuelPercentage] " & _
     "WHERE #" & Format(Me!CurrentDate, "yyyy\/mm\/dd") & "# " & _
     "Between [tblFuel].[StartDate] And [tblFuel].[EndDate];"

/gustav
0
 
ExpressMan1Author Commented:
Perfect! Thank You Gustav
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now