Populate text box from value in query

[bgwool]

I am working on a report that will display how many hours each employee worked for each day in a selected time period. I have 16 text boxes in my report that I would like to associate with the user-inputted start date and end date. For example, text box 1 is associated with [StartDate], text box 2 is associated with [StartDate]+1. I need the text boxes to display a value ([TotalHours]) in a query where the date is equal to the date it is associated with, as well as the employeeID with another text box on the screen. I know I'm probably not explaining this very well, but I will try to clarify if anybody has any questions. Thank you for any help!

In case this helps clear up my question, I want the text box to display value [TotalHours] in Query [TimeCards] where [EmployeeID] = value of a text box AND [Date] = value of a text box

[June7]

Sounds like you want to pivot the data (a CROSSTAB query). Show example raw data and example of desired output.

[bgwool]

This the query (Time Cards) I'm using for all data in report (much simplified, but this is the only data needed in my report:

Name Date TotalHours

John 8.15 6
John 8.16 7
Jim 8.16 8
Jim 8.18 4
Jim 8.20 7

-------------------------
I have a report based on the query TimeCards. It is filtered by user-inputted [StartDate] and [EndDate] to get a specific 15/16 day period.

On the report I have a text box [EmpName] that will display each employee's name [Name]
Then I have 16 text boxes that I would like to display the value [TotalHours] for each date in the period for the employee.

Basically, on the 1st box I want it to display [TimeCards].[TotalHours] where [Name]=[EmpName] AND [Date]=[StartDate]
On the 2nd box, I want it to display [TimeCards].[TotalHours] where [Name]=[EmpName] AND [Date]=[StartDate]+1
etc., until the last box
-----------------
So, in this example, if the user-input [StartDate] is 8.15, I would like it to display

John
6 , 7 , Null, Null etc.
Jim
Null, 8, Null, 4, Null, 7

[June7]

Well, a CROSSTAB output would be like:

Name	8.15	8.16	8.18	8.20
John	6	7
Jim		8	4	7

There is no 8.17 column because there is no record in the sample data for 8.17.

[bgwool]

The crosstab format is perfect (but I do need the dates without values to remain in the data), except that I need it to be in a report because there are specific formatting requests that I'm trying to accommodate (i'm making it for my job).

[June7]

Building a report to run perpetually based on a CROSSTAB is difficult because of their dynamic nature, especially when pivoting on date criteria and the date range of desired records constantly changes. Review http://allenbrowne.com/ser-67.html

If you need all dates then need a dataset of all possible dates to be included in the query.

[bgwool]

Do you think Dlookup could work to populate a text box? something like =Dlookup ("[TotalHours]",[TimeCardDetails],"[Date]=" & [StartDate])

[June7]

A domain aggregate is a possibility but can be slow performer.

I have a report where I want all twelve months to display so I have twelve textboxes on report arranged vertically. Example expression:
=Sum(IIf([MO]=7,[Revenue],0))

[MO] is a calculated field in the report RecordSource query to extract the month value. Code opens the report filtered to a fiscal year parameter input on a form.

You might find this thread of interest and the example db in post 60 https://www.accessforums.net/forms/g...tml#post239648

Also, http://forums.aspfree.com/microsoft-...ry-322123.html