Syntax question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following string worked fine when the date argument in the DateAdd
function was Date(). Please could someone tell me how I should represent the
variable datDate in this string? I've tried using '#' & datDate & '#'. Any
help much appreciated.

strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.DateOff, "
strSQL = strSQL & "tblEmployees.StartDate FROM tblEmployees "
strSQL = strSQL & "WHERE (((tblEmployees.DateOff)>DateAdd('m',-6,datDate)) "
strSQL = strSQL & "AND ((tblEmployees.StartDate)<DateAdd('m',-12,datDate)))
OR "
strSQL = strSQL & "(((tblEmployees.DateOff) Is Null)) Or "
strSQL = strSQL & "(((tblEmployees.DateOff) Is Null) AND "
strSQL = strSQL & "((tblEmployees.StartDate) Is Null));"
 
Try

strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.DateOff, "
strSQL = strSQL & "tblEmployees.StartDate FROM tblEmployees "
strSQL = strSQL & "WHERE (((tblEmployees.DateOff)>#" &
DateAdd('m',-6,datDate) & "#)"
strSQL = strSQL & " AND ((tblEmployees.StartDate)< #" &
DateAdd('m',-12,datDate) & "#)) OR "
strSQL = strSQL & "(((tblEmployees.DateOff) Is Null)) Or "
strSQL = strSQL & "(((tblEmployees.DateOff) Is Null) AND "
strSQL = strSQL & "((tblEmployees.StartDate) Is Null));"
 
Solves the problem! Thanks very much.

Ofer Cohen said:
Try

strSQL = "SELECT tblEmployees.EmployeeID, tblEmployees.DateOff, "
strSQL = strSQL & "tblEmployees.StartDate FROM tblEmployees "
strSQL = strSQL & "WHERE (((tblEmployees.DateOff)>#" &
DateAdd('m',-6,datDate) & "#)"
strSQL = strSQL & " AND ((tblEmployees.StartDate)< #" &
DateAdd('m',-12,datDate) & "#)) OR "
strSQL = strSQL & "(((tblEmployees.DateOff) Is Null)) Or "
strSQL = strSQL & "(((tblEmployees.DateOff) Is Null) AND "
strSQL = strSQL & "((tblEmployees.StartDate) Is Null));"
Click to expand...
 
I have a further problem with syntax further on in the subroutine. Looking
for records matching the following criterion:

strAppraisal = "AppraisalDate = 'Between #" & DateAdd("m", -12, datDate) &
"# And #" _
& datDate & "#'"
MyRst.FindFirst (strAppraisal)

eg strAppraisal = "Appraisal date = 'Between #18/03/2006# And #18/03/2007#'"

I have tried every variation I can think of to represent the criterion but,
each time, I either get a data type mismatch error or an operator missing
error.

Any help much appreciated.
 
There are a few things wrong.

First, you don't use both = and Between.

Second, there should be no quotes around the Between clause.

Third, regardless of what your Short Date format has been set to through
Regional Settings, you must use mm/dd/yyyy form in SQL statements (okay,
this isn't 100% true: you can use any unambiguous format, such as yyyy-mm-dd
or dd mmm yyyy. And, in fact, #18/03/2006# will work. However, #11/03/2006#
will ALWAYS be interpretted as 03 Nov, 2006. Because of this, you're best
off using the Format function to ensure your dates are correct.)

Finally (and this may not be a true error), it looks as though your field
name might have a space in it (is it AppraisalDate or Appraisal Date?) If it
does have a space, you need square brackets around the field name.

Try:

strAppraisal = "[Appraisal Date] Between #" & _
Format(DateAdd("m", -12, datDate), "\#yyyy\-mm\-dd\#") & _
" And " & Format(datDate, "\#yyyy\-mm\-dd\#")
 
Back
Top