IIF statemen in SQL syntax- VBA code

[Guest]

I am attempting to run SQL code which I have asssigned to a variable in VBA
environment.

Example :

Function MappingTable ()
Dim X as String

DIm Y as String

X = "SELECT A.ID ,A.CustName,A.OrderID,A.Amount ,IIF(...(IIF(...(IIF...)))
as OutputAnlaysis FROM PseudoTable;"

CurrentDB.CreatequeryDef "Analysis" , X

Y = "Select * INTO MappingTable;"


Docmd.RunSQL(Y)


End Function

=============

When I attempt to run code like above ,with the nested IIF statement ,the
function procedure doesn't work.

I then subsequently assigned the nested IIF statement into a string/variant
variable and concatenated it to the string variable with the rest on the SQL
code - no joy !

Can someone please help me with a solution or workaround?




Regards

SysAccountant

 

[John Vinson]

CurrentDB.CreatequeryDef "Analysis" , X

Y = "Select * INTO MappingTable;"


Docmd.RunSQL(Y)

If this is your actual code, you're not using the Analysis query at
all. What is Y supposed to do? It's an invalid query - you're not
selecting anything from anywhere! Might this be the error?

Not knowing what your nested IIF's are doing, it's hard to advise -
but often a nested IIF can be replaced by the Switch() function. Or
you should be able to build a SQL string by concatenating string
constants ("SELECT foo FROM bar WHERE [ID]=") and variables into a
valid SQL string. Use the debugger to make sure you're not missing
blanks and that the syntax is correct.

John W. Vinson[MVP]

 

[Guest]

John


Sorry about that - apparently I made a typo.

Also in the IIF statement I can't attached an alias name to IIF segment of
the SQL script using the keyword " AS" - VBA doesn't apper to like it.


Please find attached the problematic code:


Function MappingOfCustomerOrderMovements()


On Error GoTo Err_handler



Dim sqlMappingCode_trial As String
Dim sqlCustomerAnalysis As String



sqlMappingCode_trial = "SELECT A.LockDown, ""'" & (IIf(CodeReference Like
"xyz*", (IIf(IsNumeric(Right((Trim(Mid(A.CodeReference, 5, 9))), 1)),
(Trim(Mid(A.CodeReference, 5, 9))), Left((Trim(Mid(A.CodeReference, 5, 9))),
8))), A.CodeReference)) & "'" _
& " " & ", A.CodeReference, A.TransactionNo, A.Date, A.Reason, B.RECEIPT
" _
& " " & "FROM (Customer A LEFT JOIN OrderMovements B ON A.CodeReference =
B.OrderRef) LEFT JOIN Products C ON A.CodeReference = B.ProductCode;"






CurrentDb.CreateQueryDef "qryCustomer_And_Product_Movements",
sqlMappingCode_trial


sqlCustomerAnalysis = "SELECT DISTINCT * INTO
tblCustomerProfitablityAnalysis FROM qryCustomer_And_Product_Movements "




With DoCmd

.SetWarnings (False)

.RunSQL (sqlCustomerAnalysis)

.SetWarnings (True)

End With



Exit Function

Err_handler:

MsgBox "Error Description : " & Err.Description & vbCrLf + vbCrLf &
"Error code : " & Err.Number




End Function

Thanks for your help in advance.

sysAccountant

CurrentDB.CreatequeryDef "Analysis" , X

Y = "Select * INTO MappingTable;"


Docmd.RunSQL(Y)

If this is your actual code, you're not using the Analysis query at
all. What is Y supposed to do? It's an invalid query - you're not
selecting anything from anywhere! Might this be the error?

Not knowing what your nested IIF's are doing, it's hard to advise -
but often a nested IIF can be replaced by the Switch() function. Or
you should be able to build a SQL string by concatenating string
constants ("SELECT foo FROM bar WHERE [ID]=") and variables into a
valid SQL string. Use the debugger to make sure you're not missing
blanks and that the syntax is correct.

John W. Vinson[MVP]