Multi options

D

Dan @BCBS

This may be a duplicate - I have a strange error when posting...

My Goal: A form has 1 command button, 2 list boxes and 1 combo box (and 2
text boxes for the start & stop dates). When the user clicks the command
button the results are (DoCmd.SendObject acReport) but before that = 9
options based on the 2 list boxes and 1 combo box...

Here is what I have but I would have no problem starting over if it looks
like a mess to you... It only partially works::


Private Sub cmdEMailAudit_Click()
On Error GoTo Err_cmdAudit2_Click
Dim stDocName As String
Dim X As Integer
Dim stArea As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If

stStart = Me.txtStart
stEnd = Me.txtEnd

If stAreaList = ")" Then
MsgBox "Please select a Location."
Else
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
End If

Exit_cmdAudit2_Click:
Exit Sub
Err_cmdAudit2_Click:
MsgBox err.Number & " " & err.Description
Resume Exit_cmdAudit2_Click
End Sub
 
B

Barry Gilbert

What error are you getting?

It's not clear to me what you're doing with the variables stAreaList,
stStart and stEnd.

Barry
 
D

Dan @BCBS

In the attached code, I am not getting an error, but it's not reporting all
the possibilities for any combination of the 2 list boxes and 1 combo box.

Looking at the code, the start & end dates work fine:
The date ranges work fine (If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub)

After that I'm struggling:
If the user picks 1 or 5 values from list box a, then all the values in list
box b and 1 value in the combo box.... or any combination like that.... is
what I am stuck on.

Thanks
 
B

Barry Gilbert

I think you can keep appending to stAreaList.

(air code)
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea
For Each stArea In lstAreaB.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstAreaB.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstAreaB.ItemData(stArea)
& "'"
End If
X = X + 1
Next stArea
stArea = stArea & ", '" & cboMyCombo & "'"

Barry
 
D

Dan @BCBS

Sorry I don't follow. I think I have made this question harder than it really
is:
As I mentioned the code works but only for one value (stArea)

All I'm trying to do report data based on 3 values.
1. stArea
2. lstProduct
3. cboReviewer

So whatever combination the user chooses the report generates.
If they only pick from 1 & 2 or 1,2 and 3 or just one of them, the report
would produce data equal to that value.

Hope that was a better explanation.
 
B

Barry Gilbert

As I said a few postings ago, I don't see what you're doing with the
stAreaList variable that you're building. Nowhere in your code does it show
how you're passing it to your report or back to the form. The same with
stStart and stEnd. My suggestion was simply to extend your string-building
routine to incorporate the 2nd and 3rd controls' values. How are you
filtering the report? Is it based on a query that you want to have look at
the three controls? Does it have it's own code that builds a filter string?

Barry
 
D

Dan @BCBS

Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one or more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public stStart As String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box = "cboReviewer"

Any Suggestions??
 
D

Dan @BCBS

Are you able to suggest how I can specify the results further by including
combo box = "cboReviewer"???

Thanks





Dan @BCBS said:
Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one or more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public stStart As String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box = "cboReviewer"

Any Suggestions??









Barry Gilbert said:
As I said a few postings ago, I don't see what you're doing with the
stAreaList variable that you're building. Nowhere in your code does it show
how you're passing it to your report or back to the form. The same with
stStart and stEnd. My suggestion was simply to extend your string-building
routine to incorporate the 2nd and 3rd controls' values. How are you
filtering the report? Is it based on a query that you want to have look at
the three controls? Does it have it's own code that builds a filter string?

Barry
Click to expand...
Click to expand...
 
D

Douglas J. Steele

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns a number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer & Chr$(34)

I'd also recommend changing how you're passing the dates, just in case you
end up with a user who has his/her Short Date format set to dd/mm/yyyy
format:

stLinkCriteria = "[issueclosedate] between " & Format$(Me.txtStart,
"\#mm\/dd\/yyyy\#") & " and " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#") & "
And Somefield = " & Me.cboReviewer

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
Are you able to suggest how I can specify the results further by including
combo box = "cboReviewer"???

Thanks





Dan @BCBS said:
Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one or more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public stStart As
String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box = "cboReviewer"

Any Suggestions??









Barry Gilbert said:
As I said a few postings ago, I don't see what you're doing with the
stAreaList variable that you're building. Nowhere in your code does it
show
how you're passing it to your report or back to the form. The same with
stStart and stEnd. My suggestion was simply to extend your
string-building
routine to incorporate the 2nd and 3rd controls' values. How are you
filtering the report? Is it based on a query that you want to have look
at
the three controls? Does it have it's own code that builds a filter
string?

Barry

:

Sorry I don't follow. I think I have made this question harder than
it really
is:
As I mentioned the code works but only for one value (stArea)

All I'm trying to do report data based on 3 values.
1. stArea
2. lstProduct
3. cboReviewer

