Syntax question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 16 Mar 2007 12:45 GMT

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));"

Reply to this Message

Ofer Cohen - 16 Mar 2007 13:59 GMT

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));"

Signature

Good Luck
BS"D

> 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
[quoted text clipped - 9 lines]
> strSQL = strSQL & "(((tblEmployees.DateOff) Is Null) AND "
> strSQL = strSQL & "((tblEmployees.StartDate) Is Null));"

Reply to this Message

Sandy - 18 Mar 2007 09:42 GMT

Solves the problem! Thanks very much.

> Try
>
[quoted text clipped - 21 lines]
> > strSQL = strSQL & "(((tblEmployees.DateOff) Is Null) AND "
> > strSQL = strSQL & "((tblEmployees.StartDate) Is Null));"

Reply to this Message

Sandy - 18 Mar 2007 17:59 GMT

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.

> Solves the problem! Thanks very much.
>
[quoted text clipped - 23 lines]
> > > strSQL = strSQL & "(((tblEmployees.DateOff) Is Null) AND "
> > > strSQL = strSQL & "((tblEmployees.StartDate) Is Null));"

Reply to this Message

Douglas J. Steele - 18 Mar 2007 19:02 GMT

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\#")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I have a further problem with syntax further on in the subroutine. Looking
> for records matching the following criterion:
[quoted text clipped - 46 lines]
>> > > strSQL = strSQL & "(((tblEmployees.DateOff) Is Null) AND "
>> > > strSQL = strSQL & "((tblEmployees.StartDate) Is Null));"

Reply to this Message