Lookup Parameter for Report Query?

[GUIDO22]

I have a RATES table that holds a handful of records for a number of departments. Each department has its own rateable value.

I also have a handful of different reports that display costing data for each department and I need to utilise the RATE value for the appropriate department on each of these reports.

At present, the report is bound to a query that prompts the user to enter the appropriate rate each time the report is run. But, I wish to use the stored value for the dept. in the RATES table.

The problem I am having is that the RATES table contains no key to any of the other tables from which I am reporting on and so I cannot incorporate the RATES table into the QUERY design window.

I presume I need a lookup value and then need to apply the lookup value (ie. the department rate) to the report Query. But what is the sequence of operation here and where would the lookup value go : on the report, VBA code ?

Any ideas on how best to do this please?

NB. Is it possible to pass the lookup RATE parameter to the report query from VBA?

Thank you.
Guido

 

[namliam]

I assume you will have some sort of key value present to link the values and the records together....

Cannot you add that into the query? Using a (D)Lookup function is not very fast, and to say the least should be your ultimate last resort...

Seasons greetings from Amsterdam

The Mailman

 

[GUIDO22]

I assume you will have some sort of key value present to link the values and the records together....

I have a record Identifier in the rate table but this does not key to any associated records in the other tables. The table is an arbitrary table containing costing figures, that is all - other than that it has little to do with the other tables in the report criteria. It is solely a reference for a rate value......

 

[namliam]

How then? If you have no "meaningfull" key values... How are you going to be able to look them up?

What is your logic?

Seasons greetings from Amsterdam

The Mailman

 

[Colby]

You originally stated that...At present, the report is bound to a query that prompts the user to enter the appropriate rate each time the report is run. But, I wish to use the stored value for the dept. in the RATES table.

You could have the user select which dept he/she is with and on that selection have a Case statement that would return the correct rate. This would mean a bit more work on the front end but it should work.

HTH

 

[GUIDO22]

Thanks for your input - I have managed to get around the issue by using DLookup() on a handful of text boxes that display the rate values for each Dept. on the dialog - the dialog from which the user initiates display of the reports.

The query underlying each report then references the text boxes on the dialog box for the rate value that each requires to generate the appropriate totals! It works very well!

Happy Christmas!

 

[Colby]

good deal. Merry Christmas to you and yours also.