Limiting Criteria to Exact String (not contain)

  • Thread starter Thread starter ASchaum
  • Start date Start date
A

ASchaum

I have a database that was designed for my non-profit a few years ago and I
just discovered that one of the reports doesn't limit the records being
pulled correctly. The designer created a form with assorted options that
then pulls a report based on those options. The field posing the problem is
the CompanyID (my primary key for my company table) - entries for 3 records
(V332, V3320 and V3328) are appearing on a report when I only want the V332
entry.
How can I limit the string to exactly what is selected on the form?
Below is the code from the button that pulls the report:

Private Sub Command13_Click()

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim StrSQL As String, strRestrict As String
Dim lngX As Long
Dim ArgCount As Integer
Dim Tmp As Variant
Dim i As Integer
Dim strCategories As String
Dim intCount As Integer

MySQL = "SELECT tService.ServiceID, tService.CompanyID,
tService.ServiceDate, tService.Staff, tService.Why, tService.ServiceCategory,
tService.ServiceDesc, tService.Priority, tService.ServiceProvider,
tService.Status, tService.NextStep, tService.CallBackDate,
tService.DateServiceCompleted, tService.PotentialServiceCategory,
tService.Referal, tService.[Service Provided], tService.Completed,
tService.ServiceDueDate, tService.TimeInvestment, tService.ProgramArea,
tCompany.CompanyName, tCompany.LDCLoc " _
& "FROM tService INNER JOIN tCompany ON tService.CompanyID =
tCompany.CompanyID " _
& "WHERE "

'Build Criteria
AddToWhere [CDCArea], "[LDCLoc]", MyCriteria, ArgCount
AddToWhere [Staff], "[ServiceProvider]", MyCriteria, ArgCount
AddToWhere [Status], "[Completed]", MyCriteria, ArgCount
AddToWhere [CompanyName], "[tCompany].[CompanyID]", MyCriteria, ArgCount


If MyCriteria = "" Then
MyCriteria = "True"
End If

'Build Date Range
If Me.StartDate <> "" And Me.EndDate <> "" Then
If MyCriteria <> "True" Then
MyCriteria = MyCriteria & " And " & "[ServiceDate] Between " & "#" &
Me.StartDate & "#" & " And " & "#" & Me.EndDate & "# "

Else
MyCriteria = "[ServiceDate] Between " & "#" & Me.StartDate & "#" & "
And " & "#" & Me.EndDate & "# "
End If

End If

'Check to make sure at least one Category is selected
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
intCount = intCount + 1
End If
Next i
If intCount >= 1 Then ' Selected at least one

'Built string to hold student id's
strCategories = "IN("
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
strCategories = strCategories & "'" & List0.Column(0, i)
& "'" & ","
End If
Next i

If MyCriteria = "TRUE" Then
MyCriteria = "[ServiceCategory] " & Left(strCategories,
Len(strCategories) - 1) & ")"
Else
MyCriteria = MyCriteria & " And [ServiceCategory] " &
Left(strCategories, Len(strCategories) - 1) & ")"
End If
End If







MyRecordSource = MySQL & MyCriteria
Me.Criteria = MyRecordSource
If Me.Frame28 = 1 Then 'Detail Report Based On Selected Criteria
DoCmd.OpenReport "rptServiceRequest", acViewPreview
ElseIf Me.Frame28 = 2 Then 'Summary Report Based On Selected
Criteria, Need to Select Grouping
Select Case Me.Frame36
Case 1 'Grouped by Service Category
DoCmd.OpenReport "rptServiceSummaryByCategory",
acViewPreview
Case 2 'Grouped by Service Provider
DoCmd.OpenReport "rptServiceSummaryByProvider",
acViewPreview
Case 3 'Grouped by CDC Location
DoCmd.OpenReport "rptServiceSummaryByCDC", acViewPreview
End Select
End If

DoCmd.Close acForm, Me.Name
Forms![frmReportSwitchboard].Visible = False



End Sub
 
You need to show us the code for the subroutine AddToWhere, which is being
called by your button's code. Likely that subroutine is building a criterion
that uses Like "YourString*" instead of = "YourString".

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


