SQL Statement error or Data Type error? (RESOLVED)

VB Code:

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.

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 & "#"

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?

Try....

VB Code:

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

well done!!it works now...

If you put

VB Code:

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

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

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

Apek,

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

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..