Need help with a Parameter Query Using Date selection

[Guest]

I have a company database with stock trades done by our consultants. I need
to be able to pull out Monthly reports based on when trades are done with
user interaction. I know how to do basic criteria expressions for parameter
queries, but I'm afraid this one is a little beyond my current knowledge
base. I have two date fields that I need to work with [ProcessDate] and
[SettleDate]. The following statement (although probably not in the proper
format) will do the job for me: "Where [ProcessDate] <02/01/2005 And
[SettleDate] is Between 01/01/2005 And 01/31/2005 OR [ProcessDate] is
Between 01/01/2005 And 01/31/2005 And [SettleDate]<02/01/2005" This would
ideally give me the records I need for my reports. My problems are the
following: The actual dates need to be user input. The dates could be any
month. I used January and February only as an example above. Dates would be
great if just "January" or "August" or "March" could be entered to represent
the full month range of days. Additionally, I'm not sure where I would enter
the finished statement. Would it have to be a SQL syntax? Would I have to
setup another table to represent the months somehow? Any help would be most
appreciated.

Semperfi1967

 

[Allen Browne]

See:
Limiting a Report to a Date Range
at:
http://members.iinet.net.au/~allenbrowne/casu-08.html

The article explains two approaches you could use: parameters in a query, or
the WhereCondition of the OpenReport action.

 

[Guest]

Allen, I want to thank you for the quick response to my question. I have
printed the info from your site and will go over it in depth shortly, but at
first glance I'm not sure it will cover the OR clause and the name
substitution I need. Is it possible for you to review my original question
and see if this is so?

Thanks again,
Semperfi1967

See:
Limiting a Report to a Date Range
at:
http://members.iinet.net.au/~allenbrowne/casu-08.html

The article explains two approaches you could use: parameters in a query, or
the WhereCondition of the OpenReport action.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a company database with stock trades done by our consultants. I
need
to be able to pull out Monthly reports based on when trades are done with
user interaction. I know how to do basic criteria expressions for
parameter
queries, but I'm afraid this one is a little beyond my current knowledge
base. I have two date fields that I need to work with [ProcessDate] and
[SettleDate]. The following statement (although probably not in the
proper
format) will do the job for me: "Where [ProcessDate] <02/01/2005 And
[SettleDate] is Between 01/01/2005 And 01/31/2005 OR [ProcessDate] is
Between 01/01/2005 And 01/31/2005 And [SettleDate]<02/01/2005" This
would
ideally give me the records I need for my reports. My problems are the
following: The actual dates need to be user input. The dates could be
any
month. I used January and February only as an example above. Dates would
be
great if just "January" or "August" or "March" could be entered to
represent
the full month range of days. Additionally, I'm not sure where I would
enter
the finished statement. Would it have to be a SQL syntax? Would I have
to
setup another table to represent the months somehow? Any help would be
most
appreciated.

Semperfi1967

 

[Allen Browne]

Okay, given that you need to use a form anyway for the user to select the
date, it makes sense to use the WhereCondition approach.

For your particular example, you need to end up with a string like this:

