编码函数以返回值 [英] Coding a function to return a value
问题描述
我有一个查询来提取当月的数据，使用表格（用户输入）中的月末日期
作为标准。有用。我想使用
相同的查询来拉动月到日。数据，每周一次。因此，对于
第1周，它只显示前7天;第2周第14天，
等
如果我现在进入下个月的结束日期（1/1/05），我会得到
自上个月结束至当前日期的天数。
这是完美的。我的问题是，我希望系统从输入周结束日期的用户的月末日期开始计算
，并将此b * b月末结束日期提供给此查询报告。我需要的所有数据都在查询中，如果我手动输入结束的月份
日期，它可以工作，但我不知道如何获取代码这样做。
我写了一个选择查询给我所有关联的一个销售的日期
日期 - 去年，月开始和结束日期等（我们在一个
唯一的财政日历上，所以我在表格中有我的日期）。我想
有代码get当用户输入周末日期时，月末结束日期并将其提供给查询
此报告并选择每周报告运行
。
这就是我试过的。我只是不太了解函数。
函数GetMonthEndDate（MonthEndDate为日期）
''运行查询以获取MonthEndDate for WeekEndingDate输入（用于构建
报告）
DoCmd.OpenQuery" qryGetDatesforSalesDate"，acNormal，acReadOnly
MsgBox" New " &安培; MonthEndDate
结束功能
我知道我在代码中，因为我看到查询打开了，我得到了我的
消息框，但它只是说new。
要运行该功能，我说：
''运行查询以获取输入WeekEndingDate的MonthEndDate（对于Spags
构建报告）
GetMonthEndDate（MonthEndDate）
MsgBox" in 2 Code" &安培; MonthEndDate
我收到此消息框，但它只是说In 2 Code。
非常感谢。
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
推荐答案
" Sara" < SA ******* @ yahoo.com>在消息中写道
news：d9 ************************** @ posting.google.c om ...
"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.
创建以下函数并将周结束日期传递给它。
公共函数MonthEnd（ dt As Date）
''显示给定日期的月份的最后一天
MonthEnd = DateSerial（年（dt），月（dt）+ 1,0）
结束功能
hth，
Tim Mills-Groninger
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
Tim -
谢谢，但问题仍然存在。也许我不清楚：
问题1：我们有一个独特的财政日历。所有月份周六结束时间为
，周日开始。有些是5周，有些是4周
长。例如，今年12月，即我们的第11个月，结束于
1/1/05。这就是我有一个单独的表，称为AllDates的原因。那个
给了我今天，去年同一天，周末，月底，财政月
等等我需要记录用户输入的日期并找到该月的
结束日期。如果用户输入11/5/04，我会看到
11/27/04，例如。
问题2：我把你的代码放入并添加了代码：
Dim dt As Date
Call MonthEnd（dt）
我添加了Msgbox dt在你的功能代码的最后看看我是什么
回来。
我希望是对的。
问题是我早上12点00分回来了。我很确定它没有更多的代码或
a查询或表格参考（这是我的原始问题，真的），我不能知道我的财政日历吗？ ，但是
我正在用这个来学习如何调用一个函数而且我明显有点错误。
你能帮忙吗？
谢谢 - 提前。
sara
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
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!
sara< an ******** @ devdex.com>在消息新闻中写道：< 1102183458.6NhiR9PM +7bLtXRfOtRDkA@teranews>。 ..
sara <an********@devdex.com> wrote in message news:<1102183458.6NhiR9PM+7bLtXRfOtRDkA@teranews>. ..
蒂姆 -
谢谢，但问题仍然存在。也许我不清楚：
问题1：我们有一个独特的财政日程表。所有月份在星期六结束，周日开始。有些是5周，有些是4周。例如，今年12月，即我们的第11个月，结束于1/1/05。这就是我有一个单独的表，称为AllDates的原因。那个今天给我的，去年的同一天，周末，月末，财政月份的数字等等。我需要记录用户输入的日期并找到月份
该日期的结束日期。如果用户输入11/5/04，我会看到
11/27/04，例如。
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.
以下函数假定有三个或者
最后几周减少的日子将该周纳入下一个财政月：
公共职能LastDayOfFiscalMonth（dt As Date）作为日期
Dim int5thSat As Integer
Dim int1stSat As Integer
Dim intMonthEnd As Integer
Dim dtNM As Date
int1stSat =（6 - WeekDay（DateSerial（年（dt），月（dt），1），2））Mod 7
+ 1
如果int1stSat< 4天和日（dt）< = int1stSat然后
''属于上个月
LastDayOfFiscalMonth = DateSerial（年（dt），月（dt），int1stSat）
退出函数
结束如果
int5thSat =（6 - WeekDay（DateSerial（年（dt），月（dt），1），2 ））Mod 7
+ 29
intMonthEnd = Day（DateSerial（年（dt），月（dt）+ 1,0））
dtNM = DateAdd（" m"，1，dt）
如果int5thSat< = intMonthEnd那么
如果Day（dt）< = int5thSat那么
LastDayOfFiscalMonth = DateSerial（年（dt），月（dt），int5thSat）
否则
LastDayOfFiscalMonth = LastDayOfFiscalMonth（DateAdd（" d"，7） ，dt））
结束如果
否则
''上周六是第四个星期六。检查剩余天数以查看
一周的走势。
如果intMonthEnd - int5thSat + 7< 4然后''三个或更少的额外天
进入下个会计月
如果Day（dt）< = int5thSat - 7那么
LastDayOfFiscalMonth = DateSerial（年（dt），月（dt），int5thSat
- 7）
否则
LastDayOfFiscalMonth = LastDayOfFiscalMonth（DateAdd（"） ; d"，7，dt））
结束如果
否则
''额外四天或更多天迫使财政月结束一周
之后
LastDayOfFiscalMonth = DateSerial（年（dtNM），月（dtNM），
int5thSat - intMonthEnd）
结束如果
结束如果
结束功能
注意linewrap。我用Tim给出的表达式来表示月末的
。我做了一些温和的测试，但在使用之前一定要测试这个
功能。如果我猜错了
关于你如何将你的财政月份分回来。
James A. Fortune
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
这篇关于编码函数以返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