ASchaum said:
I have a database that was designed for my non-profit a few years ago and I
just discovered that one of the reports doesn't limit the records being
pulled correctly. The designer created a form with assorted options that
then pulls a report based on those options. The field posing the problem
is
the CompanyID (my primary key for my company table) - entries for 3
records
(V332, V3320 and V3328) are appearing on a report when I only want the
V332
entry.
How can I limit the string to exactly what is selected on the form?
Below is the code from the button that pulls the report:

Private Sub Command13_Click()

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim StrSQL As String, strRestrict As String
Dim lngX As Long
Dim ArgCount As Integer
Dim Tmp As Variant
Dim i As Integer
Dim strCategories As String
Dim intCount As Integer

MySQL = "SELECT tService.ServiceID, tService.CompanyID,
tService.ServiceDate, tService.Staff, tService.Why,
tService.ServiceCategory,
tService.ServiceDesc, tService.Priority, tService.ServiceProvider,
tService.Status, tService.NextStep, tService.CallBackDate,
tService.DateServiceCompleted, tService.PotentialServiceCategory,
tService.Referal, tService.[Service Provided], tService.Completed,
tService.ServiceDueDate, tService.TimeInvestment, tService.ProgramArea,
tCompany.CompanyName, tCompany.LDCLoc " _
& "FROM tService INNER JOIN tCompany ON tService.CompanyID =
tCompany.CompanyID " _
& "WHERE "

'Build Criteria
AddToWhere [CDCArea], "[LDCLoc]", MyCriteria, ArgCount
AddToWhere [Staff], "[ServiceProvider]", MyCriteria, ArgCount
AddToWhere [Status], "[Completed]", MyCriteria, ArgCount
AddToWhere [CompanyName], "[tCompany].[CompanyID]", MyCriteria, ArgCount


If MyCriteria = "" Then
MyCriteria = "True"
End If

'Build Date Range
If Me.StartDate <> "" And Me.EndDate <> "" Then
If MyCriteria <> "True" Then
MyCriteria = MyCriteria & " And " & "[ServiceDate] Between " & "#"
&
Me.StartDate & "#" & " And " & "#" & Me.EndDate & "# "

Else
MyCriteria = "[ServiceDate] Between " & "#" & Me.StartDate & "#" &
"
And " & "#" & Me.EndDate & "# "
End If

End If

'Check to make sure at least one Category is selected
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
intCount = intCount + 1
End If
Next i
If intCount >= 1 Then ' Selected at least one

'Built string to hold student id's
strCategories = "IN("
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
strCategories = strCategories & "'" & List0.Column(0,
i)
& "'" & ","
End If
Next i

If MyCriteria = "TRUE" Then
MyCriteria = "[ServiceCategory] " & Left(strCategories,
Len(strCategories) - 1) & ")"
Else
MyCriteria = MyCriteria & " And [ServiceCategory] " &
Left(strCategories, Len(strCategories) - 1) & ")"
End If
End If







MyRecordSource = MySQL & MyCriteria
Me.Criteria = MyRecordSource
If Me.Frame28 = 1 Then 'Detail Report Based On Selected Criteria
DoCmd.OpenReport "rptServiceRequest", acViewPreview
ElseIf Me.Frame28 = 2 Then 'Summary Report Based On Selected
Criteria, Need to Select Grouping
Select Case Me.Frame36
Case 1 'Grouped by Service Category
DoCmd.OpenReport "rptServiceSummaryByCategory",
acViewPreview
Case 2 'Grouped by Service Provider
DoCmd.OpenReport "rptServiceSummaryByProvider",
acViewPreview
Case 3 'Grouped by CDC Location
DoCmd.OpenReport "rptServiceSummaryByCDC",
acViewPreview
End Select
End If

DoCmd.Close acForm, Me.Name
Forms![frmReportSwitchboard].Visible = False



End Sub
Click to expand...
 
Sorry, I'm not quite sure what you mean. The code that I attached was from
the button that generates the report. I don't see where to find code for the
subroutine.

I don't know if this helps, but if I look at the On Open code for one of the
reports that is generated through this form, it says:

Private Sub Report_Open(Cancel As Integer)
If IsLoaded("frmServiceReportOptions") Then
Me.RecordSource = Forms![frmServiceReportOptions].Criteria
End If
End Sub


Ken Snell said:
You need to show us the code for the subroutine AddToWhere, which is being
called by your button's code. Likely that subroutine is building a criterion
that uses Like "YourString*" instead of = "YourString".

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


