Okay, I got the date to work correctly. To fix it I used DateSerial
instead of DateValue. I think DateValue was the wrong function to use
for it. Anyhow, unfortunately the problem isn't completely resolved
just yet because the YTD calulates the total year-to-date amount up to
the current date and not up to the date the the user specifies. For
example, if I choose February, 2006 from my form I want to see the
total amount up to February in my YTD (i.e. January total plus February
total) although it may be January of 2007. Right now the YTD shows the
same amount (YTD for the whole year) for any month in 2006 that I
choose. Is there a way to give YTD only up to the month that is chosen?
Thanks for your help!
Duane said:
Unhide the text box and check to make sure the correct date is displaying.
Reply with your actual SQL view that includes your exact SQL with your
control, field, form, and table names.
If your field data types aren't what we would expect, please let us know.
--
Duane Hookom
Microsoft Access MVP
:
Alright, I tried it but the RptDate fields are all blank and the MTD
and YTD fields are all zero. I guess I don't understand why MTD and YTD
expressions multiply the absolute value of the dates by the sales
amounts. Some sales amounts will be zero because we make Sales Orders
for things we give out to customers for free. Anyhow, it seems like
this is close.
Duane Hookom wrote:
Try something like:
Add a text box to the form Date Parameters
Name: txtRptDate
Control Source: DateValue([Year],[Month],1)
Visible: No
SELECT [Product], Forms![Date Parameters]!txtRptDate as RptDate,
Sum(Abs(Format(Orderdate,"yyyymm") = Format(Forms![Date
Parameters]!txtRptDate,"yyyymm"))*[Sales]) as MTD,
Sum(Abs(Format(Orderdate,"yyyy") = Format(Forms![Date
Parameters]!txtRptDate,"yyyy"))*[Sales]) as YTD
FROM [that one Transaction table]
GROUP BY [Product];
--
Duane Hookom
Microsoft Access MVP
:
Well, I've set up a form, named "Date Parameters," that asks the user
to select a month from a list, named "Month," and then to select a year
from a list as well, named "Year." This form is tied to a query with
some simple VBA code. The query will then use these inputs as values to
determine what month and year to show, then the report is also
popluated and opened. The end result of the query will show Product
Name, Month, Year, and Sales from a Transaction table. The month and
year values are determined using the form inputs in the criteria for
the OrderDate field (can be text or date format). I'm also bringing in
budget data from a Budget table but that's rather simple to do. The
real problem lies within the YTD information I need to retrieve. I need
to show YTD sales figures for each product in a summary report. All the
information I need for it is in that one Transaction table but there
are several transactions each day for each product so I guess I need to
manipulate that table in some way to be able to view YTD data. Any
ideas?
Duane Hookom wrote:
Could you share how/where this happens "the user to select a specific month
and year"? Please provide form and control names for this.
Then provide your table/query fields and possibly data types if they aren't
obvious.
