Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Question Unanswered: Problems with null values

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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 ...

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  4. #4
    Join Date
    Dec 2002
    Posts
    5
    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

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    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

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  7. #7
    Join Date
    Dec 2002
    Posts
    5
    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

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    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) & "#', ")

  9. #9
    Join Date
    Dec 2002
    Posts
    5
    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

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  11. #11
    Join Date
    Dec 2002
    Posts
    5
    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

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    Please post the entire insert statement through the immediate view, so I can see the actual state of your sql statement.

  13. #13
    Join Date
    Jan 2003
    Location
    Turkey
    Posts
    2

    put a blank to your query

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •