Test if value is in any records of a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to make a public function using vba to test if a value is located
anywhere in a fields records. For example

FieldTest
A
B
C
D

If A is in FieldTest(and this checks all records)
Then something
Else something else
End if.

Thanks
 
Public Sub ValExists(ByVal pValue As String)
If DCount("FieldTest", "MyTable", "[FieldTest] = '" & pValue & "'") Then
DoSomething
Else
DoSomethingElse
End If
End Sub

Barry
 
Thanks for the fast response.
What if ComplianceIndexRange1 was a field in Query Tire? How would that work?
I am obviously new to access but I'm learning.

Public Function XStopCompliant(ByVal QFractComplianceIndex As String)
If DCount("ComplianceIndexRange1", "Tire", "[ComplianceIndexRange1] = '" &
QFractComplianceIndex & "'") Then
XStopCompliant = "No"
Else
XStopCompliant = "Yes"
End If
End Function

Barry Gilbert said:
Public Sub ValExists(ByVal pValue As String)
If DCount("FieldTest", "MyTable", "[FieldTest] = '" & pValue & "'") Then
DoSomething
Else
DoSomethingElse
End If
End Sub

Barry

JJJ said:
I want to make a public function using vba to test if a value is located
anywhere in a fields records. For example

FieldTest
A
B
C
D

If A is in FieldTest(and this checks all records)
Then something
Else something else
End if.

Thanks
Click to expand...
Click to expand...
 
That should work as long as the query has no other criteria specified. The
DCount function (and others like DLookup, DSum, etc.) don't care if it's
looking at a table or a query.

Barry

JJJ said:
Thanks for the fast response.
What if ComplianceIndexRange1 was a field in Query Tire? How would that work?
I am obviously new to access but I'm learning.

Public Function XStopCompliant(ByVal QFractComplianceIndex As String)
If DCount("ComplianceIndexRange1", "Tire", "[ComplianceIndexRange1] = '" &
QFractComplianceIndex & "'") Then
XStopCompliant = "No"
Else
XStopCompliant = "Yes"
End If
End Function

Barry Gilbert said:
Public Sub ValExists(ByVal pValue As String)
If DCount("FieldTest", "MyTable", "[FieldTest] = '" & pValue & "'") Then
DoSomething
Else
DoSomethingElse
End If
End Sub

Barry

JJJ said:
I want to make a public function using vba to test if a value is located
anywhere in a fields records. For example

FieldTest
A
B
C
D

If A is in FieldTest(and this checks all records)
Then something
Else something else
End if.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
It works! Thank you. I was wondering if there would be any way to make the
search more efficient as it looks 35,211 times through 36,906 records.

Thanks

Barry Gilbert said:
That should work as long as the query has no other criteria specified. The
DCount function (and others like DLookup, DSum, etc.) don't care if it's
looking at a table or a query.

Barry

JJJ said:
Thanks for the fast response.
What if ComplianceIndexRange1 was a field in Query Tire? How would that work?
I am obviously new to access but I'm learning.

Public Function XStopCompliant(ByVal QFractComplianceIndex As String)
If DCount("ComplianceIndexRange1", "Tire", "[ComplianceIndexRange1] = '" &
QFractComplianceIndex & "'") Then
XStopCompliant = "No"
Else
XStopCompliant = "Yes"
End If
End Function

Barry Gilbert said:
Public Sub ValExists(ByVal pValue As String)
If DCount("FieldTest", "MyTable", "[FieldTest] = '" & pValue & "'") Then
DoSomething
Else
DoSomethingElse
End If
End Sub

Barry

:

I want to make a public function using vba to test if a value is located
anywhere in a fields records. For example

FieldTest
A
B
C
D

If A is in FieldTest(and this checks all records)
Then something
Else something else
End if.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Are you calling this function from within another query? If so, yes, it will
be slow. If this is the case, you're better off doing your evaluation using a
joined table in the query.

If you're just calling the DCount from another subroutine, you could
probably speed it up by adding an index on the ComplianceIndexRange1 field.

Barry

JJJ said:
It works! Thank you. I was wondering if there would be any way to make the
search more efficient as it looks 35,211 times through 36,906 records.

Thanks

Barry Gilbert said:
That should work as long as the query has no other criteria specified. The
DCount function (and others like DLookup, DSum, etc.) don't care if it's
looking at a table or a query.

Barry

JJJ said:
Thanks for the fast response.
What if ComplianceIndexRange1 was a field in Query Tire? How would that work?
I am obviously new to access but I'm learning.

Public Function XStopCompliant(ByVal QFractComplianceIndex As String)
If DCount("ComplianceIndexRange1", "Tire", "[ComplianceIndexRange1] = '" &
QFractComplianceIndex & "'") Then
XStopCompliant = "No"
Else
XStopCompliant = "Yes"
End If
End Function

:

Public Sub ValExists(ByVal pValue As String)
If DCount("FieldTest", "MyTable", "[FieldTest] = '" & pValue & "'") Then
DoSomething
Else
DoSomethingElse
End If
End Sub

Barry

:

I want to make a public function using vba to test if a value is located
anywhere in a fields records. For example

FieldTest
A
B
C
D

If A is in FieldTest(and this checks all records)
Then something
Else something else
End if.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top