data type mismacth in criteria expression

[syd_master]

when i run my code...this error come out

"data type mismatch in criteria expression"

here my code:

Private Sub Text2_Change()
   
    TXT1 = ""
    TXT1 = "SELECT * FROM Profile WHERE sID =' " & Text2 & "'"
    
    If RS.State = 1 Then
        RS.Close
        
    End If
    
    RS.Open TXT1, Conn, adOpenKeyset, adLockOptimistic
    
    If RS.RecordCount > 0 Then
    
    txtText2 = RS.Fields(1)
    txtName = RS.Fields(2)
    txtDateOfBirth = RS.Fields(3)
    txtAddress = RS.Fields(4)
    txtIC = RS.Fields(6)
    txtContact = RS.Fields(5)
    cboStatus = RS.Fields(9)
    txtAge = RS.Fields(8)
    txtCourse = RS.Fields(11)
    cboFaculty = RS.Fields(10)
    txtMatric = RS.Fields(7)
    txtLicense = RS.Fields(12)
    txtEngine = RS.Fields(13)
    txtNoPlat = RS.Fields(15)
    txtModel = RS.Fields(16)
    cboType = RS.Fields(14)
    cboColour = RS.Fields(17)
    
    End If
    RS.Close
    End Sub

my system suppose save the database on access
really need the answer ASAP...thank a lot

[dspnhn]

check your datatype within your access table....try to remove "'" from your sql statement and then run it.

[syd_master]

check your datatype within your access table....try to remove "'" from your sql statement and then run it.

thanks for reply...
i have remove "'" from sql statement but another error come out.

run time error '-2147217900(80040e14)':
syntax error in string in query expression 'sID = 'xxxxxxxxx'.

my code after remove "'" :

Private Sub Text2_Change()
   
    TXT1 = ""
    TXT1 = "SELECT * FROM Profile ORDER BY sID = '" & Text2
    
    If RS.State = 1 Then
        RS.Close
        
    End If
    
    RS.Open TXT1, Conn, adOpenKeyset, adLockOptimistic (this line where the error occur)
    
    If RS.RecordCount > 0 Then
    
    txtText2 = RS.Fields(1)
    txtName = RS.Fields(2)
    txtDateOfBirth = RS.Fields(3)
    txtAddress = RS.Fields(4)
    txtIC = RS.Fields(6)
    txtContact = RS.Fields(5)
    cboStatus = RS.Fields(9)
    txtAge = RS.Fields(8)
    txtCourse = RS.Fields(11)
    cboFaculty = RS.Fields(10)
    txtMatric = RS.Fields(7)
    txtLicense = RS.Fields(12)
    txtEngine = RS.Fields(13)
    txtNoPlat = RS.Fields(15)
    txtModel = RS.Fields(16)
    cboType = RS.Fields(14)
    cboColour = RS.Fields(17)
    
    End If
    RS.Close
    End Sub

for your extra information...the system suppose read the ID number from RFID tag and than save the information to database.

really apperiaciate your help..

[AndreRet]

TXT1 = "SELECT * FROM Profile ORDER BY sID = '" & Text2

Try the following -

TXT1 = "SELECT * FROM Profile ORDER BY sID =" & "'" & Text2.Text & "'"

The data type mismatch normally refers to a field in your database table that is for instance set to accept only integers whilst you are trying to enter text. Make sure that all the fields is set up correctly to the data type they will receive.

[dspnhn]

you have not removed sID = '"

[syd_master]

when i put sID = '" in my code this error happen:

TXT1 = "SELECT * FROM Profile ORDER BY sID ='" & "'" & Text2.Text & "'"

run time error '-2147217900(80040e14)':
syntax error in string in query expression 'sID = 'xxxxxxxxx'.

but when i remove sID = ":

TXT1 = "SELECT * FROM Profile ORDER BY sID =" & "'" & Text2.Text & "'"

run time error '-2147217900(80040e14)':
data type mismatch in criteria expression

i really dont know what the real problem is...is there maybe my problem lies on my database?? but really have check the data type on MS Access...
all data type on Access are Text only sID are AutoNumber...

thank for helping...

[syd_master]

tag ID are in ANSI language...can VB6 or Access read this language?? im not sure but i think that ok, right??

[AndreRet]

If you are saving an integer (number) to your database, make sure that the text returned is a number. Further, if it needs to be a number, you can remove the ' from your select statement. It is only needed when retrieving a string (text).

TXT1 = "SELECT * FROM Profile ORDER BY sID =" & Text2.Text

[syd_master]

my fren...i managed to solve earlier problem...the problem are at my database...when i design new database there are no

data type mismatch in criteria

but new error come out...

sub or function not defined

here my code:

Private Sub cmdSave_Click() <-- here the error

    Call Connect
    
    rsInfo.Open "SELECT * FROM Info ORDER BY sTag", Conn, adOpenStatic, adLockOptimistic
    
    Call Enable_Info
    rsInfo.AddNew
    
    If txtName.Text = "" And txtIC.Text = "" And txtText2.Text = "" And txtMatric.Text = "" And txtAge.Text = "" And txtAddress.Text = "" And txtDateOfBirth.Text = "" And txtContact.Text = "" And txtLicense.Text = "" And txtEngine.Text = "" And txtNoPlat.Text = "" And txtModel.Text = "" And cboStatus.Text = "Select" And cboFaculty.Text = "Select" And cboType.Text = "Select" And cboColour.Text = "Select" And txtCourse.Text = "" Then
        txtName.SetFocus
        Set rsInfo = Nothing
        Set Conn = Nothing
        Exit Sub
    End If
    
    If True Then
            rsInfo.Fields("sTag") = txtText2.Text
            rsInfo.Fields("sIC") = txtIC.Text
            rsInfo.Fields("sMatric") = txtMatric.Text
            rsInfo.Fields("sName") = txtName.Text
            rsInfo.Fields("sAddress") = txtAddress.Text
            rsInfo.Fields("sDOB") = txtDateOfBirth.Text
            rsInfo.Fields("sAge") = txtAge.Text
            rsInfo.Fields("sContact") = txtContact.Text
            rsInfo.Fields("sStatus") = cboStatus.Text
            rsInfo.Fields("sFaculty") = cboFaculty.Text
            rsInfo.Fields("sCourse") = txtCourse.Text
            rsInfo.Fields("sLicense") = txtLicense.Text
            rsInfo.Fields("sEngine") = txtEngine.Text
            rsInfo.Fields("sType") = cboType.Text
            rsInfo.Fields("sNoPlat") = txtNoPlat.Text
            rsInfo.Fields("sModel") = txtModel.Text
            rsInfo.Fields("sColour") = cboColour.Text
            rsInfo.Update
            
            MsgBox "Document Information Was Stored In Database", vbInformation, "Document Registration"
            
            Set rsInfo = Nothing
            Set Conn = Nothing
   
     End If
     Call Clear_Info
    
End Sub

do you have any idea why this happen??
if before my database is Profile but my new database is Info...i have change all profile into Info...

[AndreRet]

First check if you have a command button with the name cmdSave.

Secondly, make sure there is a sub called Connect and a sub called Enable_Info.

Your problem lies in one of those 3.

[dspnhn]

i m messed up here.....can't say i actually understand your predicament...for once you have another issue than another than another......please stick to the problem

[AndreRet]

dspnhn, no one asked you to reply to the posters question. If you feel that they are posting too many questions, you have the option to refrain from answering.:-/

If you read the posters post carefully, you will notice that the "new" question IS related to the original post, hence my previous reply to solve the sub error.:)

[syd_master]

hello....to both of you..AndreRet and dspnhn...
thanks to both of you...i managed to solved all the error in my program...I really appreciated what both of you have done to help me...again...THANKS A LOT...peace!!

[AndreRet]

Only a pleasure. Happy coding...