How to write IIF() in INSERT INTO statement

[jerry28ph]

Hi Everyone,

I'd like to ask your help on how to write IIF() condition inside the INSERT INTO statement. HEre's my code:

strSQL = "INSERT INTO tab_Summary (areaOfCalls, cs_datefrom, cs_dateto, MCalls, Destin, OCalls) " & _
"VALUES ('" & Me.[Text295] & "', " & _
"'" & Me.[dfrom] & "', " & _
"'" & Me.[dto] & "', " & _
"'" & Me.[minCalls] & "', " & _
"'" & Me.[Dest] & "', " & _
"'" & Me.[Outgoing Calls subform1].Form![OutCalls] & "');"
DoCmd.RunSQL strSQL

If NumCalls is empty/null, how can I write my condition:

if minCalls = null then ' minCalls is based on the calculated value
NCalls equals to "00:00:00"
else
NCalls equals to value of NumCalls
end if

Please Im asking your help.

Thank you and more power.

REgards,
Jerry

 

[CameronM]

Since you are already using code, why don't you use the If..then..else statement you already have and assign the result to a local variable NCalls, which you can refer to in your SQL string? That is, use NCalls instead of Me.[minCalls] in your query.

Otherwise you could utilise the usual IIF syntax along the lines of

IIF(IsNull(Me.[minCalls],"00:00:00",Me.[minCalls])

 

[Banana]

Just few minor points:

1) It sounds like you're getting the values from form and updating the tables... If this is the case, then you would find it much easier and less bug-prone to just assign a default value of "00:00:00" to the textbox that holds the minutes so you don't have to check for nulls.

2) To avoid putting your database in a inconsistent state or annoying warning messages, you may find it preferable to use 'CurrentDb.Execute strSQL, dbFailOnError' rather than 'DoCmd.RunSQL strSQL'

3) IsNull() is a VBA function. If we were doing this in purely SQL fashion, we should be doing thus:

Iif(Me.[minCalls] Is Null, "00:00:00", Me.[minCalls])

HTH.

 

[jerry28ph]

Hi,

Im trying to insert the code with IIF statement inside my INSERT INTO statement, and I dont if I can do it as follows:

strSQL = "INSERT INTO tab_Summary (id_no, cs_datefrom, cs_dateto, pc_ext, pc_hunt, pc_name, in_ccec, in_totccec, in_int_ext, in_ext_caller, ttt_ccec, ttt_in_ext, ttt_ext_caller, out_all_calls, out_ttt) " & _
"VALUES ('" & Me.[Text135] & "', " & _
"'" & Me.[Text295] & "', " & _
"'" & Me.[Text297] & "', " & _
"'" & Me.[Text135] & "', " & _
"'" & Me.[Text133] & "', " & _
"'" & Me.[Combo17] & "', " & _
"'" & Me.[Qry_Total_CCEC (PC) subform].Form![Text2] & "', " & _
"'" & Me.[FrmTotalCCEC].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Direct subform1].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Queue subform].Form![Text2] & "', " & _
"'" & IIf(Me.[Total Talk Time CCEC subform] Is Null, "00:00:00", Me.[Query of Total Talk Time CCEC subform].Form![Text2]) & "', " & _
"'" & Me.[Total Talk Time (CCEC) - Direct subform].Form![Text2] & "', " & _
"'" & Me.[Total Talk Time (CCEC) - Queue subform].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Outgoing subform].Form![Text8] & "', " & _
"'" & Me.[Total Talk Time Outgoing Calls subform1].Form![Text2] & "');"
DoCmd.RunSQL strSQL


On the red tags, it gave me Runtime Error: '424' Object Required

Please help me on this to resolve, i was stuck on this.
Thanks a lot.

Regards,
Jerry

Just few minor points:

1) It sounds like you're getting the values from form and updating the tables... If this is the case, then you would find it much easier and less bug-prone to just assign a default value of "00:00:00" to the textbox that holds the minutes so you don't have to check for nulls.

2) To avoid putting your database in a inconsistent state or annoying warning messages, you may find it preferable to use 'CurrentDb.Execute strSQL, dbFailOnError' rather than 'DoCmd.RunSQL strSQL'

3) IsNull() is a VBA function. If we were doing this in purely SQL fashion, we should be doing thus:

Iif(Me.[minCalls] Is Null, "00:00:00", Me.[minCalls])

HTH.

 

[jerry28ph]

Hi CameronM,

I cc'd you my reply to Banana re my problem wth IIF: Maybe you could help me to utilize this.

******************
Hi,

Im trying to insert the code with IIF statement inside my INSERT INTO statement, and I dont if I can do it as follows:

strSQL = "INSERT INTO tab_Summary (id_no, cs_datefrom, cs_dateto, pc_ext, pc_hunt, pc_name, in_ccec, in_totccec, in_int_ext, in_ext_caller, ttt_ccec, ttt_in_ext, ttt_ext_caller, out_all_calls, out_ttt) " & _
"VALUES ('" & Me.[Text135] & "', " & _
"'" & Me.[Text295] & "', " & _
"'" & Me.[Text297] & "', " & _
"'" & Me.[Text135] & "', " & _
"'" & Me.[Text133] & "', " & _
"'" & Me.[Combo17] & "', " & _
"'" & Me.[Qry_Total_CCEC (PC) subform].Form![Text2] & "', " & _
"'" & Me.[FrmTotalCCEC].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Direct subform1].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Queue subform].Form![Text2] & "', " & _
"'" & IIf(Me.[Total Talk Time CCEC subform] Is Null, "00:00:00", Me.[Query of Total Talk Time CCEC subform].Form![Text2]) & "', " & _
"'" & Me.[Total Talk Time (CCEC) - Direct subform].Form![Text2] & "', " & _
"'" & Me.[Total Talk Time (CCEC) - Queue subform].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Outgoing subform].Form![Text8] & "', " & _
"'" & Me.[Total Talk Time Outgoing Calls subform1].Form![Text2] & "');"
DoCmd.RunSQL strSQL

On the red tags, it gave me Runtime Error: '424' Object Required

Please help me on this to resolve, i was stuck on this.
Thanks a lot.

Regards,
Jerry

******************

Since you are already using code, why don't you use the If..then..else statement you already have and assign the result to a local variable NCalls, which you can refer to in your SQL string? That is, use NCalls instead of Me.[minCalls] in your query.

Otherwise you could utilise the usual IIF syntax along the lines of

IIF(IsNull(Me.[minCalls],"00:00:00",Me.[minCalls])

 

[Banana]

The thing is that Me is not valid in SQL context. You have to be explicit in referencing your forms... Something like

Forms![MyParentForm]![MyParentFormControl]
Forms![MyParentForm]![MySubformContainer].Form![SubformControl]

HTH.

 

[jerry28ph]

Hi Banana,

I know its too much for me to ask this, maybe you have an example of a script on how to write IIF() in INSERT INTO statement. I tried to write the syntax that you gave, but still "Object required" is missing.

Here's my code:

strSQL = "INSERT INTO tab_Summary (id_no, cs_datefrom, cs_dateto, pc_ext, pc_hunt, pc_name, in_ccec, in_totccec, in_int_ext, in_ext_caller, ttt_ccec, ttt_in_ext, ttt_ext_caller, out_all_calls, out_ttt) " & _
"VALUES ('" & Me.[Text135] & "', " & _
"'" & Me.[Text295] & "', " & _
"'" & Me.[Text297] & "', " & _
"'" & Me.[Text135] & "', " & _
"'" & Me.[Text133] & "', " & _
"'" & Me.[Combo17] & "', " & _
"'" & Me.[Qry_Total_CCEC (PC) subform].Form![Text2] & "', " & _
"'" & Me.[FrmTotalCCEC].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Direct subform1].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Queue subform].Form![Text2] & "', " & _
"'" & IIf([Forms]![frmLog].[Total Talk Time CCEC subform] Is Null, "00:00:00", [Forms]![frmLog]![Total Talk Time CCEC subform].Form![Text2]) & "', " & _
"'" & Me.[Total Talk Time (CCEC) - Direct subform].Form![Text2] & "', " & _
"'" & Me.[Total Talk Time (CCEC) - Queue subform].Form![Text2] & "', " & _
"'" & Me.[Qry_Total_Outgoing subform].Form![Text8] & "', " & _
"'" & Me.[Total Talk Time Outgoing Calls subform1].Form![Text2] & "');"
DoCmd.RunSQL strSQL


