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
ExpressMan1Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
IrogSintaCommented:
Change this line:
"Between [tblFuel].[StartDate] And [tblFuel].[EndDate];"

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

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.