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.

 

[MajP]

For sorting and grouping on a Month I convert the dates to the first of the month.

Public Function getFirstOfMonth(dtmDate As Variant) As Variant
  If Not IsNull(dtmDate) Then
    getFirstOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)
  End If
End Function

Public Function addMonth(dtmDate As Variant, MonthsToAdd As Integer) As Variant
  Dim I As Integer
  If Not IsNull(dtmDate) Then
    addMonth = getFirstOfMonth(dtmDate)
    addMonth = DateAdd("M", MonthsToAdd, addMonth)
  End If
End Function

Select getFirstOfMonth([someDateField}) as MonthYear, addMonth([someDateField],1) as NextMonth,..

If you have a public variable as your start month you can use that in a query by wrapping it in a function

public startDate as date

public function getStartDate() as date
getStartDate = startDate
end function

 

[shelby55]

Hi

Sorry but you lost me....where am I entering this information? What will be in query1 and all subsequent queries?

Thanks.

 

[MajP]

So lets say you make a module that looks like this

Public startDate As Date

Public Function getFirstOfMonth(dtmDate As Variant) As Variant
  If Not IsNull(dtmDate) Then
    getFirstOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)
  End If
End Function

Public Function addMonth(dtmDate As Variant, MonthsToAdd As Integer) As Variant
  Dim I As Integer
  If Not IsNull(dtmDate) Then
    addMonth = getFirstOfMonth(dtmDate)
    addMonth = DateAdd("M", MonthsToAdd, addMonth)
  End If
End Function

Public Function getStartDate()
  If startDate = 0 Then
    getStartDate = getFirstOfMonth(Date)
  Else
    getStartDate = getFirstofMonth(startDate)
  End If
End Function

Now you have a form that allows you to set a start month. You would have some code that sets the value of the public variable startMonth.

Lets say I have a field orderDate that has dates. The dates can occur on any day of the month. If I want to return all records that occur in the month and year I set as a variable, then:

SELECT Orders.OrderDate
FROM Orders
WHERE (((DateSerial(Year([orderDate]),Month([orderDate]),1))=getStartDate()));

If I want to get all the order from 2 months after my start date then.

SELECT Orders.OrderDate
FROM Orders
WHERE (((DateSerial(Year([orderDate]),Month([orderDate]),1))=addMonth(getStartDate(),2)));

 

[MajP]

Should say that your form allows you to set the value of "startDate" not "startMonth".

 

[shelby55]

Hi MajP

Sorry but I'm still not understanding.

I created a module (should it be standard or class?) with the code you created.

I then created a query and in SQL I typed the second part using my table information:

select flo_data.DisDate
from flo_data
where (((DateSerial(Year([DisDate]),1))=getstartdate()));

When I run the query nothing shows up but I assume that is because I have to have somewhere for the "startdate" to be selected.

You indicated that "now you have a form that allows you to set a start month"....what part of what I did gave me a form?

So how do I utilize the functions you created on a form or otherwise so that I have a start date to then create the other queries?

Thanks MajP!!

 

[MajP]

If you have a global variable that you use throughout the application, you put it in a standard module and declare it as public

Option Compare Database
Option Explicit

public startDate as date

you can set this variable anywhere in the application by code like
startDate = ...

So maybe you have a popup form that asks for the startdate. Then it would be something like this in one of the form's events.

startDate = me.txtBoxStartDatePrompt

Since this is a global variable you can call it anywhere in the application by simply
.... = startDate

But you can not use a variable in a sql string or in an unbound control. But if you wrap the variable in a function you can use it in both.

Public Function getStartDate()
getStartDate = startDate
End Function

If we want to make sure the start date is the first of the month versus any selected date then you can modify the function.

Public Function getStartDate()
If startDate = 0 Then
getStartDate = getFirstOfMonth(Date)
Else
getStartDate = getFirstofMonth(startDate)
End If
End Function

