SQL DLookup problem (DateAdd)

[Willem!]

Hi,

I have the following problem. I want a query to look up a certain (1 month) lagged valuta-return EURUSD. My SQL-code is as follows:

SELECT qryValuta.RecordDate AS RecordDt, DLookUp("[EURUSD]","[qryValuta]","[RecordDate]=#" & DateAdd('m',-1,[RecordDate]) & '#') AS LaggedEURUSD
FROM qryValuta;

This 'kind' of works, but it only returns the result in the month February! It is driving me completely mad! See attached for a sampe database (I want to succesfully run qryValuta2)

Can anyone see what I am doing wrong?

 

[DCrake]

Are you attempting to see what the value was one month earlier that the record date?

 

[Willem!]

I want to create a record which holds a RecordDate and a LaggedEURUSD; the EURUSD from one month earlier.

I want to do this to calculate the monthly index change in % (the return) (returnEURUSD = ([LaggedEURUSD]/[EURUSD])-1 )

When I have al the data one 1 row, I can simply do that.

 

[Ron_dK]

SELECT qryValuta.RecordDate AS RecordDt, DLookUp("[EURUSD]","[qryValuta]","[RecordDate]=#" & [B][COLOR="DarkGreen"]DateAdd('m',-1,[/COLOR][/B][RecordDate]) & '#') AS LaggedEURUSD
FROM qryValuta;

I guess the DateAdd('m,-1,.... gives you the limitation. Try changing that part.
By the way , with me , it gives me only the January month dates.


Hth

 

[Willem!]

How do you suggest I should change it? I need to substract 1 month, and the syntaxis is allright (when I try 'mm' or 'month' as suggested on some SQL-websites, I get an error).

 

[Rabbie]

The problem is caused by Access date format defaulting to mm/dd/yyyy

If you change qryValuta to evaluate RecordDate as Dateserial(Year,Month,"13") then it works correctly

 

[Willem!]

Wow! Incredible! Many, MANY thanx! That did the trick!

I never had thought of that, superb! I am really ecstatic, thanx!

 

[Ron_dK]

The problem is caused by Access date format defaulting to mm/dd/yyyy

If you change qryValuta to evaluate RecordDate as Dateserial(Year,Month,"13") then it works correctly

If you change that to Recdate: DateSerial([Year],[Month]-1,13), you'll have a month difference between the two dates.

HTh

 

[Willem!]

Thanx Ron for your suggestion and your help

 

[Rabbie]

Wow! Incredible! Many, MANY thanx! That did the trick!

I never had thought of that, superb! I am really ecstatic, thanx!

Happy to have been of help

 

[Ron_dK]

Thanx Ron for your suggestion and your help

Met plezier Willem