Dynamic Query

[ACASEY]

Hello;

I am working on Historical Crude Oil Monthly-Spread Analysis.

The spread is between the front month and 2 months out. Example: Today's
trade date is 2-18-06. I am trading the spread between March/May.

On trade date 3-18-06, I will be trading the spread between April/June and
on 4-15-06 I will be trading the spread between May/July.......

Moving to the next month is marked by the expiration of the contract.

What I am aiming to do is build a query or logic where I can extract the
settlement prices of these months dynamically. I have historical settlement
data for 3 years.

Any help would be much appreciated.

Arno

 

[Nicholas Scarpinato]

Assuming I'm understanding your question, if you're going to do this with a
query, you would want something like this in the Criteria of the Date field:

Between ([Spread Date] + 30) and ([Spread Date] + 90)

Since Access stores dates as serial numbers rather than actual dates, adding
30 to the date essentially moves you ahead a month. But I'm not 100% sure
that I understand exactly what you're asking, so if this isn't what you
needed to make this work, please explain your problem in greater detail.

 

[Frank]

This is rough code ... but you may wish to paste it into in a new Access
module and run it and see if it gives you what you want -

Public Sub DoIt()

dtmTrade = CDate("18/12/07")


dtmSpreadStart = CDate("1/" & Month(DateAdd("m", 1, dtmTrade)) & "/" &
Year(DateAdd("m", 1, dtmTrade)))

dtmSpreadEnd = DateAdd("d", -1, CDate("1/" & Month(DateAdd("m", 3,
dtmTrade)) & "/" & Year(DateAdd("m", 3, dtmTrade))))

MsgBox dtmSpreadStart & " - " & dtmSpreadEnd

End Sub

Change "18/12/07" to other dates to test it.

Note that I'm using Australian date format ("dd/mm/yy").

Cheers ...

 

[Frank]

Here's a simpler version -

Public Sub DoIt()

dtmTrade = CDate("20/12/07")


dtmSpreadStart = CDate("1/" & Month(DateAdd("m", 1, dtmTrade)) & "/" &
Year(DateAdd("m", 1, dtmTrade)))

dtmSpreadEnd = DateAdd("d", -1, DateAdd("m", 2, dtmSpreadStart))

MsgBox dtmSpreadStart & " - " & dtmSpreadEnd

End Sub

 

[Nicholas Scarpinato]

Wow, I like that DateAdd function... I thought your code wouldn't work
because of the way Access handles date values, which led me to research
DateAdd. Very nice... Is that a new function as of Access 2K3, or did I just
miss it?

But

 

[Frank]

It has been around for sveral versions.

Have you worked out how to use it in a query which looks back at, say,
txtTradeDate on a form?

 

[Nicholas Scarpinato]

According to what I read, you can't use it in a query...?

 

[Frank]

Oh yes you can.

According to what I read, you can't use it in a query...?

 

[Robert Morley]

You may be thinking of ADPs or something similar. In those instances, you
can't. In an MDB, it shouldn't be a problem.


Rob