ASchaum said:
I have a database that was designed for my non-profit a few years ago and I
just discovered that one of the reports doesn't limit the records being
pulled correctly. The designer created a form with assorted options that
then pulls a report based on those options. The field posing the problem
is
the CompanyID (my primary key for my company table) - entries for 3
records
(V332, V3320 and V3328) are appearing on a report when I only want the
V332
entry.
How can I limit the string to exactly what is selected on the form?
Below is the code from the button that pulls the report:

Private Sub Command13_Click()

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim StrSQL As String, strRestrict As String
Dim lngX As Long
Dim ArgCount As Integer
Dim Tmp As Variant
Dim i As Integer
Dim strCategories As String
Dim intCount As Integer

MySQL = "SELECT tService.ServiceID, tService.CompanyID,
tService.ServiceDate, tService.Staff, tService.Why,
tService.ServiceCategory,
tService.ServiceDesc, tService.Priority, tService.ServiceProvider,
tService.Status, tService.NextStep, tService.CallBackDate,
tService.DateServiceCompleted, tService.PotentialServiceCategory,
tService.Referal, tService.[Service Provided], tService.Completed,
tService.ServiceDueDate, tService.TimeInvestment, tService.ProgramArea,
tCompany.CompanyName, tCompany.LDCLoc " _
& "FROM tService INNER JOIN tCompany ON tService.CompanyID =
tCompany.CompanyID " _
& "WHERE "

'Build Criteria
AddToWhere [CDCArea], "[LDCLoc]", MyCriteria, ArgCount
AddToWhere [Staff], "[ServiceProvider]", MyCriteria, ArgCount
AddToWhere [Status], "[Completed]", MyCriteria, ArgCount
AddToWhere [CompanyName], "[tCompany].[CompanyID]", MyCriteria, ArgCount


If MyCriteria = "" Then
MyCriteria = "True"
End If

'Build Date Range
If Me.StartDate <> "" And Me.EndDate <> "" Then
If MyCriteria <> "True" Then
MyCriteria = MyCriteria & " And " & "[ServiceDate] Between " & "#"
&
Me.StartDate & "#" & " And " & "#" & Me.EndDate & "# "

Else
MyCriteria = "[ServiceDate] Between " & "#" & Me.StartDate & "#" &
"
And " & "#" & Me.EndDate & "# "
End If

End If

'Check to make sure at least one Category is selected
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
intCount = intCount + 1
End If
Next i
If intCount >= 1 Then ' Selected at least one

'Built string to hold student id's
strCategories = "IN("
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
strCategories = strCategories & "'" & List0.Column(0,
i)
& "'" & ","
End If
Next i

If MyCriteria = "TRUE" Then
MyCriteria = "[ServiceCategory] " & Left(strCategories,
Len(strCategories) - 1) & ")"
Else
MyCriteria = MyCriteria & " And [ServiceCategory] " &
Left(strCategories, Len(strCategories) - 1) & ")"
End If
End If







MyRecordSource = MySQL & MyCriteria
Me.Criteria = MyRecordSource
If Me.Frame28 = 1 Then 'Detail Report Based On Selected Criteria
DoCmd.OpenReport "rptServiceRequest", acViewPreview
ElseIf Me.Frame28 = 2 Then 'Summary Report Based On Selected
Criteria, Need to Select Grouping
Select Case Me.Frame36
Case 1 'Grouped by Service Category
DoCmd.OpenReport "rptServiceSummaryByCategory",
acViewPreview
Case 2 'Grouped by Service Provider
DoCmd.OpenReport "rptServiceSummaryByProvider",
acViewPreview
Case 3 'Grouped by CDC Location
DoCmd.OpenReport "rptServiceSummaryByCDC",
acViewPreview
End Select
End If

DoCmd.Close acForm, Me.Name
Forms![frmReportSwitchboard].Visible = False



End Sub
Click to expand...
Click to expand...
 
Go the line of code where you see AddToWhere, click on the middle of that
word, and press Shift+F2. You'll be taken the module where that subroutine
is located. Copy and paste the code here.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


ASchaum said:
Sorry, I'm not quite sure what you mean. The code that I attached was
from
the button that generates the report. I don't see where to find code for
the
subroutine.

