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 "qryGetDatesfor SalesDate", 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*******@yaho o.com> wrote in message
news:d9******** *************** ***@posting.goo gle.com...

... 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********@dev dex.com> wrote in message news:<110218345 8.6NhiR9PM+7bLt XRfOtRDkA@teran ews>...

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 LastDayOfFiscal Month(dt As Date) As Date
Dim int5thSat As Integer
Dim int1stSat As Integer
Dim intMonthEnd As Integer
Dim dtNM As Date

int1stSat = (6 - WeekDay(DateSer ial(Year(dt), Month(dt), 1), 2)) Mod 7
+ 1
If int1stSat < 4 And Day(dt) <= int1stSat Then
'Belongs to previous month
LastDayOfFiscal Month = DateSerial(Year (dt), Month(dt), int1stSat)
Exit Function
End If
int5thSat = (6 - WeekDay(DateSer ial(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
LastDayOfFiscal Month = DateSerial(Year (dt), Month(dt), int5thSat)
Else
LastDayOfFiscal Month = LastDayOfFiscal Month(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
LastDayOfFiscal Month = DateSerial(Year (dt), Month(dt), int5thSat
- 7)
Else
LastDayOfFiscal Month = LastDayOfFiscal Month(DateAdd(" d", 7, dt))
End If
Else
'Four or more extra days force the fiscal month to end a week
later
LastDayOfFiscal Month = 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********@dev dex.com> wrote in message
news:1102257861 .4KiiT9eM69p7Ib gkYA6Cdw@terane ws...

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 qryGetDatesforS alesDate i'd do something like
what fallows instead..

Assuming that qryGetDatesforS alesDate 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.

<getMonthEndDat e type='aircode'>

Function getMonthEndDate (wkend As Date) As Date
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.Query Defs("qryGetDat esforSalesDate" )
qdf.Parameters( "EndWeekDat e") = wkend
Set rs = qdf.OpenRecords et

With rs
If Not .EOF Then
getMonthEndDate = .Fields("MonthE ndDate")
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_PrintS alesReports!Sal esDate") = 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 "getWkMoDat e". When I mouse over
"GetWkMoDat e" 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_PrintS alesReports!Sal esDate") = 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 "getWkMoDat e". When I mouse over
"GetWkMoDat e" 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 qryGetDatesforS alesDate. I am assuming that it is a parameter
query and that the parameter is named "EndWeekDat e". It probably
isn't so you have to substitute the actual parameter name where
you find "EndWeekDat e" in the function I posted.

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

Forms!F_PrintSa lesReports!Sale sDate = getMonthEndDate (Me.getWkMoDate )

That should fill Forms!F_PrintSa lesReports!Sale sDate 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("MonthE ndDate")

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.SalesD ate, ALLDates.DayofY ear, ALLDates.DOW,
ALLDates.Fiscal WeekNum, ALLDates.WeekSt artDate, ALLDates.WeekEn dDate,
ALLDates.Fiscal MonthNum, ALLDates.MonthS tartDate, ALLDates.MonthE ndDate,
ALLDates.Fiscal QtrNum, ALLDates.QtrSta rtDate, ALLDates.QtrEnd Date,
ALLDates.YearNu m, ALLDates.YearSt artDate, ALLDates.YearEn dDate,
ALLDates.LYSale sDate, ALLDates.LYWeek StartDate, ALLDates.LYWeek EndDate,
ALLDates.LYMont hStartDate, ALLDates.LYMont hEndDate,
ALLDates.LYQtrS tartDate, ALLDates.LYQtrE ndDate, ALLDates.LYYear Num,
ALLDates.LYStar tDate, ALLDates.LYEndD ate, ALLDates.Specia l
FROM ALLDates
WHERE
(((ALLDates.Sal esDate)=[Forms]![F_PrintSalesRep orts]![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( "WeekEndDat e") = 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(Mo EndDate)

' MsgBox MoEndDate

Forms!F_PrintSa lesReports!Sale sDate = getMonthEndDate (Me.getWkMoDate )

MsgBox MoEndDate
stDocName = "rptSpagsSalesb yWeekTYLY"
On Error GoTo PrintSalesRepor ts_Err
strStepErrorMsg = "Tell IT there was a problem with Weekly
Spags store sales"
DoCmd.OpenRepor t 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.Query Defs("qryGetDat esforSalesDate" )
qdf.Parameters( "WeekEndDat e") = wkend

Set rs = qdf.OpenRecords et

With rs
If Not .EOF Then
getMonthEndDate = .Fields("MonthE ndDate")
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!