Querying a yet to be created table?

[Ken Robertson via AccessMonster.com]

I have built a quasi-program using switchboard manager and various forms.
This program was created to streamline the data entry here where I work.
Thus I have four tables that data is entered into via the forms, then once
a month they need to create a bill for all the customers. Right now I’m
using run query commands to update everything, then I have a command button
where the user can choose which customer to bill, and I use a SELECT INTO
statement to make a new table for that month i.e. 05-05-05Billing is the
table. I also use this table to export a fixed field file that is the bill.
Now I would like to give the user the ability to search these newly created
tables. So,
1 does anybody have any syntax for querying a table that will be created
months from now preferably with a command button and an entered text.
2 how to OutputTo or export table information from a table that has not
been named i.e. it will have the date as its name.
3 While I’m here and asking, can I pull info from a yet to be created table
to fill form i.e. the old total will come from the table made 04-05-05 and
then on the form the new total will be entered

This is the code for updates and create the new table for this month.

If Me.Frame22.Value = 2 Then

DoCmd.RunSQL "UPDATE WebClients SET batchNumber = Forms!
createBillsForm.batchNumber;"
DoCmd.RunSQL "UPDATE WebClients SET lastBillDate = Forms!
createBillsForm.date;"

' *** create the query table name
strTableName = Format(Now(), "mmddyy")

DoCmd.RunSQL "SELECT copierList.billableCopies,
copierList.departmentContact, copierList.costPerCopy, copierList.total INTO
" + strTableName + " FROM copierList;"

This is the code for the out put to function but I need to have a variable
name for each new table created
DoCmd.OpenForm "finishDialog"
DoCmd.RunMacro "Macro4"

This is the SQL that is probably the hardest once again 051705 is the new
table created for this month’s bills and the question is can I change that
table name i.e. 051705 will be 061805 next month so that users can search
the new billing file.

SELECT [051705].batchNumber, [051705].customerID, [051705].billableCopies,
[051705].previousMeter, [051705].departmentContact, [051705].costPerCopy,
[051705].total, *
FROM 051705
WHERE ((([051705].batchNumber) Like "*" & [Forms]![searchAll]![searchText]
& "*") AND (([051705].customerID) Like "*" & [Forms]![searchAll]!
[searchText] & "*")

 

[Guest]

Tell me the # I'm thinking of writing down before I write it down? Almost
impossible right? That is what your asking the computer to do. The difference
between the above problem and yours is you have the data necessary to figure
that # out. Either make a temp table or use a query to hold your billing
before writing it to a table. Then you can query or search it before writing
it.
--
HTH
Martin J

I have built a quasi-program using switchboard manager and various forms.
This program was created to streamline the data entry here where I work.
Thus I have four tables that data is entered into via the forms, then once
a month they need to create a bill for all the customers. Right now I’m
using run query commands to update everything, then I have a command button
where the user can choose which customer to bill, and I use a SELECT INTO
statement to make a new table for that month i.e. 05-05-05Billing is the
table. I also use this table to export a fixed field file that is the bill.
Now I would like to give the user the ability to search these newly created
tables. So,
1 does anybody have any syntax for querying a table that will be created
months from now preferably with a command button and an entered text.
2 how to OutputTo or export table information from a table that has not
been named i.e. it will have the date as its name.
3 While I’m here and asking, can I pull info from a yet to be created table
to fill form i.e. the old total will come from the table made 04-05-05 and
then on the form the new total will be entered

This is the code for updates and create the new table for this month.

If Me.Frame22.Value = 2 Then

DoCmd.RunSQL "UPDATE WebClients SET batchNumber = Forms!
createBillsForm.batchNumber;"
DoCmd.RunSQL "UPDATE WebClients SET lastBillDate = Forms!
createBillsForm.date;"

' *** create the query table name
strTableName = Format(Now(), "mmddyy")

DoCmd.RunSQL "SELECT copierList.billableCopies,
copierList.departmentContact, copierList.costPerCopy, copierList.total INTO
" + strTableName + " FROM copierList;"

This is the code for the out put to function but I need to have a variable
name for each new table created
DoCmd.OpenForm "finishDialog"
DoCmd.RunMacro "Macro4"

This is the SQL that is probably the hardest once again 051705 is the new
table created for this month’s bills and the question is can I change that
table name i.e. 051705 will be 061805 next month so that users can search
the new billing file.

SELECT [051705].batchNumber, [051705].customerID, [051705].billableCopies,
[051705].previousMeter, [051705].departmentContact, [051705].costPerCopy,
[051705].total, *
FROM 051705
WHERE ((([051705].batchNumber) Like "*" & [Forms]![searchAll]![searchText]
& "*") AND (([051705].customerID) Like "*" & [Forms]![searchAll]!
[searchText] & "*")

 

[Ken Robertson via AccessMonster.com]

Thank you anyway, I figured it out. Its not as though I am asking the
computer to guess a number I merely used the date function and operators on
the date to construct the table, then use a SELECT INTO SQL statement, but
thanks anyway.