error in SQL statement

[xerife007]

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) & &quot"

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

 

[johnwm]

What datatype is the "Null" going in to? You are actually posting the string "Null", so if it's a numeric field you will get type mismatch.
If you want to post a Null value, just remove quote marks.

....IIf(Len(MaskEdBox1(11).ClipText) = 0, Null, MaskEdBox1(11).ClipText)....
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[xerife007]

Hi, what i have in maskedbox that i want to put Null value is a date\time field.

I must have this value [""] in the maskedbox to put null value?

thanks

PJM

 

[johnwm]

Did you try what I suggested? If so, then:

Make sure that your SQL database field is set to Nullable

In Access set Required property to No
In SQL set Nullable in SQL Server Enterprise Manager, Table properties
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[xerife007]

Yes,

I work with access2000 and I already check the required field to "No",I already run in query sql in accsess one comand that confir the Null value in the record but my problem is in condition is the first time i use this IIF()
and doesn't work. there is some thing rong with the command?
I dont understand why it must be .cliptext in property and must be =0 if I have in maskedbox(11) the [""] value.

thanks
PJM

 

[johnwm]

The .cliptext property just removes any literal characters in your mask.

The test you are doing is for
....IIf(Len(MaskEdBox1(11).ClipText) = 0, Null, MaskEdBox1(11).ClipText)..

Len returns the length of the string.

If your mask is ##-##-#### and nothing is typed in then:
If clipmode is set to mskIncludeLiterals
MaskedBox1.text will return "__-__-____"
MaskedBox1.ClipText will return ""
and Len(MaskedBox1.ClipText) will return 0
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'