rewrite QBE SQL statement

[Guest]

Hi

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. I thought about nested subqueries but I want to make sure
that the joins are correct. How can I do this please?

Thanks

 

[David F Cox]

If the SQL will work in the QBE grid it will work when passed as a VBA
string. I would get the query to work in QBE, switch to SQL view and and
paste that into Notepad.

The IIF function has the syntax IIF(test, value_if_true, value_if_ false)
e.g.
IIf(([Field1] Is Null),"Val is False","Val is true")

 

[Guest]

Thanks for answering. 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).

If the SQL will work in the QBE grid it will work when passed as a VBA
string. I would get the query to work in QBE, switch to SQL view and and
paste that into Notepad.

The IIF function has the syntax IIF(test, value_if_true, value_if_ false)
e.g.
IIf(([Field1] Is Null),"Val is False","Val is true")

Hi

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. I thought about nested subqueries but I want to make
sure
that the joins are correct. How can I do this please?

Thanks

 

[David F Cox]

My apologies, I have been responding to questions in these groups too
quickly, and not giving enough detail.

this looks like the wrong syntax to me.
SELECT IIf([table1].[field_1] Is Not Null, ("Val is true", "Val is false",))

I think it should be:
SELECT IIf([table1].[field_1] Is Not Null, "Val is true", "Val is false")

and I would remove the "Not" and swap results for clarity (to my eyes)
SELECT IIf([table1].[field_1] Is Null, "Val is false", "Val is true")

Thanks for answering. 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).

If the SQL will work in the QBE grid it will work when passed as a VBA
string. I would get the query to work in QBE, switch to SQL view and and
paste that into Notepad.

The IIF function has the syntax IIF(test, value_if_true, value_if_ false)
e.g.
IIf(([Field1] Is Null),"Val is False","Val is true")

Hi

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. I thought about nested subqueries but I want to make
sure
that the joins are correct. How can I do this please?

Thanks

 

[Guest]

Hi Thanks for Answering,

I am now getting a syntax error that tells me that I am missing an operator
somewhere. Is there a way to rewrite this as nested subqueries in such a way
that would recognise the 1:1, 1:n relationships between tables like the
"INNER JOIN", "LEFT JOIN" words do?

My present nested attempt (gives syntax error - "in SELECT statement"):

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" & _

Thanks for Your help

My apologies, I have been responding to questions in these groups too
quickly, and not giving enough detail.

this looks like the wrong syntax to me.
SELECT IIf([table1].[field_1] Is Not Null, ("Val is true", "Val is false",))

I think it should be:
SELECT IIf([table1].[field_1] Is Not Null, "Val is true", "Val is false")

and I would remove the "Not" and swap results for clarity (to my eyes)
SELECT IIf([table1].[field_1] Is Null, "Val is false", "Val is true")

Thanks for answering. 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).

If the SQL will work in the QBE grid it will work when passed as a VBA
string. I would get the query to work in QBE, switch to SQL view and and
paste that into Notepad.

The IIF function has the syntax IIF(test, value_if_true, value_if_ false)
e.g.
IIf(([Field1] Is Null),"Val is False","Val is true")


Hi

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. I thought about nested subqueries but I want to make
sure
that the joins are correct. How can I do this please?

Thanks