Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44

    Unanswered: Can someone tell me what I did wrong.

    The function below is a search enging that will control 6 different search screans. I have the values of the current search passed to the function to have this work. The code runs up until it hits the Where clause = statements. I need each search to name the filed names that correspont to the search at hand. But it doesn't work. Please just tell me how to fix what I already have then when I get it working I can explore alternatives. This one function will replace SIX when I get it to work.

    Thanks in Advance
    Kevin

    Code:
    Function Findit4me(ByVal FC As String, ByVal FldV1 As String, ByVal FldV2 As String, ByVal FldV3 As String, ByVal FldV4 As String, ByVal RTC As String)
    On Error GoTo FinditError
    
    'FIeld Names
    Dim FN1 As Field, FN2 As Field, FN3 As Field, Fn4 As Field
    
    'Openform Criterion Fields
    Dim Frm2Open As String, WhClause As String
    
    'Numeric Values
    Dim NumFlds As Single, BinVal As Single
    
    Select Case FC
        Case "HD"
            Frm2Open = "Hospital_Directory_Frm"
            NumFlds = 3
            FN1 = "[NOP]"
            FN2 = "[DN]"
            FN3 = "[Itel]"
            WhClause = Empty
            BinVal = 0
        Case "PD"
            Frm2Open = "Physician_Database_Frm"
            NumFlds = 4
            FN1 = "[MDName]"
            FN2 = "[Section]"
            FN3 = "[City]"
            Fn4 = "[Name]"
            WhClause = Empty
            BinVal = 0
        Case "MH"
            Frm2Open = "Maine_Hospitals_Frm"
            NumFlds = 3
            FN1 = "[Hosp_Abbr]"
            FN2 = "[Hospital Name]"
            FN3 = "[Hospital City]"
            WhClause = Empty
            BinVal = 0
        Case "LF"
            Frm2Open = "Lost_and_Found_Frm"
            NumFlds = 2
            FN1 = "[Item Type]"
            FN2 = "[Item Description]"
            WhClause = Empty
            BinVal = 0
        Case "FN"
            Frm2Open = "Fax_Listings_Frm"
            NumFlds = 4
            FN1 = "[Department]"
            FN2 = "[Specialty]"
            FN3 = "[Office Name]"
            Fn4 = "[Location Address]"
            WhClause = Empty
            BinVal = 0
        Case "BN"
            Frm2Open = "Pager_Directory_Frm"
            NumFlds = 3
            FN1 = "[Name]"
            FN2 = "[Position]"
            FN3 = "[Department]"
            WhClause = Empty
            BinVal = 0
        Case Else
            MsgBox "Invalid Function Call" & vbCrLf & vbCrLf & "The Form Code is incorrect", vbInformation, "Fix the form code"
        End Select
        
        If FldV1 = Empty Then BinVal = BinVal + 0 Else BinVal = BinVal + 1
        If FldV2 = Empty Then BinVal = BinVal + 0 Else BinVal = BinVal + 10
        If FldV3 = Empty Then BinVal = BinVal + 0 Else BinVal = BinVal + 100
        If FldV4 = Empty Then BinVal = BinVal + 0 Else BinVal = BinVal + 1000
        
        Select Case BinVal
            Case 0
                MsgBox "I'm Sorry there is no information entered on the form to search for." & vbCrLf & " Please enter somthing before pressing the Find it! Button." & vbCrLf & "Click OK to try again", vbInformation, "No Information Entered, All Fields are Blank!"
            Case 1
                WhClause = "[FN1] Like '" & "*" & FldV1 & "*" & "'"
            Case 10
                WhClause = "[FN2] Like '" & "*" & FldV2 & "*" & "'"
            Case 11
                WhClause = ("[FN1] Like '" & "*" & FldV1 & "*" & "' AND [FN2] Like '" & "*" & FldV2 & "*" & "'")
            Case 100
                WhClause = "[FN3] Like '" & "*" & FldV3 & "*" & "'"
            Case 101
                WhClause = ("[FN1] Like '" & "*" & FldV1 & "*" & "' AND [FN3] Like '" & "*" & FldV3 & "*" & "'")
            Case 110
                WhClause = ("[FN2] Like '" & "*" & FldV2 & "*" & "' AND [FN3] Like '" & "*" & FldV3 & "*" & "'")
            Case 111
                WhClause = ("[FN1] Like '" & "*" & FldV1 & "*" & "' AND [FN2] Like '" & "*" & FldV2 & "*" & "' AND [FN3] Like '" & "*" & FldV3 & "*" & "'")
            Case 1000
                WhClause = "[FN4] Like '" & "*" & FldV4 & "*" & "'"
            Case 1001
                WhClause = ("[FN1] Like '" & "*" & FldV1 & "*" & "' AND [FN4] Like '" & "*" & FldV4 & "*" & "'")
            Case 1010
                WhClause = ("[FN2] Like '" & "*" & FldV2 & "*" & "' And [FN4] Like '" & "*" & FldV4 & "*" & "'")
            Case 1011
                WhClause = ("[FN1] Like '" & "*" & FldV1 & "*" & "' AND [FN2] Like '" & "*" & FldV2 & "*" & "' AND [FN4] Like '" & "*" & FldV4 & "*" & "'")
            Case 1100
                WhClause = ("[FN3] Like '" & "*" & FldV3 & "*" & "' AND [FN4] Like '" & "*" & FldV4 & "*" & "'")
            Case 1101
                WhClause = ("[FN1] Like '" & "*" & FldV1 & "*" & "' AND [FN3] Like '" & "*" & FldV3 & "*" & "' AND [FN4] Like '" & "*" & FldV4 & "*" & "'")
            Case 1110
                WhClause = ("[FN2] Line '" & "*" & FldV2 & "*" & "' AND [FN3] Like '" & "*" & FldV3 & "*" & "' AND [FN4] Like '" & "*" & FldV4 & "*" & "'")
            Case 1111
                WhClause = ("[FN1] Like '" & "*" & FldV1 & "*" & "' AND [FN2] Like '" & "*" & FldV2 & "*" & "' AND [FN3] Like '" & "*" & FldV3 & "*" & "' AND [FN4] Like '" & "*" & FldV4 & "*" & "'")
            Case Else
                MsgBox "Something Went wrong, Check the Programming", vbInformation, "Error in Processing"
                DoCmd.GoToControl RTC
                'GoTo Findit4meExit
            End Select
            
            DoCmd.GoToControl RTC
            
            If WhClause = Empty Then
                GoSub EmptyAll
                Exit Function
            Else
                DoCmd.OpenForm Frm2Open, acNormal, , WhClause, acFormEdit, acWindowNormal
                GoSub EmptyAll
                Exit Function
            End If
        
    EmptyAll:
        FldV1 = Empty
        FldV2 = Empty
        FldV3 = Empty
        FldV4 = Empty
        BinVal = 0
        WhClause = Empty
    Return
    
    Findit4meExit:
        Exit Function
    
    FinditError:
        MsgBox Err.Number & Err.Descrition, vbMsgBoxHelpButton, "Something Bad Happened -- Fix ME!"
        Resume Findit4meExit
        
    End Function
    Last edited by Kmcarman72; 12-07-04 at 19:38.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi


    If I have read it correctly then I think you should Dimension FN1 etc as String variables and change the WhClause concatenations to suit.

    I think it should be something like:-

    Case 1
    WhClause = FN1 & " Like '*" & FldV1 & "*'"

    etc., etc.

    You have defined FN1 as =”[Name]” etc .,assigning the square brackets in the variable, there for you do not need than in the string!

    This is also simpler as I have removed unnecessary concatenations

    Of course there could be some else wrong, there usually is (at least in my case).


    Hope this helps.

    MTB

  3. #3
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44

    Can someone tell me what I did wrong.

    That would be OK for the single variable options (1, 10, 100, 1000) what about how the Quotes and ampersands(&) go in the multi variables are my assumptions right on these as well or did I do those wrong too?

    I originally Dim FN1 - FN4 as strings but I think the error I got (I am not at the same computer now) was Variable not defined or something. I will try again.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Where clause with field name as (string) variables

    Hi again

    OK for the multivariable example I have used Case 1111 (the longest!) withnexample FN assignments

    Code:
        Dim FN1 As String
        Dim FN2 As String
        Dim FN3 As String
        Dim FN4 As String
        
        'EXAMPLE FN ASSIGNMENTS
        FN1 = "[Department]"
        FN2 = "[Specialty]"
        FN3 = "[Office Name]"
        FN4 = "[Location Address]"
        
        
        Select Case BinVal
        
        
            Case 1111
            WhClause = FN1 & " Like '" & FldV1 & "*' AND " & FN2 & " Like '*" & FldV2 & "*' AND " & FN3 & " Like '*" & FldV3 & "*' AND " & FN4 & " Like '*" & FldV4 & "*'"
            
        End Select

    Hope this is a little clearer.

    MTB

  5. #5
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44
    Thans for the info, however the Case 1111 does not work as the 2nd field value '"*" is treated as a comment.

    I got all the single to work per your suggestion but when I try an AND like below:

    WhClause = FN1 & " Like '*" & FldV1 & "*'" And FN2 & " Like '*" & FldV2 & "*'"

    I get a Run-time error 438 -- Object doesn't support this property or method and all I did was copy a FN1 Line place AND then Copy FN2 Line but it doesn't like it.

    It compiles just fine and debugs just fine but when run Error above is shown.

    any suggestions?

    TIA
    Kevin

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi kmcarman72

    Without seeing the whole line of code I cannot be sure why ‘”*” is interpreted as a comment but if it is & ‘”*” & then it will be as it should be & “’*” & ie the apostrophe should be inside the quotation marks.

    As for the ‘Run-time error 438 -- Object doesn't support this property or method ‘, again without seeing the whole string difficult to say. Also, I would need to know what you are using it for WhCriteria for. Are you opening a recordtset, filtering a form or recordset ??. For instance I have had trouble with ADO recordsets Find method with an ‘AND’ in the criteria, but it works OK with DAO’s FindFirst method !!!!

    Sorry I can’t be more helpful.


    MTB

  7. #7
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44

    About the Where Clause

    Quote Originally Posted by MikeTheBike
    Hi kmcarman72

    Without seeing the whole line of code I cannot be sure why ‘”*” is interpreted as a comment but if it is & ‘”*” & then it will be as it should be & “’*” & ie the apostrophe should be inside the quotation marks.

    MTB You shoud have the complete line of code as it is the one you suggested in a previous reply.

    What I have done now is taken the single option and copied it to the various combinations. I think I know now why the 438 message is coming up,
    I copited the single FN1 (See your previous post) as the singles work just fine, and just added the work and then copied the FN2 and so on.

    I think it needs to be

    Whclause = fn1 & "Like & '*" & FldV1 & "*'" & " AND " & FN2...

    I will test this and get back later tonight.

    Thanks for your help.
    Kevin

  8. #8
    Join Date
    Dec 2002
    Location
    Prιverenges, Switzerland
    Posts
    3,740
    try & "Like '*" & FldV1 & "*'" &
    instead of your & "Like & '*" & FldV1 & "*'" & <<-WRONG

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44
    Thanks but MTB was correct, my code did not enclose the AND in quotes and it did not like the AND because I was defining a string. I have since enclosed the Ands in quotes and it worked just fine. Thanks for all the help. I have since done one better, I added checkboxes to allow for ORs instead of ands and it too works it does go logically through the multi ands and ors.

    Thanks again. If you want a correct statement let me know and I will post it.
    Kevin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •