Trying to find previous (fiscal) month to date

D

DIH

Hi all,

First some background info:

See:
http://groups.google.com/group/micr...ddcf69d?hl=en&q=fiscal+month#f69070bfd762f19f

I have a lookup table called [tblFiscalLookup1].
I have a unbound control on a form called
[forms]![frmDailyProdReport]![txtStartDate].

When the user clicks a date in a calendar control (which in turn puts
the selected date into a control called CalStartDate) the following code
will return the first day of our (rather unique) fiscal month by looking
up the value from the lookup table (and then puts that date into a
control called txtStartDate (which gets used in a query later on):

==================================================================
Private Sub CalStartDate_AfterUpdate()
If IsNull(Me.CalStartDate) Then
Exit Sub
End If

intMonthNum = DLookup("MonthOfYear", _
"tblFiscalLookUp1", _
"DayOfYear=" & Format(CalStartDate, "\#yyyy-m-d\#"))
Me.txtstartdate = DMin("DayOfYear", _
"tblFiscalLookUp1", _
"MonthOfYear=" & intMonthNum)
End Sub
====================================================================
I then can use the WHERE clause in a summary query to give the month to
date results using:

Between [Forms]![frmDailyProdReport].[txtStartDate] And
[Forms]![frmDailyProdReport].[cbostartdate].

What I now need is a query that will return the "previous" month's month
to date value. I started out thinking that since I already have the
first day of the current fiscal month , all I would need to do is
subtract that date by one and use a lookup again to give me what the new
first date of the previous month is.

Here is what I've come up with but I get the following error:

"You did not enter the keyword AND in the Between... And operator."

Between DMin("DayOfYear","tblFiscalLookUp1","MonthOfYear=" &
Dlookup("MonthOfYear","tblFiscalLookUp1","DayOfYear=" &
[txtStartDate]-1) And ([forms]![frmDailyProdReport]![txtStartDate]-1))

I know something just isn't right with this though.

In other words if the first day of a fiscal month starts on 11/23/08, I
would then get as one value 11/22/08 and I would then need the first
date of the fiscal month that has 11/22/08 in it.

I know this may seem rather confusing, so any help would be greatly
appreciated. Thanks again to Marshall Barton who gave me the answer to
my original post.


Dave
 
D

Dale Fye

DIH,

I think you just have your ) in the wrong place. But you should also
probably wrap the date in the DayOfYear part of the DLookup in the # symbol.
Try:

Between DMin("DayOfYear", "tblFiscalLookUp1", "MonthOfYear=" _
& Dlookup("MonthOfYear", "tblFiscalLookUp1", "DayOfYear=#" &
me.[txtStartDate]-1 & "#"))
And [forms]![frmDailyProdReport]![txtStartDate]-1

Two additional points:
1. I like to use the DateAdd function rather than straight arithmetic, when
adding and subtracting days. It may take a little extra typing, but it
makes it easier for me to recognize what I was trying to accomplish when I'm
maintaining code.
2. When comparing the values in date/time fields (in this manner), you need
to be careful that you are actually comparing just the date part of the
field. If it is filled in with the Now() function rather than the Date()
function, it will contain both date and time values, and doing a comparison
of Between #12/1/2008# and #12/31/2008# will get you different values than
would between #12/1/2008# and #1/1/2009#. For that reason, I like to use >=
and < in my date comparisons, rather than BETWEEN. This would look like:

WHERE [Some field] >= DMin("DayOfYear", "tblFiscalLookUp1", "MonthOfYear=" &
Dlookup("MonthOfYear", "tblFiscalLookUp1", "DayOfYear=#" & Dateadd("d", 1,
[forms]![frmDailyProdReport].[txtStartDate]) & "#"))
AND [Some field] < [forms]![frmDailyProdReport]![txtStartDate]

The other point is that since you already know the month of the year (which
you computed the CalStartDate_AfterUpdate), you could just use that value to
populate a hidden control "txtPrevMonthStartDate" with a single line of code
in that same subroutine.

me.txtPrevMonthStartDate = DMIN("DayOfMonth", "tbl_FiscalLookup1",
"MonthOfYear = " & intMonth - 1)

Then, in the query I would use

WHERE [some field] >= [Forms]!frmDailyProdReport.txtPrevMonthStartDate
AND [some field] < [Forms]!frmDailyProdReport.txtStartDate

HTH
Dale


DIH said:
Hi all,

First some background info:

See:
http://groups.google.com/group/micr...ddcf69d?hl=en&q=fiscal+month#f69070bfd762f19f

I have a lookup table called [tblFiscalLookup1].
I have a unbound control on a form called
[forms]![frmDailyProdReport]![txtStartDate].

