stephenlecomptejr
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:
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!
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"
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]
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!
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 & ")#
DateAdd(xdx, -7, #DateEnding#)
Change it by
#DateAdd(" & Chr(34) & "d" & Chr(34) & ", -7," & sDateEnding & ")#
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)
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"
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
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
ASKER
I think I'm getting warmer but still have questions:
Should I do it this way?
Or this way? Cause right now both give off that same 3075 error.
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
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
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
[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(mydateorexpressi on).
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(mydateorexpressi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Fabrice, that can be reduced to:
qd.Parameters("myFirstDate") = DateAdd("d", -1, Date) '// yesterday, no time
qd.Parameters(mySecondDate") = Date '// today, no time
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"
That said, use parameters as Fabric showed.
ASKER
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!
Thanks again!
Open in new window
That (and the closing parenthesis) needs to be removed.