Hi,

I am trying to accomplish the following (the example is somewhat simplified for arguments sake). I have a query qryValuta which holds EUR-returns. I want to get a record of a RecordDate with a LaggedEUR, an EUR-return from the previous RecordDate.



The query doesn't work, it points toward the 'AS' as source of error. Am I overseeing something here?

Try "[qryValuta!RecordDate]=#" & DATEADD(MONTH, -1,[qryValuta!RecordDate]) & "#"

Edited by: b0b1 on Mon Oct 20 6:41:45 EDT 2008.

Hi!,

I implemented your solution, but with no results, when I run it, it askes me to enter a parameter value for 'qryValuta.RecordDate' and 'MONTH'.

So.... should I make clear in the code that qryValuta.RecordDate is a Date?

BTW: in my initial code that I posted I forgot to put an extra bracket ')' for the DLookup() in there, that was a mistake, I do have that bracket in the real code.

Replace MONTH with 'm'.

Replace 'qryValuta.RecordDate' with [RecordDate]

i.e.

DateAdd("[RecordDate]=#" & DATEADD('m', -1,[RecordDate]) & "#" )

Aargh, that doesn't help either, it still asks me to enter parameter values...





P.S. the brackets in the criteria section you suggested don't work, I need to use ' '

Edited by: Willem on Mon Oct 20 7:15:44 EDT 2008.

hold on, hold on, some light at the end of the tunnel...

...I got the RecordDate-thing to work... problem there was that I hadn't saved the qryValuta with the new RecordDate column yet ... I am deeply ashamed as you can imagine...

...however, it still is going wrong at the DateAdd, for some reason the query doesn't get the 'm' (or MONTH) part...

I am using Access 2007.

Best thing to see the other options for the interval value, on your pc, press ALT-F11, then F1 for help, type in "DATEADD Function" into the search and it will give you all the options for the intervals.

This is the exact code I used to test it out and works great (at least in 2007)

Thanx Bob for your help, it kind of works now, your last post helped me a lot, the last part of the criteria needed to be:


So ' instead of "....

...it is still not working quit right though, when I run the query I only get the results of 1 month, like this:

1-3-2008
1-2-2008 some result here
1-1-2008
1-12-2007
1-11-2007
1-10-2007
1-9-2007
1-8-2007
1-7-2007
1-6-2007
1-5-2007
1-4-2007
1-3-2007
1-2-2007 some result here
1-1-2007
1-12-2006

I am not sure why this is happening (...) I'll try to figure it out. Thanx for your great help so far. The DLookup tend to be problematic, many topics on this forum are related to it...

Unfortunately unless I see the actual database query I can't be much more help.

Good luck.

...as I'm driving mad here at the office.. please hold, I will strip my database and upload it with the table and the query (it has boiled down to just those 2 items...)

Hi,

See attached, the database has one table and two queries, qryValuta2 is the query where it all goes wrong.

I already can't thank you enough for your help, if you could take a peak at my problem, that would be awesome...

..could the datatypes of the Year & Month in my core-table be an issue? Unintentionally the types are Doubles now..

Hurray! I figured it out!

Actually I got some help from another forum (here). The problem was caused by Access date format defaulting to mm/dd/yyyy, I've changed qryValuta to evaluate RecordDate as Dateserial(Year,Month,"13") and it works like a charm! Initiallly I used '1' for the day-date.

Hurray, my headache is over, I can finally work again!

P.S.: Thanx b0b1 for taking so much time for me. You've also helped me a lot.