Hi, Forum

I have a problem with this code:

m_oConn.Execute "Insert Into T_Dts_Doc_Comprador(PK_Processo,Dt_ped_BI_Tit_Comp ,Dt_prev_BI_Tit_Comp,Dt_recep_BI_Tit_Comp,N_tot_di as_BI_Tit_Comp)" _
& "values(" & txtfields(0) & ",'" & CDate(MaskEdBox1(10).ClipText) & "','" & IIf(Len(MaskEdBox1(11).ClipText) = 0, "Null", MaskEdBox1(11).ClipText) & "','" & CDate(MaskEdBox1(12).ClipText) & "'," & txtfields(13) & ")"

it give me this error:

data type mismatch in criteria expression.

What i want is, let the maskedbox(11) in form field blank and insert the record Null in this record to put later.


Regards,
PJM

Which database are you using - access ? Are you saying that when inserting you always want that field to be null and that later it will be updated with another value - or is there a possibility that data will be entered at insert time (your sql statement with iff leads me to believe the latter) ? How are the fields defined in the table - include any default values, if nulls are allowed ...

You may have already done this - but create a string that will store the sql statement that you are passing to the execute method. Populate this string into a text box - verify that it looks correct and copy it. Go into your database software (access ?) and make a query of it and test it.

Hi rnealejr

I use access2000 in this database . the data type of my record is date/time.
I want to updated the null values latter.
I already try to use the Sql query in access and i confirm the field accept the Null value but I don't try with condition IIF(),
this because I don't have this objects in access.
How can i try the condition?

I guess this aswer to your questions

Thanks a lot,

PJM

Are all the fields datetime that you are inserting ? If you output the query in your code at the point before the connection execute, the sql statement would have the values of the objects (not the object references in your code) - this way you can first make sure the query is correct - then copy it and test it in access.

Example:
Dim strSQL as string

strSQL = "Insert Into T_Dts_Doc_Comprador(PK_Processo,Dt_ped_BI_Tit_Comp , Dt_prev_BI_Tit_Comp,Dt_recep_BI_Tit_Comp,N_tot_di
as_BI_Tit_Comp)" _
& "values(" & txtfields(0) & ",'" & CDate(MaskEdBox1(10).ClipText) & "','" & IIf(Len(MaskEdBox1(11).ClipText) = 0, "Null", MaskEdBox1(11).ClipText) & "','" & CDate(MaskEdBox1(12).ClipText) & "'," & txtfields(13) & ")"

text1.text = strSQL ' you have to create text1 on your form

m_oConn.Execute strSQL ' put a breakpoint here and go to the form and examine/copy the string from the text box

The problem is with the single quotes before and after the iif statement. Basically you are passing 'Null' not Null - so access is thinking string. Take them out and put them in the false part of the iif statement

IIf(Len(MaskEdBox1(11).ClipText) = 0, "Null", "'#" & MaskEdBox1(11).ClipText & "#'")

You may already be handling the pounds in your mask edit box - if so, then just leave them out.

I try this code :

m_oConn.Execute "Insert Into T_Dts_Doc_Comprador(PK_Processo,Dt_ped_BI_Tit_Comp ,Dt_prev_BI_Tit_Comp,Dt_recep_BI_Tit_Comp,N_tot_di as_BI_Tit_Comp)" _
& "values(" & txtfields(0) & ",'" & CDate(MaskEdBox1(10).ClipText) & "','" & IIf(Len(MaskEdBox1(11).ClipText) = 0, "Null", "'#" & CDate(MaskEdBox1(11).ClipText) & "'#", " & CDate(MaskEdBox1(12).ClipText) & ", " & txtfields(13) & ")

It select the iif word and guive me the follow error :

Compile error!
Wrong number of arguments or invalid property assignment

thanks
PJM

You are missing a closing parenthesis and a couple of other things - try this:

IIf(Len(MaskEdBox1(11).ClipText) = 0, "Null", "'#" & CDate(MaskEdBox1(11).ClipText) & "#', ")

hi I have thi result in immediate window :

? "values(" & txtfields(0) & ",'" & CDate(MaskEdBox1(10)) & "'," & IIf(Len(MaskEdBox1(11).ClipText) = 0, "Null","'#" & CDate(MaskEdBox1(11).ClipText) & "#',") & ",'" & CDate(MaskEdBox1(12)) & "', " & txtfields(13) & ")"

values(77,'11-10-2002','#29-11-2203#',,'12-10-2002', 1)

why the second date appears like that the value that i want is
11-10-2002.


Regards
PJM

What is the value for MaskEdBox1(11).ClipText ? You do not need the single quotes around the date if you use the pounds - or you can remove the pounds and leave the single quotes.

hi, rnealejr

My Maskedbox.cliptext have the [""] and with the cliptext i have the [0] value.

And the Update withe null values how can i do?

some thig like that :

m_oConn.Execute "update T_Dts_Doc_Comprador set Dt_ped_BI_Tit_Comp=" & IIf(Len(MaskEdBox1(10).ClipText) = 0, "Null", "#" & CDate(MaskEdBox1(10)) & "#") & ",Dt_prev_BI_Tit_Comp=" & IIf(Len(MaskEdBox1(11).ClipText) = 0, "Null", "#" & CDate(MaskEdBox1(11)) & "#") & ",Dt_recep_BI_Tit_Comp=" & IIf(Len(MaskEdBox1(12).ClipText) = 0, "Null", "#" & CDate(MaskEdBox1(12)) & "#") & ",N_tot_dias_BI_Tit_Comp =" & txtfields(13) & "," _
& " Dt_ped_NIF_Tit_Comp=" & IIf(Len(MaskEdBox1(10).ClipText) = 0, "Null", "#" & CDate(MaskEdBox1(14)) & "#") & ",Dt_prev_NIF_Tit_Comp=" & IIf(Len(MaskEdBox1(15).ClipText) = 0, "Null", "#" & CDate(MaskEdBox1(15)) & "#") & ",Dt_recep_NIF_Tit_Comp=" & IIf(Len(MaskEdBox1(16).ClipText) = 0, "Null", "#" & CDate(MaskEdBox1(16)) & "#") & ",N_tot_dias_NIF_Tit_Comp =" & txtfields(17) & "" _
& " where PK_Processo=" & txtfields(0)

But this code give me "type mistach" error when i leave blank maskedboxs fields.


Best regards,
PJM

Please post the entire insert statement through the immediate view, so I can see the actual state of your sql statement.

Hello Friend,
I attracted attention to lack of blank in your query. Please try your code with putting a blank between "as_BI_Tit_Comp)" and "values(" . Query should be like this

"... as_BI_Tit_Comp) values(" & txtfields(0) & ...."