VB to Add Month to Start Month

[shelby55]

Hi

I'm using Access 2003 but haven't had much experience except to run standard queries and store data in tables.

I posted in the Reports section about wanting the following format
[tt]
Apr May
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 [/tt]

MajP was kind enough to respond and advise me to aggregate the data by disposition code first and then create one query per month (for a total of 12 queries). Then link all the queries together on disposition and use that for the report.

To separate out by fiscal year I also had fiscal year as a link so that I could distinguish between 2008 Apr data and 2009 Apr data.

This idea worked well except for years where there wasn't full data i.e. I only have Apr to Sep 2010 data and since there aren't entries for all of 2010 NO 2010 data shows up.

MajP suggested:

....you probably want to modify the 12 queries so they are not specific months but they are a month based off of a start month. You would have qryMonth1 to qryMonth12. Then you could prompt the user for a start month and build the report for any 12 month period. So you would have in your criteria of qryMonth2 something like
"where yearMonth = getYearMonth(1). getYearMonth(1) would be a vb function to add a month to your startmonth.

Problem is, I don't know how to do that. Can anyone suggest how I can do this? Thanks very much.

 

[shelby55]

Hi

Okay maybe I'm not as stupid as I thought!! Because I've created buttons for Print Preview and Print the start date needs to be established BEFORE I push those buttons to open the report, not on close of the report dialog form.

So I just added the line of startdate=me.txtStartDate at the beginning of the button code and it worked. For the Inst, I had in each query which was stupid since it only needs to be in one: the one being used in the report so in that query I added that Unit = [Forms]![frmReportDialog]![lstInst].

I also took the part out of the Print and Preview button fields that filters the month ending date because startdate and addmonth functions take care of that so that filter only has where Unit = [Forms]![frmReportDialog]![lstInst].

So I may be back but I think I'm on the right track....thanks so much for all of your guidance and patience!!

 

[shelby55]

Hi

I have the same problem as originally and that is that if I don't have an entire year of data then the report isn't working. I am assuming that it is because the query linking all the 12 queries for the year together isn't left outer join. But when I try to make left out join I get an error message indicating "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement".

Can someone please help me with this....thanks very much.

 

[shelby55]

Hi

Sorry...false alarm. I hadn't changed all of the joins thus the error message...it's fixed now.

Thanks.

 

[shelby55]

Hi

Sorry but another question: can the label print only if there is data? Because the fields for "add month" will calculate whether there is data for those months or not, how do I have the labels only print when there is data in the months the labels are for?

What about other labels i.e. I have cases, days and alos for each month of data and I don't want them showing for months that don't have data....

Thanks very much.

 

[MajP]

you should be able to do something like this:
For the controls in the detail section. You can put a ? in the Tag field (no "" necessary around the ?). This way the code only looks at the controls you are concerned about.

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

for the labels up in the header

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
  Me.lblMonth1.Visible = Not IsNull(Me.SomeMonth1Field)
End Sub

 

[shelby55]

Hi

One final question: On the same dialog box as the start date I have a list object for selection of Unit. Unit is the nursing unit for the patient. As with above, there will be multiple queries so using the forms!frmReportDialog!lstUnit won't work.

In a module I added:

Public Function getUnit()
Dim Unit As String

Unit = Nz(Forms("frmReportDialog").lstUnit,0)
End Function

For the button on the frmReportDialog I have the code of:

Private Sub cmdPreview_Click()
On Error GoTo Err_Command33_Click

startDate = Me.txtStartDate
Unit = Me.lstUnit

    Dim stDocName As String
      
    stDocName = "rptTable"
    DoCmd.OpenReport stDocName, acPreview, , strFilter
    Me.Visible = False
    
Exit_Command33_Click:
    Exit Sub

Err_Command33_Click:
    MsgBox Err.Description
    Resume Exit_Command33_Click

End Sub

So in the query for the report I put getUnit() in the Unit but I get a compile error indicating that the variable is not defined.

Note that the lstUnit multiselect = none. I think that I need to indicate that the result of the lstUnit = Unit in the frmReportDialog but using .ItemsSelected produced the same compile error.

Thanks.

 

[MajP]

in general

public function functionName(parameter, parameter2,..) as returnType
... some code
'Now set the function name to some value
functionName = ...
end function

Public Function getUnit()
Dim Unit As String
Unit = Nz(Forms("frmReportDialog").lstUnit,0)
' so getUnit = null because you never set it equal unit
End Function

