Select Query IIF Not testing column value

[AlexB123]

Hi all,

I have a query that I use to export a text file for another database. I am currently testing changes, and need the same output, but formatted into a series of "Insert Into" statements to be able to develop. So I wrote another query to produce a fully formatted text string for each of the four "TempTbl's". The query pulls the correct number of rows, and all the correct information, EXCEPT for each value of the TempTbl. I only return the final temp table in the last nested "else" part of IIF (i.e. "#TempW ").

How can I get the query to insert "#Temp4 ", "#Temp5 ", etc.?

Many thanks!

SELECT (IIf([tmpTbl]="[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL] ","INSERT INTO [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL]  VALUES ('[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL] ','"&[CHKAccount]&"','"&[RequestID]&"','"&[PlanID]&"','"&[Payroll]&"','"&[SSN]&"','"&[AMT]&"','"&[PayrollAMT]&"','"&[SOURCE]&"','"&[DBID]&"')",IIf([tmpTbl]="[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp5]#Temp5[/URL] ","INSERT INTO [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp5]#Temp5[/URL]  VALUES ('[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp5]#Temp5[/URL] ','"&[CHKAccount]&"','"&[RequestID]&"','"&[PlanID]&"','"&[Payroll]&"','"&[SSN]&"','"&[AMT]&"','"&[PayrollAMT]&"','"&[SOURCE]&"','"&[DBID]&"')",IIf([tmpTbl]="[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TempG]#TempG[/URL] ","INSERT INTO [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TempG]#TempG[/URL]  VALUES ('[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TempG]#TempG[/URL] ','"&[CHKAccount]&"','"&[RequestID]&"','"&[PlanID]&"','"&[Payroll]&"','"&[SSN]&"','"&[AMT]&"','"&[PayrollAMT]&"','"&[SOURCE]&"','"&[DBID]&"')","INSERT INTO [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TempW]#TempW[/URL]  VALUES ('[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TempW]#TempW[/URL] ','"&[CHKAccount]&"','"&[RequestID]&"','"&[PlanID]&"','"&[Payroll]&"','"&[SSN]&"','"&[AMT]&"','"&[PayrollAMT]&"','"&[SOURCE]&"','"&[DBID]&"')")))) AS SQLstring
FROM qryFormattedToast2;

 

[stumac]

As far as I know this can't be done using SQL in Access - can be done in VBA, however, it is not clear what tells the query which temp table to use...i.e. what is [tmpTbl]

 

[xenou]

Also note that this query has #Temp4 hard coded in so I think it will *always* be an "insert into #Temp4 ..."

SELECT (IIf([tmpTbl]="[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL]  ","INSERT INTO [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL]   VALUES ('[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL]  ','"&...

You need that part of the result to also be dynamic (a variable).
Possibly something like:

SELECT (IIf([tmpTbl]="[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL]  ","INSERT INTO " & [B][TmpTbl][/B] & " VALUES ('" & [B][TmpTbl][/B] & "','"&...

 

[james_lankford]

Also note that this query has #Temp4 hard coded in so I think it will *always* be an "insert into #Temp4 ..."

SELECT (IIf([tmpTbl]="[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4"]#Temp4[/URL]  ","INSERT INTO [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4"]#Temp4[/URL]   VALUES ('[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4"]#Temp4[/URL]  ','"&...

You need that part of the result to also be dynamic (a variable).
Possibly something like:

SELECT (IIf([tmpTbl]="[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4"]#Temp4[/URL]  ","INSERT INTO " & [B][TmpTbl][/B] & " VALUES ('" & [B][TmpTbl][/B] & "','"&...

look at the rest of the string
the nested iifs go on forever LOL

 

[xenou]

oh okay. Hmmm not sure then.

Note: perhaps a little bit of sample data would make it clearer.

 

[AlexB123]

Ha ha ha ... James_Lankford ... they are pretty nasty Iif's.

I attempted to single down to just a single 'tmpTbl', using a WHERE condition. It didn't work ...

SELECT ("INSERT INTO " & [#Temp4 ] & " VALUES('" & [#Temp4 ] & "','"&[CHKAccount]&"','"&[RequestID]&"','"&[PlanID]&"','"&[Payroll]&"','"&[SSN]&"','"&[AMT]&"','"&[PayrollAMT]&"','"&[SOURCE]&"','"&[DBID]&"')") AS SQLstring
FROM qryFormattedToast2
WHERE tmpTbl = "#Temp4 ";

Is it possible for it to work in a single Select statement like this? If I were referencing a permanent table instead of a query, would that change what is returned? Is my query attempting to pull in too many columns on each iteration?

Here is some sample data... (I took out some of the variables for size)

[tempTbl] [CHKAccount] [RequestID] [PlanID] [SSN] [AMT] [DBID]
#Temp4 1000000001 042020180001 00000ABC 000-00-0000 10.00 0001
#Temp5 1000000001 042020180002 00000DEF 111-11-1111 20.00 0002
#TempG 1000000001 00000STU 222-22-2222 30.00 0003
#TempW 1000000001 00000XYZ 333-33-3333 40.00 0004

 

[AlexB123]

I changed the base query into a Make Table query, and then ran the following:

SELECT ("INSERT INTO " & [tempTbl] & " VALUES('" & [tempTbl] & "','"&[CHKAccount]&"','"&[RequestID]&"','"&[PlanID]&"','"&[Payroll]&"','"&[SSN]&"','"&[AMT]&"','"&[PayrollAMT]&"','"&[SOURCE]&"','"&[DBID]&"')") AS SQLstring
FROM tblFormattedToast2;

I need to verify, but it seems to have worked. Why would a permanent table work, but not another query? I don't want to add tables to my database ...

 

[xenou]

Back to Post #6 this worked for me as a quick rewrite:

SELECT ("INSERT INTO " & [TempTbl ] & " VALUES('" & [TempTbl] & "','"&[CHKAccount]&"')") AS SQLstring
FROM qryFormattedToast2
WHERE tempTbl = "[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL]  ";

qryTempTable returns the results:

[tempTbl] [CHKAccount] [RequestID] [PlanID] [SSN] [AMT] [DBID] 
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL]  1000000001 042020180001 00000ABC 000-00-0000 10.00 0001
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp5]#Temp5[/URL]  1000000001 042020180002 00000DEF 111-11-1111 20.00 0002
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TempG]#TempG[/URL]  1000000001 00000STU 222-22-2222 30.00 0003
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TempW]#TempW[/URL]  1000000001 00000XYZ 333-33-3333 40.00 0004

Result of the query:

INSERT INTO [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL]  VALUES('[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Temp4]#Temp4[/URL] ','1000000001')

Note that TmpTbl became TempTbl in this version of the data (probably by mistake but I stayed with what was given).

It seems like there is ongoing confusion between the name of the column (TmpTbl or TempTbl), which is constant, and the values of the column (#Temp4 , etc.) which is variable, and also how to use these in the query. It's a little extra confusing also because the query produces a query string as a result - however, from the point of view of running this query, it's just a string like any other.


Note that I apologize but I am ignoring post 7 for new. I'm not sure what you are doing with make table queries (the query is not a make-table query and it isn't using a make table query).

 

[AlexB123]

Xenou,

Thank you. You are correct, my post seven was not a make-table query, although I did end up making a table to get my code to work. However, following your response, I carefully checked and updated my queries, and all work perfectly ( and without the need to make an extra table).

 

[xenou]

Okay - great that it's working now.