So whatever combination the user chooses the report generates.
If they only pick from 1 & 2 or 1,2 and 3 or just one of them, the
report
would produce data equal to that value.

Hope that was a better explanation.




:

I think you can keep appending to stAreaList.

(air code)
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea
For Each stArea In lstAreaB.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstAreaB.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstAreaB.ItemData(stArea)
& "'"
End If
X = X + 1
Next stArea
stArea = stArea & ", '" & cboMyCombo & "'"

Barry


:

In the attached code, I am not getting an error, but it's not
reporting all
the possibilities for any combination of the 2 list boxes and 1
combo box.

Looking at the code, the start & end dates work fine:
The date ranges work fine (If IsNull(Me.txtStart) Or
IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub)
Click to expand...
Click to expand...
Click to expand...
 
D

Dan @BCBS

Your suggestions are great/simple but let me expalin why my dates are this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to open the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates entered at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how can I add
another value...
This code correctly makes sure the "dates" were entered, also the "1stArea"
(if 1 or more choices are picked from the list box) and returns the proper
data on the report..

*** I need to add the combo box choice called "cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






Douglas J. Steele said:
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns a number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer & Chr$(34)

I'd also recommend changing how you're passing the dates, just in case you
end up with a user who has his/her Short Date format set to dd/mm/yyyy
format:

stLinkCriteria = "[issueclosedate] between " & Format$(Me.txtStart,
"\#mm\/dd\/yyyy\#") & " and " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#") & "
And Somefield = " & Me.cboReviewer

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
Are you able to suggest how I can specify the results further by including
combo box = "cboReviewer"???

Thanks





Dan @BCBS said:
Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one or more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public stStart As
String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box = "cboReviewer"

Any Suggestions??









:

As I said a few postings ago, I don't see what you're doing with the
stAreaList variable that you're building. Nowhere in your code does it
show
how you're passing it to your report or back to the form. The same with
stStart and stEnd. My suggestion was simply to extend your
string-building
routine to incorporate the 2nd and 3rd controls' values. How are you
filtering the report? Is it based on a query that you want to have look
at
the three controls? Does it have it's own code that builds a filter
string?

Barry

:

Sorry I don't follow. I think I have made this question harder than
it really
is:
As I mentioned the code works but only for one value (stArea)

All I'm trying to do report data based on 3 values.
1. stArea
2. lstProduct
3. cboReviewer

So whatever combination the user chooses the report generates.
If they only pick from 1 & 2 or 1,2 and 3 or just one of them, the
report
would produce data equal to that value.

Hope that was a better explanation.




:

I think you can keep appending to stAreaList.

(air code)
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea
For Each stArea In lstAreaB.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstAreaB.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstAreaB.ItemData(stArea)
& "'"
End If
X = X + 1
Next stArea
stArea = stArea & ", '" & cboMyCombo & "'"

Barry


:

In the attached code, I am not getting an error, but it's not
reporting all
the possibilities for any combination of the 2 list boxes and 1
combo box.

Looking at the code, the start & end dates work fine:
The date ranges work fine (If IsNull(Me.txtStart) Or
IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub)
Click to expand...
Click to expand...
Click to expand...
 
D

Dan @BCBS

I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" & cboReviewer.ItemData(stReviewer) & "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
DoCmd.OpenReport stDocName, acPreview
End If











Dan @BCBS said:
Your suggestions are great/simple but let me expalin why my dates are this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to open the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates entered at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how can I add
another value...
This code correctly makes sure the "dates" were entered, also the "1stArea"
(if 1 or more choices are picked from the list box) and returns the proper
data on the report..

*** I need to add the combo box choice called "cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






Douglas J. Steele said:
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns a number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer & Chr$(34)

I'd also recommend changing how you're passing the dates, just in case you
end up with a user who has his/her Short Date format set to dd/mm/yyyy
format:

stLinkCriteria = "[issueclosedate] between " & Format$(Me.txtStart,
"\#mm\/dd\/yyyy\#") & " and " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#") & "
And Somefield = " & Me.cboReviewer

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
Are you able to suggest how I can specify the results further by including
combo box = "cboReviewer"???

Thanks





:

Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one or more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public stStart As
String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box = "cboReviewer"

Any Suggestions??









:

As I said a few postings ago, I don't see what you're doing with the
stAreaList variable that you're building. Nowhere in your code does it
show
how you're passing it to your report or back to the form. The same with
stStart and stEnd. My suggestion was simply to extend your
string-building
routine to incorporate the 2nd and 3rd controls' values. How are you
filtering the report? Is it based on a query that you want to have look
at
the three controls? Does it have it's own code that builds a filter
string?

Barry

:

Sorry I don't follow. I think I have made this question harder than
it really
is:
As I mentioned the code works but only for one value (stArea)