I don't know if this helps, but if I look at the On Open code for one of
the
reports that is generated through this form, it says:

Private Sub Report_Open(Cancel As Integer)
If IsLoaded("frmServiceReportOptions") Then
Me.RecordSource = Forms![frmServiceReportOptions].Criteria
End If
End Sub


Ken Snell said:
You need to show us the code for the subroutine AddToWhere, which is
being
called by your button's code. Likely that subroutine is building a
criterion
that uses Like "YourString*" instead of = "YourString".

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


ASchaum said:
I have a database that was designed for my non-profit a few years ago
and I
just discovered that one of the reports doesn't limit the records being
pulled correctly. The designer created a form with assorted options
that
then pulls a report based on those options. The field posing the
problem
is
the CompanyID (my primary key for my company table) - entries for 3
records
(V332, V3320 and V3328) are appearing on a report when I only want the
V332
entry.
How can I limit the string to exactly what is selected on the form?
Below is the code from the button that pulls the report:

Private Sub Command13_Click()

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim StrSQL As String, strRestrict As String
Dim lngX As Long
Dim ArgCount As Integer
Dim Tmp As Variant
Dim i As Integer
Dim strCategories As String
Dim intCount As Integer

MySQL = "SELECT tService.ServiceID, tService.CompanyID,
tService.ServiceDate, tService.Staff, tService.Why,
tService.ServiceCategory,
tService.ServiceDesc, tService.Priority, tService.ServiceProvider,
tService.Status, tService.NextStep, tService.CallBackDate,
tService.DateServiceCompleted, tService.PotentialServiceCategory,
tService.Referal, tService.[Service Provided], tService.Completed,
tService.ServiceDueDate, tService.TimeInvestment, tService.ProgramArea,
tCompany.CompanyName, tCompany.LDCLoc " _
& "FROM tService INNER JOIN tCompany ON tService.CompanyID =
tCompany.CompanyID " _
& "WHERE "

'Build Criteria
AddToWhere [CDCArea], "[LDCLoc]", MyCriteria, ArgCount
AddToWhere [Staff], "[ServiceProvider]", MyCriteria, ArgCount
AddToWhere [Status], "[Completed]", MyCriteria, ArgCount
AddToWhere [CompanyName], "[tCompany].[CompanyID]", MyCriteria,
ArgCount


If MyCriteria = "" Then
MyCriteria = "True"
End If

'Build Date Range
If Me.StartDate <> "" And Me.EndDate <> "" Then
If MyCriteria <> "True" Then
MyCriteria = MyCriteria & " And " & "[ServiceDate] Between " &
"#"
&
Me.StartDate & "#" & " And " & "#" & Me.EndDate & "# "

Else
MyCriteria = "[ServiceDate] Between " & "#" & Me.StartDate & "#"
&
"
And " & "#" & Me.EndDate & "# "
End If

End If

'Check to make sure at least one Category is selected
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
intCount = intCount + 1
End If
Next i
If intCount >= 1 Then ' Selected at least one

'Built string to hold student id's
strCategories = "IN("
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
strCategories = strCategories & "'" &
List0.Column(0,
i)
& "'" & ","
End If
Next i

If MyCriteria = "TRUE" Then
MyCriteria = "[ServiceCategory] " & Left(strCategories,
Len(strCategories) - 1) & ")"
Else
MyCriteria = MyCriteria & " And [ServiceCategory] " &
Left(strCategories, Len(strCategories) - 1) & ")"
End If
End If







MyRecordSource = MySQL & MyCriteria
Me.Criteria = MyRecordSource
If Me.Frame28 = 1 Then 'Detail Report Based On Selected Criteria
DoCmd.OpenReport "rptServiceRequest", acViewPreview
ElseIf Me.Frame28 = 2 Then 'Summary Report Based On Selected
Criteria, Need to Select Grouping
Select Case Me.Frame36
Case 1 'Grouped by Service Category
DoCmd.OpenReport "rptServiceSummaryByCategory",
acViewPreview
Case 2 'Grouped by Service Provider
DoCmd.OpenReport "rptServiceSummaryByProvider",
acViewPreview
Case 3 'Grouped by CDC Location
DoCmd.OpenReport "rptServiceSummaryByCDC",
acViewPreview
End Select
End If

