How do use the same paramater for every query?

[willsaunders]

Hi there,

Small problem. Maybe a lengthy description but please read. Summarises at the bottom.

I am making a database to manage time sheets for the company I work for and calculate and print out reports of the amount to be charged to each client based on the hours worked. There is, amongst others, a table for time sheet entries and a table for invoices and credits. They could be incorporated into one another which might make things easier but that's not too important for me.

In the main report after each clients name I want to have a field for the total of all the entries previous for the month being printed out, all charges from each employee +/- invoices and credits. Basically this would be the calculated current balance for the clients account with us.

The next fields are all the employees and the total charges for their work to each client for the month being printed out.

The final fields will include the total of invoicing and crediting, the total charge for the month to invoice each client and then the new balance.

Their are a number of ways to simplify the database which I can think of to look into but basically, the thing that I need to be able to make it work is to have some sort of global variable in my database that can be used by all the queries, so that the report can be printed out with the correct information. Can it get initialised when the report is run or when Access opens? I just want to be able to input the month number (1-12) and maybe the year.

How can any sort of global variable be achieved for queries in Access...is it possible?

I hope so!

E-mail if necessary,

Cheers, Will.

 

[Chris RR]

If you just need the month and year, and are happy with determining that based on the current date, you don't need any kind of global variable at all. You can just calculate this and use this in your query criteria.

But, if it isn't something easily calculated, then if you are new to Access, start by reading up on parameter queries.

One way to set this up is to build a form that lets the user enter the parameter(s), then hit a button to run the reports. The queries underlying the reports look to the form for their criteria.

And, yes, there are true global variables in Acces, but I don't think that's really what you want here...

 

[willsaunders]

Cheers mate, never knew about paramter queries. I'll look into it. May be back for more help. Using the current date wouldn't have worked but I can see this working.

Cheers,

Will.

 

[willsaunders]

Parameter queries are not working to well for me. I already knew about them, I just hadn't realised what they were called. I need to use the same parameter in different queries, but I don't want access to keep asking me for them every time I open or print the report.

Can you offer any more help, those global variables maybe?

I've got a few ideas myself now from a book so I'll take a look at those. But anything you could help with, or anyone else for that matter, would be great.

Cheers,

Will.

 

[pcs]

from the 'quick and dirty' department.

create a table with one field: date.
create a form to plug it with the desired date.
execute the form when access opens (or whenever).
do a dlookup in your form that opens the report (or in your queries) to get the selected date.

hth,
al