Selections to view a report

W

weircolin

Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = " &
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If



I would like to be able to have it display information when either (or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
 
G

Guest

There as a fundamental problem with your code. Year and Month are Access
reserved words. I think you will get an "Argument Not Optional" error when
you try to run this code. Both are date functions and need a date value
passed to them. What month and year are you trying to compare to?
 
W

weircolin

Sorry, I should have specified, Year and Month a two tables on my
database and in this code Year and Month refer to combo boxes on my
form.

Cheers
There as a fundamental problem with your code. Year and Month are Access
reserved words. I think you will get an "Argument Not Optional" error when
you try to run this code. Both are date functions and need a date value
passed to them. What month and year are you trying to compare to?

[email protected] said:
Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = " &
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If



I would like to be able to have it display information when either (or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
 
D

Douglas J. Steele

While Klatuu's correct that Year and Month are reserved words, so that you
should rename them, if you cannot (or will not) rename them, you can try
enclosing them in square brackets so that Access will recognize them as
field names:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value & _
" And [Year] = " & cboissueyear.Value & _
" And [Month] = " & cboissuemonth.Value
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

If you're saying you want to be able to select all rows, regardless of month
if a month hasn't been specified or regardless of year if a year hasn't been
specified, try something like:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear.Value) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth.Value) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

However, that won't work when you don't provide a value for Topic Name. Is
that what you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
There as a fundamental problem with your code. Year and Month are Access
reserved words. I think you will get an "Argument Not Optional" error
when
you try to run this code. Both are date functions and need a date value
passed to them. What month and year are you trying to compare to?

[email protected] said:
Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = " &
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If



I would like to be able to have it display information when either (or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
 
G

Guest

Change the names. It will create a problem. If they are combos, what are
cboissuesyear and cboissuesmonth?

[email protected] said:
Sorry, I should have specified, Year and Month a two tables on my
database and in this code Year and Month refer to combo boxes on my
form.

Cheers
There as a fundamental problem with your code. Year and Month are Access
reserved words. I think you will get an "Argument Not Optional" error when
you try to run this code. Both are date functions and need a date value
passed to them. What month and year are you trying to compare to?

[email protected] said:
Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = " &
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If



I would like to be able to have it display information when either (or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
W

weircolin

Hi Doug

Thanks for that. I would like to be able to perhaps select a Category
with a month and year but no Topic, is that possible?

Thanks

Colin said:
While Klatuu's correct that Year and Month are reserved words, so that you
should rename them, if you cannot (or will not) rename them, you can try
enclosing them in square brackets so that Access will recognize them as
field names:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value & _
" And [Year] = " & cboissueyear.Value & _
" And [Month] = " & cboissuemonth.Value
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

If you're saying you want to be able to select all rows, regardless of month
if a month hasn't been specified or regardless of year if a year hasn't been
specified, try something like:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear.Value) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth.Value) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

However, that won't work when you don't provide a value for Topic Name. Is
that what you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
There as a fundamental problem with your code. Year and Month are Access
reserved words. I think you will get an "Argument Not Optional" error
when
you try to run this code. Both are date functions and need a date value
passed to them. What month and year are you trying to compare to?

[email protected] said:
Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = " &
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If



I would like to be able to have it display information when either (or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
W

weircolin

Sorry, cboissueyear and cboissueamonth are the combos, getting
confused. Sorry :$
Klatuu said:
Change the names. It will create a problem. If they are combos, what are
cboissuesyear and cboissuesmonth?

[email protected] said:
Sorry, I should have specified, Year and Month a two tables on my
database and in this code Year and Month refer to combo boxes on my
form.

Cheers
There as a fundamental problem with your code. Year and Month are Access
reserved words. I think you will get an "Argument Not Optional" error when
you try to run this code. Both are date functions and need a date value
passed to them. What month and year are you trying to compare to?

:

Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = " &
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If



I would like to be able to have it display information when either (or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You've got a redundant check on [Topic Name] that you need to change:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If IsNull([Topic Name]) = False Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

If you're concerned that someone might have put a blank in Topic Name,
replace

If IsNull([Topic Name]) = False Then

with

If Len([Topic Name] & vbNullString) > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for that. I would like to be able to perhaps select a Category
with a month and year but no Topic, is that possible?

Thanks

Colin said:
While Klatuu's correct that Year and Month are reserved words, so that
you
should rename them, if you cannot (or will not) rename them, you can try
enclosing them in square brackets so that Access will recognize them as
field names:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value & _
" And [Year] = " & cboissueyear.Value & _
" And [Month] = " & cboissuemonth.Value
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

If you're saying you want to be able to select all rows, regardless of
month
if a month hasn't been specified or regardless of year if a year hasn't
been
specified, try something like:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear.Value) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth.Value) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