DoCmd.Close acForm, Me.Name
Forms![frmReportSwitchboard].Visible = False



End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks.
Here's that code:
Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As
String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) &
FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub

Ken Snell said:
Go the line of code where you see AddToWhere, click on the middle of that
word, and press Shift+F2. You'll be taken the module where that subroutine
is located. Copy and paste the code here.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


ASchaum said:
Sorry, I'm not quite sure what you mean. The code that I attached was
from
the button that generates the report. I don't see where to find code for
the
subroutine.

I don't know if this helps, but if I look at the On Open code for one of
the
reports that is generated through this form, it says:

Private Sub Report_Open(Cancel As Integer)
If IsLoaded("frmServiceReportOptions") Then
Me.RecordSource = Forms![frmServiceReportOptions].Criteria
End If
End Sub


Ken Snell said:
You need to show us the code for the subroutine AddToWhere, which is
being
called by your button's code. Likely that subroutine is building a
criterion
that uses Like "YourString*" instead of = "YourString".

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a database that was designed for my non-profit a few years ago
and I
just discovered that one of the reports doesn't limit the records being
pulled correctly. The designer created a form with assorted options
that
then pulls a report based on those options. The field posing the
problem
is
the CompanyID (my primary key for my company table) - entries for 3
records
(V332, V3320 and V3328) are appearing on a report when I only want the
V332
entry.
How can I limit the string to exactly what is selected on the form?
Below is the code from the button that pulls the report:

Private Sub Command13_Click()

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim StrSQL As String, strRestrict As String
Dim lngX As Long
Dim ArgCount As Integer
Dim Tmp As Variant
Dim i As Integer
Dim strCategories As String
Dim intCount As Integer

MySQL = "SELECT tService.ServiceID, tService.CompanyID,
tService.ServiceDate, tService.Staff, tService.Why,
tService.ServiceCategory,
tService.ServiceDesc, tService.Priority, tService.ServiceProvider,
tService.Status, tService.NextStep, tService.CallBackDate,
tService.DateServiceCompleted, tService.PotentialServiceCategory,
tService.Referal, tService.[Service Provided], tService.Completed,
tService.ServiceDueDate, tService.TimeInvestment, tService.ProgramArea,
tCompany.CompanyName, tCompany.LDCLoc " _
& "FROM tService INNER JOIN tCompany ON tService.CompanyID =
tCompany.CompanyID " _
& "WHERE "

'Build Criteria
AddToWhere [CDCArea], "[LDCLoc]", MyCriteria, ArgCount
AddToWhere [Staff], "[ServiceProvider]", MyCriteria, ArgCount
AddToWhere [Status], "[Completed]", MyCriteria, ArgCount
AddToWhere [CompanyName], "[tCompany].[CompanyID]", MyCriteria,
ArgCount


If MyCriteria = "" Then
MyCriteria = "True"
End If

'Build Date Range
If Me.StartDate <> "" And Me.EndDate <> "" Then
If MyCriteria <> "True" Then
MyCriteria = MyCriteria & " And " & "[ServiceDate] Between " &
"#"
&
Me.StartDate & "#" & " And " & "#" & Me.EndDate & "# "

Else
MyCriteria = "[ServiceDate] Between " & "#" & Me.StartDate & "#"
&
"
And " & "#" & Me.EndDate & "# "
End If

End If

'Check to make sure at least one Category is selected
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
intCount = intCount + 1
End If
Next i
If intCount >= 1 Then ' Selected at least one

'Built string to hold student id's
strCategories = "IN("
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
strCategories = strCategories & "'" &
List0.Column(0,
i)
& "'" & ","
End If
Next i

If MyCriteria = "TRUE" Then
MyCriteria = "[ServiceCategory] " & Left(strCategories,
Len(strCategories) - 1) & ")"
Else
MyCriteria = MyCriteria & " And [ServiceCategory] " &
Left(strCategories, Len(strCategories) - 1) & ")"
End If
End If







