Calculate $$$ YTD in new Query column? (1 Viewer)

[Sidney Lynn]

QueryA has records for all 12 months, and has fields named: Month#, AccountType, SalesRep, and Sales$. A new query, QueryB, has these same fields, but I enter a Month# from a Dialog Form and QueryB gives me values for that particular month. (The Month# is just a number and not a datefield; there is no year data.)

I want to add a new column to QueryB that will calculate the Year-To-Date Sales from the beginning of the year (Month #1) to Month X -- X being the month# I entered into the Dialog Form.

Can this be done? The sales figures for all 12 months is in QueryA that QueryB is based upon. I’ve tried various expressions but nothing works. If I can get this to work it will save me lots of extra queries and subreports!!! Thanks.

 

[Mile-O]

Between DateSerial(Year(Date()),1,1) And (DateSerial(Year(Date()),[Enter Month] + 1, 1) - 1)

 

[CJBIRKIN]

Hi


Take a look at the attached database. I think this is what you are looking for. If you have an questions please ask.

Chris

 

[DewMan]

This should work....Modify query B to get all data =or< Month and add SUM TOTAL to qry.
Filter form to show only the month from Dialog box.

 

[Sidney Lynn]

Thanks for your replies. I'm reviewing the downloaded DB and your comments and will get back to y'all(!) --- Mile-O-Phile: where would I put your expression? And since I don't have an actual date but a number field (1-12) and text field (Jan, etc.), does DateSerial still work? Thx.

 

[Sidney Lynn]

Progress!!

I downloaded the sample queries. One thing I was doing wrong was on the queries I hadn't 'unchecked' show on the Mo# field. Anyway, I have successfully used your methodology and with just 3 queries and one report I can run a report for each month showing that's month's figures and YTD figures. I use a dialog form w/ a listbox [CurrentMo] that let's the user choose the month. (The listbox is bound to a little table that just lists the 12 month#s and 12 month names). I show the month name on the listbox, but the month# is the bound field). I also created an unbound listbox on the form and called it BeginMo and set it's default value to 1 and then in the query criteria for field [Mo#] I have: Between [Forms]![FormName]![BeginMo] And [Forms]![FormName]![CurrentMo]. In the future mgmt will probably want to report on Qtrs, etc., so all I'll have to do is bind that listbox to my little table... Also, even tho the bound listbox field is the month#, I figured out how to dynamically have the month name print on the report by creating a text box and having its ControlSource be =[Forms]![FormName]![Mo#].Column where n is the column in my little table that lists the month name. Access seems inconsistent in whether it starts counting at 0 or 1... Anyway, I really appreciate the sample and all your time. Hopefully this will help others. THANKS!!