All I'm trying to do report data based on 3 values.
1. stArea
2. lstProduct
3. cboReviewer

So whatever combination the user chooses the report generates.
If they only pick from 1 & 2 or 1,2 and 3 or just one of them, the
report
would produce data equal to that value.

Hope that was a better explanation.




:

I think you can keep appending to stAreaList.

(air code)
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea
For Each stArea In lstAreaB.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstAreaB.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstAreaB.ItemData(stArea)
& "'"
End If
X = X + 1
Next stArea
stArea = stArea & ", '" & cboMyCombo & "'"

Barry


:

In the attached code, I am not getting an error, but it's not
reporting all
the possibilities for any combination of the 2 list boxes and 1
combo box.

Looking at the code, the start & end dates work fine:
The date ranges work fine (If IsNull(Me.txtStart) Or
IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub)
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You've already got an Else in the construction: you can't use ElseIf once
you've used Else. Not only that, but ElseIf requires a condition to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example, I'll define
variables strWhere1, strWhere2 and strWhere3, as well as an overall
strWhere. I'll then have a section of code that possible defines strWhere1,
or leaves it blank if there isn't anything applicable to that condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave them blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by checking the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dan @BCBS said:
I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" & cboReviewer.ItemData(stReviewer) & "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
DoCmd.OpenReport stDocName, acPreview
End If











Dan @BCBS said:
Your suggestions are great/simple but let me expalin why my dates are
this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to open the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates entered
at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how can I
add
another value...
This code correctly makes sure the "dates" were entered, also the
"1stArea"
(if 1 or more choices are picked from the list box) and returns the
proper
data on the report..

*** I need to add the combo box choice called "cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea)
&
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






Douglas J. Steele said:
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #"
&
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns a
number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #"
&
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer & Chr$(34)

I'd also recommend changing how you're passing the dates, just in case
you
end up with a user who has his/her Short Date format set to dd/mm/yyyy
format:

stLinkCriteria = "[issueclosedate] between " & Format$(Me.txtStart,
"\#mm\/dd\/yyyy\#") & " and " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#") &
"
And Somefield = " & Me.cboReviewer

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Are you able to suggest how I can specify the results further by
including
combo box = "cboReviewer"???

Thanks





:

Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one or
more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public stStart As
String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and
#" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box =
"cboReviewer"

Any Suggestions??









:

As I said a few postings ago, I don't see what you're doing with
the
stAreaList variable that you're building. Nowhere in your code
does it
show
how you're passing it to your report or back to the form. The same
with
stStart and stEnd. My suggestion was simply to extend your
string-building
routine to incorporate the 2nd and 3rd controls' values. How are
you
filtering the report? Is it based on a query that you want to have
look
at
the three controls? Does it have it's own code that builds a
filter
string?

Barry

:

Sorry I don't follow. I think I have made this question harder
than
it really
is:
As I mentioned the code works but only for one value (stArea)

All I'm trying to do report data based on 3 values.
1. stArea
2. lstProduct
3. cboReviewer

So whatever combination the user chooses the report generates.
If they only pick from 1 & 2 or 1,2 and 3 or just one of them,
the
report
would produce data equal to that value.

Hope that was a better explanation.




:

I think you can keep appending to stAreaList.

(air code)
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" &
lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea
For Each stArea In lstAreaB.ItemsSelected
If X = 0 Then
stAreaList = "In('" &
lstAreaB.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstAreaB.ItemData(stArea)
& "'"
End If
X = X + 1
Next stArea
stArea = stArea & ", '" & cboMyCombo & "'"

Barry


:

In the attached code, I am not getting an error, but it's
not
reporting all
the possibilities for any combination of the 2 list boxes
and 1
combo box.

Looking at the code, the start & end dates work fine:
The date ranges work fine (If IsNull(Me.txtStart) Or
IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run
this
report."
Exit Sub)
Click to expand...
Click to expand...
Click to expand...
 
D

Dan @BCBS

Your code below answers a lot of questions and makes what I've been trying to
accomplish it look simple.
But your last "If" confuses me, I don't see how all 3 values are passed to
"strWhere" or what it does?
My 3 values are "lstArea","lstProduct" and "cboReviewer"