Now in an unbound control
=getStartDate
will show the global variable date that you set somewhere else in the application

Or in a sql
Select getStartDate() as StartDate .... where someField = getStartDate()


Unfortunately, this is one of those problems that takes a lot of steps
1)make a global variable
2)make code to set the global variable
3)make a function to return the global variable
4)make aggregate queries to get the data
5)make several queries that use the function and the aggregate data

Each step is relatively easy, but they all work together which makes the solution hard and harder to explain.

If you still have problems getting this all together then, I can show you another approach. It would be real helpful if you could post a table (run a make table query) of the aggregate data. Something that has the cases, days, and avg days. That will let me see if this can be done in a crosstab query. If not I will read the data in the table starting from the start month and put it into a columanar table for 12 months.

 

[shelby55]

Hi MajP

Thank you so much for taking such time to try to help me! I'm still not sure I have it but will forge ahead and see what I can do. I'll let you know how I do...thanks again!

 

[MajP]

Here is a working 3 month solution. Just keep following the pattern. The assumption is you can create a query that returns data similar to the "tblDisposition"

1) Look at the functions in the module
2) Look at the queries and how they use the function
3) Look at the code on the report
4) Run the report

 

[MajP]

http://www.4shared.com/document/WvJZkTvm/MonthAdd.html

Sorry here is the link.

 

[shelby55]

HI MajP

Thank you so much for taking the time to create the test database...I really, really appreciate it!!

I understand the workings of this but your database was already using aggregrate data but my data is from a table and then I'm aggregating via a query. I'm changing the month/year to just Aug-2010 or Apr-2010 etc. but currently startdate is a full date, correct? I changed the mmm-yyyy to monthend using code from PHV which is

dateserial(Year[DisDate]),1+Month([DisDate]),0)

When I take the query and place in M1 query and place getstartdate() in the column for the above code, nothing shows up. I know that nothing shows up for your example either but you still have blanks showing or zeros showing for the initial (and only) entry whereas I have nothing as in closed off.

What am I doing wrong? Also, because I have other parameters such as institution number I think I may wish to include the parameters in a dialog box form.

Thanks.

 

[MajP]

I'm changing the month/year to just Aug-2010 or Apr-2010 etc. but currently startdate is a full date, correct? I changed the mmm-yyyy to monthend using code from PHV which is

All dates in vb are stored behind the scenes as as number. The integer part is the amount of elapsed days since 12/30/1899 (the base date). The decimal part is the fraction of the next day.

4/1/2010 is stored as 40269
4/1/2010 12:00:00 pm is 40269.5 (or 40269 elapsed days since the base date, and half another day)

Aug-2010 is not what is stored, that is only a format on the day for display purposes.

The code you show converts a day into the last day of the month: dateserial(Year[DisDate]),1+Month([DisDate]),0)
My code assumed you converted everything to the first day of the month. It does not matter which one you choose, just have to be in agreement.

You should just be able to replace this:
dateserial(Year[DisDate]),1+Month([DisDate]),0) last day of month
with
dateserial(Year[DisDate]),Month([DisDate]),1) first day of month

Without seeing the aggregate, I do not know if there was a specific requirement to use the last day of the month. So I cannot tell if you can simply replace it. Also cannot tell if you coverted the date into a string, which would also require some modification.

I have other parameters such as institution number I think I may wish to include the parameters in a dialog box form.

Yest, the input box was for demo purposes only. I would never do it that way. I would create a popup form and pull the values from there.

 

[shelby55]

Hi MajP

Sorry to be such a pain but I think I'm doing this wrong. I created a form to capture startdate from the user. But I think I'm using it wrong with the queries because now, if I have multiple queries or I split the queried data into subreports, I'm prompted multiple times for the start date and I should be passing it to the function of startdate so once it's entered then I don't get prompted again, right?

So right now qryM1 has for criteria [frmReportDialog]![txtStartDate]. The subsequent fields are: addmonth([frmReportDialog]![txtStartDate],1).