When the user clicks a date in a calendar control (which in turn puts the
selected date into a control called CalStartDate) the following code
will return the first day of our (rather unique) fiscal month by looking
up the value from the lookup table (and then puts that date into a control
called txtStartDate (which gets used in a query later on):

==================================================================
Private Sub CalStartDate_AfterUpdate()
If IsNull(Me.CalStartDate) Then
Exit Sub
End If

intMonthNum = DLookup("MonthOfYear", _
"tblFiscalLookUp1", _
"DayOfYear=" & Format(CalStartDate, "\#yyyy-m-d\#"))
Me.txtstartdate = DMin("DayOfYear", _
"tblFiscalLookUp1", _
"MonthOfYear=" & intMonthNum)
End Sub
====================================================================
I then can use the WHERE clause in a summary query to give the month to
date results using:

Between [Forms]![frmDailyProdReport].[txtStartDate] And
[Forms]![frmDailyProdReport].[cbostartdate].

What I now need is a query that will return the "previous" month's month
to date value. I started out thinking that since I already have the first
day of the current fiscal month , all I would need to do is subtract that
date by one and use a lookup again to give me what the new first date of
the previous month is.

Here is what I've come up with but I get the following error:

"You did not enter the keyword AND in the Between... And operator."

Between DMin("DayOfYear","tblFiscalLookUp1","MonthOfYear=" &
Dlookup("MonthOfYear","tblFiscalLookUp1","DayOfYear=" & [txtStartDate]-1)
And ([forms]![frmDailyProdReport]![txtStartDate]-1))

I know something just isn't right with this though.

In other words if the first day of a fiscal month starts on 11/23/08, I
would then get as one value 11/22/08 and I would then need the first date
of the fiscal month that has 11/22/08 in it.

I know this may seem rather confusing, so any help would be greatly
appreciated. Thanks again to Marshall Barton who gave me the answer to my
original post.


Dave
Click to expand...
 
D

DIH

Thanks much Dale! Right off the bat your first statement works
perfectly. I will look into your other suggestions as well.

Thanks again!

Dave

Dale said:
DIH,

I think you just have your ) in the wrong place. But you should also
probably wrap the date in the DayOfYear part of the DLookup in the # symbol.
Try:

Between DMin("DayOfYear", "tblFiscalLookUp1", "MonthOfYear=" _
& Dlookup("MonthOfYear", "tblFiscalLookUp1", "DayOfYear=#" &
me.[txtStartDate]-1 & "#"))
And [forms]![frmDailyProdReport]![txtStartDate]-1

Two additional points:
1. I like to use the DateAdd function rather than straight arithmetic, when
adding and subtracting days. It may take a little extra typing, but it
makes it easier for me to recognize what I was trying to accomplish when I'm
maintaining code.
2. When comparing the values in date/time fields (in this manner), you need
to be careful that you are actually comparing just the date part of the
field. If it is filled in with the Now() function rather than the Date()
function, it will contain both date and time values, and doing a comparison
of Between #12/1/2008# and #12/31/2008# will get you different values than
would between #12/1/2008# and #1/1/2009#. For that reason, I like to use >=
and < in my date comparisons, rather than BETWEEN. This would look like:

WHERE [Some field] >= DMin("DayOfYear", "tblFiscalLookUp1", "MonthOfYear=" &
Dlookup("MonthOfYear", "tblFiscalLookUp1", "DayOfYear=#" & Dateadd("d", 1,
[forms]![frmDailyProdReport].[txtStartDate]) & "#"))
AND [Some field] < [forms]![frmDailyProdReport]![txtStartDate]

The other point is that since you already know the month of the year (which
you computed the CalStartDate_AfterUpdate), you could just use that value to
populate a hidden control "txtPrevMonthStartDate" with a single line of code
in that same subroutine.

me.txtPrevMonthStartDate = DMIN("DayOfMonth", "tbl_FiscalLookup1",
"MonthOfYear = " & intMonth - 1)

Then, in the query I would use

WHERE [some field] >= [Forms]!frmDailyProdReport.txtPrevMonthStartDate
AND [some field] < [Forms]!frmDailyProdReport.txtStartDate

HTH
Dale


DIH said:
Hi all,

First some background info:

See:
http://groups.google.com/group/micr...ddcf69d?hl=en&q=fiscal+month#f69070bfd762f19f

I have a lookup table called [tblFiscalLookup1].
I have a unbound control on a form called
[forms]![frmDailyProdReport]![txtStartDate].

When the user clicks a date in a calendar control (which in turn puts the
selected date into a control called CalStartDate) the following code
will return the first day of our (rather unique) fiscal month by looking
up the value from the lookup table (and then puts that date into a control
called txtStartDate (which gets used in a query later on):

==================================================================
Private Sub CalStartDate_AfterUpdate()
If IsNull(Me.CalStartDate) Then
Exit Sub
End If

intMonthNum = DLookup("MonthOfYear", _
"tblFiscalLookUp1", _
"DayOfYear=" & Format(CalStartDate, "\#yyyy-m-d\#"))
Me.txtstartdate = DMin("DayOfYear", _
"tblFiscalLookUp1", _
"MonthOfYear=" & intMonthNum)
End Sub
====================================================================
I then can use the WHERE clause in a summary query to give the month to
date results using:

Between [Forms]![frmDailyProdReport].[txtStartDate] And
[Forms]![frmDailyProdReport].[cbostartdate].

What I now need is a query that will return the "previous" month's month
to date value. I started out thinking that since I already have the first
day of the current fiscal month , all I would need to do is subtract that
date by one and use a lookup again to give me what the new first date of
the previous month is.

Here is what I've come up with but I get the following error:

"You did not enter the keyword AND in the Between... And operator."

Between DMin("DayOfYear","tblFiscalLookUp1","MonthOfYear=" &
Dlookup("MonthOfYear","tblFiscalLookUp1","DayOfYear=" & [txtStartDate]-1)
And ([forms]![frmDailyProdReport]![txtStartDate]-1))

I know something just isn't right with this though.

In other words if the first day of a fiscal month starts on 11/23/08, I
would then get as one value 11/22/08 and I would then need the first date
of the fiscal month that has 11/22/08 in it.

I know this may seem rather confusing, so any help would be greatly
appreciated. Thanks again to Marshall Barton who gave me the answer to my
original post.


Dave
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fiscal year (another try) 6
Running Month to date query 6
Fiscal year 2
Fiscal YTD Filter 7
Updating Fiscal month field value based on transaction date 1
Need Function for Year-to-Date spent total 3
Previous Month Date Function 5
Fiscal YTD Calculation Not Working Correctly 2

Top