SQL in VBA with a acess query in from

[tebule]

I am trying to do something new in my query to make it run faster and I hope simpler.

I am first: Deleting out the data from my temp table:
CurrentDb.Execute "DELETE * FROM tblTemp_SG"

Second I am appending records based off of a qry into my table that I just sweeped.



strSQL = "SELECT ACCOUNTING_METHOD, ACCOUNT_NAME_FIRST, ACCOUNT_NAME_LAST, " & _
"ACCOUNT_TYPE, ACCOUNT_NUMBER, AS_OF_DATE, CUSIP_NUMBER, " & _
"HOLDINGS_TYPE, REGISTERED_UNITS, SHORT_NAME, TICKER_SYMBOL, " & _
"SEC_TYPE, SHORT_NAME INTO tblTemp_SG2 " & _
"FROM qrySG1" & _
"Where ((AS_OF_DATE)= #" & dtedteEntered & "#);"

Extra information:
The data in the dtedteEntered field is off of a form and I had to have it in code form to get it to work.

The sql view of the access query qrySG1 is as follows:

SELECT Addv_HOLDINGS.ACCOUNT_NAME_FIRST, Addv_HOLDINGS.ACCOUNT_NAME_LAST, Addv_HOLDINGS.ACCOUNT_NUMBER, Addv_HOLDINGS.CUSIP_NUMBER, Addv_HOLDINGS.HOLDINGS_TYPE, Max(Addv_HOLDINGS.REGISTERED_UNITS) AS MaxOfREGISTERED_UNITS, Addv_HOLDINGS.SHORT_NAME, Addv_HOLDINGS.TICKER_SYMBOL, Addv_HOLDINGS.SEC_TYPE, Addv_HOLDINGS.AS_OF_DATE
FROM Addv_HOLDINGS
GROUP BY Addv_HOLDINGS.ACCOUNT_NAME_FIRST, Addv_HOLDINGS.ACCOUNT_NAME_LAST, Addv_HOLDINGS.ACCOUNT_NUMBER, Addv_HOLDINGS.CUSIP_NUMBER, Addv_HOLDINGS.HOLDINGS_TYPE, Addv_HOLDINGS.SHORT_NAME, Addv_HOLDINGS.TICKER_SYMBOL, Addv_HOLDINGS.SEC_TYPE, Addv_HOLDINGS.AS_OF_DATE, Addv_HOLDINGS.ACCOUNTING_METHOD, Addv_HOLDINGS.ACCOUNT_TYPE
HAVING (((Addv_HOLDINGS.ACCOUNTING_METHOD)="o") AND ((Addv_HOLDINGS.ACCOUNT_TYPE)=5555))


The problem. When I run my strSQL query, I receive popups that ask me what the accounting _method value is. Which are in the query referenced in the From line. Why is it doing that? Do I need a query def or something? Thanks in advance for the help.

 

[pbaldy]

ACCOUNTING_METHOD is not in the SELECT clause of the query, which I think it needs to be in order to be seen by the next query.

 

[tebule]

ACCOUNTING_METHOD is in the query that is in the from statement. I am using a specific search string of "o" in that query. Since it should all be the same value in that column I didn't want to include it again in my second query.

Can you please help me understand why it is needed?

 

[tebule]

I see what you are talking about now. I am trying that.

 

[tebule]

This worked perfectly. Thank you for the second set of eyes.

 

[pbaldy]

No problem; we all go blind at times, and miss things like that.