Thanks.

 

[MajP]

There are several ways to do it.
You can stick with the global variable declared in a standard module:

Public startDate As Date

Then in my report I popped open an input box. Instead you would pop open your own form (ensure you open it dialog so that the code stops executing until you close the form). On the forms close event you can set the value of the global variable equal to the value in your textbox:
startDate = me.txtStartDate

The other way to do it is to open the form first. Make your inputs on the form, and then have a button to open the report. You could get rid of the global variable, and Now you could modify the getStartDate to something like

Public Function getStartDate()
dim startDate as date
startDate = nz(forms("reportDialog").txtStartDate,0)
If startDate = 0 Then
getStartDate = getFirstOfMonth(Date)
Else
getStartDate = getFirstofMonth(startDate)
End If
End Function

 

[PHV]

{i]qryM1 has for criteria [frmReportDialog]![txtStartDate][/i]
I'd use Forms!ReportDialog!txtStartDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Thanks PHV...so this will prevent me from being queried multiple times for the start date?

 

[shelby55]

Hi MajP

Okay, still stuck. I created the form document and on have buttons for preview, cancel and print.

The Preview button has the criteria of:

rivate Sub cmdPreview_Click()
On Error GoTo Err_Command33_Click

    Dim stDocName As String
    Dim strFilter As String
        
    strFilter = "[MonthEnd]>= #" & [Forms]![frmReportDialog]![txtStartDate] & "#" & "Unit = & [Forms]![frmReportDialog]![lstInst]"
    
    
    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

I also included the code of startDate = me.txtStartDate on the close event as per your suggestion.

I also changed the code within the module as per your suggestion to:

Option Explicit
Public startDate As Date

Public Function getFirstOfMonth(dtmDate As Variant) As Variant
  If Not IsNull(dtmDate) Then
    getFirstOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)
  End If
End Function

Public Function addMonth(dtmDate As Variant, MonthsToAdd As Integer) As Variant
  Dim I As Integer
  If Not IsNull(dtmDate) Then
    addMonth = getFirstOfMonth(dtmDate)
    addMonth = DateAdd("M", MonthsToAdd, addMonth)
  End If
End Function


Public Function getStartDate()
Dim startDate As Date
startDate = Nz(Forms("frmReportDialog").txtStartDate, 0)
If startDate = 0 Then
    getStartDate = getFirstOfMonth(Date)
    Else
    getStartDate = getFirstOfMonth(startDate)
End If
End Function

Option Explicit
Public startDate As Date

Public Function getFirstOfMonth(dtmDate As Variant) As Variant
If Not IsNull(dtmDate) Then
getFirstOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)
End If
End Function

Public Function addMonth(dtmDate As Variant, MonthsToAdd As Integer) As Variant
Dim I As Integer
If Not IsNull(dtmDate) Then
addMonth = getFirstOfMonth(dtmDate)
addMonth = DateAdd("M", MonthsToAdd, addMonth)
End If
End Function

But I'm still confused as to what I include in the queries because I'm still getting queried multiple times for the start date and unit.

These parameters are required for the 12 queries we discussed but also another 12 queries for different data. All will be in subreports which is the other reason I'd like it to be just a single passing of these variables.

Thanks.

 

[MajP]

any chance you can post a representative portion of the database? Debugging this starts to get hard because it is a chain: A form that sets a variable that is used in a function that is used in a query that is use by other queries that is used in a report. Each part is relatively simple, but how they come all together can be difficult to see.

 

[PHV]

strFilter = "[MonthEnd]>=#" & [Forms]![frmReportDialog]![txtStartDate] & "# AND Unit=" & [Forms]![frmReportDialog]![lstInst]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Sorry MajP, not sure what you're asking. I can provide anything you want, just not sure what you mean about representative portion of database. So what's in the queries? Or table?

Thanks.

PHV...thanks for correcting my syntax....still not sure what to put in my queries but glad that the form works.