Re: rewrite QBE SQL statement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"David F Cox" wrote:

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")


"Intrepid4901" <Intrepid4901@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F114BCFD-83A1-426A-84A3-287C4321B3AF@xxxxxxxxxxxxxxxx
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).

"David F Cox" wrote:

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")


"Intrepid4901" <Intrepid4901@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3D6344DA-65DE-472E-B562-232376F640A0@xxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: Syntax error in FROM clause
    ... It appears that the maximum length of the SQL statement has been reached. ... "George wilson" wrote in message ... >><MS ACCESS MVP> ... >>> a "Syntax error in FROM clause" and is unable to view ...
    (microsoft.public.access.forms)
  • Re: INSERT command doesnt work in Access
    ... that OleDbException, that says "Syntax error in SQL statement". ... Lately I've tried simple OleDbCommand, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Syntax error in INSERT INTO statement.
    ... > I got syntax error in INSERT INTO statement. ... Run the page and look at the sql statement in the browser window. ... This email account is my spam trap so I ...
    (microsoft.public.scripting.vbscript)
  • Re: Change table date/time field to text field in code
    ... That means your SQL statement is not correct. ... > When I try this method, I am getting error 3293 Syntax error in ALTER ... >> Dim strSql As String ...
    (comp.databases.ms-access)
  • Re: Combo Box Type Mismatch
    ... I suggest taking this SQL into the query builder and checking it there. ... Here is the sql statement: ... > Syntax error in query expression '[SELECT DISTINCTROW ...
    (microsoft.public.access.gettingstarted)