Dynamic Query

  • Thread starter Thread starter ACASEY
  • Start date Start date
A

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
 
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.
 
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 ...
 
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
 
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
 
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?
 
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
 
Back
Top