Complex SQL Statement

  • Thread starter Thread starter George Addison
  • Start date Start date
G

George Addison

I thought I might post this to see if there is a pro that
can help me. Here's what I want to do:

- I have two tables ('tblPositions', 'tblTrades')
- A relationship exists at tblPositions.ID =
tblTrades.PositionID
- I need to pull only one record from each relation set
(the one with the most recent TradeDate value) from the
tblTrades table
- I need to sort the retrieved records by TradeDate DESC

Here is the SQL statement that I have so far:

"Select DISTINCT tblTrades.PositionID as ID FROM
tblPositions, tblTrades WHERE tblPositions.Status = " &
Status & " AND tblTrades.PositionID = tblPositions.ID AND
tblTrades.TradeDate BETWEEN '" & Format
(StartDate.Date, "yyyy-MM-dd") & "' AND '" & Format
(EndDate.Date, "yyyy-MM-dd") & "' ORDER BY
tblTrades.TradeDate DESC"
 
Hi George,

I see two potential issues here.
1. You should not pass dates using concatenation. Pass them as a parameters.
In this case you will avoid any issues with date formats and international
settings
2. You should not use concatenation at all, since it could lead to the
injection into your SQL statement (like in case of using Status)
 
Thanks Val, but even using date parameters, do I not have
to worry about some format elements (ie. yyyy vs. yy)?
 
Not really, except the system could guess the wrong date, if you allow
future dates. There is a sliding window, so 99 would equal 1999 and 03 would
equal 2003. But, 30 would most likely be 2030 (have not tested) as the
window should be 40 some odd years.

I tend to stick to Y2k formatting, which alleviates any mistakes.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
See Cowboy's response. Basically if you are working with the current dates,
then you should not experience any date related issues with the parameters.
It does not matter which format you have on your PC
 
Back
Top