Run-time error '3464' Data type mismatch in criteria expression

[Kobus-Dippenaar]

Hello to all,
hope you are all well.
Sometimes I don't understand Bill Gates.

Here is my code:
tWhere = " [P_DateF]='" & txtSDate & "' ORDER BY '" & "[P_DateF]'"
'
strSQL = "SELECT * FROM [Prices] WHERE " & tWhere
Set rs = db.OpenRecordset(strSQL)

both [P_DatF] and txtSDate = "08/12/2014"
The SQL statement returns a Data type mismatch.

I am looking forward to your tutorial advice.

Blessings.
Kobus

 

[smig]

Make sure you compare and join the same data types fields.

 

[Kobus-Dippenaar]

P_DateF Data type Date/Time Format Short Date
Dim txtSDate As Date

Anything else?

 

[BlueIshDan]

If your text is in the correct format remove the single quotes:

tWhere = " [P_DateF]=[COLOR="Red"]'[/COLOR]" & txtSDate & "[COLOR="Red"]'[/COLOR] ORDER BY [P_DateF]"

If you wish to parse your text into a date try:

tWhere = " [P_DateF]=CDate([COLOR="Green"]'[/COLOR]" & txtSDate & "[COLOR="Green"]'[/COLOR]) ORDER BY [P_DateF]"

 

[BlueIshDan]

Remember this entire query is a string being parsed as a query. it does not know what your data types of your Variables are.

 

[Kobus-Dippenaar]

@BlueIshDan, Thx,
The first solution provided give no error, but nor does it give me a record. If sort of find a EOF.

tWhere = " [P_DateF]=" & txtSDate & " ORDER BY [P_DateF] ASC"
'
strSQL = "SELECT * FROM [Prices] WHERE " & tWhere
Set rs = db.OpenRecordset(strSQL)
If rs.EOF Then GoTo C9800

 

[BlueIshDan]

Make sure your date format is correct:

dd/mm/yyyy
dd/mm/yy
mm/dd/yyyy
mm/dd/yy

etc.

The passed value has to match the records format, SO figure out your records format and use the second solution, but pass a second parameter to the CDate function.

CDate(value, 'mm/dd/yyyy')

replace with the format of your table's field.
Feel free to display a date value here and I can determine the format string for you

 

[BlueIshDan]

You're programming style is a little off also try

While Not rs.EOF

Wend

'' PLACE: C9800 code here. Usually shouldnt use GoTo statements unless you're error catching.

 

[Kobus-Dippenaar]

[Resolved] Re: Run-time error '3464' Data type mismatch in criteria expression

@BlueIshDan,
Thx the second solution worked, I will continue checking to see if all is well.
Thanks for all the advice.

Blessings.

 

[BlueIshDan]

No problem.