Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    20

    Exclamation Unanswered: Trouble with Code

    Hey All,

    Can someone check the code on this database to see what I screwed up? I grabbed a smaple dtabase from the forums b/c it was just what I wanted but I think I did something wrong int he code and can't figure it out.

    The Reset Button (butReset) on the form does what it is suppossed to but the Search button (butSearch) isn't bringing up any results even though I can see there should be some.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my old code come back to haunt me!
    the comments are in the code for a reason - read.

    bottom line is your modified code is generating SQL like
    SELECT * FROM blah WHERE ((1=0) And something)

    your WHERE is never going to happen.
    1=0 is permanently False
    so...
    1=0 And anythingYouCanImagine = False
    ...so you get no hits.

    if you want And: use 1=1
    if you want Or: use 1=0


    izy

    LATER - illustration:
    WHERE 1=0 Or yourTag = 'TCPhd' Or myTag = 'izyrider'
    WHERE 1=1 And yourTag = 'TCPhd' And myTag = 'izyrider'
    ...not the same thing.
    Last edited by izyrider; 12-08-06 at 12:52.
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2004
    Posts
    20

    Grrrr...

    Izy,

    Thanks for the reply! I actually tried that option but with no success so I changed it back thinking I made a mistake. I figure I made another mistake elsewhere in the code.

    Maybe there is an easier way that you know for me to do this. Basically, I want users to be able to make multiple selections on the search form (from different combo boxes) in order to generate a report based on their selections.

    Thanks!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mildly depressed that the code i'm trying to fix is not the 'real' code, but i can live with that.

    the purpose of SearchDemo was to throw up lots of things a user might or might not make a selection from. the code goes through the things and ignores anthing where there is no selection and adds anything selected into the WHERE

    here we go with two combos with numeric bound fields:

    strSQL = "SELECT this, that FROM here WHERE 1=1"
    'at this stage everything is selected
    if not isnull(combo1) then
    strSQL = strSQL & " And someField = " & me.combo1
    endif
    'by now you have ignored(no selection) or included the selection in combo2
    if not isnull(combo2) then
    strSQL = strSQL & " And otherField = " & me.combo2
    endif
    'here you have either ignored(no selection) or included the selection in combo2
    'finish the query:
    strSQL = strSQL & " ORDER BY whatever;"


    where are you getting stuck?
    datatype(string/date/numeric); a missing <space>; or ??

    add:
    debug.print strSQL
    stop


    after
    'finish the query:
    strSQL = strSQL & " ORDER BY whatever;"

    then copy/paste what you see in the immediate window (Ctrl-G) into the forum.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jul 2004
    Posts
    20

    Talking Yahoo!

    Izy,

    I went back to your original code and started over. Turns out I had a semicolon in the wrong spot! Figures! The code is working perfectly now! Thanks!

    A new question though - Do you know of a good way to transfer what comes up in the lstResults based on serach criteria to a report?

    Thanks again!

    --TC

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    one way
    make a query called 'iDontCare' and you dont care what it looks like

    in the same routine that gathers the SQL...
    dim qdef as dao.querydef
    set qdef = currentdb.querydefs("iDontCare")
    qdef.sql = strSQL
    qdef.close
    set qdef = nothing

    hang your report off the query 'iDontCare' and you are done

    izy
    currently using SS 2008R2

Posting Permissions

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