How to filter record with SQL by comparing the date store in ACCESS?

My data is store at ACCESS. I've try use SQL statement in Visual Basic to filter out records that I want, but cannot. Like,
step1 :OldestDay=Date()-10

step2 : "select * from RecordingTable where StartTime <= OldestDays"

I try to use VisData to build Query also not working for Date data, it always change to String and if I change by to Date format , error will occur.
candysoongAsked:
Who is Participating?
 
HunterKillerConnect With a Mentor Commented:
If OldestDay is a variable, and StartTime is an existing field in your RecordingTable, here is your answer:

Dim OldestDay as Date

OldestDay = DateAdd("d", -10, Date)

"SELECT * FROM RecordingTable WHERE StartTime <= " & OldestDay
0
 
Éric MoreauSenior .Net ConsultantCommented:
You will need to surround the date with # like this:
"SELECT * FROM RecordingTable WHERE StartTime <= #" & OldestDay & "#"

0
 
HunterKillerCommented:
or use the TimeValue function, that I forgot =]

"SELECT * FROM RecordingTable WHERE StartTime <= TimeValue(' " & OldestDay & "')"
0
 
rfylCommented:
If you want your program to work where the date format is not American (mm/dd/yyyy), then you will need to format it... e.g.

strSQL = "SELECT * FROM RecordingTable WHERE StartTime <= " & "#" & Format$(OldestDay, "mm/dd/yy") & "#"

See:
http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbcon98/vbconsqlqueriesbasedondates.htm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.