Link to home
Start Free TrialLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

Between Add Syntax needed for SQL statement in Microsoft Access VBA program.

Need help with a difficult sql statement combined in a string where I'm using Between DateAdd with giving the correct syntax:
Here is the SQL string in code:

      Dim sDateEnding As Date
      sDateEnding = GetProperty("TimesheetWeekEnding", "")
      lType = Nz(rsType.Fields(0), 0)
      sSQL = "SELECT TimeSheetData.TimeTypeID, TimeSheetData.EmployeeID, TimeSheetData.JobTicket, TimeSheetData.AdminID, TimeSheetData.TimeTypeID, TimeSheetData.WorkDate, TimeSheetData.WorkHours, TimeSheetData.WorkDescription, TimeSheetData.Overtime, TimeSheetData.Standard, TimeSheetData.AMHours, TimeSheetData.PMHours, TimeSheetData.Posted"
      sSQL = sSQL & " FROM TimeSheetData"
      sSQL = sSQL & " WHERE TimeSheetData.EmployeeID = " & GetProperty("EmployeeID", "")
      sSQL = sSQL & " AND ([TimeSheetData].[WorkDate] Between (DateAdd(" & Chr(34) & "d" & Chr(34) & ", -7, #" & sDateEnding & "#) And #" & sDateEnding & "#))"
      'sSQL = sSQL & " TimeSheetData.TimeTypeID = " & lType
      sSQL = sSQL & " ORDER BY TimeSheetData.WorkDate"

Open in new window


The error I get is:

Error No: 3075 in GetTimeSheetData procedure;  Description:  Between operator without And in query expression 'TimeSheetData.EmployeeID = 24 AND [TimeSheetData].[WorkDate] Between (DateAdd("d", -7, #11/23/2018#) And #11/23/2018#)'

Just need the correct syntax - Not able to use the query SQL in this case cause it's using a custom parameter.
Thank you for your help in advance!
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

