SQL Statement in VB

R

Rob

I'm trying to run some conditions for a SQL statement for
a line chart. I have 1 plant that I would always want to
see the info for and 4 other plants as options, which are
referred to as cboPlantCompare1 - cboPlantCompare4. I
know that my general statement worked before I started
breaking it up into conditions. Here's what I have so far
(sorry if the lines don't wrap correctly on this form- you
can copy and paste into VB and it should be right)...

OLEUnbound0.RowSource = "SELECT tblInvoicing.Month,
([Production]/[GasConsumption]) AS [Prod Vs Cons]" & _
IIf(Not IsNull(Forms!Utilities!cboPlantCompare1) Or Not
IsNull(Forms!Utilities!cboPlantCompare2) Or Not IsNull
(Forms!Utilities!cboPlantCompare3) Or Not IsNull(Forms!
Utilities!cboPlantCompare4), ", ", "") & _
IIf(Not IsNull(Forms!Utilities!
cboPlantCompare1), "([Plant1]![SumOfProduction]/[Plant1]!
[Plant1Gas]) AS " & Forms!Utilities!cboPlantCompare1
& ", ", "") & _
IIf(Not IsNull(Forms!Utilities!
cboPlantCompare2), "([Plant2]![SumOfProduction]/[Plant2]!
[Plant2Gas]) AS " & Forms!Utilities!cboPlantCompare2
& ", ", "") & _
IIf(Not IsNull(Forms!Utilities!
cboPlantCompare3), "([Plant3]![SumOfProduction]/[Plant3]!
[Plant3Gas]) AS " & Forms!Utilities!cboPlantCompare3
& ", ", "") & _
IIf(Not IsNull(Forms!Utilities!
cboPlantCompare4), "([Plant4]![SumOfProduction]/[Plant4]!
[Plant4Gas]) AS " & Forms!Utilities!cboPlantCompare4
& " ", "") & _
"FROM " & _
IIf(Not IsNull(Forms!Utilities!cboPlantCompare1), "(", "")
& _
IIf(Not IsNull(Forms!Utilities!cboPlantCompare1), "(", "")
& _
IIf(Not IsNull(Forms!Utilities!cboPlantCompare1), "(", "")
& _
"tblInvoicing " & _
IIf(Not IsNull(Forms!Utilities!cboPlantCompare1), "INNER
JOIN Plant1 ON tblInvoicing.Month = Plant1.Month)", "") & _
IIf(Not IsNull(Forms!Utilities!cboPlantCompare2), "INNER
JOIN Plant2 ON tblInvoicing.Month = Plant2.Month)", "") & _
IIf(Not IsNull(Forms!Utilities!cboPlantCompare3), "INNER
JOIN Plant3 ON tblInvoicing.Month = Plant3.Month)", "") & _
IIf(Not IsNull(Forms!Utilities!cboPlantCompare4), "INNER
JOIN Plant4 ON tblInvoicing.Month = Plant4.Month", "") & _
"WHERE (((tblInvoicing.PlantID)=[Forms]![Utilities]!
[Combo2])); "
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help in a complex iif statement 2
Reference SQL in Access VB 5
Dynamic RecordSet Field Name 4
SQL ORDER BY 1
Like IIf statement that needs results to work with even getting bl 9
Pass a control.value to SQL statement?? 4
Filtering a Crosstab Query from Combo Boxes on Unbound Form 9
TransferSpreadsheet/TransferText: SQL Size Limit? 3

Top