However, that won't work when you don't provide a value for Topic Name.
Is
that what you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
There as a fundamental problem with your code. Year and Month are
Access
reserved words. I think you will get an "Argument Not Optional" error
when
you try to run this code. Both are date functions and need a date
value
passed to them. What month and year are you trying to compare to?

:

Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = " &
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If



I would like to be able to have it display information when either (or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
W

weircolin

Hi

Thanks! Got it working now but with one wee problem. I have to leave
the line

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

To get it to work when nothing is selected in Topic Name.

Only thing is, and this is probably due to a lack of understanding of
VB Code, as the structure of the code has changed I have nowhere to put
the Else in, so therefore when I just have the above line on its own
when I click the button I get Report "calculate" and "calculate2"
showing.

This is currently what it looks like.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value


If Len([Topic Name] & vbNullString) > 0 Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

Thanks

Colin said:
You've got a redundant check on [Topic Name] that you need to change:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If IsNull([Topic Name]) = False Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

If you're concerned that someone might have put a blank in Topic Name,
replace

If IsNull([Topic Name]) = False Then

with

If Len([Topic Name] & vbNullString) > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for that. I would like to be able to perhaps select a Category
with a month and year but no Topic, is that possible?

Thanks

Colin said:
While Klatuu's correct that Year and Month are reserved words, so that
you
should rename them, if you cannot (or will not) rename them, you can try
enclosing them in square brackets so that Access will recognize them as
field names:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value & _
" And [Year] = " & cboissueyear.Value & _
" And [Month] = " & cboissuemonth.Value
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

If you're saying you want to be able to select all rows, regardless of
month
if a month hasn't been specified or regardless of year if a year hasn't
been
specified, try something like:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear.Value) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth.Value) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

However, that won't work when you don't provide a value for Topic Name.
Is
that what you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


There as a fundamental problem with your code. Year and Month are
Access
reserved words. I think you will get an "Argument Not Optional" error
when
you try to run this code. Both are date functions and need a date
value
passed to them. What month and year are you trying to compare to?

:

Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = " &
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If



I would like to be able to have it display information when either (or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Ah. I missed the fact that the two OpenReport statements were opening
different reports.

What distinguishes when you open Calculate, vs. when you open Calculate2?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

Thanks! Got it working now but with one wee problem. I have to leave
the line

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

To get it to work when nothing is selected in Topic Name.

Only thing is, and this is probably due to a lack of understanding of
VB Code, as the structure of the code has changed I have nowhere to put
the Else in, so therefore when I just have the above line on its own
when I click the button I get Report "calculate" and "calculate2"
showing.

This is currently what it looks like.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value


If Len([Topic Name] & vbNullString) > 0 Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

Thanks

Colin said:
You've got a redundant check on [Topic Name] that you need to change:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If IsNull([Topic Name]) = False Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

If you're concerned that someone might have put a blank in Topic Name,
replace

If IsNull([Topic Name]) = False Then

with

If Len([Topic Name] & vbNullString) > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for that. I would like to be able to perhaps select a Category
with a month and year but no Topic, is that possible?

Thanks

Colin
Douglas J. Steele wrote:
While Klatuu's correct that Year and Month are reserved words, so that
you
should rename them, if you cannot (or will not) rename them, you can
try
enclosing them in square brackets so that Access will recognize them
as
field names:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value & _
" And [Year] = " & cboissueyear.Value & _
" And [Month] = " & cboissuemonth.Value
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

If you're saying you want to be able to select all rows, regardless of
month
if a month hasn't been specified or regardless of year if a year
hasn't
been
specified, try something like:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear.Value) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth.Value) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

However, that won't work when you don't provide a value for Topic
Name.
Is
that what you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


