SQL query

[kidkarma]

I have a SQl query that should pull data from an Access database only
the recordsets that havew the specified 'startDate' in the Date Field.

At the moment an error pops up that says 'type mismatch'

Please note that the field 'Date' is set to "text"(i know!..but it has
to be)

and please note that the range startdate is formatted to "Date"

I think that could be a problem and if so maybe a
worksheetfunction.text??

Thanks for any help or advice

CODE

'Building SQL String that gets all fields
sSQL = "SELECT StaffName, Counsellor, Grade, Item, Manager,
Description, Status, Date, Hours FROM tblData"
sSQL = sSQL & " WHERE ([Date] = '" & startDate & "' And [Date] = '" &
startDate + 7 & "')"

 

[Douglas J. Steele]

Try using the Format statement to convert the StartDate to a string:

'Building SQL String that gets all fields
sSQL = "SELECT StaffName, Counsellor, Grade, Item, Manager, "
sSQL = sSQL & "Description, Status, Date, Hours FROM tblData "
sSQL = sSQL & " WHERE ([Date] = '" & Format(startDate, "mm\/dd\/yyyy")
sSQL = sSQL & "' And [Date] = '"
sSQL = sSQL & Format(DateAdd("d", 7, startDate), "mm\/dd\/yyyy") & "')"

I don't understand, though, why your date "has to be" text. If it's for
interoperability with other applications, create a query that converts the
dates to text and use the query wherever you would otherwise have used the
table.