report - date specific

[georgialaw1]

I have a report where the user inputs a start date and an end date in popup dialog boxes (the 2 input boxes will be for 1 year). I want the report to generate the a total quantity for the year(which I figured out how to do), a total for the first 11 months, and a total for the last month in the year. Is this possible without using vba code?

 

[dhookom]

Could you share your current sql view? We don't have table or field names or grouping or what field you want to total.

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]

 

[danvlas]

Calculate the date field like
Year(DateField)*10000 + Month(DateField)*100 + Day(DateField)

It will end up as 20060118 and you can perform the calculation even across years by simple arithmetic.

Create 3 queries (one for the year, one for 11 months and one for 1 month), based on the same parameter(s) and left join them.


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dhookom]

Daniel,
You can do simple arithmetic with date values without your calculation. Take any date value and add 7, you will get next weeks date. A date value is a number so there is no reason to convert it to a different number.

Converting 1/18/2006 to 20060118 will only prohibit your from using date functions like DateAdd(), DateDiff(),...

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]

 

[danvlas]

Well, I thought so some time ago, but I found it easier to get some period-specific totals especially across years.

Plus, it looks simpler to use {Number1} - {Number2} and set a criterion than DateDiff("m",Date1,Date2).

And...it will not prohibit the use of those functions as long as the date field can be included in the query at any time.

But it's a personal opinion and nobody should take it as 'THE way' to do it.

Cheers,

P.S. (just to be obnoxious) "Take any date value and add 7, you will get next weeks date": I don't think so...should read 'THE' next date.



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dhookom]

Daniel,
To get to {Number1} - {Number2} you must run your calculation on each date to create the number and then do the subtraction. You would get the same result by just using {Date1}-{Date2}.

Taking this further if you want to add a month to 1/31/2006, you would really need to work with "raw" date values and the DateAdd() function. Attempting to do this with 20060131 would require a lot of extra calculation.

I have seen tables from Oracle or DB2 that store dates like 20060118 where your calculation would be very handy.

BTW: My previous post meant to point out that
Date()+7 = 1/25/2006

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]

 

[danvlas]

One thing that has been bothering me for a long time was that DateDiff("yyyy",#12/31/2005#,#1/1/2006#) returns 1 (year!) instead of 0. After all, it's just one day and no matter how you're rounding, it should return 0.
Same result for DateDiff("yyyy",#12/31/2005#,#12/31/2006#), which is rather correct.
It merely calculates the difference between years.

Same thing happens with months.

To avoid such result, especially for tax period calculations for countries where the fiscal year does not start on January 1-st, I really avoided the datediff function wherever I could, except for days calculations, where the result is the same as Date2-Date1.

The methods of calculation were various, the presented one being just an example.

I know what you meant. I just wanted to be obnoxious, because I had a nasty day.
Sorry about that.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dhookom]

No problem. I like a good debate once in a while.

The DateDiff() function can be confusing since it only returns an integer of the number of date/time intervals crossed. Sometimes this is valuable and other times it will bite you.

Now, if we could get some information back from georgialaw1 we might be able to do some actual work.

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]

 

[georgialaw1]

sorry about that. I figured out what to do earlier this week. thanks for the help!