Queries and SQl with DAO - VBA

[josekreif]

I'm using DAO to open a query, something like

Set rst = db.OpenRecordset("duplicatesForAr")

When I do that, does Access just use data from the Query, or does it actually execute the Query's SQL? That query contains the code

SELECT Ar.[Invoice No], Ar.Customer, Ar.[Invoiced Date]
FROM Ar
WHERE (((Ar.[Invoice No]) In (SELECT [Invoice No] FROM [Ar] As Tmp GROUP BY [Invoice No],[Customer] HAVING Count(*)>1  And [Customer] = [Ar].[Customer])) AND ((Ar.[Invoiced Date])>#8/10/2015#))
ORDER BY Ar.Customer, Ar.[Invoiced Date] DESC;

So by saying open "duplicatesForAr" will it execute that SQL string the query is made with?

I know with DAO, you can open SQL strings rather than the actually table/query

Set rst = db.OpenRecordset([SQL CODE HERE])

So if all else fails I will convert the SQL to a string and open it that way.

[ranman256]

just build the query and run the query.
The sql is in it, no need to build vb code, when a query can be run with a single statement; docmd.Openquery

[josekreif]

just build the query and run the query.
The sql is in it, no need to build vb code, when a query can be run with a single statement; docmd.Openquery

I think you are missing the point, or missing what I am trying to do. I'm not opening the query in basic terms per say. I'm opening it in DAO for recordsets.
Thus

Set rst = db.OpenRecordset("duplicatesForAr")

The Query displays information.

I am using DAO to manipulate the data returned. There are duplicates that are purposely being added on a daily bases. I have a code that runs directly after the dupes are added to remove the dupes with a date older than the newer one.

I have the SQL in the query returning the dupes by customer with date Desc.. for example

Invoice No	Customer	Invoiced Date
Faker	John	8/18/2015
Faker	John	8/11/2015
Faker/2	Sam	8/21/2015
Faker/2	Sam	8/17/2015

This way when checking for dupes I delete the ones with oldest date and just have this in the end.

Invoice No	Customer	Invoiced Date
Faker	John	8/18/2015
Faker/2	Sam	8/21/2015

.
My VBA code works. I just want to be sure that the data is being sorted correctly so I never delete the newer invoice data... I could hurt my job should this fail, as the company would lose some money for every invoice this fails on

[June7]

I will go out on limb and say the SQL is executed.

However, why delete records? Deleting records should be a rare event. Just filter the data to show only the most current records. http://allenbrowne.com/subquery-01.html#TopN

[josekreif]

http://allenbrowne.com/subquery-01.html#TopN[/URL]

Because Customers will be charged for the same invoice 365 times a year (and at different prices). When a invoice order is complete, it needs to be totaled, the final date is collected and it is thrown into Accounts Receivable for Aging.

Because a invoice can span more than one day, there are multiple shipments tied to a invoice number, these are totaled each day to give the final price of a invoice. Also, since the Company uses the final date for the invoice date

So if invoice A has a order with $50 on day one, its added to Accounts Receivable as invoice A: $50 ,
If invoice A has another shipment on the second day of $30, its summed up by a query and added to accounts receivable as invoice A: $80 ,

Now we have
_________________________________
-ACCOUNTS RECIEVABLE -
-------------------------------------
INVOICE CUSTOMER PRICE DATE
-------------------------------------
invoice A Bob $50 day 1
invoice A Bob $80 day 2
______________________________________


day 1 needs to be deleted since it's from when the invoice has only one order on it.
So i have code that matches Invoice and Customer, Deletes dupes. I want to keep the mode recent date since it will have the actual Sum for invoices.

My code is working, but is it working correctly? Can I leave the query name, or should I put the SQL there to be 100% sure?


Now, if day 2 was the last day for invoice A, invoice A would be added to AR every day for the rest of the year. If day 2 was Jan 2, 2015, invoice A would show up over 300 times.

So I have code that works like this

1. Add new invoice Info
2. Delete all old info. (dupes)
3. Check if invoices are ready for payment
4. Check if there are new payments
5. add new payments to queue
6. Assign payments from queue to invoices
7. Remove paid invoices from Aging. (added to closed invoice table to prevent invoice number from being used again)
8. (If customer has money left over and no more invoices to pay, save for next time)
9. Spit out reports.
10. Have a great day

[June7]

Dang, I did ask!!

Conventional database would design to produce correct billing without deleting records.

I have worked with QuickBooks and GreatPlains accounting software and deleting records was never required for accurate billing. In fact, was not even possible with GreatPlains (at least not through the GUI), don't remember if I could delete records in QuickBooks.