Solved

Sub Select in MS Access 2002 not working

Posted on 2003-12-02
7
677 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Report that will show table changes 7 44
Sub Reports 8 21
MS Access Tables Linking 6 40
Access 2010 Query Syntax 5 18
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now