Date range criteria

M

Michael Allen

I have a query which lists invoices and invoice total for invoices dated
after a given date. The SQL statement is as follows:-

SELECT DISTINCTROW [Invoice ID].Jobno, [Invoice ID].[Invoice ID], [Invoice
ID].Date, Sum(IIf([Quantity] Is Null,[Unit Price],Int([Quantity]*[Unit
Price]*100+0.5)/100)) AS InvoiceTotal
FROM [Invoice ID] INNER JOIN [Invoice detail] ON [Invoice ID].[Invoice ID] =
[Invoice detail].[Invoice ID]
GROUP BY [Invoice ID].Jobno, [Invoice ID].[Invoice ID], [Invoice ID].Date
HAVING ((([Invoice ID].Date)>#11/1/2003#));

I now want to create a new query based on the above, but this time listing
invoices and totals within the current month. I have replaced the date
criteria with : Between FirstOfMonth(Date()) And LastOfMonth(Date()).
FirstOfMonth and LastOfMonth are functions (taken from
http://www.mvps.org/access/ (the Access Web). However this does not work -
no results given.

SQL is:

SELECT DISTINCTROW [Invoice ID].Jobno, [Invoice ID].[Invoice ID], [Invoice
ID].Date, Sum(IIf([Quantity] Is Null,[Unit Price],Int([Quantity]*[Unit
Price]*100+0.5)/100)) AS InvoiceTotal
FROM [Invoice ID] INNER JOIN [Invoice detail] ON [Invoice ID].[Invoice ID] =
[Invoice detail].[Invoice ID]
GROUP BY [Invoice ID].Jobno, [Invoice ID].[Invoice ID], [Invoice ID].Date
HAVING ((([Invoice ID].Date) Between FirstOfMonth(Date()) And
LastOfMonth(Date())));

Any ideas on how I can display current month's invoices?

Thanks,

Mike
 
Top