SQL Statement error or Data Type error? (RESOLVED)

[Apek]

SELECT MC_NO, PART_CODE,MONTH " & _
        "FROM MC_DATA " & _
        "WHERE MC_NO = '& txtM1.Text &' AND MONTH = '" & txtMonthHide.Text & "'

above is my sql statement...but i get an error "Data Type Mismatch in criteria expression. i think the error refers to MC_NO = '& txtM1.Text &', because there is no value when i debugged it.FYI the data type for MC_NO in MS Access database is Number..can somene correct my coding mistake??
Thanks in advance.

[randem]

Apek,

First, You should always do something like this to eliminate simple SQL mistakes:

strSQL = "Select ...."
Debug.Print strSQL

This will allow you to see really simple mistakes.

Dates in Access must be surrounded by # signs

MONTH = #" & txtMonthHide.Text & "#"

[Apek]

emm..actually theres nothing wrong with the MONTH field because although i set the data type to Text, the value in the field is 200504 (YYYMM format).
i think the MC_NO = '& txtM1.Text &' is the problem because the data type in Access is Number, but i dont know what to use in SQL Statement for number in VB.Anyone know?

how to use the debug.print?
is it will print the error on the form or what?

[dee-u]

Try....

SELECT MC_NO, PART_CODE,MONTH " & _
        "FROM MC_DATA " & _
        "WHERE MC_NO = " & txtM1.Text & " AND MONTH = '" & txtMonthHide.Text & "'

[Apek]

well done!!it works now...

[dglienna]

If you put

Debug.Pring strSQL

you will get a look at how the sql string looks to the computer.
It will get printed in the Immediate window of the IDE.
You can open the Immediate Window by pressing control-g

[dee-u]

A rule of thumb is dont enclose your criteria with single quotes when the data type is a number....

Anyway, mark your thread as [resolved] and select the checkmark icon if your problem is already solved....

[randem]

Apek,

You are correct I misread. For the month you do not need the # sign. Is the month a text field in the database?

[Apek]

yup.,its in Text type..its ok u misread it..
happen to me all the time too...
thanks guys for all the answer and explanations..
really appreciate that..