update multiple records at one time.

[Krash878]

I have an application that does billing. I need to be able to run the billing for the month and have all the records of closed jobs that were run in the billing to be updated with a yes/no flag as yes. The report would need to have the the start date of the last report and the current date as closed date. I just need some pointers or led in the right direction.

Thanks
Kenny

 

[JoeMiller]

Put a Yes/No field in your table, make an update query that selects the same data as that of your report. Have that update query set all records selected (the ones on the report) to Yes, meaning they were printed/billed. Execute the update query when you run the report. That's the basic gist of it. I'm sure you'll want to do some logic to make sure that the query should be run, has been run, maybe a date field to show when it was run, etc...

HTH Joe Miller
joe.miller@flotech.net

 

[DRowland]

This is the language that I use in my database.
UPDATE bbb SET bbb.inqcount =0 WHERE ((bbb.inqcount) > 0 ) ;

What it does is to look for anything with 1 or more "inqcount" and change it to "0". I just inserted a link in one of my administration pages, that calls the asp page containing the results table.

I have to run an inquiry report daily, then clear the inqs after the report. Works fine..

hope that helps.

Dick Rowland

 

[Krash878]

ok let's start with the basic query.
I need to have the query give me the closed Records in the for a month from lets use Jan 1 2001 to Jan 31 2001. I am not sure what the command would be to make this work. I have a feeling it would be something like Criteria
>=01/01/01 And <= 01/31/01

I do not know exactly how this would look in the criteria.
I assume after I make the query I can pull the SQL code out and put it on the form as a SQL String.

What would it look like?
Thanks
Kenny

 

[JoeMiller]

Here's how your criteria should look, when you are typing literal dates you have to put the #'s in so Access knows you're feeding it a date:

Between #1/1/01# and #1/31/01#

Why would you need to pull the code out and put it on the form as a SQL string?

Joe Miller
joe.miller@flotech.net

 

[Krash878]

I was thinking to have the billing run I would need to have a query run. I also thought that the query would need to be run in the onclick event of a form.
hence behind the scene would look like this.

SELECT tblCloseItem.DateClosed, tblCloseItem.InterestFee, qryChargeBack.Postage, qryChargeBack.FDRFee, qryChargeBack.Fee
FROM qryChargeBack INNER JOIN tblCloseItem ON qryChargeBack.SequenceNumber = tblCloseItem.SequenceNo
WHERE (((tblCloseItem.DateClosed) Between #6/1/2001# And #6/30/2001#));

The dates can be changed out with a text boxes like [StartDate] and [EndDate]

Am I going in the wrong direction?
This is my first DB that is really indepth where I can not just use the Wizards. All my posts on this forum all have to do with this DB. This DB will later be used to frontline a SQL DB.
Thanks
Kenny

 

[JoeMiller]

From the info I have you are going in the right direction, but the query doesn't have to be run before the billing. I assume a billing is when you print an invoice. So a report would print the invoices, and the report would use this query as it's recordsource. That means the query is run when the &quot;billing&quot; is run. Capiche?

Joe Miller
joe.miller@flotech.net

 

[Krash878]

Ok what about a criteria on just the items in one month like All 01/01

Would something like that work.
Thanks
Kenny

 

[JoeMiller]

Make a new field in your query that formats the date as 01/01 then put your criteria in that column.

Joe Miller
joe.miller@flotech.net

 

[Krash878]

Ok I could need to run the billing per month. We no not print an invoice we print a list that shows each bank (our Clients) and the total of all the fees of the month. This is then fowarded to our Billing Department. I thought that I would a way to query all the closed items for a month. Then put a list of all the banks on one list and add together all the fees.
The billing would need to update each record that is Closed to being Billed (Billed/Closed are Yes/No boxes.

I could use a little pointer I am kinda flying blind here.

Do I need to use a query to get all the records that are to be billed in a month?

How do I update all the Records so they will not show as being unbilled?

I do understand that I am asking for alot of your time and I appreciate all your help.

Thanks
Kenny