You have a start parenthesis right after the Between keyword.
Between (

Open in new window

That (and the closing parenthesis) needs to be removed.
Your problem is that in the dateadd funciton you are using the # symbol

DateAdd(xdx, -7, #DateEnding#)

Change it by

#DateAdd(" & Chr(34) & "d" & Chr(34) & ", -7," & sDateEnding & ")#
Avatar of stephenlecomptejr

ASKER

I'm still having issues with the adjustments.

Here is the latest version I'm having a 3075 error now with:
(Please note the image of the error message)

      sSQL = "SELECT TimeSheetData.TimeTypeID, TimeSheetData.EmployeeID, TimeSheetData.JobTicket, TimeSheetData.AdminID, TimeSheetData.TimeTypeID, TimeSheetData.WorkDate, TimeSheetData.WorkHours, TimeSheetData.WorkDescription, TimeSheetData.Overtime, TimeSheetData.Standard, TimeSheetData.AMHours, TimeSheetData.PMHours, TimeSheetData.Posted"
      sSQL = sSQL & " FROM TimeSheetData"
      sSQL = sSQL & " WHERE TimeSheetData.EmployeeID = " & GetProperty("EmployeeID", "")
      sSQL = sSQL & " AND ([TimeSheetData].[WorkDate] Between DateAdd(" & Chr(34) & "d" & Chr(34) & ", -7, GetProperty(" & Chr(34) & "TimesheetWeekEnding" & Chr(34) & ", " & Chr(34) & Chr(34) & ") And GetProperty(" & Chr(34) & "TimesheetWeekEnding" & Chr(34) & ", " & Chr(34) & Chr(34) & ")"
      sSQL = sSQL & " AND TimeSheetData.TimeTypeID = " & lType
      sSQL = sSQL & " ORDER BY TimeSheetData.WorkDate"

Open in new window

still-an-error-3075.PNG
Now you are missing the # between the dates string

As I state change to

#DateAdd(" & Chr(34) & "d" & Chr(34) & ", -7," & sDateEnding & ")#

You have to use the # between the date strings but not inside the dateadd funciton
I think I'm getting warmer but still have questions:

Should I do it this way?

SELECT TimeSheetData.TimeTypeID, TimeSheetData.EmployeeID, TimeSheetData.JobTicket, TimeSheetData.AdminID, TimeSheetData.TimeTypeID, TimeSheetData.WorkDate, TimeSheetData.WorkHours, TimeSheetData.WorkDescription, TimeSheetData.Overtime, TimeSheetData.Standard, TimeSheetData.AMHours, TimeSheetData.PMHours, TimeSheetData.Posted FROM TimeSheetData WHERE TimeSheetData.EmployeeID = 24 AND ([TimeSheetData].[WorkDate] Between #DateAdd("d", -7, GetProperty("TimesheetWeekEnding", "")# And #GetProperty("TimesheetWeekEnding", "")# AND TimeSheetData.TimeTypeID = 2 ORDER BY TimeSheetData.WorkDate

Open in new window


Or this way?  Cause right now both give off that same 3075 error.

SELECT TimeSheetData.TimeTypeID, TimeSheetData.EmployeeID, TimeSheetData.JobTicket, TimeSheetData.AdminID, TimeSheetData.TimeTypeID, TimeSheetData.WorkDate, TimeSheetData.WorkHours, TimeSheetData.WorkDescription, TimeSheetData.Overtime, TimeSheetData.Standard, TimeSheetData.AMHours, TimeSheetData.PMHours, TimeSheetData.Posted FROM TimeSheetData WHERE TimeSheetData.EmployeeID = 24 AND ([TimeSheetData].[WorkDate] Between #DateAdd("d", -7, GetProperty("TimesheetWeekEnding", "") And GetProperty("TimesheetWeekEnding", "")# AND TimeSheetData.TimeTypeID = 2 ORDER BY TimeSheetData.WorkDate

Open in new window

Personally, I never use BETWEEN with respect to dates.  I always use:

[DateField] >= SomeValue AND [DateField] < SomeOtherValue

I've seen too many queries with dates bite people in the a$$ when they use any other syntax.

Dale
Always build complex strings like this into a variable.  That allows you to stop the code and print the string to the immediate window.  If you don't see the syntax error, you can copy the statement and using QBE paste the string into SQL view of a new querydef.  Sometimes you get better error messages.

Also, keep in mind that if your dates contain a time component, you may get strange results.  If time is not relevant to the range check, then it is best to remove time entirely by using the DateValue(mydateorexpression).
ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Fabrice, that can be reduced to:

qd.Parameters("myFirstDate") = DateAdd("d", -1, Date)    '// yesterday, no time
qd.Parameters(mySecondDate") = Date '// today, no time

Open in new window

As to the original question, the code should read, avoiding all the in-line SQL stuff and applying proper formatting of the date expressions:

Dim sDateStarting   As String
Dim sDateEnding     As String
      
sDateStarting = Format(DateAdd("d", -7, getProperty("TimesheetWeekEnding", "")), "yyyy\/mm\/dd")
sDateEnding = Format(getProperty("TimesheetWeekEnding", ""), "yyyy\/mm\/dd")

lType = Nz(rsType.Fields(0).Value, 0)

sSQL = "SELECT TimeSheetData.TimeTypeID, TimeSheetData.EmployeeID, TimeSheetData.JobTicket, TimeSheetData.AdminID, TimeSheetData.TimeTypeID, TimeSheetData.WorkDate, TimeSheetData.WorkHours, TimeSheetData.WorkDescription, TimeSheetData.Overtime, TimeSheetData.Standard, TimeSheetData.AMHours, TimeSheetData.PMHours, TimeSheetData.Posted"
sSQL = sSQL & " FROM TimeSheetData"
sSQL = sSQL & " WHERE TimeSheetData.EmployeeID = " & getProperty("EmployeeID", "") & ""
sSQL = sSQL & " AND ([TimeSheetData].[WorkDate] Between #" & sDateStarting & "# And #" & sDateEnding & "#"
sSQL = sSQL & " AND TimeSheetData.TimeTypeID = " & lType & ""
sSQL = sSQL & " ORDER BY TimeSheetData.WorkDate"

Open in new window

That said, use parameters as Fabric showed.
Man, I sure appreciate all the fine answers.  Gustav and Dale you've helped me a lot in the past - like for the past 14 years of it.
Thanks again!