Cannot use variable with SQL

[DonTanner]

I am sorry to trouble you all again but I have nowhere else to turn. I 'Think' I should be able to do this but have been unable to do it. I want to put a variable in my sql statement.

If I use a date like '#10/05/2012#' it works fine, But when I put 'ANY' variable there it bombes out. I have used text & Date Variables, but to no avail. I want to pull up records based on the date of last month. Any help would be greatly appriciated.

Im am running a laptop with Windows 7 64 and Access 2007.

\Begin code

Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set CurConn = CurrentProject.Connection
Dim SqlStr As String

Dim DateFirst As String
Dim DateBg As Date

DateFirst = #10/5/2012#
DateBg = CDate([DateFirst])


'SqlStr = ("SELECT ThatDate, Description FROM tblDay WHERE ThatDate < 'DateFirst' ( or "DateBG") ") 'Does Not Work

SqlStr = ("SELECT ThatDate, Description FROM tblDay WHERE (ThatDate > #10/05/2012#)") 'Works

rst.Open SqlStr, CurConn, adOpenKeyset, adLockOptimistic, adCmdText

/End code


Thank You

Don

 

[John Big Booty]

Try;

Dim DateFirst As Date

DateFirst = 10/5/2012

SqlStr = "SELECT ThatDate, Description FROM tblDay WHERE (ThatDate < #" & DateFirst & "#);"

 

[DonTanner]

So sorry but that's not working either. I get an error. It does not want me to put it in that format. .:banghead: The "Thatdate" is the date column from the table and I want to use it to sort records by date given. O'well. Thanks.

 

[John Big Booty]

Your Select statement needs to be in the form;

"SELECT TableName.Field1Name, TableName.Field2Name " & _
"FROM TableName " & _
"WHERE (TableName.FieldName < #" & DateFirst & "#);"

 

[mdlueck]

So sorry but that's not working either.

Or you may avoid the string / variable concatenation trouble all together and use...

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

 

[DonTanner]

Thank you Big John Booty. You gave me : "
< #" & DateFirst & "#);" . It, as it is did not work, BUT, when
I moved The last " inside the ) and dropped the ;, It worked great.

Thank You
Don