Fiscal YTD vs. past year in Query criteria

[craigfr]

I am making a graph comparing last year's defect data with YTD defect data. Our fiscal year starts Nov.1 and ends Oct.31.
To get the YTD, I started used a simple date serial criteria:

Between DateSerial(Year(Date())-1,11,1) And Date()

This works, but only for 10 months out of the year. If your'e in December, it will calculate the YTD from last year's November to the current December, not the last November.

My solution was to make an IIF statement in the query criteria that calculated it differently if the current date is November or December:

IIf(Date()>DateSerial(Year(Date()),10,31),
Between DateSerial(Year(Date()),11,1) And Date(),
Between DateSerial(Year(Date())-1,11,1) And Date()

For some reason, this doesnt work. It returns 0 records every time I run it. I can run either one of the true/false statements by themselves and they work, as well as the IIF statement (with different T/F's) and it works.

Obviously this is only for the YTD, but I figure the past year's data query criteria will be very similar in design.

I'm not big on VB, so if this can be solved somehow in the basic query window that would be better; but I'm open to anything that works. Any ideas?

[FishVal]

Hi, there.

Take a look at this thread
Year Formatting April-March

[craigfr]

Thanks, I'm sure that will point me in the right direction, but I've never worked with raw sql before. I might wait and see if there are any other ways I can do it more easily through the query window before I try and figure yours out. Thanks

[FishVal]

Thanks, I'm sure that will point me in the right direction, but I've never worked with raw sql before. I might wait and see if there are any other ways I can do it more easily through the query window before I try and figure yours out. Thanks

"Raw SQL" and query window is just the same. You can switch from "Design view" to "SQL view" to represent the same DB action via visual interface or via SQL expression.

Kind regards,
Fish

[craigfr]

Okay, but I'm unsure of how to apply this to my situation.

Compare these two queries:
first one for Jan-Dec year

Code: ( sql )

SELECT * FROM tblTable WHERE Year([dteDate])=[Your year input];

second one for Mar-Apr year

Code: ( sql )

SELECT * FROM tblTable WHERE Year(DateAdd("m", -3, [dteDate]))=[Your year input];

Right now in my SQL view I have:

SELECT TOP 100 defectsbyday.Date, Sum(defectsbyday.SumOfdetotal) AS YTDdetot, Sum(defectsbyday.SumOffetotal) AS YTDfetot, Sum(defectsbyday.SumOffrtotal) AS YTDfrtot, Sum(defectsbyday.SumOfpetotal) AS YTDpetot
FROM productionbyday INNER JOIN defectsbyday ON productionbyday.Date = defectsbyday.Date
GROUP BY defectsbyday.Date
ORDER BY defectsbyday.Date DESC;


So where will I add the

 SELECT * FROM tblTable WHERE Year(DateAdd("m", -3, [dteDate]))=[Your year input];

string into my SQL? Or can this be entered in the criteria somehow?

[craigfr]

FishVal,

I figured it out, thanks! I just put that Year(DateAdd("m", -10, Date())) into the function now it works as advertised.

Thanks again

Craig

[FishVal]

You are welcome.

Best regards,
Fish