MyRecordSource = MySQL & MyCriteria
Me.Criteria = MyRecordSource
If Me.Frame28 = 1 Then 'Detail Report Based On Selected Criteria
DoCmd.OpenReport "rptServiceRequest", acViewPreview
ElseIf Me.Frame28 = 2 Then 'Summary Report Based On Selected
Criteria, Need to Select Grouping
Select Case Me.Frame36
Case 1 'Grouped by Service Category
DoCmd.OpenReport "rptServiceSummaryByCategory",
acViewPreview
Case 2 'Grouped by Service Provider
DoCmd.OpenReport "rptServiceSummaryByProvider",
acViewPreview
Case 3 'Grouped by CDC Location
DoCmd.OpenReport "rptServiceSummaryByCDC",
acViewPreview
End Select
End If

DoCmd.Close acForm, Me.Name
Forms![frmReportSwitchboard].Visible = False



End Sub
Click to expand...
Click to expand...
Click to expand...
 
The AddToWhere subroutine is creating a criterion string that looks like
this:

[tCompany].[CompanyID] Like 'V332*'

The above use of Like and the inclusion of the "*" wildcard is what is
giving you the extra results. There are different ways to fix this.

You could add an argument to the subroutine that you'd specify with one
value when you want the wildcard search and with another value when you want
an exact search.

Or you could use a different subroutine that generates an exact search
criterion string instead of a wild card search, and call that subroutine for
your CompanyID part.

Or you could just change the maiin code to not call the subroutine at all
and just build the string in the main code.

Which do you prefer?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



ASchaum said:
Thanks.
Here's that code:
Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As
String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) &
FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub

Ken Snell said:
Go the line of code where you see AddToWhere, click on the middle of that
word, and press Shift+F2. You'll be taken the module where that
subroutine
is located. Copy and paste the code here.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


ASchaum said:
Sorry, I'm not quite sure what you mean. The code that I attached was
from
the button that generates the report. I don't see where to find code
for
the
subroutine.

I don't know if this helps, but if I look at the On Open code for one
of
the
reports that is generated through this form, it says:

Private Sub Report_Open(Cancel As Integer)
If IsLoaded("frmServiceReportOptions") Then
Me.RecordSource = Forms![frmServiceReportOptions].Criteria
End If
End Sub


:

You need to show us the code for the subroutine AddToWhere, which is
being
called by your button's code. Likely that subroutine is building a
criterion
that uses Like "YourString*" instead of = "YourString".

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a database that was designed for my non-profit a few years ago
and I
just discovered that one of the reports doesn't limit the records
being
pulled correctly. The designer created a form with assorted options
that
then pulls a report based on those options. The field posing the
problem
is
the CompanyID (my primary key for my company table) - entries for 3
records
(V332, V3320 and V3328) are appearing on a report when I only want
the
V332
entry.
How can I limit the string to exactly what is selected on the form?
Below is the code from the button that pulls the report:

Private Sub Command13_Click()

Dim MySQL As String, MyCriteria As String, MyRecordSource As
String
Dim StrSQL As String, strRestrict As String
Dim lngX As Long
Dim ArgCount As Integer
Dim Tmp As Variant
Dim i As Integer
Dim strCategories As String
Dim intCount As Integer

MySQL = "SELECT tService.ServiceID, tService.CompanyID,
tService.ServiceDate, tService.Staff, tService.Why,
tService.ServiceCategory,
tService.ServiceDesc, tService.Priority, tService.ServiceProvider,
tService.Status, tService.NextStep, tService.CallBackDate,
tService.DateServiceCompleted, tService.PotentialServiceCategory,
tService.Referal, tService.[Service Provided], tService.Completed,
tService.ServiceDueDate, tService.TimeInvestment,
tService.ProgramArea,
tCompany.CompanyName, tCompany.LDCLoc " _
& "FROM tService INNER JOIN tCompany ON tService.CompanyID =
tCompany.CompanyID " _
& "WHERE "

'Build Criteria
AddToWhere [CDCArea], "[LDCLoc]", MyCriteria, ArgCount
AddToWhere [Staff], "[ServiceProvider]", MyCriteria, ArgCount
AddToWhere [Status], "[Completed]", MyCriteria, ArgCount
AddToWhere [CompanyName], "[tCompany].[CompanyID]", MyCriteria,
ArgCount


If MyCriteria = "" Then
MyCriteria = "True"
End If

'Build Date Range
If Me.StartDate <> "" And Me.EndDate <> "" Then
If MyCriteria <> "True" Then
MyCriteria = MyCriteria & " And " & "[ServiceDate] Between "
&
"#"
&
Me.StartDate & "#" & " And " & "#" & Me.EndDate & "# "

