Openrecordset与标准问题 [英] Openrecordset with criteria problem
问题描述
我有一份报告，在查询中查找最早的开始时间
[作业]。现在它打开查询并抓住最早的
开始日期。这是工作代码：
设置rs = db.OpenRecordset（" SELECT Min（[TimeStart]）AS MinOfStartTime"
_
&" FROM Assignments"，dbOpenSnapshot）
问题是此查询显示来自
不同事件的开始和结束时间。有没有办法让它从查询中获取最早的开始
日期，其中查询中的[eventname]与表格上的
[eventname]相同[EventScheduler ]。我试过这段代码：
设置rs = db.OpenRecordset（" SELECT Min（[TimeStart]）AS MinOfStartTime"
_
&" FROM Assignments WHERE
EventName = forms！[EventScheduler] [EventName]"，dbOpenSnapshot）
此代码返回错误：参数太少。预期1.
任何想法
SY
Set Open事件中的过滤器，而不是recordsource。
DoCmd.OpenReport ...其中一个参数是过滤器EventName = ...
Shawn Yates写道：
设置rs = db.OpenRecordset（" SELECT Min（[TimeStart]） AS MinOfStartTime"
_
&" FROM Assignments WHERE
EventName = forms！[EventScheduler] [EventName]"，dbOpenSnapshot ）
也许放一个！在[EventScheduler]和[EventName]之间？
James A. Fortune
CD ******** @ FortuneJames.com
我不太确定我们是否也在考虑事情。你可能是完全正确的，但是如何适合我当前的代码。让我
告诉你更多的代码并看看你的想法。
__________________________________________________ ________
私人子报告_打开（取消为整数）
Dim db作为数据库
Dim rs作为记录集
设置db = CurrentDb
设置rs = db.OpenRecordset（" ; SELECT Min（[TimeStart]）AS MinOfStartTime
" _
&" FROM Assignments"，dbOpenSnapshot）
如果rs。 RecordCount 0然后
mtimeEarliest = rs！MinOfStartTime
结束如果
设置rs = db.OpenRecordset（" SELECT
Max（IIf（IsDate（[TimeFinish]），CDate（[TimeFinish]），Null））_
&AS MaxOfEndTime FROM Assignments"，dbOpenSnapshot）
如果rs.RecordCount 0那么
mtimeLatest = rs！MaxOfEndTime
结束如果
mintTimeDiff = DateDiff（" ; n"，mtimeEarliest，mtime最新）
Me.txtMinStartTime.Caption =格式（mtimeEar liest，hh：nn AMPM）
Me.txtMaxEndTime.Caption =格式（mtimeLatest，" hh：nn AMPM"）
设置rs = Nothing
设置db = Nothing
结束子
___________________________________________
这是发生了什么的大部分内容。现在我要做的就是输入
选择最大时间结束时间和
时间结束的最小值。希望这有助于我的第一篇文章更有意义。如果它b / b $ b b将帮助我发布我的报告的所有代码。
谢谢
SY
I have a report that looks up the earliest start time in a query
[assignments]. Right now it opens up the query and grabs the earliest
start date like it should. This is the working code:
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime "
_
& " FROM Assignments", dbOpenSnapshot)
The problem is that this query is showing start and finish times from
different events. Is there a way to have it grab the earliest start
date from the query where [eventname] in the query is the same as
[eventname] on the form [EventScheduler]. I tried this code:
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime "
_
& " FROM Assignments WHERE
EventName=forms![EventScheduler][EventName]", dbOpenSnapshot)
This code returns the error: "Too few parameters. Expected 1."
Any ideas
SY
Set the filter in the Open event, not the recordsource.
DoCmd.OpenReport... one of the arguments is the filter "EventName=..."
Shawn Yates wrote:
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime "
_
& " FROM Assignments WHERE
EventName=forms![EventScheduler][EventName]", dbOpenSnapshot)Maybe put a ! between [EventScheduler] and [EventName]?
James A. Fortune
CD********@FortuneJames.com
I''m not quite sure if we are thinking the same thing. You may be
totally correct but how would that fit into my current code. Let me
show you more of the code and see what you think.
__________________________________________________ ________
Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime
" _
& " FROM Assignments", dbOpenSnapshot)
If rs.RecordCount 0 Then
mtimeEarliest = rs!MinOfStartTime
End If
Set rs = db.OpenRecordset("SELECT
Max(IIf(IsDate([TimeFinish]),CDate([TimeFinish]),Null)) " _
& "AS MaxOfEndTime FROM Assignments", dbOpenSnapshot)
If rs.RecordCount 0 Then
mtimeLatest = rs!MaxOfEndTime
End If
mintTimeDiff = DateDiff("n", mtimeEarliest, mtimeLatest)
Me.txtMinStartTime.Caption = Format(mtimeEarliest, "hh:nn AMPM")
Me.txtMaxEndTime.Caption = Format(mtimeLatest, "hh:nn AMPM")
Set rs = Nothing
Set db = Nothing
End Sub
___________________________________________
This a larger part of what is going on. Now all I want to do is enter
some criteria when it selects the max of time finish and the min of
time finish. Hopefully this helped my first post make more sense. If it
would help I can post all of the code for my report.
Thanks
SY
这篇关于Openrecordset与标准问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
