Results 1 to 7 of 7
  1. #1
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39

    Custom function to check if text is in field string and return records where true

    So I'm confused by this and I'm probably doing something stupid but I'm stumped. I have a custom function:



    Code:
    Public Function chemid_infield(SampleID As Integer, chkfield As String) As Boolean
    Dim chkstring As String, fieldstr As String
    
    
    chkstring = "r." & CStr(SampleID)
    
    
    If InStr(1, CStr(chkfield), chkstring) Then
     chemid_infield = True
    Else
     chemid_infield = False
    End If
    
    
    
    
    End Function
    which just checks if a string is in another string and returns true if so and I want to use this as a criteria to return only specific values. I run the query and it looks like it is working, I'm not getting errors, the function goes through each record and returns true when it comes upon a string that has the search string in it. But the resulting table is empty.

    Here is the sql query:

    Code:
    SELECT Plates.PlateID, Plates.Reagents FROM Plates
    WHERE ((chemid_infield(12,[Plates].[Reagents])="True"));

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
    Public Function chemid_infield(SampleID As Integer, chkfield As String) As Boolean
    Dim chkstring As String, fieldstr As String
    Code:
    
    chkstring = "r." & CStr(SampleID)
    chemid_infield =InStr(chkfield, chkstring)>0 
    End Function
    



  3. #3
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Thanks for responding but it's still not returning any records despite the function correctly returning true

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Ranman provided a shorthand version of the function.
    The query should be:
    Code:
    SELECT Plates.PlateID, Plates.Reagents FROM Plates WHERE chemid_infield(12,[Plates].[Reagents])=True;

  5. #5
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Ok figured it out. In my sql qry true should not have been in quotes

  6. #6
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Quote Originally Posted by davegri View Post
    Ranman provided a shorthand version of the function.
    The query should be:
    Code:
    SELECT Plates.PlateID, Plates.Reagents FROM Plates WHERE chemid_infield(12,[Plates].[Reagents])=True;
    Thanks davegri, I figured it out just before I saw your post!

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    High five!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 02-24-2016, 10:58 PM
  2. Replies: 4
    Last Post: 02-03-2014, 12:16 PM
  3. Replies: 6
    Last Post: 01-21-2014, 06:39 PM
  4. Trouble with string variable in custom vba function
    By CurrentUser in forum Programming
    Replies: 4
    Last Post: 01-10-2014, 09:13 PM
  5. Replies: 3
    Last Post: 11-22-2012, 08:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums