Since EndDate doesn't match anything else, Access assumes it's a parameter
and pops up a dialog for you to enter a value.
The Year() function extracts the year from a date.
The DateSerial() function builds a date from a year, month, and day.
The criteria therefore reads the year of the date you entered, and builds a
date for the 1st day of the year. The WHERE clause therefore asks for all
dates between the first of the year and the date you entered.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cnkses$ifs$1@titan.btinternet.com...[color=blue]
> Thanks Allen worked like a dream. If you get chance though could you
> explain
> what's happening in that statement? Although it works I would like to try
> and understand why it works.
> Thanks
> Tony
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:419d4878$0$25770$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
>> So you want all the values so far in this calendar year?
>> Ask the user for the ending date:
>>
>> Between DateSerial(Year([EndDate]),1,1) And [EndDate]
>>
>>
>> "Tony Williams" <tw@tcpinvalid.com> wrote in message
>> news:cnileg$p7m$1@titan.btinternet.com...[color=darkred]
>> > Thanks Allen, have followed your guide but when I enter say June 2003 I
>> > only
>> > get the figures for the quarterJune 2003, I don't get the figures for
>> > January to June 2003. In other words I should be getting the total of
>> > March's figures and June's Figures
>> > Any help?
>> > TIA
>> > Tony Williams
>> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
>> > news:419cca43$0$25769$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
>> >> You have a GROUP BY query, so drag the date field into the grid.
>> >> In the Total row, under this field, choose Where.
>> >> In the Criteria row, enter:
>> >> >= [Quarter Starting Date] And < DateAdd("q", 1, [Quarter Starting
>> >> Date])
>> >>
>> >> To ensure the user enters a valid date, choose Paramters from the
>> >> Query
>> >> menu, and enter:
>> >> [Quarter Starting Date] Date
>> >>
>> >>
>> >> "Tony Williams" <tw@tcpinvalid.com> wrote in message
>> >> news:cnih26$fd6$1@hercules.btinternet.com...
>> >> >I have a database that hold s figures for each quarter. I want to[/color][/color]
> create[color=green][color=darkred]
>> > a
>> >> > query whereby the user can ask for the running total of the data
>> >> > upto
>> > the
>> >> > quarter they enter into a prompt. The database stores the quarter[/color][/color]
> name[color=green][color=darkred]
>> > in
>> >> > txtmonthlabel (a date field) and the quarters totals in txtdomic (a
>> > number
>> >> > field) EG If the user enters March 2004 they get figures upto March
>> > 2004,
>> >> > if
>> >> > they enter June 2004 they get total upto June 2004, in other words[/color][/color]
> the[color=green][color=darkred]
>> >> > query
>> >> > would add March's figures to June's figures to give the totals, not
>> >> > just
>> >> > show June's figures.
>> >> > Can this be done in a query and what would the prompt criteria be?
>> >> > TIA
>> >> > Tony Williams[/color][/color][/color]