There as a fundamental problem with your code. Year and Month are
Access
reserved words. I think you will get an "Argument Not Optional"
error
when
you try to run this code. Both are date functions and need a date
value
passed to them. What month and year are you trying to compare to?

:

Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = "
&
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, ,
strWhere
End If
End If



I would like to be able to have it display information when either
(or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
W

weircolin

Hi

I've tried tagging this onto the end

If Not IsNull([Topic Name]) Then
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
End If
If IsNull([Topic Name]) Then
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If

But when the Topic Name field is left empty it returns a blank form.

Running true to form I'm pretty hopeless!

Colin said:
Ah. I missed the fact that the two OpenReport statements were opening
different reports.

What distinguishes when you open Calculate, vs. when you open Calculate2?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

Thanks! Got it working now but with one wee problem. I have to leave
the line

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

To get it to work when nothing is selected in Topic Name.

Only thing is, and this is probably due to a lack of understanding of
VB Code, as the structure of the code has changed I have nowhere to put
the Else in, so therefore when I just have the above line on its own
when I click the button I get Report "calculate" and "calculate2"
showing.

This is currently what it looks like.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value


If Len([Topic Name] & vbNullString) > 0 Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

Thanks

Colin said:
You've got a redundant check on [Topic Name] that you need to change:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If IsNull([Topic Name]) = False Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

If you're concerned that someone might have put a blank in Topic Name,
replace

If IsNull([Topic Name]) = False Then

with

If Len([Topic Name] & vbNullString) > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for that. I would like to be able to perhaps select a Category
with a month and year but no Topic, is that possible?

Thanks

Colin
Douglas J. Steele wrote:
While Klatuu's correct that Year and Month are reserved words, so that
you
should rename them, if you cannot (or will not) rename them, you can
try
enclosing them in square brackets so that Access will recognize them
as
field names:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value & _
" And [Year] = " & cboissueyear.Value & _
" And [Month] = " & cboissuemonth.Value
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

If you're saying you want to be able to select all rows, regardless of
month
if a month hasn't been specified or regardless of year if a year
hasn't
been
specified, try something like:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear.Value) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth.Value) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

However, that won't work when you don't provide a value for Topic
Name.
Is
that what you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


There as a fundamental problem with your code. Year and Month are
Access
reserved words. I think you will get an "Argument Not Optional"
error
when
you try to run this code. Both are date functions and need a date
value
passed to them. What month and year are you trying to compare to?

:

Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = "
&
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, ,
strWhere
End If
End If



I would like to be able to have it display information when either
(or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
W

weircolin

Hi

Got it sorted, changed it to the following and its working great now!

If Not IsNull([Topic Name]) Then
If Not ([Topic Name].Value = "") Then
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

Thanks again for all your help.

Colin said:
Ah. I missed the fact that the two OpenReport statements were opening
different reports.

What distinguishes when you open Calculate, vs. when you open Calculate2?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

Thanks! Got it working now but with one wee problem. I have to leave
the line

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

To get it to work when nothing is selected in Topic Name.

Only thing is, and this is probably due to a lack of understanding of
VB Code, as the structure of the code has changed I have nowhere to put
the Else in, so therefore when I just have the above line on its own
when I click the button I get Report "calculate" and "calculate2"
showing.

This is currently what it looks like.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value


If Len([Topic Name] & vbNullString) > 0 Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

Thanks

Colin said:
You've got a redundant check on [Topic Name] that you need to change:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If IsNull([Topic Name]) = False Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

If you're concerned that someone might have put a blank in Topic Name,
replace

If IsNull([Topic Name]) = False Then

with

If Len([Topic Name] & vbNullString) > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for that. I would like to be able to perhaps select a Category
with a month and year but no Topic, is that possible?

Thanks

Colin
Douglas J. Steele wrote:
While Klatuu's correct that Year and Month are reserved words, so that
you
should rename them, if you cannot (or will not) rename them, you can
try
enclosing them in square brackets so that Access will recognize them
as
field names:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value & _
" And [Year] = " & cboissueyear.Value & _
" And [Month] = " & cboissuemonth.Value
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

If you're saying you want to be able to select all rows, regardless of
month
if a month hasn't been specified or regardless of year if a year
hasn't
been
specified, try something like:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear.Value) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth.Value) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

However, that won't work when you don't provide a value for Topic
Name.
Is
that what you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


