reference text variable in VBA

[BrockLanders]

Hi, I have the below code, but keep getting an error (Invalid argument, Expected 1) on my execute line.

DtTxtBx = Forms![Form1]![Text13].[Value]

query = "SELECT * FROM qryDates Where [Start Date]>= '" & DtTxtBx & "' order by [ChainName]"

My Start Date field is a text format (yyyymmdd) and DtTxtBx is picking up the correct value that I type in my form. I think it might have something to do with the >= sign.
Any help would be much appreciated. Thanks

 

[alvechurchdata]

Try using hash marks around the dates instead of quotes:

query = "SELECT * FROM qryDates Where [Start Date]>= #" & DtTxtBx & "# order by [ChainName]"

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[BrockLanders]

The hash marks (#) is for when it's actually formatted as a Date/Time field. My Start Date field is a text format.

 

[SQLSister]

You cannot properly use date to filter records unless it is a date datatype. Since a text datatype uses text sorting a date of 10/2/2004 would be considered less than a date of 2/1/2004.

It is a very bad practice to ever store a date as any kind of string datatype.

Questions about posting. See faq183-874

 

[BrockLanders]

I think this might work

DtTxtBx = Forms![Form1]![Text13].[Value]
DtToday = Date()
    
'Get new Activity Data
query = "SELECT * FROM qryDates Where [Start Date] Between '" & DtTxtBx & "'  And '" & DtToday & "' order by [ChainName]"

 

[Zion7]

brockLanders, I agree implicitly with SQLSister.
With all due respect, It's futile to make your comparison using a string type against a Date type, or to strings formatted as dates.

If changing the data type in your table, is not an option, I would consider the CDate(), to convert the string to a Date type. For example (maybe a little "roundabout"?)...

Dim dDate as Date, dStartDate As Date
Dim sCriteria As String

dDate = Forms![Form1]![Text13].[Value]

rec.Open "qryDates", CurrentProject.Connection....

Do Until rec.EOF

dStartDate = CDate(rec("Start Date"))

If dStartDate Between dDate And Date() Then
sCriteria = sCriteria & "," & rec!pkID
End If

rec.MoveNext
Loop

sCriteria = Mid(sCriteria,2)

query = "SELECT * FROM qryDates WHERE pkID In (" & sCriteria & ")"

..feasible but maybe a little unconventional?
Good luck either way!