Year to Date data

[kato68]

Im running a query that sums the costs occuring from 1/1/2003 to a year entered by the user in a form. So right now the critieria in my query looks like this:

between #1/1/2003# and [forms][myForm][date2]

Which works fine. But i want the query to work for any year, right now it will work for only the year 2003.

Any suggestions???

 

[dcx693]

You can run a parameter query by replacing the 1/1/2003 like this:
between #1/1/[Please select a beginning year]# and [forms][myForm][date2]

When you run the query, you will get this prompt: [Please select a beginning year] with a text box where the user can input a year.

But since you've already got the user selecting the end date on a form, the better way to do it is to allow the user to select the beginning year on the form also. Just reference it the same way you referenced the end date.

 

[kato68]

I already have them entering the beginning and ending years on the form. Say they want to run the query for the month of May, they would input 5/1/2003 and 5/31/2002, So the month totals are calculated, but I also need the YTD totals also. So I dont know how to pull the year from what they are inputting.

 

[dcx693]

You can pull the year from a date field using the aptly named Year() function. So if the field is called txtBeginDate, use a reference from your query like this:
DateValue("1/1/"& Year([forms]![myForm]![txtBeginDate] ))

So the entire criteria line will be:
between DateValue("1/1/"& Year([forms]![myForm]![txtBeginDate] )) and [forms]![myForm]![date2]

You might need some # symbols in there around the dates to make it work. Try it without first.

 

[whatever]

I use the dateserial and datepart functions in my queries to automatically filter the records for the current year using :

> DateSerial(Datepart("YYYY",date()),1,1)

Using datepart to aquire the year from todays date

and dateserial to create the first day of the 1st month of the current year.

I also use this teqnique to get the first of the current month

> DateSerial(Datepart("YYYY",date()),datepart("m",date()),1)

and use the dateadd function to get the first of last month:

dateadd("m",-1, DateSerial(Datepart("YYYY",date()),datepart("m",date()),1))


Hope this helps

Kevin

 

[kato68]

Thanks so much! The DateValue function worked perfectly.

 

[MM-UK]

This is interesting.
I have a module which defines public holidays from a holiday table.

So in a query, how would I use this module.

This query needs to calculate hrs available for this current month less weekends and public holidays.

A help with the syntax would be greatly appreciated