Please maybe you have an example of this, I would really appreciate.
Thank you and more power.

Regards

The thing is that Me is not valid in SQL context. You have to be explicit in referencing your forms... Something like

Forms![MyParentForm]![MyParentFormControl]
Forms![MyParentForm]![MySubformContainer].Form![SubformControl]

HTH.

 

[Banana]

Sometime I find it easier to build SQL in query builder then copy it to VBA and format as necessary. Try and recreate it in query builder and use Build... to help ensure you have the correct references.

How to use Expression Builder with Query Builder

See if this helps...

 

[jerry28ph]

Hi, Im just using Access not VBA, its my first time to use access that's why im having hard times to utilize my queries. Do you have an example of code for IIF condition in INSERT INTO statement?

Maybe you have one.

Please,

Thanks

Sometime I find it easier to build SQL in query builder then copy it to VBA and format as necessary. Try and recreate it in query builder and use Build... to help ensure you have the correct references.

How to use Expression Builder with Query Builder

See if this helps...

 

[CameronM]

Sorry, only got back to the forum and saw your posts. This should be an easy fix, replace the Is Null part of your IIF with either = Null, or IsNull(value).

"'" & IIf(IsNull([Forms]![frmLog].[Total Talk Time CCEC subform]), "00:00:00", [Forms]![frmLog]![Total Talk Time CCEC subform].Form![Text2]) & "', " & _

I have just mocked up a query string using IIF and it works using of IsNull(value) rather than Is Null.

    strSQL = "INSERT INTO tblInsertValues (Field1, Field2) " & _
        "VALUES (" & Me.txtTransactionHeaderID & ",'" & IIf(IsNull(Me.frmTransactions.Form!Text2), "00:00:00", Me.frmTransactions.Form!Text2) & "')"

 

[CameronM]

As for Banana's comments, the ppint of this post is to create a SQL string in VBA and as such anything outside the actual SQL string, such " & Me.Forms![MyParentForm]![MyParentFormControl] & " is VBA code and only the results of the call are sent in the SQL string.

In the case of this post you can see what is sent via SQL by adding a debug.print strSQL line after creating the string and you will see the actual values, with no references to the [forms] objects at all.

The thing is that Me is not valid in SQL context. You have to be explicit in referencing your forms... Something like

Forms![MyParentForm]![MyParentFormControl]
Forms![MyParentForm]![MySubformContainer].Form![SubformControl]

HTH.

 

[jerry28ph]

Hi, Thank you for your post replied. Appreciated, would you mind if I'l get back to you if didn't work?

Regards,

Sorry, only got back to the forum and saw your posts. This should be an easy fix, replace the Is Null part of your IIF with either = Null, or IsNull(value).

"'" & IIf(IsNull([Forms]![frmLog].[Total Talk Time CCEC subform]), "00:00:00", [Forms]![frmLog]![Total Talk Time CCEC subform].Form![Text2]) & "', " & _

I have just mocked up a query string using IIF and it works using of IsNull(value) rather than Is Null.

    strSQL = "INSERT INTO tblInsertValues (Field1, Field2) " & _
        "VALUES (" & Me.txtTransactionHeaderID & ",'" & IIf(IsNull(Me.frmTransactions.Form!Text2), "00:00:00", Me.frmTransactions.Form!Text2) & "')"

 

[jerry28ph]

Hi CameronM,

It didn't work, when I click the command button that has the SQL string, still null value was saved. I know it should be "00:00:00".

******

'"'" & IIf(IsNull([Forms]![frmLog].[Total Talk Time CCEC subform]), "00:00:00", [Forms]![frmLog]![Total Talk Time CCEC subform].Form![Text2]) & "', " & _


Im really sorry to bother you, i know its too much for you, to ask this question again and again.

Hi, Thank you for your post replied. Appreciated, would you mind if I'l get back to you if didn't work?

Regards,

 

[CameronM]

I just noticed in your code you only have IsNull([Forms]![frmLog].[Total Talk Time CCEC subform]) - this would only refer to the subform, you need to refer to the actual textbox on the subform that you want to check the value of.

'"'" & IIf(IsNull([Forms]![frmLog].[Total Talk Time CCEC subform].Form![Text2]), "00:00:00", [Forms]![frmLog]![Total Talk Time CCEC subform].Form![Text2]) & "', " & _