convert QBE SQL statement to SQL for VBA

[ultraJeep421]

Hello

I am trying to write a SQL statement based on a SQL statement from the QBE
grid. The QBE query takes values from multiple tables.

SELECT IIf([table1].[field_1] Is Not Null, ("Val is true", "Val is false",))
As [field_A], IIf([table2].[field_1] Is Not Null, ("Val is true", "Val is
false",)) As [field_B], ([tablen].[field_n] Is Not Null, ("Val is true", "Val
is false",)) As [field_n]

FROM ((table1
INNER JOIN [table1].[table2]
LEFT JOIN [table1].[table3]
LEFT JOIN [table1].[table4]
INNER JOIN [table1].[table5]
LEFT JOIN [table1].[table6]
LEFT JOIN [table1].[table7]

I am trying to rewrite this into workable SQL that I can pass as string
using Access VBA. When I try to paste QBE SQL in Access VBA as a string I get "expected.. ." error message (Sounds like it needs a bracket to be closed). I thought about nested subqueries but I want to make sure that the joins are correct.

My nested attempt (giving syntax errors):


SELECT [field_1] AS '[field_A]' ('Val is true')" & _
"FROM [table1]" & _
"WHEN [field_1] Is Not Null" & _
"WHERE [field_2] IN" & _
"(SELECT [field_2] AS '[field_B] ('Val is true')" & _
"FROM [table2]" & _
"WHEN [field_1] Is Not Null" & _
"WHERE [field_3] IN" & _
"(SELECT [field_n] AS '[field_n] ('Val is true')" & _
"FROM [tablen]" & _
"WHEN [field_n] Is Not Null" & _
"WHERE [field_5] IN" & _


How can I do this please?

Thanks

[Killer42]

...My nested attempt (giving syntax errors):

SELECT [field_1] AS '[field_A]' ('Val is true')" & _
"FROM [table1]" & _
"WHEN [field_1] Is Not Null" & _
"WHERE [field_2] IN" & _
...

I'm not that fluent in SQL, but in the example above I believe you are leaving out all the spaces between clauses (easy to miss when concatenating strings). Try displaying the actual string your code builds. To cut out a brief excerpt, I think you'll get things like...

WHEN [field_1] Is Not [b]NullWHERE[/b] [Field_2] IN

Also, is "WHEN" a valid SQL clause?

[MMcCarthy]

"SELECT IIf(Not IsNull([table1].[field_1]),'Val is true','Val is false') " & _
"As [field_A], IIf(Not IsNull([table2].[field_1]),'Val is true','Val is false') " & _
"As [field_B], IIf(Not IsNull([tablen].[field_n]),'Val is true','Val is false') " & _
"As [field_n] " & _
"FROM (table1 INNER JOIN " & _
"([table2] LEFT JOIN " & _
"([table3] LEFT JOIN " & _
"([table4] INNER JOIN " & _
"([table5] LEFT JOIN " & _
"([table6] LEFT JOIN [table7] " & _
"ON [table5].[ID]=[table6].[ID]) " & _
"ON [table4].[ID]=[table5].[ID]) " & _
"ON [table3].[ID]=[table4].[ID]) " & _
"ON [table2].[ID]=[table3].[ID]) " & _
"ON [table1].[ID]=[table2].[ID]);

You need something like this.

All you tables must be joined on a unique value common to both tables.
Use single quotes on anything inside the statement
don't bracket the second part of the IIf statement

[Killer42]

"SELECT IIf(Not IsNull([table1].[field_1]),'Val is true','Val is false') " & _
"As [field_A], IIf(Not IsNull([table2].[field_1]),'Val is true','Val is false') " & _
...

Quick question - if you are coding an IIF() and setting values for both True and False parts, why on Earth would you code the "Not" in there? Surely it serves no purpose but to make the string longer. In other words, wouldn't this have the same effect?

"SELECT IIf(IsNull([table1].[field_1]),'Val is false','Val is true') " & _
"As [field_A], IIf(IsNull([table2].[field_1]),'Val is false','Val is true') " & _

I realise the "not logic" was in the original code, just want to check that I'm not missing something here.

[MMcCarthy]

You're right of course.

Quick question - if you are coding an IIF() and setting values for both True and False parts, why on Earth would you code the "Not" in there? Surely it serves no purpose but to make the string longer. In other words, wouldn't this have the same effect?

"SELECT IIf(IsNull([table1].[field_1]),'Val is false','Val is true') " & _
"As [field_A], IIf(IsNull([table2].[field_1]),'Val is false','Val is true') " & _

I realise the "not logic" was in the original code, just want to check that I'm not missing something here.