Month to Date

[smaumau]

I need a query to look between two dates. The first date needs to be the first of the month of date given in [forms]![frmflash]![enddate] and the last date needs to be [forms]![frmflash]![enddate]. How can I make a between statement that looks between these two dates? Thank you in advance.

 

[Brian1960]

Build the query

Not sure if this will help but it may lead you in the right direction. This is for weekly billing to make sure it is always Mon to Sunday.
This is from a website I have in a support application which bills on elapsed time in completed jobs.

BillingDateDay = Weekday(BillingDate,2) 
Select Case BillingDateDay
	Case 1
		PreBillingDate = dateAdd("d",-1,BillingDate)
		PostBillingDate = dateAdd("d",+8,BillingDate)
	Case 2
		PreBillingDate = dateAdd("d",-2,BillingDate)
		PostBillingDate = dateAdd("d",+7,BillingDate)

then

whereclause = " where completedDT between #"&PreBillingDate&"# and #"&PostBillingDate&"# "

I then buid the query. You could used similar code to buid the bewteen dates.

Get the month of the entered date
intMonth=Month(entereddate)
intYear=Year(entereddate)

this will give you a start date as a string
strStartDate = "01/"& intMonth &"/"&intYear

You could then easily do a select case for the last day.

NB remember between does not include these dates so you may have more work such as

PreBillingDate = dateAdd("d",-1,strStartDate)
PostBillingDate = dateAdd("d",+1,strEndDate)

Hope this helps.

 

[smaumau]

Thanks, that got me headed in the right direction and I have a solution.