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

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 wrote:
> 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#))

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

"John Spencer" wrote:

> 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 wrote:
> > 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#))
>

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

"John Spencer" wrote:

> 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 wrote:
> > 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#))
>

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
http://I.Am/DougSteele
(no e-mails, please!)


"blake7" <(E-Mail Removed)> wrote in message
news:69EE23FE-3565-445C-B2D0-(E-Mail Removed)...
> 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
>
> "John Spencer" wrote:
>
>> 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 wrote:
>> > 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#))
>>