Make Lines Visible Based on Data 1

[shelby55]

Hi

I'm using Access 2003.

Thanks to MajP I was able to create a report where header fields are visible or not depending on whether there is data for the month selected.

I added vertical lines to the report and was able to make the lines invisible in the group header and footer sections based on the same information MajP gave me. For the footer I used:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.ln1_C.Visible = Not IsNull(Me.qryM2_A_Cases)
Me.ln2_C.Visible = Not IsNull(Me.qryM3_A_Cases)
Me.ln3_C.Visible = Not IsNull(Me.qryM4_A_Cases)
Me.ln4_C.Visible = Not IsNull(Me.qryM5_A_Cases)
Me.ln5_C.Visible = Not IsNull(Me.qryM6_A_Cases)
Me.ln6_C.Visible = Not IsNull(Me.qryM7_A_Cases)
Me.ln7_C.Visible = Not IsNull(Me.qryM8_A_Cases)
Me.ln8_C.Visible = Not IsNull(Me.qryM9_A_Cases)
Me.ln9_C.Visible = Not IsNull(Me.qryM10_A_Cases)
Me.ln10_C.Visible = Not IsNull(Me.qryM11_A_Cases)
Me.ln11_C.Visible = Not IsNull(Me.qryM12_A_Cases)
End Sub

MajP also gave me the following code for the detail sections for data fields that are empty:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Access.Control

For Each ctl In Me.Controls
    If ctl.Tag = "?" Then
    ctl.Visible = Not IsNull(ctl)
    End If
Next ctl
End Sub

The problem with using the same code for the detail section is that if there is data for the whole month but no data for one of the groups then a vertical line doesn't show up for that group and I don't get a solid line for the entire detail section which is what I want.

What do I need to do so the line is solid in the detail section? Thanks.

 

[MajP]

I am a little confused in the description. I assume the results look something like this.
[tt]
Header Month 1 Month 2
Disposition # Cases # Days Avg Days # Cases # Days Avg Days

Detail

Directly Home | 8 80 10.0 | 12 48 4.0 |
Tsfr to Acute | 10 80 8.0 | 12 48 8.0 |
Tsfr to Rehab | 5 50 10.0 | 6 24 4.0 |
[/tt]

Are you referring to a case where for a given month there is no data for say Tsfr To Acute, but there is data for the other Dispositions. Something like.
[tt]
Header Month 1 Month 2
Disposition # Cases # Days Avg Days # Cases # Days Avg Days

Detail

Directly Home | 8 80 10.0 | 12 48 4.0 |
Tsfr to Acute | 10 80 8.0 | |
Tsfr to Rehab | 5 50 10.0 | |
[/tt]

If that is the case I would think your footer code would also have problems when there is data for the month, but not in the last record.

This is what I think you actually need to do, but there may be a cleaner solution I have not thought about.

Assume your reports query is qry12MonthDisposition. Each month probably has a field for cases. I think it is qryM1_A_Cases...qryM12_A_Cases. To determine if there is data for an entire month then you could do something like this.

You pass the function the month value in question based on which line or which control (1-12). Then it does a dcount to determine if there are any records not null for that month.

public function hasData(intMonth as integer) as boolean
dim fldName as string
dim strWhere as string
dim qryName as string
qryName = me.recordsource
select case intMonth
case 1
fldName = "qryM1_A_Cases"
...
case 12
fldName = "qryM12_A_Cases"
end select
strWhere = fldName & " is not null"
hasData = (dcount(fldName,qryName,strWhere)>0)
end function

so now you would do something like this:

Me.ln1_C.Visible = hasData(2)
Me.ln2_C.Visible = hasData(3)

This function would go in the forms module. I believe the logic is correct, but it is untested.

I have no idea how big your data set is. This will be very inefficient. Do you have a few dispositions (records) or thousands?

 

[MajP]

Maybe I am misunderstanding because I would think you would want to have the lines regardless of data.

I would think for dispaly you would want this:
[tt]
Month 1 Month 2
Disposition # Cases # Days Avg Days # Cases # Days Avg Days
Directly Home | | 12 48 4.0 |
Tsfr to Acute | | 12 48 8.0 |
Tsfr to Rehab | | 6 24 4.0 |
[/tt]
Not
[tt]
Month 1 Month 2
Disposition # Cases # Days Avg Days # Cases # Days Avg Days
Directly Home 12 48 4.0 |
Tsfr to Acute 12 48 8.0 |
Tsfr to Rehab 6 24 4.0 |
[/tt]

 

[MajP]

Also not sure if you have thought of it or if it is helpful, but you can very easily make a 3,6,9, months report to go with your 12 month report. Once you are satisfied with the 12 month report, just copy the report query and start chopping off the individual month queries to make qry9MonthDispositions... qry3MonthDispoistions. Then chop of the fields for the report. Should only take a few minutes. Then on your form where you select start date, you could have a choice to select 3,6,9,12 month report, based on how many months of data there really is.

 

[shelby55]

Hi MajP

Thanks for the responses. I actually figured something out last night at 2:00 a.m. but was too tired to post!

Sorry but you misunderstood what the detail line was doing. It looked like this

[tt]Header Month 1 Month 2
Disposition # Cases # Days Avg Days # Cases # Days Avg Days

Detail

Directly Home | 8 80 10.0 | 12 48 4.0 |
Tsfr to Acute |
Tsfr to Rehab | 5 50 10.0 | |
[/tt]

So the line wasn't continuous. Anyway I figured that because I had already added totals in the footer, I would use that for the detail lines:

