Date Range. Please help

[Sammy82]

Hi Guys,

I'm new to all this so please excuse my terminology.
I have a database that gets updates everyday with new data.
I have created a query in access to extract the data I require, but how can I only grab 1st date of month to current date.
For example: Today's date is 26/10/07. So I want to grab data from 01/10/07 to 26/10/07.
I hope I explained it right. Please help ASAP. Thanks guys :o)

Kind Regards,

Sam

[Jim Doherty]

Hi Guys,

I'm new to all this so please excuse my terminology.
I have a database that gets updates everyday with new data.
I have created a query in access to extract the data I require, but how can I only grab 1st date of month to current date.
For example: Today's date is 26/10/07. So I want to grab data from 01/10/07 to 26/10/07.
I hope I explained it right. Please help ASAP. Thanks guys :o)

Kind Regards,

Sam

Paste this function into a module (go to the access window create a new module and paste it below the line 'Option Compare Database' and save the module as bas_MyModule. Then close the module

 Function FirstOfMonth(InputDate As Date) 
' Return a date that is the first day of the month of the date passed
Dim d As Integer, m As Integer, Y As Integer
If IsNull(InputDate) Then
FirstOfMonth = Null
Else
d = Day(InputDate)
m = Month(InputDate)
Y = Year(InputDate)
FirstOfMonth = DateSerial(Y, m, 1)
End If
End Function

Then open up your query in design and reference the newly created function in the criteria section of your query, in the query grid like this

Between FirstOfMonth(Da te()) and Date()

What happens here is that the function will 'calculate' whatever the first date of the month is from the current todays date (it being passed to the function as a parameter)

Regards

Jim