So
Public Function getUnit()
Dim Unit As String
Unit = Nz(Forms("frmReportDialog").lstUnit,0)
getUnit = Unit
'however your NZ will try to set your string to 0
'Use ""
End Function

Or shorter
Public Function getUnit()as string
getUnit = Nz(Forms("frmReportDialog").lstUnit,"")
End Function

 

[shelby55]

Hi

You rock, MajP ... I wish I could give you a billion stars!!! Thanks so much for your assistance.

 

[shelby55]

Hi MajP

Sorry but I've screwed up somewhere...so in the cmdButton code, I've indicated at the beginning of the code:

startDate=Me.txtStartDate[code] but what do I put for the Unit?  

I used:  [code]getUnit=me.lstUnit

but I get the compile error "function call on left hand side of assignment must return variant or object". Not sure what this means...thanks.

 

[shelby55]

Hi MajP

Never mind...I figured it out!! Thanks again for all your help!

 

[shelby55]

Hi

I just found an issue with this method: the 12 months are linked via Unit and Disposition starting with a disposition table that has all dispositions. But I found that if the month preceding doesn't have that particular disposition then it doesn't show up in that month's data.

For example: Month8 doesn't have any cases under "Died" so the death case for Month9 isn't showing up. They are linked using left outer (selection 2 in the join properties window).

Is there any other way to do this so the data is accurately reported? Thanks.

 

[MajP]

Link all subsequent queries to the disposition table, instead of chaining them.

 

[shelby55]

Hi MajP

Thanks for responding. I've been playing around with this and found that because the "group" for this data is Unit that I had to create a new table with all disposition types per Unit. Now I link this table to each of them via Unit and Disp and I think it's going to work.

 

[shelby55]

Hi

I'm back at this hoping for help with changing a title.

In the report I have the title for the timeframe as:

=Format(Forms!frmReportDialog!txtStartDate,"MMM-yyyy") & " to " & Format(addmonth(getStartDate(),11),"MMM-yyyy")

But if there isn't a full year of data then the full year name is showing up. So if I only have Apr to Sep 2010 in the database then the title shows as April 1, 2010 to March 31, 2011.

The dialog box noted above only gives start date so the other queries will work (the report shows 12 months of data from what is entered on a dialog box as start date).

Is there any way to change the title for instances where there isn't the full year of data? Thanks.

 

[dhookom]

If your report is bound to records in the date range, you might be able to use:

=Format(Min([DateField]),"MMM-yyyy") & " to " & Format(Max([DateField]),"MMM-yyyy")

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I do not have time to look at it in detail, but I thought in another post we came up with a function/s to determine if a month had data and possibly the last month with data. Look if you have any functions

But basically you would need to change this

=Format(Forms!frmReportDialog!txtStartDate,"MMM-yyyy") & " to " & Format(addmonth(getStartDate(),11),"MMM-yyyy")

==Format(Forms!frmReportDialog!txtStartDate,"MMM-yyyy") & " to " & Format(getLastDataDate(11),"MMM-yyyy")

then some function like this pseudo code

Public function getLastDataDate(monthsToAdd as integer) as date
dim startDate as date
dim i as integer
startDate = getStartDate
---
for i = monthsToAdd to 1 step -1
check to see if that month has data
current month = the start date + i
getLastDataDate = the current month
if has data then exit function
next i

end function

 

[shelby55]

Hi MajP/Duane

Sorry for the delay in replying...I've been off sick.

I do have the maximum date function created but it is for maximum function of the entire database:

Public Function getMaxDate() As Date
Dim strWhere As String

Const domain = "qryALCDays_C1"
Const expr = "MonthEnd"
strWhere = expr & ">" & SQLDate(startDate)

getMaxDate = Nz(DMax(expr, domain, strWhere))
getMaxDate = DateSerial(Year(getMaxDate), Month(getMaxDate) + 1, 0)

End Function

The report is programmed to be 12 months no matter the start date but if there isn't 12 months from the start date, I need the max date to be the database max. I realize this will be an if statement for the title but don't know how to phrase the if startdate + end date = 12 then do this else do this.

Thanks.
What I need is max date for the period chosen. For instance, if the

 

[dhookom]

Shelby55,
Did you even try my suggestion? It is not the same as your getMaxDate() function.

Your function ignores the filtering of the report and relies on a saved query. In addition your function creates a greater performance hit.


Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

The report is not bound by a table or query, it is a series of subreports with each based on a different query. For that reason, your solution won't work though I appreciate the suggestion. Thanks.