Select syntax, whether to use a query table or recreate the join

[JDRoss]

I have a situation where I keep getting a syntax error for a Select statement.

My questions is.

Can I use select in VBA Code with a query I have already built for two related tables or should I start from scratch using the join?

Here is the code copied from the SQL View of the Query:

SELECT DISTINCTROW * FROM Parishoners INNER JOIN [Monthly Contribution] ON Parishoners.ParishonersID = [Monthly Contribution].ParishonersID;

OR

Can I use the query

SELECT * FROM [Parishoners Query] where Parishoners.ParishonersID = [Monthly Contribution].ParishonersID

In either case, can somebody show me how to parse the SELECT statement using the above.

Much appreciated

John

 

[nabiS2K]

John,

I am still not totally sure what you need, but I can tell you that you can effectively query from another saved query just as if it was a table.

If you have [Parishoners Query] saved, you can write a SQL statement in another query that pulls the results from [Parishoners Quey]. In VBA Code, specifically DAO, you could do something like the following:

Dim dbs As DAO.Database
Dim qry As DAO.QueryDef

Set dbs = CurrentDB
Set qry = CreateQueryDef("","SELECT * FROM [Parishoners Query] where Parishoners.ParishonersID = [Monthly Contribution].ParishonersID;")

In the above example, I created a temporary query which will not appear in your queries listing. You can then use OpenRecordset to set the query information to a recordset.

Let me know if you need more information, or if I was too vague. Also, let me know if need help with ADO instead of DAO.
God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[JDRoss]

Thanks Mike

I tnink that is what I was looking for.

I have never used the the CreateQueryDef function before, could you explain what the first two quotation marks are for?

Set dbs = CurrentDB
Set qry = CreateQueryDef("","SELECT * FROM [Parishoners Query] where Parishoners.ParishonersID = [Monthly Contribution].ParishonersID;")

How do I reference qry above in the openrecordset?

Regards

John

 

[nabiS2K]

The first two quotes are the name of the query. Since I left it as a zero-length string ("&quot, it creates a temporary query that is not saved to your queries list. If you place anything else in the first set of quotes, it will save it as a query in your listing.

If you decide to use a query that is saved instead of a created one, you will need to use dbs.QueryDefs("NameOfQuery&quot instead of the CreateQueryDef line.

God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)