Syntax error on Date range query - Help!

[Guest]

Hi Guys

I keep getting a "syntax error" on this query:

intFuture = DCount("[ID]", "[Issues]", "[Due Date] BETWEEN Now() And
DateAdd("d",30,Date())"

I basically want to count all records bewteen today's date and 30 days into
the future.

Any help is very much appreciated!

Thanks
Regards
Greg

 

[Allen Browne]

You have quotes inside your quotes. Double-them up:

intFuture = DCount("[ID]", "[Issues]",
"[Due Date] BETWEEN Now() And DateAdd(""d"",30,Date())"

More info:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Guest]

I'd suggest that rather than including the DateAdd function call as part of
the literal string you concatenate its value into the string expression. A
few things you need to take account of here are:

1. The date value needs to be in US date format or an otherwise
internationally unambiguous format.

2. The Now() function returns the current date and time, so any rows today
with times before the current time will be excluded. This will include any
dates which have been entered without a time, as in Access there is no such
thing as a date value per se, but only date/time values. A date entered
without a time is in fact a date/time value at midnight at the start of the
day.

3. For the same reason a BETWEEN…AND operation will omit any rows on the
last day of the range with a non-zero time of day (which can creep in without
you realising if the Now() function is used as a default value.

Taking these factors into account I'd suggest:

Dim strCriteria As String

strCriteria = "[Due Date] >= #" & Format(Date(),"mm/dd/yyyy") & "# And " & _
"[Due Date] < #" & Format(DateAdd("d",31,Date()),"mm/dd/yyyy") & "#"

intFuture = DCount("[ID]", "[Issues]", strCriteria)

Ken Sheridan
Stafford, England

 

[Guest]

Hi Allen

I copied and pasted your answer but still get a syntax error.

Regards
Greg

You have quotes inside your quotes. Double-them up:

intFuture = DCount("[ID]", "[Issues]",
"[Due Date] BETWEEN Now() And DateAdd(""d"",30,Date())"

More info:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Guys

I keep getting a "syntax error" on this query:

intFuture = DCount("[ID]", "[Issues]", "[Due Date] BETWEEN Now() And
DateAdd("d",30,Date())"

I basically want to count all records bewteen today's date and 30 days
into
the future.

Any help is very much appreciated!

Thanks
Regards
Greg

 

[Allen Browne]

I didn't add your closing bracket, outside the string:
intFuture = DCount("[ID]", "[Issues]",
"[Due Date] BETWEEN Now() And DateAdd(""d"",30,Date())")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I copied and pasted your answer but still get a syntax error.

Regards
Greg

You have quotes inside your quotes. Double-them up:

intFuture = DCount("[ID]", "[Issues]",
"[Due Date] BETWEEN Now() And DateAdd(""d"",30,Date())"

More info:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Hi Guys

I keep getting a "syntax error" on this query:

intFuture = DCount("[ID]", "[Issues]", "[Due Date] BETWEEN Now() And
DateAdd("d",30,Date())"

I basically want to count all records bewteen today's date and 30 days
into the future.

 

[Guest]

Thanks Allen,

Implemented and tested ok

Thanks again

I didn't add your closing bracket, outside the string:
intFuture = DCount("[ID]", "[Issues]",
"[Due Date] BETWEEN Now() And DateAdd(""d"",30,Date())")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I copied and pasted your answer but still get a syntax error.

Regards
Greg

You have quotes inside your quotes. Double-them up:

intFuture = DCount("[ID]", "[Issues]",
"[Due Date] BETWEEN Now() And DateAdd(""d"",30,Date())"

More info:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Hi Guys

I keep getting a "syntax error" on this query:

intFuture = DCount("[ID]", "[Issues]", "[Due Date] BETWEEN Now() And
DateAdd("d",30,Date())"

I basically want to count all records bewteen today's date and 30 days
into the future.