Help - with Year to Data query

  • Thread starter Thread starter Karen Middleton
  • Start date Start date
K

Karen Middleton

Hello All

I have a table - CustSales with the following structure:

Customer
Qty
Amount
Calmonth


and I have data in this table for the following
calmonth's = 0012004
0022004
0032004
0042004
0052004
0062004
0072004
0082004
0092004
0102004
0112004




Now from this table I have a need to do reporting for current month
and the current month can be anything like 0012004 or 0022004 or
0032004 or 0112004
that works fine with the following query:


select customer, qty, amount from CustSales where calmonth = [calm];


so when I run this query I enter a value for the parameter - calm and
it reports the current month values for that calmonth parameter
entered.

Now from this same table I want to do YTD (year to date ) reporting as
well
like the following:


select customer, sum(qty), sum(amount) from CustSales
where ( ( calmonth >= 2004001 ) and ( calmonth <= [calm] ) );


But my problem is I cannot use this hardcoding like ( calmonth >=
2004001 )
since the same table as data for year 2002 , 2003, 2004 so if the user
entered the parameter value as 2003005 I want the value to be
automatically computed for 2003001 as the from period.


Please advice how I can compute this from period automatically in the
query given the toperiod parameter value like 2003005 I want the from
period computed automatically as 2003001.

Any ideas how to do this is highly appreciated.

Thanks
Karen
 
Please advice how I can compute this from period automatically in the
query given the toperiod parameter value like 2003005 I want the from
period computed automatically as 2003001.
Click to expand...

I'm not certain how you're actually storing the month: in your example
you list 0012003, but here you have 2003001. Which is it?

In any case - you're paying the penalty for using a non-date field to
store date data. Text strings will not sort chronologically unless you
contort a bit (2002001 does indeed sort before 2003004, but not if you
turn them around!) You're also storing two more-or-less discrete
values (years and months) in one field.

I'd suggest using the DateSerial function to construct an actual
Access Date/Time value which can be sorted or on which you can use
date-range data. Assuming that your original example, 0032004, is the
actual format, you could use

SaleDate: DateSerial(Val(Right([calmonth], 4)), Val(Left([calmonth],
3)), 1)

You could then use a criterion

BETWEEN DateSerial([Enter year:], 1, 1) AND DateSerial([Enter year:],
[Enter month:], 1)


John W. Vinson[MVP]
 
Back
Top