Dlookup in Reports

[jband]

I need to pull the previous months record and I keep getting blank for the dlookup. The Date is set up to be th last day of each month.

Here is the dlookup I am trying to use
=DLookUp("[Net Insured]","[CashFlow Link]","[RCNAME]=" & "'" & [RCNAME] & "'" & " And [Date1]=" & "#" & DateAdd("m",-1,[Date1]) & "#")

here is what the data looks like

RCNAME Date1 Net nsured
Blue 3/31/2013 $5,600
Blue 4/30/2013 $8,000
Blue 5/31/2013 $7,500
Red 3/31/2013 $4,000
Red 4/30/2013 $2,500
Red 5/31/2013 $4,500


When the report is generated it will look like below
RCNAME Date1 Net Insured Prev
Blue 4/30/2013 $8,000 $5,600

 

[pr2-eugin]

jband, Try this..

=DLookUp("[Net Insured]","[CashFlow Link]","[RCNAME]='" & [RCNAME] & "' And [Date1]=#" & DateAdd("m",-1,[Date1]) & "#")

I guess you are getting a bit confused with multiple concatenation.. Look up on this LINK...

 

[jband]

Thank you for the reply, the report is still coming up blank for the previous Net Insured

 

[spikepl]

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

but on reflection, the date condition will never be true. What is it supposed to be? Oh, is it a control in your form?

 

[jband]

Still coming up blank

 

[spikepl]

IS Date1 a control on your form? AND are you assuming that deducting one month from the last day of a month gives you the last day of previous month?

 

[jband]

I am thinking it may be the date1, When I try to pull the data by just a single date it comes up blank but if I use the excel serial number for the date the data appears. Anyway around this

 

[jband]

I have tried to use the DateSerial instead of the Dateadd and it still is blank

 

[jband]

This is the Dlookup with the DateSerial in it but it comes up with an Error
=DLookUp("[Net Insured Charges]","[CashFlow Link]","[RCNAME]=" & "'" & [RCNAME] & "'" & " And [Date1]= DateSerial(Year([CashFlow Link].Date1),Month([CashFlow Link].Date1)-1,0))")

 

[jband]

I figured out the answer. The dates were not matching up. Here is what i came up with and it works.

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