Question Revised about use of Option Boxes...

  • Thread starter Thread starter Bayou BoB
  • Start date Start date
B

Bayou BoB

Hello!

My form has an option box on it, that is supposed to allow a user to
choose the time duration of a report. As it stands now, they will be
able to print a report for the current month, the current quarter, and
the current year. One button on the form opens a report based on the
option group selection, and then will print a report of all our
clients based on the duration of the report selected via the option
group. The second button is supposed to allow you to print a report on
a single client that is selected from a combo-box, and as well, the
duration of the report selected from the option group. The code that
I'm using for this second instance is below. When I choose the first
button, I am able to select the duration of the report and then get
all of the client's entries printed without too much problem. When I
select a client from the combo box, select a report duration from the
option group, and then press the button, I do get just that client
coming up on the report. However, when I select "Current Month" from
the option group, I still get the whole year for that client, and not
just the current month. Do you see something in the code of the button
below? Many thanks in advance.

Kevin

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "CLI_DetailedAttendance_Rep"
Select Case [Frame0]

Case 1 'Current month
stWhere = "Month([Date]) = " & Month(Date) & "And Year([Date])
= " & Year(Date)

Case 2 'Current quarter
stWhere= (the code I have on current quarter is incorrect and
does not function properly. Perhaps you might suggest some proper code
for this option)

Case 3 'Current year
stWhere = "Year([Date]) = " & Year(Date)

End Select
DoCmd.OpenReport stDocName, acPreview, , "ClientID=" & Me!ClientID,
stWhere

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click



Many Thanks!!

Kevin
 
With the comma in there, you have actually placed strWhere in the WindowMode
argument.

Try:
DoCmd.OpenReport stDocName, acPreview, , "ClientID=" & Me!ClientID & " And "
& stWhere

Another potential problem is that you have used a reserved word, date, as
the name of your field. It is recommened that you don't do this because it
can potentially cause you problems.

For the quarter test, replace the month test with
"Format([Date], "q")=" & Format(Date, "q") & "And Year([Date])= " &
Year(Date)
 
I suspect your problem has something to do with the where clause in the
OpenReport method. You need to include the ClientID field into the stWhere
string so that your where clause is on string. The way you have it written,
the stWhere is in place of the WindowMode arguement. I'm surprised this
isn't causing an error.

You can make the change simply by adding this code right after the End
Select statement:

stWhere = "ClientID=" & Me!ClientID & " AND " & stWhere

then, take the "ClientID=" & Me!ClientID out of the OpenReport statement and
just use stWhere.

Also, I believe you can extract the quarter out of a date field by using the
DatePart function. Try this:

stWhere = "DatePart(q,[Date]) = " & DatePart(q,[Date]) & "And Year([Date]) =
" & Year(Date)

Good Luck!


Bayou BoB said:
Hello!

My form has an option box on it, that is supposed to allow a user to
choose the time duration of a report. As it stands now, they will be
able to print a report for the current month, the current quarter, and
the current year. One button on the form opens a report based on the
option group selection, and then will print a report of all our
clients based on the duration of the report selected via the option
group. The second button is supposed to allow you to print a report on
a single client that is selected from a combo-box, and as well, the
duration of the report selected from the option group. The code that
I'm using for this second instance is below. When I choose the first
button, I am able to select the duration of the report and then get
all of the client's entries printed without too much problem. When I
select a client from the combo box, select a report duration from the
option group, and then press the button, I do get just that client
coming up on the report. However, when I select "Current Month" from
the option group, I still get the whole year for that client, and not
just the current month. Do you see something in the code of the button
below? Many thanks in advance.

Kevin

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "CLI_DetailedAttendance_Rep"
Select Case [Frame0]

Case 1 'Current month
stWhere = "Month([Date]) = " & Month(Date) & "And Year([Date])
= " & Year(Date)

Case 2 'Current quarter
stWhere= (the code I have on current quarter is incorrect and
does not function properly. Perhaps you might suggest some proper code
for this option)

Case 3 'Current year
stWhere = "Year([Date]) = " & Year(Date)

End Select
DoCmd.OpenReport stDocName, acPreview, , "ClientID=" & Me!ClientID,
stWhere

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click



Many Thanks!!

Kevin
Click to expand...
 
With the comma in there, you have actually placed strWhere in the WindowMode
argument.

Try:
DoCmd.OpenReport stDocName, acPreview, , "ClientID=" & Me!ClientID & " And "
& stWhere

Another potential problem is that you have used a reserved word, date, as
the name of your field. It is recommened that you don't do this because it
can potentially cause you problems.

For the quarter test, replace the month test with
"Format([Date], "q")=" & Format(Date, "q") & "And Year([Date])= " &
Year(Date)
Click to expand...

I have changed the Date field to ActDate throughout to not use a
system word. Now I have tried using the code you've added here to give
me quarterly results, but it doesn't like the syntax for some reason?
It comes up with the cursor over the first "q" and says "Compile
Error: Expected End of Statement". Any thoughts?

Thanks!!

Kevin
 
stWhere = "DatePart(q,[Date]) = " & DatePart(q,[Date]) & "And Year([Date]) =
" & Year(Date)
Click to expand...


Hi Tom;

Still having troubles. I use the code above that you have suggesed
(though I now have renamed the date field ActDate instead of just
"date".... What it tells me is that "Microsoft Access Can't find the
field "l" referred to in your expression. Here. I've cut and past the
whole button syntax as I've modified it as per above. Thanks for your
assistance!!

Kevin

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "ClientWeightReport"
Select Case [Frame29]
Case 1 'Current month
stWhere = "Month([ActDate]) = " & Month(Date) & "And
Year([ActDate]) = " & Year(Date)

Case 2 'Current quarter
stWhere = "DatePart(q,[ActDate]) = " & DatePart(q, [ActDate])
& "And Year([ActDate]) = " & Year(Date)

Case 3 'Current year
stWhere = "Year([ActDate]) = " & Year(Date)

End Select

stWhere = "ClientID=" & Me!ClientID & " AND " & stWhere

DoCmd.OpenReport stDocName, acPreview, , stWhere

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click
End Sub
 
I saw your reply to Tom, it had the q before [Date] and without quotes. It
should go after [Date] and have quotes around it.

--
Wayne Morgan
MS Access MVP


Bayou BoB said:
With the comma in there, you have actually placed strWhere in the WindowMode
argument.

Try:
DoCmd.OpenReport stDocName, acPreview, , "ClientID=" & Me!ClientID & " And "
& stWhere

Another potential problem is that you have used a reserved word, date, as
the name of your field. It is recommened that you don't do this because it
can potentially cause you problems.

For the quarter test, replace the month test with
"Format([Date], "q")=" & Format(Date, "q") & "And Year([Date])= " &
Year(Date)
Click to expand...

I have changed the Date field to ActDate throughout to not use a
system word. Now I have tried using the code you've added here to give
me quarterly results, but it doesn't like the syntax for some reason?
It comes up with the cursor over the first "q" and says "Compile
Error: Expected End of Statement". Any thoughts?

Thanks!!

Kevin
Click to expand...
 
Back
Top