There as a fundamental problem with your code. Year and Month are
Access
reserved words. I think you will get an "Argument Not Optional"
error
when
you try to run this code. Both are date functions and need a date
value
passed to them. What month and year are you trying to compare to?

:

Hi

I am using the following code to change the values that are being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month = "
&
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, ,
strWhere
End If
End If



I would like to be able to have it display information when either
(or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Probably simpler to use:

If Len([Topic Name] & "") > 0 Then
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

Got it sorted, changed it to the following and its working great now!

If Not IsNull([Topic Name]) Then
If Not ([Topic Name].Value = "") Then
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

Thanks again for all your help.

Colin said:
Ah. I missed the fact that the two OpenReport statements were opening
different reports.

What distinguishes when you open Calculate, vs. when you open Calculate2?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

Thanks! Got it working now but with one wee problem. I have to leave
the line

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

To get it to work when nothing is selected in Topic Name.

Only thing is, and this is probably due to a lack of understanding of
VB Code, as the structure of the code has changed I have nowhere to put
the Else in, so therefore when I just have the above line on its own
when I click the button I get Report "calculate" and "calculate2"
showing.

This is currently what it looks like.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value


If Len([Topic Name] & vbNullString) > 0 Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

DoCmd.OpenReport "calculate2", acViewPreview, , strWhere

Thanks

Colin
Douglas J. Steele wrote:
You've got a redundant check on [Topic Name] that you need to change:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If IsNull([Topic Name]) = False Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If

DoCmd.OpenReport "calculate", acViewPreview, , strWhere

If you're concerned that someone might have put a blank in Topic Name,
replace

If IsNull([Topic Name]) = False Then

with

If Len([Topic Name] & vbNullString) > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for that. I would like to be able to perhaps select a
Category
with a month and year but no Topic, is that possible?

Thanks

Colin
Douglas J. Steele wrote:
While Klatuu's correct that Year and Month are reserved words, so
that
you
should rename them, if you cannot (or will not) rename them, you
can
try
enclosing them in square brackets so that Access will recognize
them
as
field names:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value & _
" And [Year] = " & cboissueyear.Value & _
" And [Month] = " & cboissuemonth.Value
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

If you're saying you want to be able to select all rows, regardless
of
month
if a month hasn't been specified or regardless of year if a year
hasn't
been
specified, try something like:

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & _
" And [Topic Name] = " & [Topic Name].Value
End If
If IsNull(cboissueyear.Value) = False Then
strWhere = strWhere & _
" And [Year] = " & cboissueyear.Value
End If
If IsNull(cboissuemonth.Value) = False Then
strWhere = strWhere & _
" And [Month] = " & cboissuemonth.Value
End If
DoCmd.OpenReport "calculate", acViewPreview, , strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, , strWhere
End If
End If

However, that won't work when you don't provide a value for Topic
Name.
Is
that what you want?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


There as a fundamental problem with your code. Year and Month
are
Access
reserved words. I think you will get an "Argument Not Optional"
error
when
you try to run this code. Both are date functions and need a
date
value
passed to them. What month and year are you trying to compare
to?

:

Hi

I am using the following code to change the values that are
being
displayed on a report.

Dim strWhere As String
strWhere = "[Category] = " & Category.Value

If Not (IsNull([Topic Name].Value)) Then
If Not ([Topic Name].Value = "") Then
strWhere = strWhere & " And [Topic Name] = " & [Topic
Name].Value & " And Year = " & cboissueyear.Value & " And Month
= "
&
cboissuemonth.Value

DoCmd.OpenReport "calculate", acViewPreview, ,
strWhere
Else
DoCmd.OpenReport "calculate2", acViewPreview, ,
strWhere
End If
End If



I would like to be able to have it display information when
either
(or
both) "Year" and "Month" have no selection made.

Can anyone point me in the right direction?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form to pass strWhere to open report 2
Open Report with the parameter 1
Run select query from VBA code where criteria comes from a multi select ListBox 2
Error 3464 Data type mismatch in criteria expression for between 2 dates look up 3
Two Multi-Select List Boxes as Filters 11
Combo Box & Report Questions 22
Filter data by using a form and open a report based on this data 1
error 3464 data type mismatch in criteria expression 0

Top