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

    Unanswered: Search Function Question - Can I do This...

    Hello All;

    I currently have a DB search form which has a tab control to ssearch different tables in one convenient place.

    None of the fields on the form are bound to any particular table due to each tab searching a different table.

    The fields are comboboxes set up as a rowsource like:

    Select distinct nop from hospital_directory order by nop

    to search for a persons name in a hospital extension directory

    on each tab is a button marked Find It! which has an underlying function the call is:

    [code =Findit4Me("CHL","chlsearchdenom",[chlsearchdenom],[chlao1],[chlsearchnoc],[chlao2],[chlsearchtown],[chlao3],[chlsearchchid]) /code]

    'this tab only has 3 search fields but some have 4 hence the 2 [""] on the end.

    The function is defined as:

    [code Function Findit4me(ByVal FC As String, ByVal RTC As String, ByVal FldV1 As String, ByVal Opr1 As Boolean, ByVal FldV2 As String, ByVal Opr2 As Boolean, ByVal FldV3 As String, ByVal Opr3 As Boolean, ByVal FldV4 As String) /code]

    the fields in the function are:

    FC - form code -- RTC - Return to control
    fldv1 - fldv4 are the field values -- opr1 - 3 are operands used for AND (unchecked) & OR (checked)

    the function has worked flawlessly for a year and a half, but a few days ago we added 2 new searches to the mix (a clergy list and a churces list).

    Yesterday I built the search and tested the Selects and they all work but when I called the function on the church list which incidentally fldv4 would end up being a NUMBER not a string. I got a Type mismatch error (probably because of the fldv4 being a number.

    what i tryed (and got a function not known error instead, was to surround the [chlsearchchid] (the numeric field) like Text([chlsearchchid]).

    I did a search this morning within this forum and found maybe what I want is:

    cstr[chlsearchchid]) instead.

    I gues that my questions are :

    1 - does this sound right to you? and
    2 - If fldv4 is changed to a string and the field in the table is a number, wouldn't that cause a mismatch also (a string in a number field.

    there could be a way around this I suppose, the way my searches are set up,

    If a fldv has a value a binval is incremented in a binary sort of way, fldv1 increases binval by 1, fldv2 by 10, fldv3 by 100, and fldv4 by 1000 so there are 16 cases and I do use a select case for each binval possible.

    where I know it will be fldv4 in bothe the clergy and church lists I suppose I could add an if FC = "CHL" or FC = "CLL" change fldv4 back into a number. or crate a new fldv4a where in another section fldv4a = cint([fldv4])

    and in the where clauses sub the fldv4 for the 4a.

    Like I said this function has worked for over a year and it works well, but up to now it has always searched for only text.

    tell me what you think.

    I have worked with Access since 98 but only started VBA in 2001 when I started this neverending project (I love it and I know it

    I am a novice-intermediate but I learn alot by doing and failing then coming to this forum for the help of how I screwed it up

    TIA
    Kevin M Carman

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

    stupid me it was never broke

    Upon coming into the desk tonight I did some checking and one of my existing searches has a number already. the way we get the number to process as a string as the Byval fldv4 as string shows is to enclose the chsearchchid field name within the NZ() like:

    =findit4me("CHL", "chsearchdenom", [chsearchdenom], [chlao1], [chsearchnoc], [chlao2], [chsearchtown], [chlao3], Nz([chsearchchid]))

    this way has worked for the past 2 years but I usually dont search the particular table where I found my own answer {stupidity}.

    At first it was the type mismatch (number into a string) then I gota message that a function did not exist (enclosing field name in Text([fiedname]) which was the wrong thing anyway)

    Then I posted my message above to the group after reading backposts suggesting cStr([fieldname]) to convert to text. Upon doing this tonoght, it worked on the first attempt them proptly died on subsequent test.

    Then I found that the funtion was never broken in the first place if I just used NZ([fieldname]) as was on the form I already had. Stupid, Stupid, Stupid!

    But like i said earlier I learn by failing miserably and then asking for the help.

    Kevin M Carman

Posting Permissions

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