How to narrow a recordset by date range

[acoppini]

I have been trying to select a recordset that filters on a text field for all records in the source table that are <= a specific date that I tried to specify earlier in the code. The select works fine without the AND date condition, but fails every time whenever I try to add a date.

My code is below - any help you can offer would be great. I am new to this. Thanks Ozy

Dim db As Database
Dim recTest As DAO.Recordset
Dim recBuys As DAO.Recordset
Dim ttl As Double
Dim pay As Double
Dim tradedate As Date
Set db = CurrentDb
 
'specify tradedate for later use
 
Set recTest = db.OpenRecordset("SELECT * FROM RefRecords WHERE RefRecords.TranType = 'Pay' ORDER By RefRecords.TranAsset_TradeDate", dbOpenDynaset)
recTest.MoveFirst
Do While Not recTest.EOF
With recTest
pay = !TranAsset_ParAmount
tradedate = !TranAsset_TradeDate
End With
 
'opens a dataset using SQL to select all preceding buys and stores CalcPAR total in ttl
 
Dim mySQL As String
mySQL = "SELECT * FROM RefRecords WHERE (RefRecords.TranType)='Buy' AND"
mySQL = mySQL + "" (RefRecords.TranAsset_TradeDate)<=tradedate"
 
Set recBuys = db.OpenRecordset(mySQL, dbOpenDynaset)
recBuys.MoveFirst
Do While Not recBuys.EOF
With recBuys
.FindNext !TranType = "Buy"
ttl = ttl + !CalcPAR
End With
recBuys.MoveNext
Loop
 

[colintis]

in specifying the dates in the SQL, try putting a # before and after the dates. Such as

"WHERE #" & (RefRecords.TranAsset_TradeDate) & "# <= #" & tradedate & "#"
 

If (RefRecords.TranAsset_TradeDate) & tradedate are table elements (not variables defined in vba), remove the [#" &] and [& "#] beside those variables.

[acoppini]

Thanks colintis,

tradedate is a variable I define in VBA to store the tradedate of the first sell records. My goal is then to use tradedate as a condition to select all preceding buy records.

I don't quite understand the use of the [#"&] - can you explain further?

[colintis]

@acoppini
In SQL any date format values except the table elements, must be quote with # such as:

Table.Date = #06/18/2010#
 

Where 06/18/2010 is in the format of MM/DD/YYYY as SQL standard parameter. So any non table elements will need to be quote by # for identification as date.

The & is just a string combining function in VBA, usually when you try to declare the string in a mixture with some words and a variable such as:

Dim todayDate as String
 
todayDate = "Today is " & Date()
 

As using + is incorrect as well. So in your second line of the mySQL string would be like this, with a ";" as the ending of SQL statement:

mySQL = mySQL & " RefRecords.TranAsset_TradeDate <= #" & tradedate & "#;"
 

Hope my explanation won't make you more confused :P

[acoppini]

@colintis
haha - you are awesome - i will try this out when I am back in the office on Monday - thanks so much!

[NeoPa]

That's mainly right Colintis. You did a pretty good job of explaning :)

However, the '+' can be used as a concatenation character in Jet SQL (Accesses version of SQL), but it has a special meaning when dealing with Null values. See Using "&" and "+" in WHERE Clause.

Using date literals is described more fully in Literal DateTimes and Their Delimiters (#), though I can't think of anything wrong in what you said.