Coding a function to return a value

[Sara]

I have a query that pulls data for the month, using the Month End Date
from a form (user enters) as criteria. It works. I want to use the
same query to pull "month to date" data, on a weekly basis. So, for
week 1, it would only show the first 7 days; week 2 the first 14 days,
etc.

If I enter the next month ending date (1/1/05) right now, I get the
information for days since the last month end until current date.
That's perfect. My problem is that I would like the system to figure
out the Month End Date from the user entering Week End Date and feed
Month End Date to this query for the different report. All the data I
need is in the query and it works if I manually enter the month ending
date, but I don't know how to get the code to do it.

I wrote a select query to give me all "associated" dates for one sales
date - Last Year, Month Start and Ending Dates, etc. (We are on a
unique fiscal calendar, so I have my dates in a table). I want to
have the code "get" the Month Ending date and feed it to the query for
this report when the user enters "week endind date" and chooses to run
the weekly reports.

This is what I tried. I just don't understand functions too well.

Function GetMonthEndDate(MonthEndDate As Date)

' Run query to get MonthEndDate for WeekEndingDate entered (for build
report)

DoCmd.OpenQuery "qryGetDatesforSalesDate", acNormal, acReadOnly
MsgBox "New " & MonthEndDate

End Function

I know I'm in the code because I see the query opened and I get my
message box, but it just says "new".

To run the function, I say:
' Run query to get MonthEndDate for WeekEndingDate entered (for Spags
build report)
GetMonthEndDate (MonthEndDate)
MsgBox "in 2 Code " & MonthEndDate

I get this message box, but it just says "In 2 Code".

Thanks so much.
Sara

[Tim Mills-Groninger]

"Sara" <sa*******@yahoo.com> wrote in message
news:d9**************************@posting.google.c om...

... My problem is that I would like the system to figure
out the Month End Date from the user entering Week End Date and feed
Month End Date to this query for the different report.

Create the following function and pass the week end date to it.

Public Function MonthEnd(dt As Date)
'Show the last day of month for a given date

MonthEnd = DateSerial(Year(dt), Month(dt) + 1, 0)

End Function

hth,

Tim Mills-Groninger

[sara]

Tim -
Thanks, but the problem still exists. Perhaps I wasn't clear:

Problem 1: We have a unique fiscal calendar. All months end on
Saturday and start on Sunday. Some are 5 weeks and some are 4 weeks
long. For, example, this year, December, our 11th month, ends on
1/1/05. That is why I have a separate table, called "AllDates" that
gives me today, last year's same day, week end, month end, fiscal month
number, etc. I need to take the date the user enters and find the month
ending date for that date. If the user enters 11/5/04, I would see
11/27/04, for example.

Problem 2: I put your code in and added the code:
Dim dt As Date

Call MonthEnd(dt)
I added "Msgbox dt" at the end of your function code to see what I was
getting back.

I hope that is right.

The problem is that I got back 12:00:00 am. I am pretty sure it
wouldn't have been able to know my fiscal calendar without more code or
a query or table reference (which is my origianl question, really), but
I'm using this to try to learn how to call a function and I clearly have
something wrong.

Can you help?

Thanks - in advance.
sara

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[James Fortune]

sara <an********@devdex.com> wrote in message news:<1102183458.6NhiR9PM+7bLtXRfOtRDkA@teranews>. ..

Tim -
Thanks, but the problem still exists. Perhaps I wasn't clear:

Problem 1: We have a unique fiscal calendar. All months end on
Saturday and start on Sunday. Some are 5 weeks and some are 4 weeks
long. For, example, this year, December, our 11th month, ends on
1/1/05. That is why I have a separate table, called "AllDates" that
gives me today, last year's same day, week end, month end, fiscal month
number, etc. I need to take the date the user enters and find the month
ending date for that date. If the user enters 11/5/04, I would see
11/27/04, for example.

The following function assumes that having three or less days in the
final week puts that week into the next fiscal month:

Public Function LastDayOfFiscalMonth(dt As Date) As Date
Dim int5thSat As Integer
Dim int1stSat As Integer
Dim intMonthEnd As Integer
Dim dtNM As Date

