Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002

    Lightbulb Unanswered: Ad Hoc Query issue


    is there anyone brave enough to help on this one...

    Ive built a form which looks just like regular data input screen with unbound
    textboxes, checkboxes, etc... this screen, however, is used for adhoc searching which constructs the following SQL string (depending on text box being filled or checkbox ticked) when no criteria are entered.

    the isssue is that although I've got fair few records in the "dbo_tblCompany" table, no records are returned as a result of executing the query below...
    (I was expecting all records to be returned in this case)

    Is there anyone that can tell me what is going on, and why am I not able to retrieve any records?



    ############ MS Access Query ################

    SELECT dbo_tblCompany.*
    FROM dbo_tblCompany
    WHERE dbo_tblCompany.strCompanyName Like '*' AND dbo_tblCompany.strPhone Like '*' AND dbo_tblCompany.strContactEmail1 Like '*' AND dbo_tblCompany.strContactPhone2 Like '*' AND dbo_tblCompany.strContactEmail2 Like '*' AND dbo_tblCompany.strContactPhone3 Like '*' AND dbo_tblCompany.strContactEmail3 Like '*' AND dbo_tblCompany.strAddress1 Like '*' AND dbo_tblCompany.strAddress2 Like '*' AND dbo_tblCompany.strAddress3 Like '*' AND dbo_tblCompany.strPostalCode Like '*' AND dbo_tblCompany.strFAX Like '*' AND dbo_tblCompany.FlCost Like '*' AND dbo_tblCompany.BlContract Like '*' AND dbo_tblCompany.strTerms Like '*' AND dbo_tblCompany.strPurchaseRecord Like '*' AND dbo_tblCompany.strWebURL Like '*' AND dbo_tblCompany.DtExpieryDate Like '*' AND dbo_tblCompany.strRecordCard Like '*' AND dbo_tblCompany.strSizeBleed Like '*' AND dbo_tblCompany.strSizeTrim Like '*' AND dbo_tblCompany.strSizeType Like '*' AND dbo_tblCompany.strDeadlineBooking Like '*' AND dbo_tblCompany.strDeadlineCopy Like '*' AND dbo_tblCompany.dtLastUsed Like '*' AND dbo_tblCompany.strMT_Website Like '*' AND dbo_tblCompany.strMT_Print Like '*' AND dbo_tblCompany.strMT_Mailshots Like '*' AND dbo_tblCompany.strMT_Suppliers Like '*' AND dbo_tblCompany.strMT_Other Like '*' AND dbo_tblCompany.strDM_ACA Like '*' AND dbo_tblCompany.strDM_ACCA Like '*' AND dbo_tblCompany.strDM_CIMA Like '*' AND dbo_tblCompany.strDM_CIPFA Like '*' AND dbo_tblCompany.strDM_CISA Like '*' AND dbo_tblCompany.strDM_AAT Like '*' AND dbo_tblCompany.strDM_IIA Like '*' AND dbo_tblCompany.strDM_CIOT Like '*' AND dbo_tblCompany.strDM_ATT Like '*' AND dbo_tblCompany.strDM_ACMT Like '*' AND dbo_tblCompany.strDM_ACT Like '*' AND dbo_tblCompany.strDM_QICA Like '*' AND dbo_tblCompany.strDM_MBA Like '*' AND dbo_tblCompany.strDM_CPA Like '*' AND dbo_tblCompany.strDM_ALL Like '*' AND dbo_tblCompany.strCD_PartQal Like '*' AND dbo_tblCompany.strCD_Qual Like '*' AND dbo_tblCompany.strCD_Junior Like '*' AND dbo_tblCompany.strCD_Senior Like '*' AND dbo_tblCompany.strCD_Languages Like '*' AND dbo_tblCompany.strCD_Temp Like '*' AND dbo_tblCompany.strCD_Perm Like '*' AND dbo_tblCompany.strCD_Interim Like '*' AND dbo_tblCompany.strCD_Systems Like '*' AND dbo_tblCompany.strCD_ALL Like '*' AND dbo_tblCompany.strTM_Banking Like '*' AND dbo_tblCompany.strTM_Commerce Like '*' AND dbo_tblCompany.strTM_PublicSector Like '*' AND dbo_tblCompany.strTM_NHS Like '*' AND dbo_tblCompany.strTM_CentralGvt Like '*' AND dbo_tblCompany.strTM_HR Like '*' AND dbo_tblCompany.strTM_InvestmentMgmt Like '*' AND dbo_tblCompany.strTM_PublicPractice Like '*' AND dbo_tblCompany.strTM_MCS Like '*' AND dbo_tblCompany.strTM_Charities Like '*' AND dbo_tblCompany.strTM_LocalGvt Like '*' AND dbo_tblCompany.strTM_Marketing Like '*' AND dbo_tblCompany.strTM_Exec Like '*' AND dbo_tblCompany.strTM_Tax Like '*' AND dbo_tblCompany.strTM_Housing Like '*' AND dbo_tblCompany.strTM_ArtsHeritage Like '*' AND dbo_tblCompany.strTM_ALL Like '*' AND dbo_tblCompany.strFP_Daily Like '*' AND dbo_tblCompany.strFP_Weekly Like '*' AND dbo_tblCompany.strFP_Fortnightly Like '*' AND dbo_tblCompany.strFP_Monthly Like '*' AND dbo_tblCompany.strFP_Quaterly Like '*' AND dbo_tblCompany.strFP_Supplement Like '*' AND dbo_tblCompany.strFP_Other Like '*'
    ORDER BY dbo_tblCompany.strCompanyName;

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if its an ad hoc query generator you are developing, where a users can select some or all, or none of the parameters then I 've always found in the past it better to filter out the dreck prior to writing the SQL

    frankly your SQL is too long for me to look at, I'd suspect either its malformed, or it could just be that you have overwhelmed the SQL parser and its lost up its own backside. Its possible that the SQL engine may be binning rows containing NULL values, so unless you explicitly don't want NULL values I'd ignore the sentance - most users are fairly flaky one the difference between NULL & non NULL values.

    In my view there is no point in saying where <column> like "*", as effectively its meaningless you will get any row in any event.

    so i'd test to see if the user has specified something before writing up the SQL
    if len(tbUserID)>0 'has use put anythin in this criteria
    if len(strWhere)>0 then 'have we got something in the where
    strwhere=strWhere & " AND " ' if so add an AND clause
    'finally add the criteria for this column
    strwhere=strwhere & 'tblUserData.EmailID like '"'' & tbUserID & '*"'
    then repeat as many times as required for each column

    you could expand your intiial form to include the type of match

    eg your users may want to search for several names in one hit, eg show all company names ending in LLc, PLC, plc, Ltd, or show me all users whose addrress includes xx, or yy, or zz (say if you wanted to search by state, country, county)

  3. #3
    Join Date
    Dec 2002
    Préverenges, Switzerland
    i'm not a fanatic supporter of that style of query. it's a waste of space and concatenation time filling the SQL with a bunch of Like*
    it's also tiring to read and debug.

    there's a simple demo of another way attached somewhere in this

    Like* stuff and the excess-AND issue are eliminated with the 1=1
    only real user-entered criteria need to be ANDed in.

    currently using SS 2008R2

  4. #4
    Join Date
    Jul 2002

    Thumbs up Thanks Guys your suggestions worked a treat !!

    Thanks Guys your suggestions worked a treat !!

Posting Permissions

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