RE: Querying a yet to be created table?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"Ken Robertson via AccessMonster.com" wrote:

> 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] & "*")
>
.

Relevant Pages

  • Re: An unhandled exception of type System.Data.SqlClient.SqlException
    ... Fixed the problem in the parametised query, ... > command in production and get the same error ... >> The same error is produced if I change to a parameterised command ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Query Only Creent Record?
    ... I would approach this by building a query that refers to that form's record ... For the command button, it sounds like you've already set up one that works, ... >> Jeff Boyce ...
    (microsoft.public.access.queries)
  • Re: Dataset question...
    ... I agree, Bill. ... command to get what you want or using some method that contains a dozen ... > same query) where the invoice items are returned. ... >> to have a dataset with the related data, each in its original tables, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Query Only Creent Record?
    ... I have a table called Memos that the user enters information into only ... I build a command button for e-mailing with sendobject but it send ... I have tried and tried but building a query to ...
    (microsoft.public.access.queries)
  • Re: InputBox Function in a Query - Hidden Response
    ... > I am writing a simple query which ask for data which I do not want to be ... Not possible in the query itself. ... Add an unbound text control. ... Add a command button. ...
    (microsoft.public.access.queries)