Datatype mismatch error in SQL query

[dom24]

Using this SQL query in Access 97 but keep gettign a datatype mismatch error. All my table fields are set to text so don't know what the problem is.
This is my query:

Set rst = dbs.OpenRecordset("SELECT StaffNo, CourseKey, CourseDate, TrainerNo, ValidationQ1, ValidationQ2, ValidationQ3, " & _
"ValidationQ4, Validationq5, Validationq6, Validationq7, Validationq8, Validationq9, Validationq10, Validationq11, " & _
"Validationq12, Validationq13, Validationq14, Validationq15, Validationq16, Validationq17, Validationq18, Validationq19, " & _
"Completed FROM dbo_Validation WHERE (((dbo_Validation.[Completed]) Between '" & [Forms]![Form-feedback-statistics]![Start] & "' And '" & [Forms]![Form-feedback-statistics]![End] & "' ));", dbOpenDynaset)

Thanks.

 

[Trendsetter]

Just a question!
Are hyphens allowed in SQL??

"Form-feedback-statistics" could be the cause of the problem

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[dom24]

Yeah because that's just the name of the form and it's surrounded by quotes so it knows it's a text field.
That's what I would've thought anyway.

 

[PHV]

Is rst dimmed as DAO.Recordset and dbs as Database?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dom24]

Yep. It works fine without the WHERE clause in it, but soon as I put that in it just returns 0.

 

[JonFer]

Is dbo_Validation.[Completed] a date or text? Are you typing start and end dates on the form? Try converting the fields to the same type.

John

http://www.interautomateconsulting.com

 

[dom24]

It's a date.
SOrted it now. Apparently it has to bei n USA format no matter what system you're using, therefore I had to put DATEVALUE in front of all the dates.
Phew. THanks for your help.