Date Help with DLookup

[jband]

I have a report that looks up a previous date then finds the data that goes with that date. I went to run the report and the data now is blank. I am thinking it is because of the formula is looking for 1/28/2013 instead of 1/31/2013. Can come one help with this. Here is the formula

DLookUp("[Net Insured]","[CashFlow Link]","[RCNAME]=" & "'" & [RCNAME] & "'" & " And [Date1]= DateAdd('m',-1,DateSerial(Year(Date()),Month(Date())-0,0))")

 

[plog]

You said it looks for the previous date--how's it to know that? What exactly is the previous date? You make it sound like its the last day of month, 2 months prior. Is that correct? (e.g. 3/2014 -> 1/31/2014, 2/2014 ->12/31/2013, 4/2012 -> 2/29/2012)

If so, you would need code to do the following:

1.Take 1 month from your date (call this PriorMonthDate)

2. Use PriorMonthDate to build a date for the first day of that month (call this PriorMonthFirstDate)

3. Subtract 1 day from PriorMonthFirstDate to arrive at the value you want.

If that's not what you want, describe in more detail what the "previous date" for a record is.

 

[jband]

Sorry I should of been more clear.

The date that I have in my data set is already put in the last day of the month. Example January date is 1/31/2014, February is 2/28/2014 and so on. The issue I am having is when I use Date1 field in a Dlookup to find the previous month it won't do it for February. When I look at the Dateadd code separately and minus 1 month it gives me 1/28/2014.

I hope this helps

 

[plog]

So in summary, I was correct with my initial post?

 

[jband]

In short yes

 

[Big Pat]

If you ALWAYS want to get the last day of the previous month, then you can use DateSerial(Year(Date()),Month(Date()),0)

As of today, 3rd March, that function returns 28th Feb. If you want to calculate the last date of the month previous to some other date, then you would replace Date() with SomeOtherDate in two places.

Does that help?

 

[jband]

But what if I want to go back 2 months

 

[jband]

Nevermind I figured it out. Just had to think about it a little. Thanks you