int1stSat = (6 - WeekDay(DateSerial(Year(dt), Month(dt), 1), 2)) Mod 7
+ 1
If int1stSat < 4 And Day(dt) <= int1stSat Then
'Belongs to previous month
LastDayOfFiscalMonth = DateSerial(Year(dt), Month(dt), int1stSat)
Exit Function
End If
int5thSat = (6 - WeekDay(DateSerial(Year(dt), Month(dt), 1), 2)) Mod 7
+ 29
intMonthEnd = Day(DateSerial(Year(dt), Month(dt) + 1, 0))
dtNM = DateAdd("m", 1, dt)
If int5thSat <= intMonthEnd Then
If Day(dt) <= int5thSat Then
LastDayOfFiscalMonth = DateSerial(Year(dt), Month(dt), int5thSat)
Else
LastDayOfFiscalMonth = LastDayOfFiscalMonth(DateAdd("d", 7, dt))
End If
Else
'Last Saturday is the fourth Saturday. Check remaining days to see
which way week goes.
If intMonthEnd - int5thSat + 7 < 4 Then 'Three or less extra days
go to next fiscal month
If Day(dt) <= int5thSat - 7 Then
LastDayOfFiscalMonth = DateSerial(Year(dt), Month(dt), int5thSat
- 7)
Else
LastDayOfFiscalMonth = LastDayOfFiscalMonth(DateAdd("d", 7, dt))
End If
Else
'Four or more extra days force the fiscal month to end a week
later
LastDayOfFiscalMonth = DateSerial(Year(dtNM), Month(dtNM),
int5thSat - intMonthEnd)
End If
End If
End Function

Watch out for the linewrap. I used the expression given by Tim for
the month end. I did some moderate testing, but be sure to test this
function to your satisfaction before using it. If I guessed wrong
about how you split up your fiscal months post back.

James A. Fortune

[sara]

James -
I certainly don't mean to seem ungrateful, but there are still 2
problems.

1. I put your code in, along with Msgboxes to display the variable
"dt", which gave 12:00:00 AM all the time. Then I added the display of
dtNM, which gave 1/30/1900 in the function and nothing after the call to
the function and return from the call. I don't know what's wrong there.

2. The second thing, and probably more important (the first is, I
think, my understandig of coding and displaying variables), is that I
think you have the Fiscal calendar wrong. It can't be calculated. It
is calculated then decided each year by Accounting and the Chairman.
(every now and then we have a 53rd week and we have to plan where that
goes) Generally, the months (there are only 12 in a year) go 4-5-4 (4
weeks, 5 weeks, 4 weeks).

So, if I understand your code correctly, you are determining the end of
the month based on how many days in the calendar month after the last
Saturday. I think that works for 2004 and even 2003, but I am not sure
it is ALWAYS going to be the case, or what will happen when we have a
53rd week. This is why we have a lookup table that holds ALL our dates
for all weeks for current and prior years.