So here is what a I did. It bombs at the end: Len(strWhere - 5 :

Dim strWhere As Integer
Dim strWhere1 As Integer
Dim strWhere2 As Integer
Dim strWhere3 As Integer


stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If


Thanks







Douglas J. Steele said:
You've already got an Else in the construction: you can't use ElseIf once
you've used Else. Not only that, but ElseIf requires a condition to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example, I'll define
variables strWhere1, strWhere2 and strWhere3, as well as an overall
strWhere. I'll then have a section of code that possible defines strWhere1,
or leaves it blank if there isn't anything applicable to that condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave them blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by checking the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dan @BCBS said:
I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" & cboReviewer.ItemData(stReviewer) & "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
DoCmd.OpenReport stDocName, acPreview
End If











Dan @BCBS said:
Your suggestions are great/simple but let me expalin why my dates are
this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to open the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates entered
at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how can I
add
another value...
This code correctly makes sure the "dates" were entered, also the
"1stArea"
(if 1 or more choices are picked from the list box) and returns the
proper
data on the report..

*** I need to add the combo box choice called "cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea)
&
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #"
&
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns a
number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #"
&
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer & Chr$(34)

I'd also recommend changing how you're passing the dates, just in case
you
end up with a user who has his/her Short Date format set to dd/mm/yyyy
format:

stLinkCriteria = "[issueclosedate] between " & Format$(Me.txtStart,
"\#mm\/dd\/yyyy\#") & " and " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#") &
"
And Somefield = " & Me.cboReviewer

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Are you able to suggest how I can specify the results further by
including
combo box = "cboReviewer"???

Thanks





:

Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one or
more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public stStart As
String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and
#" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box =
"cboReviewer"

Any Suggestions??









:

As I said a few postings ago, I don't see what you're doing with
the
stAreaList variable that you're building. Nowhere in your code
does it
show
how you're passing it to your report or back to the form. The same
with
stStart and stEnd. My suggestion was simply to extend your
string-building
routine to incorporate the 2nd and 3rd controls' values. How are
you
filtering the report? Is it based on a query that you want to have
look
at
the three controls? Does it have it's own code that builds a
filter
string?

Barry

:

Sorry I don't follow. I think I have made this question harder
than
it really
is:
As I mentioned the code works but only for one value (stArea)

All I'm trying to do report data based on 3 values.
1. stArea
2. lstProduct
3. cboReviewer

So whatever combination the user chooses the report generates.
If they only pick from 1 & 2 or 1,2 and 3 or just one of them,
the
report
would produce data equal to that value.

Hope that was a better explanation.




:

I think you can keep appending to stAreaList.

(air code)
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" &
lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
Click to expand...
Click to expand...
Click to expand...
 
D

Dan @BCBS

I've simplified the code to only pull in one value if entered.
The only error I get is a popup saying "13 Type Mismatch"
Any idea what that means???
Or at what point in my code to correct??

Private Sub cmdKey_Click()
On Error GoTo Err_cmdKey_Click
Dim stDocName As String
Dim strWhere As Integer
Dim strWhere3 As Integer

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere3 = [cboReviewer]

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If


stStart = Me.txtStart
stEnd = Me.txtEnd

'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
DoCmd.OpenReport stDocName, acPreview
End If

Dan @BCBS said:
Your code below answers a lot of questions and makes what I've been trying to
accomplish it look simple.
But your last "If" confuses me, I don't see how all 3 values are passed to
"strWhere" or what it does?
My 3 values are "lstArea","lstProduct" and "cboReviewer"

So here is what a I did. It bombs at the end: Len(strWhere - 5 :

Dim strWhere As Integer
Dim strWhere1 As Integer
Dim strWhere2 As Integer
Dim strWhere3 As Integer


stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If


Thanks







Douglas J. Steele said:
You've already got an Else in the construction: you can't use ElseIf once
you've used Else. Not only that, but ElseIf requires a condition to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example, I'll define
variables strWhere1, strWhere2 and strWhere3, as well as an overall
strWhere. I'll then have a section of code that possible defines strWhere1,
or leaves it blank if there isn't anything applicable to that condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave them blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by checking the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dan @BCBS said:
I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" & cboReviewer.ItemData(stReviewer) & "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
DoCmd.OpenReport stDocName, acPreview
End If











:

Your suggestions are great/simple but let me expalin why my dates are
this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to open the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates entered
at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how can I
add
another value...
This code correctly makes sure the "dates" were entered, also the
"1stArea"
(if 1 or more choices are picked from the list box) and returns the
proper
data on the report..

*** I need to add the combo box choice called "cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea)
&
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #"
&
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns a
number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and #"
&
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer & Chr$(34)

I'd also recommend changing how you're passing the dates, just in case
you
end up with a user who has his/her Short Date format set to dd/mm/yyyy
format:

stLinkCriteria = "[issueclosedate] between " & Format$(Me.txtStart,
"\#mm\/dd\/yyyy\#") & " and " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#") &
"
And Somefield = " & Me.cboReviewer

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Are you able to suggest how I can specify the results further by
including
combo box = "cboReviewer"???

Thanks





:

Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one or
more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public stStart As
String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and
#" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box =
"cboReviewer"

Any Suggestions??









:

As I said a few postings ago, I don't see what you're doing with
the
stAreaList variable that you're building. Nowhere in your code
does it
show
how you're passing it to your report or back to the form. The same
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You've declared strWhere as an Integer, but you're trying to assign a String
to it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
I've simplified the code to only pull in one value if entered.
The only error I get is a popup saying "13 Type Mismatch"
Any idea what that means???
Or at what point in my code to correct??

Private Sub cmdKey_Click()
On Error GoTo Err_cmdKey_Click
Dim stDocName As String
Dim strWhere As Integer
Dim strWhere3 As Integer

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere3 = [cboReviewer]

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If


stStart = Me.txtStart
stEnd = Me.txtEnd

'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
DoCmd.OpenReport stDocName, acPreview
End If

Dan @BCBS said:
Your code below answers a lot of questions and makes what I've been
trying to
accomplish it look simple.
But your last "If" confuses me, I don't see how all 3 values are passed
to
"strWhere" or what it does?
My 3 values are "lstArea","lstProduct" and "cboReviewer"

So here is what a I did. It bombs at the end: Len(strWhere - 5 :

Dim strWhere As Integer
Dim strWhere1 As Integer
Dim strWhere2 As Integer
Dim strWhere3 As Integer


stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If


Thanks







Douglas J. Steele said:
You've already got an Else in the construction: you can't use ElseIf
once
you've used Else. Not only that, but ElseIf requires a condition to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example, I'll
define
variables strWhere1, strWhere2 and strWhere3, as well as an overall
strWhere. I'll then have a section of code that possible defines
strWhere1,
or leaves it blank if there isn't anything applicable to that
condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave them
blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by checking the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" & cboReviewer.ItemData(stReviewer)
& "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , ,
True
DoCmd.OpenReport stDocName, acPreview
End If











:

Your suggestions are great/simple but let me expalin why my dates
are
this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to open
the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates
entered
at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how can
I
add
another value...
This code correctly makes sure the "dates" were entered, also the
"1stArea"
(if 1 or more choices are picked from the list box) and returns the
proper
data on the report..

*** I need to add the combo box choice called
"cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea)
&
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "#
and #"
&
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns
a
number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "#
and #"
&
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer &
Chr$(34)

I'd also recommend changing how you're passing the dates, just in
case
you
end up with a user who has his/her Short Date format set to
dd/mm/yyyy
format:

stLinkCriteria = "[issueclosedate] between " &
Format$(Me.txtStart,
"\#mm\/dd\/yyyy\#") & " and " & Format(Me.txtEnd,
"\#mm\/dd\/yyyy\#") &
"
And Somefield = " & Me.cboReviewer

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Are you able to suggest how I can specify the results further by
including
combo box = "cboReviewer"???

Thanks





:

Your question, what am I doing with stAreaList:
My answer: stArea is a list of counties, if the user picks one
or
more it
passes that choice as stAreaList. This works fine.

Your question, what am I doing with stStart and stEnd:
My answer: These are caputred in a public string. Public
stStart As
String

Public stEnd As String....
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart &
"# and
#" &
Me.txtEnd & "#".....
////////////////////////////////

Now I need to specify the results further by combo box =
"cboReviewer"

Any Suggestions??









:

As I said a few postings ago, I don't see what you're doing
with
the
stAreaList variable that you're building. Nowhere in your
code
does it
show
how you're passing it to your report or back to the form. The
same
Click to expand...
Click to expand...
Click to expand...
 
D

Dan @BCBS

oops..that worked.
Now, I add the second value and get error popup saying "94 Invalid use of
Null"

Dim strWhere As String
Dim strWhere2 As String
Dim strWhere3 As String

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

stStart = Me.txtStart
stEnd = Me.txtEnd







Douglas J. Steele said:
You've declared strWhere as an Integer, but you're trying to assign a String
to it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
I've simplified the code to only pull in one value if entered.
The only error I get is a popup saying "13 Type Mismatch"
Any idea what that means???
Or at what point in my code to correct??

Private Sub cmdKey_Click()
On Error GoTo Err_cmdKey_Click
Dim stDocName As String
Dim strWhere As Integer
Dim strWhere3 As Integer

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere3 = [cboReviewer]

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If


stStart = Me.txtStart
stEnd = Me.txtEnd

'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
DoCmd.OpenReport stDocName, acPreview
End If

Dan @BCBS said:
Your code below answers a lot of questions and makes what I've been
trying to
accomplish it look simple.
But your last "If" confuses me, I don't see how all 3 values are passed
to
"strWhere" or what it does?
My 3 values are "lstArea","lstProduct" and "cboReviewer"

So here is what a I did. It bombs at the end: Len(strWhere - 5 :

Dim strWhere As Integer
Dim strWhere1 As Integer
Dim strWhere2 As Integer
Dim strWhere3 As Integer


stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If


Thanks







:

You've already got an Else in the construction: you can't use ElseIf
once
you've used Else. Not only that, but ElseIf requires a condition to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example, I'll
define
variables strWhere1, strWhere2 and strWhere3, as well as an overall
strWhere. I'll then have a section of code that possible defines
strWhere1,
or leaves it blank if there isn't anything applicable to that
condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave them
blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by checking the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" & cboReviewer.ItemData(stReviewer)
& "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , ,
True
DoCmd.OpenReport stDocName, acPreview
End If











:

Your suggestions are great/simple but let me expalin why my dates
are
this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to open
the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates
entered
at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how can
I
add
another value...
This code correctly makes sure the "dates" were entered, also the
"1stArea"
(if 1 or more choices are picked from the list box) and returns the
proper
data on the report..

*** I need to add the combo box choice called
"cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea)
&
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "#
and #"
&
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns
a
number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "#
and #"
&
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer &
Chr$(34)
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If you haven't chosen anything in the listbox or combobox, the reference to
it is going to return Null. You cannot assign Null to a string variable: the
only variable type that can accept a Null value is a variant.

Assuming you make those changes to the declarations, you'll also need to
change

If Len(strWhere2) > 0 Then

to

If Len(strWhere2 & vbNullString) > 0 Then

or

If Len(strWhere2 & "") > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
oops..that worked.
Now, I add the second value and get error popup saying "94 Invalid use of
Null"

Dim strWhere As String
Dim strWhere2 As String
Dim strWhere3 As String

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

stStart = Me.txtStart
stEnd = Me.txtEnd







Douglas J. Steele said:
You've declared strWhere as an Integer, but you're trying to assign a
String
to it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
I've simplified the code to only pull in one value if entered.
The only error I get is a popup saying "13 Type Mismatch"
Any idea what that means???
Or at what point in my code to correct??

Private Sub cmdKey_Click()
On Error GoTo Err_cmdKey_Click
Dim stDocName As String
Dim strWhere As Integer
Dim strWhere3 As Integer

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere3 = [cboReviewer]

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If


stStart = Me.txtStart
stEnd = Me.txtEnd

'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , ,
True
DoCmd.OpenReport stDocName, acPreview
End If

:

Your code below answers a lot of questions and makes what I've been
trying to
accomplish it look simple.
But your last "If" confuses me, I don't see how all 3 values are
passed
to
"strWhere" or what it does?
My 3 values are "lstArea","lstProduct" and "cboReviewer"

So here is what a I did. It bombs at the end: Len(strWhere - 5 :

Dim strWhere As Integer
Dim strWhere1 As Integer
Dim strWhere2 As Integer
Dim strWhere3 As Integer


stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If


Thanks







:

You've already got an Else in the construction: you can't use ElseIf
once
you've used Else. Not only that, but ElseIf requires a condition to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example, I'll
define
variables strWhere1, strWhere2 and strWhere3, as well as an overall
strWhere. I'll then have a section of code that possible defines
strWhere1,
or leaves it blank if there isn't anything applicable to that
condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave them
blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by checking
the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" &
cboReviewer.ItemData(stReviewer)
& "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , ,
,
True
DoCmd.OpenReport stDocName, acPreview
End If











:

Your suggestions are great/simple but let me expalin why my dates
are
this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to
open
the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates
entered
at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how
can
I
add
another value...
This code correctly makes sure the "dates" were entered, also the
"1stArea"
(if 1 or more choices are picked from the list box) and returns
the
proper
data on the report..

*** I need to add the combo box choice called
"cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea)
&
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart &
"#
and #"
&
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer
returns
a
number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart &
"#
and #"
&
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer &
Chr$(34)
Click to expand...
Click to expand...
Click to expand...
 
D

Dan @BCBS

I cannot get past your last IF it keeps bombing at "(strWhere - 5))"
When I do one value at a time the job runs but all the data is returned, not
specific to the value choosen.

This is with all 3 vlaues, it bombs "Compile Error: Variable required -
can't assign to this expression. "(strWhere - 5))"

Dim strWhere As String
Dim strWhere1 As Variant
Dim strWhere2 As Variant
Dim strWhere3 As Variant


stDocName = "Report1"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please make sure you entered a start and end date."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If

If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If
///////////////////////////////////////////




Douglas J. Steele said:
If you haven't chosen anything in the listbox or combobox, the reference to
it is going to return Null. You cannot assign Null to a string variable: the
only variable type that can accept a Null value is a variant.

Assuming you make those changes to the declarations, you'll also need to
change

If Len(strWhere2) > 0 Then

to

If Len(strWhere2 & vbNullString) > 0 Then

or

If Len(strWhere2 & "") > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
oops..that worked.
Now, I add the second value and get error popup saying "94 Invalid use of
Null"

Dim strWhere As String
Dim strWhere2 As String
Dim strWhere3 As String

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

stStart = Me.txtStart
stEnd = Me.txtEnd







Douglas J. Steele said:
You've declared strWhere as an Integer, but you're trying to assign a
String
to it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I've simplified the code to only pull in one value if entered.
The only error I get is a popup saying "13 Type Mismatch"
Any idea what that means???
Or at what point in my code to correct??

Private Sub cmdKey_Click()
On Error GoTo Err_cmdKey_Click
Dim stDocName As String
Dim strWhere As Integer
Dim strWhere3 As Integer

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere3 = [cboReviewer]

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If


stStart = Me.txtStart
stEnd = Me.txtEnd

'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , ,
True
DoCmd.OpenReport stDocName, acPreview
End If

:

Your code below answers a lot of questions and makes what I've been
trying to
accomplish it look simple.
But your last "If" confuses me, I don't see how all 3 values are
passed
to
"strWhere" or what it does?
My 3 values are "lstArea","lstProduct" and "cboReviewer"

So here is what a I did. It bombs at the end: Len(strWhere - 5 :

Dim strWhere As Integer
Dim strWhere1 As Integer
Dim strWhere2 As Integer
Dim strWhere3 As Integer


stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If


Thanks







:

You've already got an Else in the construction: you can't use ElseIf
once
you've used Else. Not only that, but ElseIf requires a condition to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example, I'll
define
variables strWhere1, strWhere2 and strWhere3, as well as an overall
strWhere. I'll then have a section of code that possible defines
strWhere1,
or leaves it blank if there isn't anything applicable to that
condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave them
blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by checking
the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" &
cboReviewer.ItemData(stReviewer)
& "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , ,
,
True
DoCmd.OpenReport stDocName, acPreview
End If











:

Your suggestions are great/simple but let me expalin why my dates
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry: it's a typo. It should be

strWhere = " WHERE " & Left$(strWhere, Len(strWhere) - 5)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dan @BCBS said:
I cannot get past your last IF it keeps bombing at "(strWhere - 5))"
When I do one value at a time the job runs but all the data is returned,
not
specific to the value choosen.

This is with all 3 vlaues, it bombs "Compile Error: Variable required -
can't assign to this expression. "(strWhere - 5))"

Dim strWhere As String
Dim strWhere1 As Variant
Dim strWhere2 As Variant
Dim strWhere3 As Variant


stDocName = "Report1"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please make sure you entered a start and end date."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If

If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If
///////////////////////////////////////////




Douglas J. Steele said:
If you haven't chosen anything in the listbox or combobox, the reference
to
it is going to return Null. You cannot assign Null to a string variable:
the
only variable type that can accept a Null value is a variant.

Assuming you make those changes to the declarations, you'll also need to
change

If Len(strWhere2) > 0 Then

to

If Len(strWhere2 & vbNullString) > 0 Then

or

If Len(strWhere2 & "") > 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
oops..that worked.
Now, I add the second value and get error popup saying "94 Invalid use
of
Null"

Dim strWhere As String
Dim strWhere2 As String
Dim strWhere3 As String

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

stStart = Me.txtStart
stEnd = Me.txtEnd







:

You've declared strWhere as an Integer, but you're trying to assign a
String
to it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I've simplified the code to only pull in one value if entered.
The only error I get is a popup saying "13 Type Mismatch"
Any idea what that means???
Or at what point in my code to correct??

Private Sub cmdKey_Click()
On Error GoTo Err_cmdKey_Click
Dim stDocName As String
Dim strWhere As Integer
Dim strWhere3 As Integer

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else

strWhere3 = [cboReviewer]

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If


stStart = Me.txtStart
stEnd = Me.txtEnd

'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , ,
True
DoCmd.OpenReport stDocName, acPreview
End If

:

Your code below answers a lot of questions and makes what I've been
trying to
accomplish it look simple.
But your last "If" confuses me, I don't see how all 3 values are
passed
to
"strWhere" or what it does?
My 3 values are "lstArea","lstProduct" and "cboReviewer"

So here is what a I did. It bombs at the end: Len(strWhere - 5 :

Dim strWhere As Integer
Dim strWhere1 As Integer
Dim strWhere2 As Integer
Dim strWhere3 As Integer


stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If


Thanks







:

You've already got an Else in the construction: you can't use
ElseIf
once
you've used Else. Not only that, but ElseIf requires a condition
to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example,
I'll
define
variables strWhere1, strWhere2 and strWhere3, as well as an
overall
strWhere. I'll then have a section of code that possible defines
strWhere1,
or leaves it blank if there isn't anything applicable to that
condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave
them
blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by
checking
the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) &
"'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" &
cboReviewer.ItemData(stReviewer)
& "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , ,
,
,
True
DoCmd.OpenReport stDocName, acPreview
End If











:

Your suggestions are great/simple but let me expalin why my
dates
Click to expand...
Click to expand...
Click to expand...
 
D

Dan @BCBS

That certainly was an over sight on my part.
But even so, I get the same error message at: (strWhere - 5))
Dim stDocName As String
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere3 As String


stDocName = "Report1"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please make sure you entered a start and end date."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If

If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If

//////////////////////////////////


Douglas J. Steele said:
You've declared strWhere as an Integer, but you're trying to assign a String
to it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dan @BCBS said:
I've simplified the code to only pull in one value if entered.
The only error I get is a popup saying "13 Type Mismatch"
Any idea what that means???
Or at what point in my code to correct??

Private Sub cmdKey_Click()
On Error GoTo Err_cmdKey_Click
Dim stDocName As String
Dim strWhere As Integer
Dim strWhere3 As Integer

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere3 = [cboReviewer]

If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If


stStart = Me.txtStart
stEnd = Me.txtEnd

'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
DoCmd.OpenReport stDocName, acPreview
End If

Dan @BCBS said:
Your code below answers a lot of questions and makes what I've been
trying to
accomplish it look simple.
But your last "If" confuses me, I don't see how all 3 values are passed
to
"strWhere" or what it does?
My 3 values are "lstArea","lstProduct" and "cboReviewer"

So here is what a I did. It bombs at the end: Len(strWhere - 5 :

Dim strWhere As Integer
Dim strWhere1 As Integer
Dim strWhere2 As Integer
Dim strWhere3 As Integer


stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else

strWhere1 = [lstArea]
strWhere2 = [lstProduct]
strWhere3 = [cboReviewer]

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If


Thanks







:

You've already got an Else in the construction: you can't use ElseIf
once
you've used Else. Not only that, but ElseIf requires a condition to
evaluate:

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

What I typically do is take each part separately. For example, I'll
define
variables strWhere1, strWhere2 and strWhere3, as well as an overall
strWhere. I'll then have a section of code that possible defines
strWhere1,
or leaves it blank if there isn't anything applicable to that
condition.
Similarly, I'll either define strWhere2 and strWhere3, or leave them
blank,
depending on the fields on the form.

Once I've done all that, I'll complete the Where clause by checking the
values of each of the variables:

If Len(strWhere1) > 0 Then
strWhere = strWhere & strWhere1 & " AND "
End If
If Len(strWhere2) > 0 Then
strWhere = strWhere & strWhere2 & " AND "
End If
If Len(strWhere3) > 0 Then
strWhere = strWhere & strWhere3 & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = " WHERE " & Left$(strWhere, Len(strWhere - 5))
End If



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I tried using "ElseIf" and just duplicate the X-value.
Could you look at this to see why it will not allow an "ElseIf"
(Note: These values are Public
Public stStart As String
Public stEnd As String
Public stAreaList As String
Public stRevList As String)


Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim stArea As Variant
Dim stReviewer As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

ElseIf

Y = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stRevList = "In('" & cboReviewer.ItemData(stReviewer)
& "'"
Else
stRevList = stRevList & ",'" &
cboReviewer.ItemData(cboReviewer) & "'"
End If
Y = Y + 1
Next cboReviewer

stRevList = stRevList & ")"
End If


If stRevList = ")" Then
MsgBox "Please select a Location."

stStart = Me.txtStart
stEnd = Me.txtEnd

Else
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , ,
True
DoCmd.OpenReport stDocName, acPreview
End If











:

Your suggestions are great/simple but let me expalin why my dates
are
this way:

A. command button- .......strReport = "Audit"...Bla Bla.... to open
the
report.

B. Report event:
If Forms!frmreportsquality.strReport = "Audit" Then.........

In the Report event, I pull out Distinct data including the dates
entered
at
the beginning on the form that the command button in on.

My Question is - and I'm going back to my original question, how can
I
add
another value...
This code correctly makes sure the "dates" were entered, also the
"1stArea"
(if 1 or more choices are picked from the list box) and returns the
proper
data on the report..

*** I need to add the combo box choice called
"cboReviewer".......****

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this
report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea)
&
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If






:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "#
and #"
&
Me.txtEnd & "# And Somefield = " & Me.cboReviewer

That assumes Somefield is a numeric field, and cboReviewer returns
a
number.
If it's text, you'll need quotes:

stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "#
and #"
&
Me.txtEnd & "# And Somefield = " & Chr$(34) & Me.cboReviewer &
Chr$(34)
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

Combo Box 15
3 choices 3
3 list boxes 1 answer 9
DoCmd.OpenQuery 4
DoCmd.SendObject 2
Type Mismatch 5
Match style 0
Making a macro only work on certain files 2

Top