strWhere = "(([ProcessDate] < #02/01/2005#) And ([SettleDate] Between
#01/01/2005# And #01/31/2005#))
OR (([ProcessDate] Between #01/01/2005# And #01/31/2005#) And ([SettleDate]
< #02/01/2005#))"

It's important to use brackets: otherwise the mix of AND and OR is
ambiguous. It's also important to delimit the literal dates with #. The code
will actually concatenate the literal dates into the string.

I take it you want to return records where either date field is before the
end of a month, and the other field is any time in that month or the 11
months ahead? Assuming a text box named "txtStartDate" where the user enters
the start of the month (e.g. 1/1/2005), the code would end up being this
kind of thing:

Dim dtStart As Date
Dim dtEnd As Date
Dim dtNextMonth As Date
Dim strWhere As String
Const strcJetDate = "\#mm/dd/yyyy\#"

If IsDate(Me.txtStartDate) Then
dtStart = Me.txtStartDate
dtEnd = DateAdd("yyyy", 1, dtStart) -1
dtNextMonth = DateAdd("m", 1, dtStart)
strWhere = "(([ProcessDate] < " Format(dtNextMonth, strcJetDate) & _
") And ([SettleDate] Between " & Format(dtStart, strcJetDate) & _
" And " & Format(dtEnd, strcJetDate) & _
")) OR (([ProcessDate] Between " & Format(dtStart, strcJetDate) & _
" And " & Format(dtEnd, strcJetDate) & _
") And ([SettleDate] < " & Format(dtNextMonth, strcJetDate) & "))"
End If

DoCmd.OpenReport "report1", acViewPreview, , strWhere

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I want to thank you for the quick response to my question. I have
printed the info from your site and will go over it in depth shortly, but
at
first glance I'm not sure it will cover the OR clause and the name
substitution I need. Is it possible for you to review my original
question
and see if this is so?

Thanks again,
Semperfi1967

See:
Limiting a Report to a Date Range
at:
http://members.iinet.net.au/~allenbrowne/casu-08.html

The article explains two approaches you could use: parameters in a query,
or
the WhereCondition of the OpenReport action.

I have a company database with stock trades done by our consultants. I
need
to be able to pull out Monthly reports based on when trades are done
with
user interaction. I know how to do basic criteria expressions for
parameter
queries, but I'm afraid this one is a little beyond my current
knowledge
base. I have two date fields that I need to work with [ProcessDate]
and
[SettleDate]. The following statement (although probably not in the
proper
format) will do the job for me: "Where [ProcessDate] <02/01/2005 And
[SettleDate] is Between 01/01/2005 And 01/31/2005 OR [ProcessDate]
is
Between 01/01/2005 And 01/31/2005 And [SettleDate]<02/01/2005" This
would
ideally give me the records I need for my reports. My problems are
the
following: The actual dates need to be user input. The dates could be
any
month. I used January and February only as an example above. Dates
would
be
great if just "January" or "August" or "March" could be entered to
represent
the full month range of days. Additionally, I'm not sure where I would
enter
the finished statement. Would it have to be a SQL syntax? Would I
have
to
setup another table to represent the months somehow? Any help would
be
most
appreciated.

Semperfi1967

 

[Guest]

Just got home from work. Thanks so much for the time and effort you put in
to this. Will fool around with this tonight and see what happens. It looks
good at first glance. Will let you know.

Thanks again,
SemperFi1967

Okay, given that you need to use a form anyway for the user to select the
date, it makes sense to use the WhereCondition approach.

For your particular example, you need to end up with a string like this:

strWhere = "(([ProcessDate] < #02/01/2005#) And ([SettleDate] Between
#01/01/2005# And #01/31/2005#))
OR (([ProcessDate] Between #01/01/2005# And #01/31/2005#) And ([SettleDate]
< #02/01/2005#))"

It's important to use brackets: otherwise the mix of AND and OR is
ambiguous. It's also important to delimit the literal dates with #. The code
will actually concatenate the literal dates into the string.

I take it you want to return records where either date field is before the
end of a month, and the other field is any time in that month or the 11
months ahead? Assuming a text box named "txtStartDate" where the user enters
the start of the month (e.g. 1/1/2005), the code would end up being this
kind of thing:

Dim dtStart As Date
Dim dtEnd As Date
Dim dtNextMonth As Date
Dim strWhere As String
Const strcJetDate = "\#mm/dd/yyyy\#"

If IsDate(Me.txtStartDate) Then
dtStart = Me.txtStartDate
dtEnd = DateAdd("yyyy", 1, dtStart) -1
dtNextMonth = DateAdd("m", 1, dtStart)
strWhere = "(([ProcessDate] < " Format(dtNextMonth, strcJetDate) & _
") And ([SettleDate] Between " & Format(dtStart, strcJetDate) & _
" And " & Format(dtEnd, strcJetDate) & _
")) OR (([ProcessDate] Between " & Format(dtStart, strcJetDate) & _
" And " & Format(dtEnd, strcJetDate) & _
") And ([SettleDate] < " & Format(dtNextMonth, strcJetDate) & "))"
End If

DoCmd.OpenReport "report1", acViewPreview, , strWhere

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I want to thank you for the quick response to my question. I have
printed the info from your site and will go over it in depth shortly, but
at
first glance I'm not sure it will cover the OR clause and the name
substitution I need. Is it possible for you to review my original
question
and see if this is so?

Thanks again,
Semperfi1967

See:
Limiting a Report to a Date Range
at:
http://members.iinet.net.au/~allenbrowne/casu-08.html

The article explains two approaches you could use: parameters in a query,
or
the WhereCondition of the OpenReport action.


I have a company database with stock trades done by our consultants. I
need
to be able to pull out Monthly reports based on when trades are done
with
user interaction. I know how to do basic criteria expressions for
parameter
queries, but I'm afraid this one is a little beyond my current
knowledge
base. I have two date fields that I need to work with [ProcessDate]
and
[SettleDate]. The following statement (although probably not in the
proper
format) will do the job for me: "Where [ProcessDate] <02/01/2005 And
[SettleDate] is Between 01/01/2005 And 01/31/2005 OR [ProcessDate]
is
Between 01/01/2005 And 01/31/2005 And [SettleDate]<02/01/2005" This
would
ideally give me the records I need for my reports. My problems are
the
following: The actual dates need to be user input. The dates could be
any
month. I used January and February only as an example above. Dates
would
be
great if just "January" or "August" or "March" could be entered to
represent
the full month range of days. Additionally, I'm not sure where I would
enter
the finished statement. Would it have to be a SQL syntax? Would I
have
to
setup another table to represent the months somehow? Any help would
be
most
appreciated.

Semperfi1967