Results 1 to 10 of 10
  1. #1
    mjm0027 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    5

    Forming SQL statement in VBA to get all records between two dates.


    Good evening,
    Wasn't sure if I needed to post this in the "programming" forum or this one, so I'm sorry if this isn't the correct place.

    I'm very new to Access, have been practicing and reading / watching tutorials for only 3 or so weeks. I'm trying to create a database to help create settlements for some contractors my company frequently uses.
    I've spent most of today trying to get a form to filter a subform based on which driver is selected in a combo box and which two dates are selected in two other text boxes..

    I got it to filter by the driver simple enough, but I'm having a hard time adding the filtering between the two selected dates..

    Currently, when I click the button that should trigger the SQL statement to be created, I get
    Code:
    Run-time error '3075': 
    Syntax error (missing operator) in query expression 'tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# and <= #" & EndDate & "# "'
    Here is the complete block of code:

    Code:
    Private Sub cmd_update_Click()
        Dim SQL As String
        Dim StartDate As String
        Dim EndDate As String
        
        StartDate = Me.txt_begdate.Value
        EndDate = Me.txt_enddate.Value
        
        
        SQL = "SELECT tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles, tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.[Position:], tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, Sum([LineHaul]*([Percentage]*0.01)) AS DriverPay, tbl1_Loads.InvoiceNumber, tbl1_Loads.[PositionID:] " _
            & "FROM tbl2Positions RIGHT JOIN (tbl1_Drivers RIGHT JOIN (tbl1_Customers RIGHT JOIN tbl1_Loads " _
            & "ON tbl1_Customers.CustomerID = tbl1_Loads.CustomerID) ON tbl1_Drivers.DriverID = tbl1_Loads.DriverID) ON tbl2Positions.PositionID = tbl1_Loads.[PositionID:] " _
            & "WHERE tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# and <= #" & EndDate & "# "
    
        Me.sfrm_settlement.Form.RecordSource = SQL
        Me.sfrm_settlement.Form.Requery
    
    End Sub
    I'm sure I'm missing something very simple or doing something completely wrong, but if someone could point out something that may be of help I would be so grateful. If you need any more information just let me know.

    Thanks so much for your time.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have to have something to compare the [DropOffDate] to.

    won't work:
    Code:
    " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# AND <= #" & EndDate & "# "'
    Will work:
    Code:
    " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# and AND tbl1_Loads.[DropOffDate] <= #" & EndDate & "#"


    Code:
    tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# and AND tbl1_Loads.[DropOffDate] <= #" & EndDate & "#"


    Also might work using the BETWEEN keyword:
    Code:
    tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] BETWEEN #" & StartDate & "# and #" & EndDate & "#"


    And welcome to the forum

  3. #3
    mjm0027 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    5
    Thank you! That makes perfect sense. I was worried when I first tried it until I realized the problem was you have "AND and" in your code block haha.
    Also, another weird thing that happened was it gave me another error: "Your query does not include the specified expression 'LoadID' as part of an aggregate function". I looked around and ended up having to add a GROUP BY statement including every single row in the query I use to get everything together... Not sure why but it works and at this point I'm not complaining.

    So here's the working piece of code:
    Code:
    Private Sub cmd_update_Click()
        Dim SQL As String
        Dim StartDate As String
        Dim EndDate As String
        
        StartDate = Me.txt_begdate.Value
        EndDate = Me.txt_enddate.Value
        
        
        SQL = "SELECT tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles, tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.[Position:], tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, Sum([LineHaul]*([Percentage]*0.01)) AS DriverPay, tbl1_Loads.InvoiceNumber, tbl1_Loads.[PositionID:] " _
            & "FROM tbl2Positions RIGHT JOIN (tbl1_Drivers RIGHT JOIN (tbl1_Customers RIGHT JOIN tbl1_Loads " _
            & "ON tbl1_Customers.CustomerID = tbl1_Loads.CustomerID) ON tbl1_Drivers.DriverID = tbl1_Loads.DriverID) ON tbl2Positions.PositionID = tbl1_Loads.[PositionID:] " _
            & "WHERE tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# and tbl1_Loads.[DropOffDate] <= #" & EndDate & "#" _
            & "GROUP BY tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles,tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.[Position:], tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, tbl1_Loads.InvoiceNumber, tbl1_Loads.[PositionID:] " _
            & "ORDER BY tbl1_Loads.LoadID"
    
        Me.sfrm_settlement.Form.RecordSource = SQL
        Me.sfrm_settlement.Form.Requery
    
    End Sub

    Thank you so much for your quick reply, I'm sure I'll be back sometime soon when I can't figure out the next thing.
    Cheers

  4. #4
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181
    Quote Originally Posted by mjm0027 View Post
    Thank you! That makes perfect sense. I was worried when I first tried it until I realized the problem was you have "AND and" in your code block haha.
    Also, another weird thing that happened was it gave me another error: "Your query does not include the specified expression 'LoadID' as part of an aggregate function". I looked around and ended up having to add a GROUP BY statement including every single row in the query I use to get everything together... Not sure why but it works and at this point I'm not complaining.

    So here's the working piece of code:
    Code:
    Private Sub cmd_update_Click()
        Dim SQL As String
        Dim StartDate As String
        Dim EndDate As String
        
        StartDate = Me.txt_begdate.Value
        EndDate = Me.txt_enddate.Value
        
        
        SQL = "SELECT tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles, tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.[Position:], tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, Sum([LineHaul]*([Percentage]*0.01)) AS DriverPay, tbl1_Loads.InvoiceNumber, tbl1_Loads.[PositionID:] " _
            & "FROM tbl2Positions RIGHT JOIN (tbl1_Drivers RIGHT JOIN (tbl1_Customers RIGHT JOIN tbl1_Loads " _
            & "ON tbl1_Customers.CustomerID = tbl1_Loads.CustomerID) ON tbl1_Drivers.DriverID = tbl1_Loads.DriverID) ON tbl2Positions.PositionID = tbl1_Loads.[PositionID:] " _
            & "WHERE tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# and tbl1_Loads.[DropOffDate] <= #" & EndDate & "#" _
            & "GROUP BY tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles,tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.[Position:], tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, tbl1_Loads.InvoiceNumber, tbl1_Loads.[PositionID:] " _
            & "ORDER BY tbl1_Loads.LoadID"
    
        Me.sfrm_settlement.Form.RecordSource = SQL
        Me.sfrm_settlement.Form.Requery
    
    End Sub

    Thank you so much for your quick reply, I'm sure I'll be back sometime soon when I can't figure out the next thing.
    Cheers
    In your GROUP BY statement, you have this twice:

    tbl1_Loads.TotalPay, tbl1_Loads.TotalPay

    I would also suggest using BETWEEN rather than your current WHERE statement. I'm not sure if it makes any difference, but it's sure easier to read:

    Code:
     "WHERE tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] BETWEEN #" & StartDate & "# AND #" & EndDate & "#" _ 

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    until I realized the problem was you have "AND and" in your code block haha.
    Sorry about that... those pesky cut and paste errors.....


    Once you had the calculated column (SUM(...)), the query became a Totals Query and that requires the GROUP BY clause.


    Field names (actually all object names) should only have letters and numbers.
    NO spaces, punctuation or special characters (exception is the underscore).

    You have a field name "Position:". I realize you found out that "Position" is a reserved word, so you added the colon. But that can/will cause other problems.
    IMO, better names would be "PositionDesc" or "PositionTitle" .... something along those lines.


    Good luck with you project.....

  6. #6
    mjm0027 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    5
    Ah, I think the problem was when I was creating the table for some reason I randomly put a : after positions and didn't realize it. I've since gone back and corrected them and it has caused such a huge headache. Apparently I'm missing somewhere in my SQL strings that I need to change because I've managed to break everything when I changed the table header names. Now when I open my settlement form I get the "Enter Parameter Value" prompt and it's asking for "tbl2Positions.Position" which doesn't exist. I renamed both columns in that table to "PositionID" and "PositionDesc" as suggested. I figured I simply just didn't change it correctly in my SQL string but I can't find anywhere that asks for "tbl2Positions.Position"...

    Code:
    Private Sub cmd_update_Click()
        Dim SQL As String
        Dim StartDate As String
        Dim EndDate As String
        Dim DriverName As String
        
        StartDate = Me.txt_begdate.Value
        EndDate = Me.txt_enddate.Value
        DriverName = Me.cbo_driver.Value
        
        
        SQL = "SELECT tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles, tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.PositionDesc, tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, Sum([LineHaul]*([Percentage]*0.01)) AS DriverPay, tbl1_Loads.InvoiceNumber, tbl1_Loads.PositionID " _
            & "FROM tbl2Positions RIGHT JOIN (tbl1_Drivers RIGHT JOIN (tbl1_Customers RIGHT JOIN tbl1_Loads " _
            & "ON tbl1_Customers.CustomerID = tbl1_Loads.CustomerID) ON tbl1_Drivers.DriverID = tbl1_Loads.DriverID) ON tbl2Positions.PositionID = tbl1_Loads.PositionID) " _
            & "WHERE tbl1_Drivers.DriverID = " & Me.cbo_driver & " AND tbl1_Loads.[DropOffDate] >= #" & StartDate & "# and tbl1_Loads.[DropOffDate] <= #" & EndDate & "#" _
            & "GROUP BY tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName, tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime, tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles,tbl1_Loads.CustomerID, tbl1_Loads.DriverID, tbl1_Drivers.Percentage, tbl2Positions.PositionDesc, tbl1_Loads.DropOffDate, tbl1_Loads.DropOffTime, tbl1_Loads.InvoiceNumber, tbl1_Loads.PositionID " _
            & "ORDER BY tbl1_Loads.LoadID"
    
        Me.sfrm_settlement.Form.RecordSource = SQL
        Me.sfrm_settlement.Form.Requery
        Me.Form.Requery
           
    
    End Sub
    If I hit cancel I get this:

    Code:
    Run-time error '2467':
    
    The expression you entered refers to an object that is closed or doesn't exist.
    If I type '1' or just press OK I get one more "Enter Parameter Value" popup asking for 'tbl1Loads.PositionID'. I can press ok then I get this error:
    Code:
    Run-time error '3131':
    Syntax error in FROM clause.
    And once again I don't know where the error is stemming from. I changed every instance I can find that has the column name.


    Either way thanks for pointing that out, obviously I'd rather have to figure all this out now than later.

    I'll keep fiddling with things, if I figure out when I messed up I'll post an update.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are missing a space BEFORE the word "GROUP".
    You have an extra ")" before the word "WHERE"


    This is how I like to write the SQL statements:
    Code:
        SQL = "SELECT tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName,"
        SQL = SQL & " tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime,"
        SQL = SQL & " tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles,"
        SQL = SQL & " tbl1_Loads.CustomerID, tbl1_Drivers.Percentage, tbl2Positions.PositionDesc,"
        SQL = SQL & " tbl1_Loads.DropOffTime, Sum([LineHaul]*([Percentage]*0.01)) AS DriverPay,"
        SQL = SQL & " tbl1_Loads.InvoiceNumber, tbl1_Loads.PositionID"
    
        SQL = SQL & " FROM tbl2Positions RIGHT JOIN (tbl1_Drivers RIGHT JOIN (tbl1_Customers RIGHT JOIN tbl1_Loads"
        SQL = SQL & " ON tbl1_Customers.CustomerID = tbl1_Loads.CustomerID) ON tbl1_Drivers.DriverID = tbl1_Loads.DriverID)"
        SQL = SQL & " ON tbl2Positions.PositionID = tbl1_Loads.[PositionID]"
    
        SQL = SQL & " WHERE tbl1_Drivers.DriverID = " & Me.cbo_driver
        SQL = SQL & " AND tbl1_Loads.DropOffDate Between " & StartDate & " And #" & EndDate & "#"
    
        SQL = SQL & " GROUP BY tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName,"
        SQL = SQL & " tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime,"
        SQL = SQL & " tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles,"
        SQL = SQL & " tbl1_Loads.CustomerID, tbl1_Drivers.Percentage, tbl2Positions.PositionDesc,"
        SQL = SQL & " tbl1_Loads.DropOffTime, tbl1_Loads.InvoiceNumber, tbl1_Loads.PositionID"
    
        SQL = SQL & " ORDER BY tbl1_Loads.LoadID;"
        '    Debug.Print SQL
    (I added lines between the clauses for readability - normally I don't have the blank lines)

    Note that there are NO spaces at the end of the lines. The space is at the beginning - easier to spot a missing space.

  8. #8
    mjm0027 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    5
    Quote Originally Posted by ssanfu View Post
    You are missing a space BEFORE the word "GROUP".
    You have an extra ")" before the word "WHERE"


    This is how I like to write the SQL statements:
    Code:
        SQL = "SELECT tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName,"
        SQL = SQL & " tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime,"
        SQL = SQL & " tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles,"
        SQL = SQL & " tbl1_Loads.CustomerID, tbl1_Drivers.Percentage, tbl2Positions.PositionDesc,"
        SQL = SQL & " tbl1_Loads.DropOffTime, Sum([LineHaul]*([Percentage]*0.01)) AS DriverPay,"
        SQL = SQL & " tbl1_Loads.InvoiceNumber, tbl1_Loads.PositionID"
    
        SQL = SQL & " FROM tbl2Positions RIGHT JOIN (tbl1_Drivers RIGHT JOIN (tbl1_Customers RIGHT JOIN tbl1_Loads"
        SQL = SQL & " ON tbl1_Customers.CustomerID = tbl1_Loads.CustomerID) ON tbl1_Drivers.DriverID = tbl1_Loads.DriverID)"
        SQL = SQL & " ON tbl2Positions.PositionID = tbl1_Loads.[PositionID]"
    
        SQL = SQL & " WHERE tbl1_Drivers.DriverID = " & Me.cbo_driver
        SQL = SQL & " AND tbl1_Loads.DropOffDate Between " & StartDate & " And #" & EndDate & "#"
    
        SQL = SQL & " GROUP BY tbl1_Loads.LoadID, tbl1_Customers.CustomerName, tbl1_Drivers.FullName,"
        SQL = SQL & " tbl1_Loads.PickupLocation, tbl1_Loads.DropLocation, tbl1_Loads.PickupTime,"
        SQL = SQL & " tbl1_Loads.PickUpDate, tbl1_Loads.TotalPay, tbl1_Loads.LineHaul, tbl1_Loads.LoadedMiles,"
        SQL = SQL & " tbl1_Loads.CustomerID, tbl1_Drivers.Percentage, tbl2Positions.PositionDesc,"
        SQL = SQL & " tbl1_Loads.DropOffTime, tbl1_Loads.InvoiceNumber, tbl1_Loads.PositionID"
    
        SQL = SQL & " ORDER BY tbl1_Loads.LoadID;"
        '    Debug.Print SQL
    (I added lines between the clauses for readability - normally I don't have the blank lines)

    Note that there are NO spaces at the end of the lines. The space is at the beginning - easier to spot a missing space.


    Ah, thank you! Obviously I'm still very inexperienced with this.. I had to add back in tbl1_Loads.DropOffDate to the SQL statement, but once I did that it worked perfectly! I'm still getting the prompts when I load the page asking for parameter values for tbls2Positions.Position then tbl1Loads.PositionID, but I can leave the values blank and click OK and the 'Update' commands works perfectly once the form loads. I use the same code in the form load event and the update button's on click event, so I'm not sure what is causing that... At least it works again!

    Once again, thank you so much for your help so far.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My SQL is based on the tables I created where I used the fields from your SQL, plus the changes to the field names.


    You could delete the record, do a "compact and repair", zip the dB and post it...... shouldn't have to have the error prompts..

  10. #10
    mjm0027 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    5
    I just deleted the record I had been working on and it doesn't have the error anymore! Thanks again for all your help. I'll be sure to post another thread sometime later when I break something else! :P

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Select Records based on dates or no dates or both
    By usatraveler in forum Queries
    Replies: 3
    Last Post: 08-12-2016, 07:52 AM
  2. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  3. Forming Access Query!
    By Shubham Aggarwal in forum Access
    Replies: 2
    Last Post: 02-23-2015, 01:58 AM
  4. Need help forming an If-Then-Else Statement
    By tlkng1 in forum Programming
    Replies: 1
    Last Post: 11-05-2014, 10:54 AM
  5. Replies: 4
    Last Post: 04-23-2012, 05:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums