[RESOLVED] Data type mismatch

[Akshaya]

hi all..
I have a program that save Employee data into Access DB

After i save a data and i want to update the data...the error message "Data type mismatch in criteria expression"..will be displayed.

can someone tell me what should i do or change with my coding below.....

badgeno---Numeric
name,grade,project,area,remarks-----Text
doj-------Datetime
camp,monthly(checkboxes)-----text

" update emp set badgeno='" & txtbadgeno.Text & "',name= '" & txtname.Text & "',grade= '" & txtgrade.Text & "', project='" & txtproject.Text & "',area='" & txtarea.Text & "',doj='" & txtdoj.Text & "',camp='" & Check1.Value & "',monthly='" & Check2.Value & "',remarks= '" & txtremarks.Text & "' where badgeno= '" & txtbadgeno.Text & "'"

[dev.rogee]

access database, right? well...

for date/time data fields in access, you have to use

,doj=#" & txtdoj.Text & "#,

using single quotes for date/time data fields are good when using SQL Server


Additional:::

if the date is coming from a text field... another thing you should do is make sure the text follows the date format in the access database... if it's the Short Date, then do this first before inserting or updating the field in the database:

Format(txtdoj.Text, "mm/dd/yyyy")

Additional Additional:::

leinad is right... for numbers, do not use single quotes(').. it is not necessary, so badgeno can go as he said...

[zynder]

can u post the line where you get an error?

[leinad31]

Don't nest numerics such as badgeno in single quotes.

"set badgeno=" & txtbadgeno.Text & ",name..."
"... where badgeno= " & txtbadgeno.Text

[Akshaya]

Thanks for your replies.

I removed single quotes for numeric fields.
When I changed to doj=#" & txtdoj.Text & "# getting an errors "Syntax error in date in query expression ##"

[si_the_geek]

Moved to Database forum

What is the value of txtdoj.Text?

And can you show us the fully built SQL string?
(print it to the Immediate window, as shown in this FAQ thread)

[Al42]

What is the value of txtdoj.Text?

It looks as if it's null - "Syntax error in date in query expression ##"

[ganeshmoorthy]

yes, I hope the txtdoj.Text is empty. You can do it in this way. Break the query statement like

Sql = "set badgeno=" & txtbadgeno.Text & ",name..."
If Not Trim(txtdoj.Text) = "" Then
    Sql = Sql & ", doj=#" & txtdoj.Text & "#"
End If
Sql = Sql & " Where where badgeno= " & txtbadgeno.Text

Hope it helps

[Al42]

Sql = "set badgeno=" & txtbadgeno.Text & ",name..."
If IsDate(Trim(txtdoj.Text)) Then
    Sql = Sql & ", doj=#" & txtdoj.Text & "#"
End If
Sql = Sql & " Where where badgeno= " & txtbadgeno.Text

[Akshaya]

Thanks for your replies friends.
I changed my code as follows and it works.Thanks.

Private Sub cmdmodify_Click()

searchcode = Trim(txtbadgeno.Text)
sql = "select * from emp where badgeno= " & searchcode

tmp_rs.Open sql, conn, adOpenDynamic, adLockOptimistic

If tmp_rs.EOF = False Then
tmp_rs("name") = txtname.Text
tmp_rs("grade") = txtgrade.Text
tmp_rs("project") = txtproject.Text
tmp_rs("area") = txtarea.Text
tmp_rs("doj") = txtdoj.Text
...
....
...