Also, I am wondering about the DLOOKUP function I've seen on posts and
in Help. Would that (somehow) work? I am thinking that I will find
occasion to use other dates in one row for other functions. (Such as
quarter start and end dates - again,using our fiscal quarters; and Last
year's quarter start and end). I was hoping for a simple solution and
am not sure there is one!

Again, I am grateful for the interest and help; we're just not there
yet.

Thanks - in advance.
sara

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[rkc]

"sara" <an********@devdex.com> wrote in message
news:1102257861.4KiiT9eM69p7IbgkYA6Cdw@teranews...

So, if I understand your code correctly, you are determining the end of
the month based on how many days in the calendar month after the last
Saturday. I think that works for 2004 and even 2003, but I am not sure
it is ALWAYS going to be the case, or what will happen when we have a
53rd week. This is why we have a lookup table that holds ALL our dates
for all weeks for current and prior years.

Also, I am wondering about the DLOOKUP function I've seen on posts and
in Help. Would that (somehow) work? I am thinking that I will find
occasion to use other dates in one row for other functions. (Such as
quarter start and end dates - again,using our fiscal quarters; and Last
year's quarter start and end). I was hoping for a simple solution and
am not sure there is one!

It sounds like you could use DLookup, but since I never use and you
already have created qryGetDatesforSalesDate i'd do something like
what fallows instead..

Assuming that qryGetDatesforSalesDate is a parameter query that takes a
WeekEndingDate parameter [EndWeekDate], the following function will
return the MonthEndDate associated with it.

wkend = the WeekEndingDate you're working with.

<getMonthEndDate type='aircode'>

Function getMonthEndDate(wkend As Date) As Date
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryGetDatesforSalesDate")
qdf.Parameters("EndWeekDate") = wkend
Set rs = qdf.OpenRecordset

With rs
If Not .EOF Then
getMonthEndDate = .Fields("MonthEndDate")
End If
.Close
End With

Set rs = Nothing
Set qdf = Nothing
End Function

</getMonthEndDate'>

[sara]

I'm getting an error
"Item not found in this collection" on the following statement:

qdf.Parameters("Forms!F_PrintSalesReports!SalesDat e") = wkEnd

I've tried
qdf.Parameters(Me.getWkMoDate); (getWkMoDate) (and every other idea and
combination I could think of)
- and I keep getting the same error

It's being called from the form above, and the user is entering the Week
End Date into a field called "getWkMoDate". When I mouse over
"GetWkMoDate" in the code it shows the date I put in the form.
Thanks again.
sara

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[rkc]

sara wrote:

I'm getting an error
"Item not found in this collection" on the following statement:

qdf.Parameters("Forms!F_PrintSalesReports!SalesDat e") = wkEnd

I've tried
qdf.Parameters(Me.getWkMoDate); (getWkMoDate) (and every other idea and
combination I could think of)
- and I keep getting the same error

It's being called from the form above, and the user is entering the Week
End Date into a field called "getWkMoDate". When I mouse over
"GetWkMoDate" in the code it shows the date I put in the form.
Thanks again.
sara

I am working blind here because you haven't shown the sql
to qryGetDatesforSalesDate. I am assuming that it is a parameter
query and that the parameter is named "EndWeekDate". It probably
isn't so you have to substitute the actual parameter name where
you find "EndWeekDate" in the function I posted.

Once you get that correct you call the function from your form
like so

Forms!F_PrintSalesReports!SalesDate = getMonthEndDate(Me.getWkMoDate)

That should fill Forms!F_PrintSalesReports!SalesDate with the
value returned from the function, which should be the MonthEndDate
associated with the date entered in Me.getWkMoDate. That also
assumes that there is a field returned by your query named
MonthEndDate which is used in the

getMonthEndDate = .Fields("MonthEndDate")

line of the function.

[jimfortune]

I don't know why you are having trouble displaying dates. If your
fiscal month end doesn't conform to rules you will have to use the
dates in your table. The 53rd week would not have caused problems with
what I posted though. I'll let rkc help you get those dates.
James A. Fortune

[sara]

I'm kind of lost here and a little frustrated. First, here is the SQL
of the query:

SELECT ALLDates.SalesDate, ALLDates.DayofYear, ALLDates.DOW,
ALLDates.FiscalWeekNum, ALLDates.WeekStartDate, ALLDates.WeekEndDate,
ALLDates.FiscalMonthNum, ALLDates.MonthStartDate, ALLDates.MonthEndDate,
ALLDates.FiscalQtrNum, ALLDates.QtrStartDate, ALLDates.QtrEndDate,
ALLDates.YearNum, ALLDates.YearStartDate, ALLDates.YearEndDate,
ALLDates.LYSalesDate, ALLDates.LYWeekStartDate, ALLDates.LYWeekEndDate,
ALLDates.LYMonthStartDate, ALLDates.LYMonthEndDate,
ALLDates.LYQtrStartDate, ALLDates.LYQtrEndDate, ALLDates.LYYearNum,
ALLDates.LYStartDate, ALLDates.LYEndDate, ALLDates.Special
FROM ALLDates
WHERE
(((ALLDates.SalesDate)=[Forms]![F_PrintSalesReports]![getWkMoDate]));

It simply takes a date that was entered on the form (the user is
entering WeekEndDate) and finds the associated dates in our Fiscal
calendar.

My problem is that the report I run needs the MonthEndDate as its
parameter, and it runs weekly. It 'builds' - the first week is just
week 1 data; the second week is weeks 1 and 2, etc., until the entire
month is filled in.

I keep getting the error "Item not found in this collection" on the
statement:
qdf.Parameters("WeekEndDate") = wkend
(You are right about the assumptions about the field names - but it's
WeekEndDate).

My code above is:

'Call the Function to get the Month Ending Date for the Spags weekly
Build report
' Call GetMoEndDate(MoEndDate)

' MsgBox MoEndDate

Forms!F_PrintSalesReports!SalesDate = getMonthEndDate(Me.getWkMoDate)

MsgBox MoEndDate
stDocName = "rptSpagsSalesbyWeekTYLY"
On Error GoTo PrintSalesReports_Err
strStepErrorMsg = "Tell IT there was a problem with Weekly
Spags store sales"
DoCmd.OpenReport stDocName, acViewPreview
And the function is:

Function getMonthEndDate(wkend As Date) As Date
'From Web
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryGetDatesforSalesDate")
qdf.Parameters("WeekEndDate") = wkend

Set rs = qdf.OpenRecordset

With rs
If Not .EOF Then
getMonthEndDate = .Fields("MonthEndDate")
End If
.Close
End With

Set rs = Nothing
Set qdf = Nothing
End Function

What I'm trying to learn/understand is how to code the function to
return a value - I seem to be all mixed up with parameters. I "get" it
in queries, and having a form "run" a query. Though I have probably
frustrated you and James, it is unintentional, I assure you. I have a
hugely better understanding of sub vs function now, but still don't feel
I can do it on my own and my books and Help - don't (help, that is).

Thanks for sticking with me. I did try DLookup and think I could get
that to work, if that makes more sense at this time.
Continuing thanks -
sara

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[sara]

I'm kind of lost here and a little frustrated. First, here is the SQL
of the query:

SELECT ALLDates.SalesDate, ALLDates.DayofYear, ALLDates.DOW,
ALLDates.FiscalWeekNum, ALLDates.WeekStartDate, ALLDates.WeekEndDate,
ALLDates.FiscalMonthNum, ALLDates.MonthStartDate, ALLDates.MonthEndDate,
ALLDates.FiscalQtrNum, ALLDates.QtrStartDate, ALLDates.QtrEndDate,
ALLDates.YearNum, ALLDates.YearStartDate, ALLDates.YearEndDate,
ALLDates.LYSalesDate, ALLDates.LYWeekStartDate, ALLDates.LYWeekEndDate,
ALLDates.LYMonthStartDate, ALLDates.LYMonthEndDate,
ALLDates.LYQtrStartDate, ALLDates.LYQtrEndDate, ALLDates.LYYearNum,
ALLDates.LYStartDate, ALLDates.LYEndDate, ALLDates.Special
FROM ALLDates
WHERE
(((ALLDates.SalesDate)=[Forms]![F_PrintSalesReports]![getWkMoDate]));

It simply takes a date that was entered on the form (the user is
entering WeekEndDate) and finds the associated dates in our Fiscal
calendar.

My problem is that the report I run needs the MonthEndDate as its
parameter, and it runs weekly. It 'builds' - the first week is just
week 1 data; the second week is weeks 1 and 2, etc., until the entire
month is filled in.

I keep getting the error "Item not found in this collection" on the
statement:
qdf.Parameters("WeekEndDate") = wkend
(You are right about the assumptions about the field names - but it's
WeekEndDate).

My code above is:

'Call the Function to get the Month Ending Date for the Spags weekly
Build report
' Call GetMoEndDate(MoEndDate)

' MsgBox MoEndDate

Forms!F_PrintSalesReports!SalesDate = getMonthEndDate(Me.getWkMoDate)

MsgBox MoEndDate
stDocName = "rptSpagsSalesbyWeekTYLY"
On Error GoTo PrintSalesReports_Err
strStepErrorMsg = "Tell IT there was a problem with Weekly
Spags store sales"
DoCmd.OpenReport stDocName, acViewPreview
And the function is:

Function getMonthEndDate(wkend As Date) As Date
'From Web
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryGetDatesforSalesDate")
qdf.Parameters("WeekEndDate") = wkend

Set rs = qdf.OpenRecordset

With rs
If Not .EOF Then
getMonthEndDate = .Fields("MonthEndDate")
End If
.Close
End With

Set rs = Nothing
Set qdf = Nothing
End Function

What I'm trying to learn/understand is how to code the function to
return a value - I seem to be all mixed up with parameters. I "get" it
in queries, and having a form "run" a query. Though I have probably
frustrated you and James, it is unintentional, I assure you. I have a
hugely better understanding of sub vs function now, but still don't feel
I can do it on my own and my books and Help - don't (help, that is).

Thanks for sticking with me. I did try DLookup and think I could get
that to work, if that makes more sense at this time.
Continuing thanks -
sara

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[rkc]

sara wrote:

<snip lots-O-stuff>

Thanks for sticking with me. I did try DLookup and think I could get
that to work, if that makes more sense at this time.

If a value for ALLDates.WeekEndDate is entered into
[Forms]![F_PrintSalesReports]![getWkMoDate]
then

DLookup ("[MonthEndDate]", "ALLDates", "[WeekEndDate] = #" & _
[Forms]![F_PrintSalesReports]![getWkMoDate] & "#"

should get you the MonthEndDate associated with it if WeekEndDate
is found in the AllDates table.

That's if I have the syntax of the DLookup function correct.
Like I said, I never use it. I'm not at all sure you need the
pound signs to delimit the date value. Try with and without them.

[sara]

James -
Just to "close the loop" - I checked your formula against our upcoming
Fiscal calendar (Fiscal Year 2006, which starts on 1/30/05 and ends on
1/28/06.

Here are some exceptions to what you coded:
August runs from 7/31/05 thru 8/27/05. There are 4 more days in August,
but they belong in September. But uusually 4 or more days makes the
week in the following month.

March runs from 2/28/05 thru 4/2/05, so the 4th Sat in March is not the
last Sat in March. But May runs May 1-28 05, so it is true there (and
in other months as well).

Anyway, I wanted to let you know that I've been through this now for 3
years and figured out over a year ago that the only way to survive this
is to set up a lookup table with all the dates in it; then use the dates
in the table for all queries and reports.

Thank you for the help; I have the function saved in case I can find a
use for it someday. I am also trying Dlookup, as well as what rkc is
sending.
My sincere thanks-
sara

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[sara]

DLookup works! Thanks.

sara

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!