Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2003
    Posts
    29

    Unanswered: Strange problem with criteria

    I'm asking this just to see if anyone has any ideas. i've got a query set up that pulls info from one LINKED table in a sql server DB. I can run the query and get all the rows in the table with no problems. When I go to design view and add LIKE "pfx%" in the criteria field for the appropriate column, I get back about 10 records that have Survey.exe in that field and a few records that have hpdiags.exe If I change the criteria to "pfxbrowse.exe" then I get just a few records that say survey.exe

    Also, just for the heck of it, I put "survey.exe" in the criteria field and I received 164 records of various results including survey.exe and many other things.

    I've never seen this before in all the reports i've ever written. Any ideas?

    fisk

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What does that data you're querying look like to begin with? The computer will only do exactly what you ask of it...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2003
    Posts
    29
    The data in the field in question is the names of different exe files on the computers in our company. There are several hundred thousand records in that table. I've gone into sql server enterprise manager and ran the same query on the table itself. I come up with 15 rows that consist of the correct data so I know the word "pfxbrowse.exe" is there.

    But when I run the query from ms access using the linked table, it skips the correct info, in favor of survey.exe I've dumped the query and rebuilt it to no avail.

    fisk

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Post the SQL.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2003
    Posts
    29
    SELECT dbo_AeXInv_AeX_SW_Audit_Software.WrkstaId, dbo_AeXInv_AeX_SW_Audit_Software.[File Name], dbo_AeXInv_AeX_SW_Audit_Software.KnownAs, qry_pri_uid.User, qry_pri_uid.Name, dbo_AeXInv_AeX_SW_Audit_Software.[File Modification Time]
    FROM dbo_AeXInv_AeX_SW_Audit_Software INNER JOIN qry_pri_uid ON dbo_AeXInv_AeX_SW_Audit_Software.WrkstaId = qry_pri_uid.WrkstaId
    WHERE (((dbo_AeXInv_AeX_SW_Audit_Software.[File Name]) Like 'pfx*'));


    Again, this is in ms access on a table that is linked to a sql server db. It is joining on an existing query. The table and other query work fine.

    fisk

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What do you get when you run this?

    SELECT dbo_AeXInv_AeX_SW_Audit_Software.[File Name]
    FROM dbo_AeXInv_AeX_SW_Audit_Software INNER JOIN qry_pri_uid ON dbo_AeXInv_AeX_SW_Audit_Software.WrkstaId = qry_pri_uid.WrkstaId
    WHERE (((dbo_AeXInv_AeX_SW_Audit_Software.[File Name]) Like 'pfx*'));
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Apr 2003
    Posts
    29
    Same thing, only with one column missing.

    i'm at home now trying to remote into my work pc so it may not have copied correctly.

    Teddy, thanks for the help and enjoy your christmas. I'll try it again when I get back to work on tuesday and let you know.

    thanks again.

    fisk

  8. #8
    Join Date
    Apr 2003
    Posts
    29
    I ran the statement you gave me. Pretty much the same results:

    File Name
    survey.exe
    survey.exe
    survey.exe
    survey.exe
    survey.exe
    survey.exe
    survey.exe
    hpdiags.exe
    hpdiags.exe
    hpdiags.exe
    survey.exe
    survey.exe
    survey.exe
    survey.exe
    hpdiags.exe

    I have never seen this before. I cant imagine why you could search for a text string, and it returns a completely different set of results.

    fisk

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hmmmmmm... I wonder...

    Try removing the parenthesis around [File Name] in the where clause:

    SELECT dbo_AeXInv_AeX_SW_Audit_Software.[File Name]
    FROM dbo_AeXInv_AeX_SW_Audit_Software INNER JOIN qry_pri_uid ON dbo_AeXInv_AeX_SW_Audit_Software.WrkstaId = qry_pri_uid.WrkstaId
    WHERE (dbo_AeXInv_AeX_SW_Audit_Software.[File Name] Like 'pfx*');


    I'm curious if it is evaluating (dbo_AeXInv_AeX_SW_Audit_Software.[File Name]) as a boolean value. Kookie I know, but I've seen bizarre stuff like that before on other platforms.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Apr 2003
    Posts
    29
    Nope, same thing:

    File Name
    survey.exe
    survey.exe
    survey.exe
    survey.exe
    hpdiags.exe
    hpdiags.exe
    hpdiags.exe
    survey.exe
    survey.exe
    survey.exe
    survey.exe
    hpdiags.exe
    survey.exe
    survey.exe
    survey.exe

    Pretty odd, huh?

    by the way, I appreciate the help with this no matter how it turns out.....lol

    fisk

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    WAAAAAAAIT just a second....

    You said this in SQL Server... Use % instead of *. You posted % in your first post, but * in the actual SQL.

    Also, try running these queries right in Query Analyzer instead of through Access. It will be easier to figure out what's going on.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Apr 2003
    Posts
    29
    Sorry for the confusion. I've been working on this for a few days and i've tried every possible combination of % and * you can imagine. The problem is with ms Access that is linking to a specific table in a db in sql server. I've ran the query in query analyzer in sql server and it works fine. It pulls up the correct info. The problem running the query in access.

    I just cant figure out why i'm looking for text with this command: LIKE 'pfx%' or LIKE 'pfx*' and it returns data that is nothing at all like the search criteria. Also, if I put: = 'pfxbrowse.exe' it also returns the wrong info, even though I know there are several instances of that exact string.

    I'm at a loss....

    fisk

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    is the query set to be a pass-thru?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Apr 2003
    Posts
    29
    Ok, now were getting somewhere. What does that mean? I dont believe i've ever heard of pass-thru.

    fisk

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    check under query type when designing the query pointed at your linked table. pass-thru basically means it is "passing" the query directly to the server instead of analyzing it first.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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