Display query results in textbox in report

[zjzastava]

I am having trouble getting the results of a query to be displayed in a textbox in a report. I was able to get the results from the following query using Dlookup

SELECT Count(*) AS NumberOfPics
FROM tblOldAbes
WHERE ((([tblOldAbes].[Day])="Monday") And (([tblOldAbes].[Time])="12") And (([tblOldAbes].[FullDate]) Between [forms].[frmDateRangeOA].[txtDateStartOA] And [forms].[frmDateRangeOA].[txtDateEndOA]));

I used =DLookUp("NumberOfPics","qryCountMondayOA12pm") and it works just fine.

The problem I am having with with my other query

SELECT [NumberOfPics]/[forms].[frmDateRangeAvgOA].[txtNumberOfSatAvgOA] AS AvgNumOfPics
FROM qryCountMondayOA12pm;

I used =Dlookup("AvgNumOfPics","qryAvgMondayOA12pmManual") and I am getting #ERROR displayed in my reports. Am I missing something simple or is it a problem with the query itself?

Any suggestions would be greatly appreciated.
Thank You
Zac Z

 

[dhookom]

Is the form open?
It looks like the query returns one, always one, and only one record. If this is true, just add [qryAvgMondayOA12pmManual] to your report's record source and add the [AvgNumOfPics] field to the field list.

Duane
Hook'D on Access
MS Access MVP

 

[zjzastava]

The report has 112 text boxes. There are 7 columns for each day of the week and 16 rows for the hours of the day. Each text box will return one and only one value. When I use the Dlookup as mentioned in my fist post I get #ERROR and when I tried [qryAvgMonday12pmManual]![AvgNumOfPics] I get Name?

Any suggestions to get the results of the query into the text box of the report would be greatfull. If I have not posted enough info please let me know.

Thank You
Zac Z

 

[dhookom]

If all of your text boxes are DLookup()s with no where condition then I expect your report doesn't have or doesn't need a record source.

I think all of this could be much easier using another method for getting the values. Maybe you should just describe your significant tables and fields and how you want these displayed in your report.


Duane
Hook'D on Access
MS Access MVP

 

[zjzastava]

I have only 1 table that all the data is being pulled from. The table has; RawData, Day, Time, Month, DayNum, Year, and FullDate. This table was created by linking an Excel file to Access. RawData is a unique number for every entry in the table. Every entry in the table represents one unit. My first report was breaking down the number of units per hour per day for any given date range. I used Dlookup because that is how I got it to display on the report. For example Monday at 9am.

The next step was to calculate the average units per hour for any given date range. I tried to use Dlookup again since it worked for the first report but no luck.

If there is a better more efficient way of doing this I am eager to learn it.

On a side note I have a query written for every day and hour that is calculating the number I would like to display.

 

[dhookom]

I think you only need to use a crosstab query that uses the Day as the column heading and [Time] as the Row Heading and Count([Day]) as the value.

Duane
Hook'D on Access
MS Access MVP

 

[zjzastava]

Will using a cross tab query help solve the problem of now being able to display the results of my query in a text box in a report.

 

[dhookom]

Can you please provide some information about your significant tables and fields, data, and how you want this displayed in your report. I really think you are working way too hard.

Duane
Hook'D on Access
MS Access MVP

 

[zjzastava]

I am not using access to its full potential. We just want to use the reports and graphing portion of the program. We have 1 table that is all. The headings in that tabel are RawData, Day, Time, Month, DayNum, Year, and FullDate. The table is an Excel file that is linked to Access. RawData is a unique number for every record in the table.

The report has colums headings of Mon, Tue, thru Sun and total. In addition, we have the times of the day labled vertically 9am, 10am, thru 12am and a total. There is a textbox in every location that will represent a time for each day of the week and for the totals.

Mon Tue Wed Total
9am
10am
11am

Total


The first report is counting the number of records for a date range and displaying them in there appropriate location in the report. The Second report is going to display the Avg number of records. Basically we want the number per hour and avg number per hour for a date range that is entered into a form by the user.

I was able to get the first report that counts the number of records per hour to work by using a query for each day and time and using Dlookup in the control source of the appropriate text box in the report.

SELECT Count(*) AS NumberOfPics
FROM tblOldAbes
WHERE ((([tblOldAbes].[Day])="Monday") And (([tblOldAbes].[Time])="12") And (([tblOldAbes].[FullDate]) Between [forms].[frmDateRangeOA].[txtDateStartOA] And [forms].[frmDateRangeOA].[txtDateEndOA]));

I used =DLookUp("NumberOfPics","qryCountMondayOA12pm")

I have had issues using the same method for the report showing the avg number per hour. The query I am currently using for getting the Avg seems to work with the small test I have given it. The problem has come from getting the results to display in the report.

SELECT [NumberOfPics]/[forms].[frmDateRangeAvgOA].[txtNumberOfSatAvgOA] AS AvgNumOfPics
FROM qryCountMondayOA12pm;

I used =Dlookup("AvgNumOfPics","qryAvgMondayOA12pmManual")

I hope this helps you to understand what I am trying to do. If I have not given enough info please let me know.

Thank You
Zac Z

 

[dhookom]

It isn't clear what all of your data types are but assuming FullDate is date and Time is string, try create a crosstab query like:

PARAMETERS [forms].[frmDateRangeOA].[txtDateStartOA] DateTime, [forms].[frmDateRangeOA].[txtDateEndOA] DateTime;
TRANSFORM Count(tblOldAbes.RawData) AS CountOfRawData
SELECT tblOldAbes.Time
FROM tblOldAbes
WHERE (((tblOldAbes.FullDate) Between [forms].[frmDateRangeOA].[txtDateStartOA] And [forms].[frmDateRangeOA].[txtDateEndOA]))
GROUP BY tblOldAbes.Time
PIVOT Format([FullDate],"dddd") In ("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");

[tt]
Time Sunday Monday Tuesday Wednesday Thursday Friday Saturday
00 2 2 2 2 3 2 5
01 3 2 6 2 3 2 4
02 1 4 7 5 0 3 1
03 5 4 3 1 3 2 1
04 5 1 1 4 5 5 1
05 0 1 4 2 4 1 3
06 4 1 0 1 1 5 3
07 5 0 4 3 6 2 2
08 1 0 4 3 0 4 1
09 1 4 1 1 1 1 2
10 2 3 3 1 1 1 0
11 2 3 4 1 5 1 5
12 1 1 6 2 1 2 1
13 2 4 1 1 0 1 0
14 0 4 5 2 4 1 2
15 3 5 4 4 2 2 7
16 1 1 2 4 1 2 4
17 2 5 6 2 2 1 2
18 5 3 3 3 3 4 4
19 2 4 3 1 2 2 3
20 0 2 2 5 1 3 3
21 1 0 1 3 1 0 2
22 4 6 5 0 3 2 2
23 2 5 1 3 2 1 0
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[zjzastava]

You were right I was working to hard. I am a novice at this as you may have figured out. I do have a question. The cross Tab query works great and is a lot easier than the way I was doing it writing a single query for each time of day. Is it possible to get the totals of the columns and rows to be added to the chart?

 

[zjzastava]

In addition, What would be the best way to display this info from this query in a report so it could be printed out

 

[dhookom]

What chart? I don't recall anything about a chart. A chart has a Row Source and a report has a Record Source. This query can be the Row Source and/or Record Source.

You can get a count of the entire week by adding a count to the crosstab as a Row Heading. Typically column totals are calcualated in the report footer section.

Duane
Hook'D on Access
MS Access MVP