Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

shelby55 (TechnicalUser) (OP)
3 Dec 10 21:25

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.

Helpful Member!  MajP (TechnicalUser)
4 Dec 10 9:20
I am a little confused in the description.  I assume the results look something like this.

Header                Month 1                  Month 2
Disposition   # Cases # Days  Avg Days  # Cases # Days Avg Days


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  |   

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.

Header                Month 1                  Month 2
Disposition   # Cases # Days  Avg Days  # Cases # Days Avg Days


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

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 (TechnicalUser)
4 Dec 10 9:27

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:

                      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  |   


                      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  |   
MajP (TechnicalUser)
4 Dec 10 9:56
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 (TechnicalUser) (OP)
4 Dec 10 12:26
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

Header                Month 1                  Month 2
Disposition   # Cases # Days  Avg Days  # Cases # Days Avg Days


Directly Home  |  8      80   10.0    |     12     48   4.0  |
Tsfr to Acute  |                          
Tsfr to Rehab  |  5      50   10.0    |                      |   

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 (TechnicalUser) (OP)
7 Dec 10 21:36

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 (TechnicalUser)
8 Dec 10 7:32
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 (TechnicalUser)
8 Dec 10 7:34
can you show an example of what it looks like and how you would want it to look when there is no data?
shelby55 (TechnicalUser) (OP)
8 Dec 10 7:38
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'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 (TechnicalUser) (OP)
8 Dec 10 7:45
Hi MajP

This is how I want it to look:

Header                Month 1                  Month 2
Disposition   # Cases # Days  Avg Days  # Cases # Days Avg Days


Directly Home  |  8      80   10.0    |     12     48   4.0                                  
Tsfr to Acute  |                      |    
Tsfr to Rehab  |  5      50   10.0    |                          

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 (TechnicalUser)
8 Dec 10 8:53
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 (TechnicalUser) (OP)
8 Dec 10 9:12
Hi MajP

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

MajP (TechnicalUser)
8 Dec 10 9:54
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#  

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., June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
shelby55 (TechnicalUser) (OP)
9 Dec 10 17:08
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.  


MajP (TechnicalUser)
9 Dec 10 17:17
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 (TechnicalUser) (OP)
9 Dec 10 17:29

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 (TechnicalUser)
9 Dec 10 18:27
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 (TechnicalUser) (OP)
9 Dec 10 19:53

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 (TechnicalUser) (OP)
9 Dec 10 19:57

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 (TechnicalUser) (OP)
9 Dec 10 20:02

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)

MajP (TechnicalUser)
9 Dec 10 21:27
startdate is a global date variable.  If it is not set to anything then by default it equals 0.  In vb a date of 0 is 12/30/1899, so that appears correct.  You can set it manually in the immediate window for test purposes.
startdate = #some date here#
shelby55 (TechnicalUser) (OP)
9 Dec 10 21:35

Thanks very much...but then why is it not working to make the lines invisible?   
MajP (TechnicalUser)
10 Dec 10 7:37
can you post your hidelines code?
shelby55 (TechnicalUser) (OP)
10 Dec 10 10:17

The code for hidelines that I placed in the Report Module is:


Public Sub hideLines()
Dim maxDate As Date
maxDate = getMaxDate

Me.ln1_A.Visible = (maxDate >= addMonth(startDate, 1))
Me.ln2_A.Visible = (maxDate >= addMonth(startDate, 2))
Me.ln3_A.Visible = (maxDate >= addMonth(startDate, 3))
Me.ln4_A.Visible = (maxDate >= addMonth(startDate, 4))
Me.ln5_A.Visible = (maxDate >= addMonth(startDate, 5))
Me.ln6_A.Visible = (maxDate >= addMonth(startDate, 6))
Me.ln7_A.Visible = (maxDate >= addMonth(startDate, 7))
Me.ln8_A.Visible = (maxDate >= addMonth(startDate, 8))
Me.ln9_A.Visible = (maxDate >= addMonth(startDate, 9))
Me.ln10_A.Visible = (maxDate >= addMonth(startDate, 10))
Me.ln11_A.Visible = (maxDate >= addMonth(startDate, 11))
Me.ln12_A.Visible = (maxDate >= addMonth(startDate, 12))

End Sub

I actually have this code per line section i.e. there are lines B for detail section and lines C for the footer.  Thanks.
MajP (TechnicalUser)
10 Dec 10 10:37
Please modify and post back

Public Sub hideLines()
 Dim maxDate As Date
 maxDate = getMaxDate
'Add code for debugging
debug.print "maxDate " & maxDate
debug.print "Ln1 " & addMonth(startDate, 1)
debug.print "Ln1 " & maxDate >= addMonth(startDate, 1)

If the results are as expected, then change the event that calls this to the detail format event.
shelby55 (TechnicalUser) (OP)
10 Dec 10 10:48

When I try to run the sub the macro window shows up but nothing happens because there is no macro to select.  In my previous tests, the info appeared in the immediate window.
shelby55 (TechnicalUser) (OP)
10 Dec 10 11:02
Hi MajP

I just went ahead and moved the code to the format event of each section and now it works!  

Thanks so much for your patience and assistance!!
MajP (TechnicalUser)
10 Dec 10 11:04
since hidelines is in a reports module it will not show up, because you have to run it from the report.  Only code in standard modules will show.

You can test code in a report/form module by fully qualifying it.  Example

Public sub testReportCode()
 'If this is in a standard module you can call code in an open form/report
 'By fully referencing the report or form
end sub  

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close