Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Portland Oregon
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query from 97 to 2000 (Access 2000)

    I converted a very simple database from 97 to 2000, most of the items converted just fine as usual. There is a search query however that I cannot get to work in 2000. It allows the user to type in a word and then the query finds all the records with that word in the Description Field. Example: She wants to look up what kinds of cups she has, so in 97 all she had to enter was the word "cup" (no quotes of course) and it would show just the records with the word Cup listed in that field.

    After converting the database to 2000 that simple query won't work, the query also can be used to produce a report for her to print out.

    In the Criteria line of the "Description" field for Access 97 it reads: Like "*|[Please Enter a Word to search for:]|*" , How do I get this same type of query to work in Access 2000?

    I hope this makes sense and I do appreciate any help I can get on this.

    Thanks..

    Safarinut <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query from 97 to 2000 (Access 2000)

    What does the | mean in the criterion ?
    Like "*|[Please Enter a Word to search for:]|*"
    Try using
    Like "*" & [Please Enter a Word to search for:] & "*"
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Apr 2001
    Location
    Portland Oregon
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from 97 to 2000 (Access 2000)

    I really don't know what the | is for, I believe I found the example somewhere when I was still just using 97. Sorry... If I figure out where it came from I will let you know.

    I tried your suggestion but it didn't work. I even tried a few variations of it with no luck. I will keep working on it though in the meantime hoping someone will be able to help.

    Thank you very much..

    Safarinut <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query from 97 to 2000 (Access 2000)

    I am puzzled by that not working because I have just been using exactly that syntax in some Access 2000 dbs and it works perfectly for me.
    What happens ? does it return no records, too many?
    Are you running the query itself, or working with a form?
    Are there any other criteria in the query?
    Just thought of of something else? what datatype is description?
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from 97 to 2000 (Access 2000)

    That technique doesn't work in Access 2000, possibly because of the change in the query engine. I don't remember where the pipe characters crept in as a shortcut for concatenation, but they don't work like that past Access 97, at least not in queries. To do it in 2000, you'll have to use an expression like this:

    <pre>Like "*" & [Please Enter a Word to search for:] & "*"</pre>

    Charlotte

  6. #6
    New Lounger
    Join Date
    Apr 2001
    Location
    Portland Oregon
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from 97 to 2000 (Access 2000)

    To answer your questions: It was returning one blank record, I was running the query itself, no other criteria, the datatype is Text.

    Since you both said it worked for you in 2000, I knew I must have been doing something wrong. So I tried it again, turns out there was one other thing I needed to change. One of the other tables was not needed, even though it was OK in Access 97, Access 2000 didn't like it. So I deleted the one table just from that query, retyped in your suggestion and WahLah !

    Thank you both SO MUCH for your help I really do appreciate it, I just knew there had to be a way for it to work! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Have a great day, you sure made mine easier !

    Safarinut <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

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