[AccessD] Need some help.

Steve,

That's a bit of a tall order, but I may have something along the lines
you're looking for.  I wrote a custom billing application for a
Builders Exchange that generates invoices.  The billing process starts
with choosing the billing month and then the user lets 'er rip.  All
the table backups and querying are done in a string of processes that
ends with a report containing the entire set of invoices to print and
mail.

All of the queries are "in-line"; that is, I don't use any saved
queries in the VBA code.    If I need to run a summary query to get a
value or two for further processing it'll look something like this:

530        strSQL1 = ""
540        strSQL1 = strSQL1 & "SELECT DISTINCT tblBillcode.[DuesCode
search string] "
550        strSQL1 = strSQL1 & "FROM tblBillcode "
560        strSQL1 = strSQL1 & "WHERE (((tblBillcode.Billmon)='" &
strBillMonth & "'));"

570        Debug.Print "Extract DuesCode search string from Billcode - 001"
580        Debug.Print strSQL1

590        Set rstDss = dbs.OpenRecordset(strSQL1)

600        strDss = rstDss![DuesCode search string]
610        rstDss.close
620        Set rstDss = Nothing

Then I'll use strDss in a query that creates a table:

650        strSQL2 = ""
660        strSQL2 = strSQL2 & "SELECT DISTINCT Membfile.[Acct#],
Membfile.[Member#], "
670        strSQL2 = strSQL2 & "Membfile.DuesCode, Membfile.[Company Name] "
680        strSQL2 = strSQL2 & "INTO tmpTblMemb "
690        strSQL2 = strSQL2 & "FROM Membfile "
700        strSQL2 = strSQL2 & "WHERE ((" & strDss & "));"

710        Debug.Print "Select Members whose DuesCode equals what we
just extracted in 001 - 002"
720        Debug.Print strSQL2

730        DoCmd.RunSQL strSQL2

This query creates an intermediate result table that's used further
down the line, etc.

Does this help?

If you're going to use saved queries, then you can create a QueryDef
object and refer to the name of your saved query and use the
DoCmd.RunSQL command to execute it, if I remember correctly.

Steve Erbach
Neenah, WI


On Thu, Sep 25, 2008 at 1:32 PM, Steve Turner <sturner at mseco.com> wrote:
>
>
> Hello List,
>
> I am using A2k with SQL Express, Excel, and other linked tables. The
> main DB is in SQL Express. We have a custom timesheet program that puts
> Time, Charges, Job numbers worked on in it. I used to export data to
> Lotus 2.1 using A2k and calculate cost for each Job to make entries to
> the GL. I have now written Query's to extract the data, calculate the
> cost and export to Excel so I can import to the GL. Calculations are
> done by the week because of Salaried Costs and the hours worked causes
> the Cost to change each week. I have a Query that sums the week by Job
> based on entered dates. I made a copy as a Make Table Query and a Copy
> to be an Append Query that writes the 4 or 5 weeks to a table to use
> another query to summarize by Job for the Month. I also have a Query
> that summarizes the data to 10 profit centers 0-9 as negative amounts to
> offset the GL.
>