can't get formatting right on dhookom example 1

[chiefman]

I was working with an example that dhookom had on his invisibleinc site. (The report of doctor's appointments and the timeline.) The problem I am having is that if the records are not in order it displays each one on it's own page. I am doing a report for hospital OR's and have it grouped by that instead of doctor, however, it will not always be entered in order. I've tried a sort and an order by with no success. Would anyone happen to have any suggestions? (My order by may have been wrong. I have since removed it from the report so I don't remember what statements I used.) Thanks.

 

[dhookom]

The report can be grouped first by OR Room and then by WeekOf. What are your actual sorting and grouping levels and are you showing headers/footers for any sections?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

I have a group footer based on date, but that's it at the moment. I think I tried sorting on date and then OR. I'll switch those and see what happens. Thanks.

 

[chiefman]

Still no luck. Am I getting the syntax correct on the order by?

ORNumber.ORNum,PerMonInput.Date

 

[dhookom]

It isn't clear which report you are trying to duplicate. There was "Weekly Schedule by Doctor" which shows an entire week on a page with dates as columns. The "Daily Schedule" show a single day with doctors as columns.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

It was the daily schedule. It has a timeline down the left side, the doctor's name (or in my case OR number) as columns, and each page displays all the records for that day. In my project I have 3 records on the particular problem day and the first is for room 6, then room 23, and then room 17. For some odd reason they won't display on the same page, no matter what I have tried. Later in the report I have 4 records on the same day that display on the same page, but that is just chance that they were entered in order. I have checked the ReportColumn values and tried different values, but all that does is move them further to the left or right on their own individual pages.

 

[dhookom]

Your report should be sorted by:
[li]Date (should not include a time value with the date)
No header necessary
Footer Yes with the footer the same size as the detail section[/li]
[li]Start Time[/li]
[li]OR Room[/li]

I would first check to make sure your date field doesn't contain a time element.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

The date field contains the time when it comes in, but I just changed it in the report itself to the "short date" format, and it still doesn't seem to help. Is there any way to use the "Format" function in the SELECT statement so as to leave off the time? When I tried it I couldn't get it to work. It would ask me for the "date" the "PerMonInput.Date" and then would display all the records on one page. That leads me to believe that if I can get the "format" to work on the SELECT statement that it might just work.

 

[dhookom]

Changing a format doesn't do much. Convert the values using DateValue([YourDateField]). For instance in your Sorting and Grouping expression use:
=DateValue([YourDateField])



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

Wow. That did it. Thanks a ton! Have a star.

 

[dhookom]

You are very welcome. That report demo is one of my favorites.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

It is a very cool report and it is perfect for what I'm working on. Thanks again.

 

[chiefman]

I have another question about this report. I have to be able to set it up based on a date range that the user selects. I do this in other reports in the code, however all the code does in this report is set the size and position of the objects, not control which records are displayed. When I unbind the report and try only to manipulate it in code (set the textbox values,etc.) I only get the last record (not the last date, only the last record). Is there a way to make this function in such a way that the date range can influence which records are displayed? The date range is chosen on another form and can be changed to include any date possible and therefore probably couldn't be used in a filter or "WHERE" statement. I think it would work if I could get each date value to display on it's own page. I have tried everything I can think of and there's probably only one little thing that needs to be fixed. I just can't figure out what it is. Any suggestions would be very well appreciated. Thanks in advance.

 

[dhookom]

I don't understand why you would try to unbind the report. You shouldn't have any issues filtering the report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

The reason I need to unbind it is because it prints the data for each record and I only want certain records. When
I try to filter based on text boxes on the report (ex. Me.text1<=![date]<=Me.text2) it always asks me for the value of text1 and text2. Maybe I have the syntax of that statement wrong. Also, is it possible to have multiple filters under the filter property?

 

[dhookom]

The report is based on a query. Why can't you filter the records in the query? "I only want certain records" suggests that you set the criteria in your report's record source to only include "certain records".

I never use the Filter property except as set when you use code like:

DoCmd.OpenReport "rptWhatever", acPreview, , " the filter string"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

I would expect if you used a where clause when opening your report, it would look something like:

Dim strWhere as String
strWhere = "[Date] Between #" & Me.txtStartDate & _
    "# AND #" & Me.txtEndDate & "#"
DoCmd.OpenReport "RoomsRunningDRWK", acPreview, , strWhere

If you only wanted to print the report for a single date:

Dim strWhere as String
strWhere = "[Date] = #" & Me.txtTheDate & "# "
DoCmd.OpenReport "RoomsRunningDRWK", acPreview, , strWhere

If this doesn't work, tell me how your users would enter the date range, the names of the controls, and the name of the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]