D
Douglas J. Steele
There's a closing parenthesis in the wrong place:
strWhere = " WHERE " & Left$(strWhere, Len(strWhere) - 5)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
strWhere = " WHERE " & Left$(strWhere, Len(strWhere) - 5)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Dan @BCBS said: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
:
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)