Query YTD totals

[jer007]

I am having trouble with a query that is used for retreiving Year to Date information for a report. The user enters in date range information in Forms!RunReport. This form contains three unbound text boxes: txtyear, txtstart, txtend. This date information is used to run the report. In qryYTD the date criteria is: Between #2004-01-01# And Forms!RunReport!txtEnd This does work except every year the criteria will need to be manually changed.

I would like to have the query run the YTD query based on the year in txtyear. I have tried the following criteria: Between "1/1" & Year(Forms!RunReport!txtYear) And Forms!RunReport!txtEnd

This does not seem to work. I don't know if it will make a difference but the db is set up with date inputs in a yyyy-mm-dd format. I would appricate the help if anyone would know how to run the query based on the year the user inputs.

Thanks,

-Jeremy

 

[GingerR]

Since you are entering the YEAR into txtYear, you don't need to then apply the Year() function to it. It messes it up.

Between ("1/1/" & [Forms]![RunReport]![txtYear]) And [forms]![RunReport]![txtEnd]

I also added another "/" after the "1/1"

If the 'year' is ALWAYS the year of whatever the end date is, you could instead just apply the year function to the txtEnd date and forget entirely about the txtYear text box.

Between ("1/1/" & Year([Forms]![RunReport]![txtEnd])) And [forms]![RunReport]![txtEnd]

 

[dhookom]

I would never use text values to query date values. There are date function that I feel are much more reliable.

Between DateSerial([Forms]![RunReport]![txtYear],1,1) and [forms]![RunReport]![txtEnd]

If you need to extract the year from a date in a text box:

Between DateSerial(Year([Forms]![RunReport]![txtEnd]),1,1) and [forms]![RunReport]![txtEnd]

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[jer007]

That works great!

Thanks for the help and Merry Christmas!


-Jeremy