Help building criteria for a long integer in VBA to be used in a form (1 Viewer)

Finance

Registered User.
Local time
Today, 04:56
Joined
Jul 11, 2018
Messages
59
This is the code
Private Sub Command2_Click()
Dim strCountry As String
Dim strFilter As String
Dim strYears as Long
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Patent_Cost_Forecast") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Country field
If IsNull(Me.ComboCountry.Value) Then
strCountry = "Like '*'"
Else
strCountry = "='" & Me.ComboCountry.Value & "'"
End If
' Build criteria string for years field
If IsNull(Me.ComboYears.Value) Then
strYears = "Like '*'"
Else
strYears = "='" & Me.ComboYears.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Country] " & strCountry & "AND [Years]" & strYears
' Apply the filter and switch it on
With Reports![Patent_Cost_Forecast]
.Filter = strFilter
.FilterOn = True
End With
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:56
Joined
Aug 30, 2003
Messages
36,118
Is there a question? A long integer wouldn't have the single quote delimiters.
 

Finance

Registered User.
Local time
Today, 04:56
Joined
Jul 11, 2018
Messages
59
Yes, I need help in writing the code for the criteria.
And I am having problems sorting the report using a list box. What changes need to be made in the following code?
Private Sub Command2_Click()
Dim strCountry As String
Dim strFilter As String
Dim varItem As Variant
Dim strYears As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Patent_Cost_Forecast") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Country field
If IsNull(Me.ComboCountry.Value) Then
strCountry = "Like '*'"
Else
strCountry = "='" & Me.ComboCountry.Value & "'"
End If
'Build criteria string from lstOffice listbox
For Each varItem In Me.ListYears.ItemsSelected
strYears = strYears & ",'" & Me.ListYears.ItemData(varItem) _
& "'"
Next varItem
If Len(strYears) = 0 Then
strYears = "Like '*'"
Else
strYears = Right(strYears, Len(strYears) - 1)
strYears = "IN(" & strYears & ")"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Country] " & strCountry & " AND [ Years_Word ] " & strYears
' Apply the filter and switch it on
With Reports![Patent_Cost_Forecast]
.Filter = strFilter
.FilterOn = True
End With
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:56
Joined
Feb 19, 2002
Messages
42,970
Given the way you are building the string, there is no reason to use LIKE when a variable is empty. Just omit the variable. This means you need to change the way you build the filter so that each individual part includes the field name rather than concatenating the field names in the last step.

Like is very inefficient. Use it when you really have to because you are searching for a partial text string but do not use it when you want all values or one specific value.

If the filter isn't working, print strFilter to the debug window and paste it here if you can't figure out the problem.

FYI - LIKE is a STRING operation and so it is never used with dates or numeric values.
 

isladogs

MVP / VIP
Local time
Today, 11:56
Joined
Jan 14, 2017
Messages
18,186
Your code in post 3 has several changes compared to post 1.
Firstly, your strYears variable was a Long but now it's a string.
Next, you've changed the Else part of the part in bold type from post 1

So which code are you currently using so we know which to comment upon.
Also, please can you use code tags in future as this case code more readable as well as preventing random spaces being added
To do so, click the # button above your message, then paste your code between the [ CODE ] and [ / CODE ] tags that appear.
 

Users who are viewing this thread

Top Bottom