Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54

    Unanswered: Parameters in Access Using LIKE

    Can anyone help,

    Im trying run a parameter query with a like in the SQL but i keep getting no records.

    This is the sql --

    SELECT propfixedLimitedG1Rentable.propref, propfixedLimitedG1Rentable.houseno, propfixedLimitedG1Rentable.proadd1, propfixedLimitedG1Rentable.proadd2, propfixedLimitedG1Rentable.propstcde
    FROM propfixedLimitedG1Rentable
    WHERE (((propfixedLimitedG1Rentable.proadd1) Like [Please enter all/part of the road name] & "*"));


    Can anyone help me please.

  2. #2
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Any sample data from the table? I have tried your sql, seems work fine with my data.
    Cheers,
    Shev

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your prompt suggests that the user can enter userText which is any substring of the target.

    your code as posted will only find userText*, if you want to find *userText* you have to say so.

    Like "*" & [Please enter all/part of the road name] & "*"

    izy

  4. #4
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54

    TRIED SQL BUT STILL DOES NOT WORK

    I have tried your sql but i still get no records found. I am just wondering if there are any setting within ms access that need to be set before parameters will work.

    This works fine :-

    SELECT propfixedLimitedG1Rentable.propref, propfixedLimitedG1Rentable.houseno, propfixedLimitedG1Rentable.proadd1, propfixedLimitedG1Rentable.proadd2, propfixedLimitedG1Rentable.propstcde
    FROM propfixedLimitedG1Rentable
    WHERE (((propfixedLimitedG1Rentable.proadd1)=[Please enter the road name]));

    But as soon as i change the = to LIKE it keeps failing. Any suggestions.

    As for sample data. all fields are data type TEXT.

    Thanks in advance all.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my way of doing what you are trying to do is to use a form with a text box for the user to enter the substring

    then

    WHERE (((propfixedLimitedG1Rentable.proadd1) LIKE "*" & [Forms]![theForm]![theTextBox] & "*"));

    works just fine.

    i don't use parameter queries (i don't like the crude prompt) so i cannot comment on whether it should or should not work with parameters.

    izy

  6. #6
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    This seems to be right, now how can i get the information that is returned from the query into a subform?

  7. #7
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    ok here we go, there seems to be a problem with the like statement.

    i have doen what you said, placed the paramenter in a text box in a form. here is the sql.....

    SELECT [propfixedLimitedG1Rentable].[propref], [propfixedLimitedG1Rentable].[houseno], [propfixedLimitedG1Rentable].[proadd1], [propfixedLimitedG1Rentable].[proadd2], [propfixedLimitedG1Rentable].[propstcde]
    FROM propfixedLimitedG1Rentable
    WHERE ((([propfixedLimitedG1Rentable].[proadd1])=[Forms]![SelectPropertiesForPropertyList(SPFPL)]![txtSPFPLRoadName]));

    this works fine as long as i type in the exact name of the road.

    however users may not now the exact name and so i want the user to be abale to type in the first few letters and results that match to be returned. so when i put the like statement as follows ----

    SELECT [propfixedLimitedG1Rentable].[propref], [propfixedLimitedG1Rentable].[houseno], [propfixedLimitedG1Rentable].[proadd1], [propfixedLimitedG1Rentable].[proadd2], [propfixedLimitedG1Rentable].[propstcde]
    FROM propfixedLimitedG1Rentable
    WHERE ((([propfixedLimitedG1Rentable].[proadd1]) like "*" & [Forms]![SelectPropertiesForPropertyList(SPFPL)]![txtSPFPLRoadName] & "*"));


    no results are show.

    must be the like but where?

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i don't see anything wrong with your query here one i've used that works fine

    SELECT AccidentList.*
    FROM AccidentList
    WHERE (((Day([Date]) & "/" & Month([Date]) & "/" & Year([date])) Like "*" & [Accident Date? (d/m/yyyy) leave blank for all] & "*") AND ((AccidentList.State)<>3))
    ORDER BY AccidentList.Date;

    this allowed the user to do "/12/2002" to get all decembers 2002's accidents
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    By the way the table [propfixedLimitedG1Rentable] is a linked table to an ingres database, would this still work?

Posting Permissions

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