me.ln1_C.visible = not isnull(Me.ttl2_C)
me.ln2_C.visible = not isnull(Me.ttl3_C) etc.

You were correct that the original footer code I posted didn't work. I had to change it to the above as well.

Thanks again for all of your help on this!

 

[shelby55]

Hi

I found instances where this isn't working: when there is truly no data for a month. I want to have blanks show up in the data because this is a report with multiple months but all the months line up so even if no data, it needs to be there.

I tried to link to the presence of the month header name which is using the functions of startdate and addmonth(startdate) but that doesn't seem to work.

Any other suggestions? Thanks very much.

 

[MajP]

can you explain this more clearly. It sounds as if a month with no data should have visible lines.

I want to have blanks show up in the data because this is a report with multiple months but all the months line up so even if no data, it needs to be there

So this goes to my question, of why not just always show the lines?

 

[MajP]

can you show an example of what it looks like and how you would want it to look when there is no data?

 

[shelby55]

Hi MajP

Thanks for responding.

There is a difference between not having ANY data for a month versus null values for a particular month. So if I ran the report with a starting date of April 2009 it would run for April 2009 to March 31, 2010. But if there isn't any data (which is possible for the data being reported in one of the subreports) then it should still have a line because there are 12 months of data.

If I run it for April 2010 and there is only Apr to Sep in the database, then I don't want lines for the other 6 months....it's a cosmetic thing. If there is no workaround then I guess the lines will have to be permanent.

I just thought since the month label caption is based on code from the Report_Open event and the presence of a date and not data that I could use it to show the lines or not but it didn't work.

 

[shelby55]

Hi MajP

This is how I want it to look:

[tt]Header Month 1 Month 2
Disposition # Cases # Days Avg Days # Cases # Days Avg Days

Detail

Directly Home | 8 80 10.0 | 12 48 4.0
Tsfr to Acute | |
Tsfr to Rehab | 5 50 10.0 | [/tt]

In the above scenario there are 2 months as evidenced by the month 1 and 2 label (which would actually be months since they are based on the startdate and addmonth functions). So if they are present, no matter what data is or isn't in the month, I want lines. Otherwise, no month then no lines required.

Hope that makes sense.

 

[MajP]

I think you only talking about hiding the lines (and maybe certain controls) that are associated with a month beyond the max date.

A dmax on the reports query would return the max date. So something like this on the reports open

dim maxDate as date
maxDate = dmax("yourDateField",me.recordsource)
'convert the max date to last of month
maxDate = dateserial(year(maxDate),month(maxDate) + 1,0)
'your lines are associated to the first of the month of a startDate + a month
Me.ln1_C.Visible = (maxDate >= addMonth(starDate, 1)
Me.ln2_C.Visible = (maxDate >= addMonth(starDate, 2)

 

[shelby55]

Hi MajP

Thanks...not sure what you mean by "add to report query"..do you mean in the report itself and if so on what event?

Thanks.

 

[MajP]

You are right, I cannot use the reports query to find the max because it is basically de-normalized into months, and there is not a single field with the dates. I need to go to the original source which has the dates in a single field.

This is untested. You should however be able to test the maxdate function after editing. In the immediate window type the following lines followed by hitting return.

startDate = #some date here#
?getMaxDate()

So call a sub something like this on the reports open event.

Public sub hideLines() 
  dim maxDate as date
  maxDate = getMaxDate()
 'your detail lines are associated to a month.
  Me.ln1_D.Visible = (maxDate >= addMonth(starDate, 1))
  Me.ln2_D.Visible = (maxDate >= addMonth(starDate, 2))
end sub

public function getMaxDate() as date
 'You want the max date in the table/query that is greater than your startdate
  'domain is the name of your table or query
  'startDate is the global variable
  'expr is the name of your date field
  dim strWhere as string
  constant domain = "yourTableName"
  constant expr = "yourfieldName"
  strWhere = expr & " > " & sqlDate(startDate)
  debug.print strWhere
  getMaxDate = nz(dmax(expr,domain,strWhere))
  'convert the max date to last of month
  getMaxDate =  dateserial(year(maxDate),month(maxDate) + 1,0)
end function

Function SQLDate(varDate As Variant) As Variant
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

 

[shelby55]

Hi MajP

Thanks so much. I typed all of this into the report itself via On Open event...should I have entered somewhere else? The code isn't crashing but it isn't working...all lines still visible even when there isn't any data for a particular month.

Thanks.

 

[MajP]

The on open event should simply call the hidelines

I can not really replicate you application, so that code is complete freehand typed into the Tek-tips window. So I would be suprised if it did work (or I am really good). You will have to do some debugging. What does the debug.print return? Also did you try to test the getmaxdate() function?

 

[shelby55]

Hi

Right but am I entering it in a module and then calling it? What part of the code is to go in the report - I know I need to test first but just trying to figure logistics. Thanks.

 

[MajP]

you can put the getMaxDate and sqlDate functions in a standard module. They do not need any information directly from the form. The hide lines procedure uses the Me keyword so it would have to go in the reports module. And it makes sense since it really is specific to the report.

 

[shelby55]

Hi

Am I supposed to having Dim statements for the domain and expr? I tried to run the getMaxDate() and it produces a compile error at domain indicated "variable not defined".

 

[shelby55]

Hi

Actually I changed them to Const and it accepted them but now it balks at the maxDate used in the second maxDate = line. We haven't set it yet so how can I reference it?

 

[shelby55]

Hi

Sorry but one more thing: strWhere = MonthEnd>#12/30/1899#. The start date in another module is defined as:

 startDate = Nz(Forms("frmReportDialog").txtStartDate,0)