How to write IIF() in INSERT INTO statement

J

jerry28ph

jerry
Local time
Today, 05:14
Joined
Nov 16, 2008
Messages
141
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
 
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])
 
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:
Code: 
Iif(Me.[minCalls] Is Null, "00:00:00", Me.[minCalls])

HTH.
 
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



Banana said:
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:
Code: 
Iif(Me.[minCalls] Is Null, "00:00:00", Me.[minCalls])

HTH.
Click to expand...
 
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

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



CameronM said:
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])
Click to expand...
 
The thing is that Me is not valid in SQL context. You have to be explicit in referencing your forms... Something like

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

HTH.
 
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



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

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

HTH.
Click to expand...
 
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...
 
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




Banana said:
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...
Click to expand...
 
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).


Code: 
"'" & 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.

Code: 
    strSQL = "INSERT INTO tblInsertValues (Field1, Field2) " & _
        "VALUES (" & Me.txtTransactionHeaderID & ",'" & IIf(IsNull(Me.frmTransactions.Form!Text2), "00:00:00", Me.frmTransactions.Form!Text2) & "')"
 
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.

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

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

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

Regards,



CameronM said:
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).


Code: 
"'" & 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.

Code: 
    strSQL = "INSERT INTO tblInsertValues (Field1, Field2) " & _
        "VALUES (" & Me.txtTransactionHeaderID & ",'" & IIf(IsNull(Me.frmTransactions.Form!Text2), "00:00:00", Me.frmTransactions.Form!Text2) & "')"
Click to expand...
 
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.

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

Regards,
Click to expand...
 
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.

Code: 
'"'" & IIf(IsNull([Forms]![frmLog].[Total Talk Time CCEC subform].Form![Text2]), "00:00:00", [Forms]![frmLog]![Total Talk Time CCEC subform].Form![Text2]) & "', " & _
 
You must log in or register to reply here.

Similar threads

S
How to insert a new row into a dynaset after normalising underlying tables
Replies
11
Views
722
The_Doc_Man
The_Doc_Man
brucemc777
Solved Take three user selected fields and use an INSERT INTO statement to create a new record in a table that only has those three field AND a Key
Replies
11
Views
549
Pat Hartman
P
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,961
Cotswold
Cotswold
J
How to insert attachment in form with VBA
Replies
10
Views
2,878
Jerme24
J
S
How to use INSERT statement with unique primary key values
Replies
12
Views
1,541
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom