Solved

Access VBA Syntax error Update query between 2 dates

Posted on 2015-02-07
4
431 Views
Last Modified: 2016-02-10
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
Comment
Question by:ExpressMan1
  • 2
4 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40596511
Change this line:
"Between [tblFuel].[StartDate] And [tblFuel].[EndDate];"

Ron
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40596585
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
 

Author Closing Comment

by:ExpressMan1
ID: 40596972
Perfect! Thank You Gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40596978
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Date Query 28 29
TT Add Columns 8 17
Error in SQL Query 36 33
execute a named excel macro from windows task scheduler 3 4
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

13 Experts available now in Live!

Get 1:1 Help Now