SQL Statement Syntax Error

[JNariss]

Hello,

I keep getting this error:

Syntax error. in query expression '((([Submit To Production].[Request
ID]) Is Null ) AND (([Breif Description].[Brief Description])=(SELECT *
FROM Brief Description, [Brief Description], Brief Description LEFT
JOIN ON [Brief Description].[Brief Description] = [Move
Request].[Request ID])


Here's what happened...........I tried to do this all by myself!!!!
haha

I am trying to create this query which will show me Requests with a
Move Number that have not been submitted to production.

I have 3 tables which contain all the information I need but the query
is not coming out right. My 3 tables are: Move Request, Submit To
Production and Request.

Your probably asking then why does my sql statement have Brief
Description in it?? Well this is because the first time I tried the
statement it told me to create a query which performs the first join
and then inlclude that query in my statement. The query I created is
called Brief Description. It contains the tables Request and Move
Request with the fields Request ID and Brief Description from the
Request table and Move # and Request ID from the Move Request table.

Back to my query that is not working......................

When I go to create a new query what I want comes out using only two
tables:

1.Table Move Request with fields Request ID, Move #, Analyst Comment
and Analyst Name
2.Table Submit To Production with only one field Request ID - then I
have this set to NOT show when the query is ran with criteria as Is
Null.

However when I add the third field of Brief Description and use my SQL
statement it fails. I am trying to add the field Brief Description from
my table Request so managers who need the report can see the
description of the record. I have tried to add the Request table to the
query and use its Brief Description field but that does not work
either. (OR it will work but my criteria is incorrect)

Can someone help me to create this query??? I am very stuck.

Thanks,
Justine

 

[Michel Walsh]

Hi,



FieldName = (SELECT * FROM ... )


is likely an error. The SELECT * pumps multiple fields, how can it be
compared to a single one? It probably pumps multiple records too, which one
would be use to compare? ANY, ALL,... you have to tell it. Instead of
=ANY( ... ) you can use IN( ... ).


Well, that is far from being the only problem. Another one is that there are
much more "(" than there are ")" .



Hoping it may help,
Vanderghast, Access MVP

 

[JNariss]

I tried something new with this code:

("SELECT * FROM [Submit To Production] WHERE [Request ID] " & " = " &
[Move Request]![Request ID])

The error I receive is: Type Mismatch.

Almost everything I need to make this happen is working. I read up on
creating a join query so I created a new query with two tables: Request
and Approval. Using fields Request ID, Name, Date, Brief Description
(from Request) and Request ID (from Approval). This shows me another
report I am using so it was easy for me to add to it b/c it already
contains the ID # and Breif Description columns that I need. So I
copied and renamed that query as the new one I needed and added two
more tables to it: Submit To Production and Move Request using fields:
Request ID (from Submit To Production) and Move #, Comment, and Analyst
(from Move Request). I took away the fields from the Approval table but
kept the fields from the Request table b/c it contains data I need.

When I run the query it works.........but it shows every single record.
So then I pasted the above sql statement into the criteria and I
recieve the type mismatch error.

I feel I could be onto something new here............and possibly
almost completed this.............but I am stuck once again..

Any ideas??

Thanks,
Justine

 

[JNariss]

YEAH..........I FIGURED IT OUT!!!!

Thanks,
Justine