Solved

Sub Select in MS Access 2002 not working

Posted on 2003-12-02
7
720 Views
Last Modified: 2008-02-01
I am trying to create a sub select in access 2002 to work in a form.  This is the code I am currently using, but access keeps telling me the sub select is invalid.

SELECT TblMediaExport.ord_confirm_date
FROM ([SELECT Table_and_Query_Lookup.TblMediaExport FROM Table_and_Query_Lookup WHERE Active = True] AS TblMediaExport)
WHERE ([TblMediaExport].[ord_id])>0;
0
Comment
Question by:Dalexan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Expert Comment

by:morpheus30
ID: 9861006
SELECT TblMediaExport.ord_confirm_date, ([SELECT Table_and_Query_Lookup.TblMediaExport FROM Table_and_Query_Lookup WHERE Active = True]) AS TblMediaExport
FROM TblMediaExport
WHERE ([TblMediaExport].[ord_id])>0;
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9861028
That subselect has no field outputs unless tblMediaExport is a field name, which still leaves that it is not the field used in the main select...

There is bracketing in the subselect that I'm not sure belongs there also.
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9861097
Dalexan

You cannot reference anything from a sub if it's not defined (ord_id, ord_confirm_date).

Why do you want to run a sub on this ? It's simple enough, - you only have the table/query Table_and_Query_Lookup as input ? To me it looks like:

SELECT ord_confirm_date
FROM Table_and_Query_Lookup
WHERE ((ord_id>0) AND  (Active = True));



Regards,
Sven
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 32

Expert Comment

by:jadedata
ID: 9861155
and then there's that....
0
 

Author Comment

by:Dalexan
ID: 9861260
This is the logic of what I am trying to do,

1.)this query will return a table name;

SELECT Table_and_Query_Lookup.TblMediaExport FROM Table_and_Query_Lookup WHERE Active = True

2.)this query has the above query as a subselect within, I need to use that value returned by the subselect in the main select to reference a field within the subselects returned table name.

This is difficult to explain, further explanation may be necessary!
Thanks for your help though.
0
 
LVL 32

Accepted Solution

by:
jadedata earned 50 total points
ID: 9861486
You are going to need to write the sql for the main query out in VBA before running it.  You can not force a field value directly into a query syntax in this manner.


  dim ssql as string
  dim stablename as string
  stablename = dlookup("tblMediaExport","Table_and_Query_Lookup","([Active] = true)")
  ssql = "SELECT ord_confirm_date "
  ssql = ssql & "FROM [" & stablename & "] " 
  ssql = ssql & "WHERE (ord_id>0);


then assign the ssql variable VALUE to a recordsouce or rowsource property somewhere.

0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9862007
OK,

This is farfetched, but if you're hooked on SQL then try this (no guarantees;-):

SELECT DLookUp("ord_confirm_date",SubXX.tblXX,"[" & SubXX.TblXX & "].[ord_id])>0") AS ReturnValue
FROM (SELECT Table_and_Query_Lookup.TblMediaExport AS tblXX FROM Table_and_Query_Lookup WHERE Active = True) AS SubXX;

Sven
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question