BradleyS BradleyS - 4 months ago 19
SQL Question

SQL in xl VBA Date search query

I have a FROM and TO cell reference in XL2010 for entering dates in dd/mm/yyyy format.

However if I enter the same date in both FROM and TO it finds nothing.
What is wrong with my query?

If I change TO to the next day it finds everything in the FROM date.
Yet my query if using >= and <=, therefore how come the equal to isn't working for the TO field?

Dim sDate As Date
sDate = Cells(6, 2)
Dim fDate As Date
fDate = Cells(6, 3)

Sheets("Appts").Select
Range("A1").Select

Dim appt As String
appt = "SELECT CONVERT(VARCHAR(10), slot_dt, 103) AS date, time " _
& "FROM slotapp " _
& "WHERE ( slot_dt >= '" & sDate & "' AND slot_dt <= '" & fDate & "' ) " _
& "ORDER BY slot_dt"

Answer

It looks like slot_dt is a date-time field. The issue that you are have is due to the time portion. I think that you can fix it by using the date values for sDate and fDate and changing <= to <.

Dim sDate As Date
    sDate = DateValue(Cells(6, 2))
Dim fDate As Date
    fDate = DateValue(Cells(6, 3)) + 1

Sheets("Appts").Select
Range("A1").Select

Dim appt As String
appt = "SELECT CONVERT(VARCHAR(10), slot_dt, 103) AS date, time " _
        & "FROM slotapp " _
        & "WHERE ( slot_dt >= '" & sDate & "' AND slot_dt < '" & fDate & "' ) " _
        & "ORDER BY slot_dt"