Else
MyCriteria = "[ServiceDate] Between " & "#" & Me.StartDate &
"#"
&
"
And " & "#" & Me.EndDate & "# "
End If

End If

'Check to make sure at least one Category is selected
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
intCount = intCount + 1
End If
Next i
If intCount >= 1 Then ' Selected at least one

'Built string to hold student id's
strCategories = "IN("
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
strCategories = strCategories & "'" &
List0.Column(0,
i)
& "'" & ","
End If
Next i

If MyCriteria = "TRUE" Then
MyCriteria = "[ServiceCategory] " & Left(strCategories,
Len(strCategories) - 1) & ")"
Else
MyCriteria = MyCriteria & " And [ServiceCategory] " &
Left(strCategories, Len(strCategories) - 1) & ")"
End If
End If







MyRecordSource = MySQL & MyCriteria
Me.Criteria = MyRecordSource
If Me.Frame28 = 1 Then 'Detail Report Based On Selected
Criteria
DoCmd.OpenReport "rptServiceRequest", acViewPreview
ElseIf Me.Frame28 = 2 Then 'Summary Report Based On Selected
Criteria, Need to Select Grouping
Select Case Me.Frame36
Case 1 'Grouped by Service Category
DoCmd.OpenReport "rptServiceSummaryByCategory",
acViewPreview
Case 2 'Grouped by Service Provider
DoCmd.OpenReport "rptServiceSummaryByProvider",
acViewPreview
Case 3 'Grouped by CDC Location
DoCmd.OpenReport "rptServiceSummaryByCDC",
acViewPreview
End Select
End If

DoCmd.Close acForm, Me.Name
Forms![frmReportSwitchboard].Visible = False



End Sub
Click to expand...
Click to expand...
Click to expand...
 
What makes sense to me is to remove the wildcard. Can I just erase the
& Chr(42)
Thanks for all your help Ken

Ken Snell said:
The AddToWhere subroutine is creating a criterion string that looks like
this:

[tCompany].[CompanyID] Like 'V332*'

The above use of Like and the inclusion of the "*" wildcard is what is
giving you the extra results. There are different ways to fix this.

You could add an argument to the subroutine that you'd specify with one
value when you want the wildcard search and with another value when you want
an exact search.

Or you could use a different subroutine that generates an exact search
criterion string instead of a wild card search, and call that subroutine for
your CompanyID part.

Or you could just change the maiin code to not call the subroutine at all
and just build the string in the main code.

Which do you prefer?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



ASchaum said:
Thanks.
Here's that code:
Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As
String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) &
FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub

Ken Snell said:
Go the line of code where you see AddToWhere, click on the middle of that
word, and press Shift+F2. You'll be taken the module where that
subroutine
is located. Copy and paste the code here.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Sorry, I'm not quite sure what you mean. The code that I attached was
from
the button that generates the report. I don't see where to find code
for
the
subroutine.

I don't know if this helps, but if I look at the On Open code for one
of
the
reports that is generated through this form, it says:

Private Sub Report_Open(Cancel As Integer)
If IsLoaded("frmServiceReportOptions") Then
Me.RecordSource = Forms![frmServiceReportOptions].Criteria
End If
End Sub


:

You need to show us the code for the subroutine AddToWhere, which is
being
called by your button's code. Likely that subroutine is building a
criterion
that uses Like "YourString*" instead of = "YourString".

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a database that was designed for my non-profit a few years ago
and I
just discovered that one of the reports doesn't limit the records
being
pulled correctly. The designer created a form with assorted options
that
then pulls a report based on those options. The field posing the
problem
is
the CompanyID (my primary key for my company table) - entries for 3
records
(V332, V3320 and V3328) are appearing on a report when I only want
the
V332
entry.
How can I limit the string to exactly what is selected on the form?
Below is the code from the button that pulls the report:

Private Sub Command13_Click()

Dim MySQL As String, MyCriteria As String, MyRecordSource As
String
Dim StrSQL As String, strRestrict As String
Dim lngX As Long
Dim ArgCount As Integer
Dim Tmp As Variant
Dim i As Integer
Dim strCategories As String
Dim intCount As Integer

