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?

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

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.

SELECT qryValuta.RecordDate AS RecordDt, DLookUp("[EURUSD]","[qryValuta]","[RecordDate]=#" & DateAdd('m',-1,[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

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).

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

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

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

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

Thanx Ron for your suggestion and your help :)

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

I never had thought of that, superb! I am really ecstatic, thanx! :)
Happy to have been of help

Thanx Ron for your suggestion and your help :)

Met plezier Willem ;)