Adding a date parameter to an SQL statement

[blake7]

Hi all, I have the following SELECT statement as below but want to add a date
parameter, where in the statement do I add it ?? can anyone help - I keep
trying different places but it returns errors - Thank You

SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults], [Main
Audit Data].[Boiler Type], [Main Audit Data].Status
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main Audit
Data].Status="warranty")
GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status;

Include this date parameter where ?????
HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And
#31/01/2008#))

 

[John Spencer]

Include that as part of the where clause.

SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults]
, [Main Audit Data].[Boiler Type]
, [Main Audit Data].Status
FROM [Main Audit Data]
WHERE [Main Audit Data].[Boiler Type] Like "*isar*"
AND [Main Audit Data].Status="warranty"
AND [Main Audit Data].[Audit Date] Between #1/1/2008# And
#31/01/2008#
GROUP BY [Main Audit Data].[Boiler Type]
, [Main Audit Data].Status;

By the way, I would enter the dates in yyyy-mm-dd format or in the US format
of mm/dd/yyyy.

See the following for an explanation of why.
International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[blake7]

Brilliant - Thanks John, I knew there was a way round it, but just could not
see it.

Include that as part of the where clause.

SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults]
, [Main Audit Data].[Boiler Type]
, [Main Audit Data].Status
FROM [Main Audit Data]
WHERE [Main Audit Data].[Boiler Type] Like "*isar*"
AND [Main Audit Data].Status="warranty"
AND [Main Audit Data].[Audit Date] Between #1/1/2008# And
#31/01/2008#
GROUP BY [Main Audit Data].[Boiler Type]
, [Main Audit Data].Status;

By the way, I would enter the dates in yyyy-mm-dd format or in the US format
of mm/dd/yyyy.

See the following for an explanation of why.
International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi all, I have the following SELECT statement as below but want to add a date
parameter, where in the statement do I add it ?? can anyone help - I keep
trying different places but it returns errors - Thank You

SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults], [Main
Audit Data].[Boiler Type], [Main Audit Data].Status
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main Audit
Data].Status="warranty")
GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status;

Include this date parameter where ?????
HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And
#31/01/2008#))

 

[blake7]

Just one more question John, when you say enter the dates in yyyy-mm-dd
format or the US style, do you mean in the design view or SQL view of the
query ?
Thanks

Include that as part of the where clause.

SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults]
, [Main Audit Data].[Boiler Type]
, [Main Audit Data].Status
FROM [Main Audit Data]
WHERE [Main Audit Data].[Boiler Type] Like "*isar*"
AND [Main Audit Data].Status="warranty"
AND [Main Audit Data].[Audit Date] Between #1/1/2008# And
#31/01/2008#
GROUP BY [Main Audit Data].[Boiler Type]
, [Main Audit Data].Status;

By the way, I would enter the dates in yyyy-mm-dd format or in the US format
of mm/dd/yyyy.

See the following for an explanation of why.
International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi all, I have the following SELECT statement as below but want to add a date
parameter, where in the statement do I add it ?? can anyone help - I keep
trying different places but it returns errors - Thank You

SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults], [Main
Audit Data].[Boiler Type], [Main Audit Data].Status
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main Audit
Data].Status="warranty")
GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status;

Include this date parameter where ?????
HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And
#31/01/2008#))

 

[Douglas J. Steele]

Definitely use yyyy-mm-dd in the SQL view. If you enter a date in the grid
in the design view, I believe Access will interpret it correctly, but it
won't in the SQL view.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Just one more question John, when you say enter the dates in yyyy-mm-dd
format or the US style, do you mean in the design view or SQL view of the
query ?
Thanks

Include that as part of the where clause.

SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults]
, [Main Audit Data].[Boiler Type]
, [Main Audit Data].Status
FROM [Main Audit Data]
WHERE [Main Audit Data].[Boiler Type] Like "*isar*"
AND [Main Audit Data].Status="warranty"
AND [Main Audit Data].[Audit Date] Between #1/1/2008# And
#31/01/2008#
GROUP BY [Main Audit Data].[Boiler Type]
, [Main Audit Data].Status;

By the way, I would enter the dates in yyyy-mm-dd format or in the US
format
of mm/dd/yyyy.

See the following for an explanation of why.
International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi all, I have the following SELECT statement as below but want to add
a date
parameter, where in the statement do I add it ?? can anyone help - I
keep
trying different places but it returns errors - Thank You

SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults],
[Main
Audit Data].[Boiler Type], [Main Audit Data].Status
FROM [Main Audit Data]
WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main
Audit
Data].Status="warranty")
GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status;

Include this date parameter where ?????
HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And
#31/01/2008#))