Datepart in query help

[eerich]

I have run into another error that I can't figure out surrounding a datepart query setup. The clause is:

WHERE (Originating_Date) < (Select min(datepart(m,[accrual_date])) from corp)

I'm trying to select all entries where the originating_date is before the month of the accrual_date.

When I run the query I get a parameter box asking for me to enter 'm'.

Any help is appreciated.

 

[eerich]

I was able to figure out how to get the parameter to go away. I added a single quote surrounding the 'm' and that did it. However, the query does not produce the results I'm after.

I'm trying to get all entries where the originating_date occurs before the month of the accrual_date. So where I have data:

Ln# Orig_Date Accrual_Date
01 1/13/2007 1/25/2007
02 10/19/2006 1/25/2007
03 1/03/2007 2/25/2007
04 12/30/2006 1/25/2007

I would expect to have my query return lines 02 & 04

The query is:
Select Ln, Orig_Date, Accrual_Date
From Corp
WHERE (Originating_Date) < (Select min(datepart('m',[accrual_date])) from corp)


Any help is appreciated.

 

[JerryKlmns]

>I'm trying to get all entries where the originating_date occurs before the month of the accrual_date.

You mean: if for each record the originating_date happens before the month of the accrual_date then

WHERE Originating_Date < DateSerial(Year([accrual_date]), Month([accrual_date]),01)

Your WHERE clause used the min value of all records' months compared to the Originating_Date.

 

[eerich]

JerryKlmns, your solution almost works; however, it's not getting me the results I'm expecting. The table contains 3 accrual dates (example: 1/25/07; 2/25/07/ 3/25/07). I need to identify all originating dates that start before 1/1/07. Because each quarter I have a different scenario, I was trying to write a query to generate results without me having to type in the date each time so I could process this in a macro with other queries.

I modified the WHERE clause you provided and got the results to contain all originating dates prior to 1/25/07, but I need it to generate originating dates beginning at 12/31/06. How can I get the dateserial to subtract 25 days?

WHERE Originating_Date <
(Select min(accrual_date) from corp
WHERE DateSerial(Year([originating_date]),
Month([originating_date]), Day([originating_date])-26))

 

[CaptainD]

Does this work?

SELECT [Corp].Orig_Date, [Corp].Accrual_Date
FROM Corp
WHERE ((([Corp].Orig_Date)<DATEADD("d",-1,Format([Corp].Accrual_Date,"mm/01/yyyy"))));

 

[eerich]

CaptainD,

Again it is very close, but I think the issue is trying to get the minimum date for each quarter to be the evaluating criteria. The query you provided was evaluating each record against the accrual_date in that record. My table has 3 accrual_dates (Jan 25; Feb 25; Mar 25) and I need my result set to evaluate all of the orig_date against Jan 25 only. When I insert the min(accrual_date) in the where clause it doesn't work. Here's my entire query as written:

SELECT corp.Originating_Date, corp.Accrual_Date, corp.Voucher
FROM Corp
WHERE Originating_Date <
(Select min(accrual_date) from corp
WHERE ((([Corp].Originating_Date)<
DATEADD("m",-1,Format([Corp].Accrual_Date,"mm/01/yyyy"))))
ORDER BY Originating_date DESC, voucher;

 

[CaptainD]

Try this

SELECT Corp.Orig_Date, Corp.Accrual_Date
FROM Corp
WHERE Corp.Orig_Date <
(SELECT MIN (corp.Accrual_Date) FROM Corp 
WHERE [Corp].Orig_Date <DATEADD("d",-1,Format([Corp].Accrual_Date,"1/01/yyyy")));

 

[eerich]

I got the same results as before. It doesnt seem to be able to back up to the last date of the prior month. The output from the last query above produced a result set with the orig_date starting at 1/24/07 and earlier, which means it did evaluate everything against the 1/25/07 accrual_date but I'm at a lost as to how to get it start with orig_dates at 12/31/06.

Any idea?

 

[PHV]

And what about this ?
SELECT Originating_Date, Accrual_Date, Voucher
FROM Corp
WHERE Originating_Date <= (Select Min(DateSerial(Year(Accrual_Date),Month(Accrual_Date),0)) From Corp)
ORDER BY Originating_date DESC, Voucher;


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CaptainD]

I guess I'm not clear on what you are looking for.

I'm trying to select all entries where the originating_date is before the month of the accrual_date.

We've been able to do that

My table has 3 accrual_dates (Jan 25; Feb 25; Mar 25) and I need my result set to evaluate all of the orig_date against Jan 25 only.

We've been able to do that

but I'm at a lost as to how to get it start with orig_dates at 12/31/06

What exactly are you looking for here

 

[eerich]

PHV,

Your query returned the correct dataset!

Thanks to all who offered suggestions.