By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

convert QBE SQL statement to SQL for VBA

P: 1
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
Oct 18 '06 #1
Share this Question
Share on Google+
4 Replies


Expert 5K+
P: 8,434
...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...
Expand|Select|Wrap|Line Numbers
  1. WHEN [field_1] Is Not NullWHERE [Field_2] IN
Also, is "WHEN" a valid SQL clause?
Oct 19 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
"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
Oct 20 '06 #3

Expert 5K+
P: 8,434
"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?
Expand|Select|Wrap|Line Numbers
  1. "SELECT IIf(IsNull([table1].[field_1]),'Val is false','Val is true') " & _
  2. "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.
Oct 20 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Expand|Select|Wrap|Line Numbers
  1. "SELECT IIf(IsNull([table1].[field_1]),'Val is false','Val is true') " & _
  2. "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.
Oct 20 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.