MySQL = "SELECT tService.ServiceID, tService.CompanyID,
tService.ServiceDate, tService.Staff, tService.Why,
tService.ServiceCategory,
tService.ServiceDesc, tService.Priority, tService.ServiceProvider,
tService.Status, tService.NextStep, tService.CallBackDate,
tService.DateServiceCompleted, tService.PotentialServiceCategory,
tService.Referal, tService.[Service Provided], tService.Completed,
tService.ServiceDueDate, tService.TimeInvestment,
tService.ProgramArea,
tCompany.CompanyName, tCompany.LDCLoc " _
& "FROM tService INNER JOIN tCompany ON tService.CompanyID =
tCompany.CompanyID " _
& "WHERE "

'Build Criteria
AddToWhere [CDCArea], "[LDCLoc]", MyCriteria, ArgCount
AddToWhere [Staff], "[ServiceProvider]", MyCriteria, ArgCount
AddToWhere [Status], "[Completed]", MyCriteria, ArgCount
AddToWhere [CompanyName], "[tCompany].[CompanyID]", MyCriteria,
ArgCount


If MyCriteria = "" Then
MyCriteria = "True"
End If

'Build Date Range
If Me.StartDate <> "" And Me.EndDate <> "" Then
If MyCriteria <> "True" Then
MyCriteria = MyCriteria & " And " & "[ServiceDate] Between "
&
"#"
&
Me.StartDate & "#" & " And " & "#" & Me.EndDate & "# "

Else
MyCriteria = "[ServiceDate] Between " & "#" & Me.StartDate &
"#"
&
"
And " & "#" & Me.EndDate & "# "
End If

End If

'Check to make sure at least one Category is selected
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
intCount = intCount + 1
End If
Next i
If intCount >= 1 Then ' Selected at least one

'Built string to hold student id's
strCategories = "IN("
For i = 0 To List0.ListCount - 1
If List0.Selected(i) Then
strCategories = strCategories & "'" &
List0.Column(0,
i)
& "'" & ","
End If
Next i

If MyCriteria = "TRUE" Then
MyCriteria = "[ServiceCategory] " & Left(strCategories,
Len(strCategories) - 1) & ")"
Else
MyCriteria = MyCriteria & " And [ServiceCategory] " &
Left(strCategories, Len(strCategories) - 1) & ")"
End If
End If







MyRecordSource = MySQL & MyCriteria
Me.Criteria = MyRecordSource
If Me.Frame28 = 1 Then 'Detail Report Based On Selected
Criteria
DoCmd.OpenReport "rptServiceRequest", acViewPreview
ElseIf Me.Frame28 = 2 Then 'Summary Report Based On Selected
Criteria, Need to Select Grouping
Select Case Me.Frame36
Case 1 'Grouped by Service Category
DoCmd.OpenReport "rptServiceSummaryByCategory",
acViewPreview
Case 2 'Grouped by Service Provider
DoCmd.OpenReport "rptServiceSummaryByProvider",
acViewPreview
Case 3 'Grouped by CDC Location
DoCmd.OpenReport "rptServiceSummaryByCDC",
acViewPreview
End Select
End If

DoCmd.Close acForm, Me.Name
Forms![frmReportSwitchboard].Visible = False



End Sub
Click to expand...
Click to expand...
Click to expand...
 
Yes, you can just remove the wildcard from the WHERE clause string. Then the
Like will work the same as an = comparison, except that = is faster than
Like.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


ASchaum said:
What makes sense to me is to remove the wildcard. Can I just erase the
& Chr(42)
Thanks for all your help Ken

Ken Snell said:
The AddToWhere subroutine is creating a criterion string that looks like
this:

[tCompany].[CompanyID] Like 'V332*'

The above use of Like and the inclusion of the "*" wildcard is what is
giving you the extra results. There are different ways to fix this.

You could add an argument to the subroutine that you'd specify with one
value when you want the wildcard search and with another value when you
want
an exact search.

Or you could use a different subroutine that generates an exact search
criterion string instead of a wild card search, and call that subroutine
for
your CompanyID part.

Or you could just change the maiin code to not call the subroutine at all
and just build the string in the main code.

Which do you prefer?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



ASchaum said:
Thanks.
Here's that code:
Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria
As
String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) &
FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top