SQL as Control Source for Text Box

[SilSuBa]

Is it possible to use a SQL select statement as the control source for a text box in a report? Here's why I ask..

I have a report built on a query that has these fields

Employee
PayType
PayAmount

I would like to list the pay amounts by type next to the employee name with the total pays at the end. The catch is, I want it to be under one header. I can accomplish this if I group by pay types, but I'd rather not form the report this way. Any Help?

Thanks!

 

[scriverb]

You can use the DLookup function to access the SQL you are referring to. This way you can select the record and return the record and data that you want.

Check out the DLoookup Function in ACCESS help. If you still need assistance post back with the SQL and what you want the DLookup to return for you and I can help format it for you.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[SilSuBa]

Thanks,

I actually forgot to include a field in my original posting. There are a total of four fields

Employee
Job
PayType
PayAmount

I have the report grouped Employee and Job and I wanted the pay amounts and type to appear next to the Job.

I used dlookup as you suggested but it seems to pull only the first record matching the criteria and instersts that into each line of the report. So I have 27 jobs listed under one employee and each has the same pay type.

Do I need to add the Job to the criteria of the dlookup somehow?

Thanks!

 

[scriverb]

After you are grouping by Employee and Job are you creating a Group Header? Is this where you want your data displayed? For Each Employee and Job combination are there multiple PayType's with a corresponding PayAmount's? Give me a visual as to what you want your report to look at also.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[DonQuichote]

You could also try a combo box. On a report, it looks like a textbox (no down arrow shown), but acts like a combobox. This may be the simplest thing you are looking for.

Best regards

 

[SilSuBa]

I've gotten it to work. Well.. sort of.

The report comes out formatted like I wanted and all the data is correct, it just takes a LONG time to format each page.

What I did was to fill the text boxes using code. I defined variables for User and Job and Amount. I used a dlookup to fill the variable of Amount with criteria User and Job and a PayType set statically, then filled the value of the text boxes on the report with the amounts returned to the amount variable.

Thanks for all the input and suggestions!