WHERE Clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to open a report using the following WHERE Clause that will find
me records over 31 days old but less than 60 days old.

strWhere = "(ReferralDate + 31 >= #" & Date - 60 & "#)"
DoCmd.OpenReport "CYF IH Cases Open", acViewPreview, , strWhere
 
Hi, Tru.

For dates more than 31 days, but less than 60 days ago, try:

strWhere = "ReferralDate BETWEEN " & DateAdd("d", -59, Date) & _
" AND " & DateAdd("d", -32, Date)

If you want to include the dates 31 days up to and including 60 days ago, try:

strWhere = "ReferralDate BETWEEN " & DateAdd("d", -60, Date) & _
" AND " & DateAdd("d", -31, Date)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
I really appreciate it, thanx again!
--
Thanx!


'69 Camaro said:
Hi, Tru.

For dates more than 31 days, but less than 60 days ago, try:

strWhere = "ReferralDate BETWEEN " & DateAdd("d", -59, Date) & _
" AND " & DateAdd("d", -32, Date)

If you want to include the dates 31 days up to and including 60 days ago, try:

strWhere = "ReferralDate BETWEEN " & DateAdd("d", -60, Date) & _
" AND " & DateAdd("d", -31, Date)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
Click to expand...
 
This looks close to what I need to pull records for a particular year. I
would like users to be able to pull reports for the current year or for
previous years. One button on a form for current year, and possibly a
dropdown menu using a combo box for previous years where the user selects the
year. Is this possible? What do I need to change?

Thanks!
Mary
 
Mary said:
This looks close to what I need to pull records for a particular year. I
would like users to be able to pull reports for the current year or for
previous years. One button on a form for current year, and possibly a
dropdown menu using a combo box for previous years where the user selects the
year. Is this possible? What do I need to change?
Click to expand...

Put all the years in the combo box. Then you can use code
similar to:

If Is Null(Me.thecombobox) Then
MsgBox "Please select a year"
Beep
Else
strWhere = "Year(yourdatefield) = " & Me.thecombobox
End If
 
Thank you! That works great for years where there is data for the report.
Where would I insert the "on no data" clause for years where there is no
data. I'd like to display a message box that states there is no data.

Here is my current code:

Private Sub Combo66_AfterUpdate()
Dim strWhere As String
strWhere = "Year(ExpenseDate) = " & Me.Combo66
DoCmd.OpenReport "CapitalRpt", acViewPreview, , strWhere
End Sub


Thanks,
Mary
 
can I have more than one where clause? For another report I'd like users to
select the Director from one combo box, then select the year from a second
combo box. The report should display data for that Director and year.

Thanks,
Mary
 
Sure, just set strWhere to include both conditions:

strWhere = "Year(yourdatefield) = " & Me.thecombobox _
" AND Director = " & Me.thedirectorcombovox
 
Mary said:
Thank you! That works great for years where there is data for the report.
Where would I insert the "on no data" clause for years where there is no
data. I'd like to display a message box that states there is no data.

Here is my current code:

Private Sub Combo66_AfterUpdate()
Dim strWhere As String
strWhere = "Year(ExpenseDate) = " & Me.Combo66
DoCmd.OpenReport "CapitalRpt", acViewPreview, , strWhere
End Sub
Click to expand...


Couple of ways to do this. You can use the report's NoData
event to display a message and cancel the event (i.e. the
report). Use error handling to catch and ignore the 2501
error.

Alternatively, you can modify the combo box to include a
calculated field for the number data records for a year. To
do this change the combo box's row source to a Totals type
query something like:

SELECT yearfield,
IIf(Count(*) > 0, Count(*) " items", "No Data") As X
FROM yeartable LEFT JOIN datatable
ON yeartable.yearfield = Year(datatable.ExpenseDate)
Group By yearfield

Don't forget to set the ColumnCount property to 2

This way, you can see the data information right in the
combo box's drop list.

You could even use the combo box's AfterUpdate event to
enable/disable the button that opens the report.

Me.commandbutton.Enabled = (Me.Combo66.Column(1) = "No
Data")
 
Ok, I've got the "no data" piece working with your help....

I'm getting a syntax error trying to add the second clause to the strWhere...

The field name on the Report and in the underlying query is Director. The
combo box on the form to select the Director is Combo69. Can you see what
I'm doing wrong?

strWhere = "Year(ExpenseDate) = " & Me.Combo01 _
" AND Director = " & Me.Combo69

Thanks again,
Mary
Marshall Barton said:
Sure, just set strWhere to include both conditions:

strWhere = "Year(yourdatefield) = " & Me.thecombobox _
" AND Director = " & Me.thedirectorcombovox
--
Marsh
MVP [MS Access]

can I have more than one where clause? For another report I'd like users to
select the Director from one combo box, then select the year from a second
combo box. The report should display data for that Director and year.
Click to expand...
Click to expand...
 
Mary said:
Ok, I've got the "no data" piece working with your help....

I'm getting a syntax error trying to add the second clause to the strWhere...

The field name on the Report and in the underlying query is Director. The
combo box on the form to select the Director is Combo69. Can you see what
I'm doing wrong?

strWhere = "Year(ExpenseDate) = " & Me.Combo01 _
" AND Director = " & Me.Combo69
Click to expand...


Two things to be careful with:

1) The type of the fields. What I posted is for numeric
type fields in the table. For example, if the Director
field is a Text field, then you must use woutes around the
value:
" AND Director = """ & Me.Combo69 & """"

2) If either of the combo boxes might not have anything
selected. In this case the value of that combo box will be
Null and you would probably want to omit the corresponding
condition.

To check what's happening it would be a good idea to add a
MsgBox strWhere right after the strWhere=... line
and, if you need further help, post what is displayed in the
messsage.
 
Back
Top