Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Posts
    29

    Unanswered: Here's a strange one

    I'm working with sql server tables linked into access 2003. I linked one specific table yesterday and i'm having problems with it. I have a very simple query saved with only three fields from that one table. When I run the query, it returns the correct number of rows, but the info displayed is all wrong. each record is the same, and made up of wrong data. If I export the data into an excel file and open it, it's the same number of records with the correct info ??????

    I suppose this could work, but it's a pain in the neck to have to export every time I want to test a query to see if it's pulling in the right data.

    Has anyone ever seen this before?

    fisk

  2. #2
    Join Date
    Apr 2003
    Posts
    29
    I've been playing with this for two days now and it still doesn't work right. Other queries that pull data from tables in the same database work fine. It's just this one table that's causing problems.

    If anyone has any ideas.......

    fisk

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    wrong results

    I'm curious...if you change the query to a make-table query and run it, does the newly made table contain the correct results?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Apr 2003
    Posts
    29
    I just checked. Yes, it does make the table with the correct info.

    Here is the data from the newly created table that is correct:

    Start InternalName User
    3/16/2006 MSACCESS CCRPWF
    3/16/2006 MSACCESS CCRPWF
    3/16/2006 MSACCESS CCRPWF
    3/16/2006 MSACCESS CCRPWF
    3/16/2006 MSACCESS CCRPWF
    3/16/2006 MSACCESS CCRPWF

    I filtered it down to the times that I opened ms access yesterday. This info is correct.

    Here is what comes up when I run the query and look at it in datasheet view:

    Start InternalName User
    2/28/2006 MSACCESS ccs7df
    2/28/2006 MSACCESS ccs7df
    2/28/2006 MSACCESS ccs7df
    2/28/2006 MSACCESS ccs7df
    2/28/2006 MSACCESS ccs7df
    2/28/2006 MSACCESS ccs7df

    In the criteria, i'm using date()-1 for the start date and 'ccrpwf' for the user id. I have no idea why the other user id would show up, or the wrong date would be displayed. And then when I export it to an excel file, it shows correctly.?????

    fisk

  5. #5
    Join Date
    Apr 2003
    Posts
    29
    i'm sorry about bumping this back up, but I just wanted to know if anyone had any ideas about this one?

    fisk

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What happens if you use DateAdd("d",-1,Date()) instead of Date()-1 for the start date?

  7. #7
    Join Date
    Apr 2003
    Posts
    29
    Thanks for replying DC... Same thing happens.

    It doesn't matter wether I have anything in the date criteria, or any other criteria, the problem is that no matter how many rows are returned, what I can see in the datasheet view is all wrong. but if I export it to excel, it's correct.... ?????

    fisk

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What if you take the SQL from the query in SQL view and copy it. Then open a new query and paste the SQL in SQL view? Is the query somehow corrupted?

    The other thing you might want to check is the query properties. Is there anything in the Fileter property?

  9. #9
    Join Date
    Apr 2003
    Posts
    29
    I copied the sql and pasted it into a new query. same thing. Here is the sql text:

    SELECT Format([start date],"m/d/yyyy") AS Start, dbo_AeXEvt_AeX_Application_Start.InternalName, dbo_AeXEvt_AeX_Application_Start.User
    FROM dbo_AeXEvt_AeX_Application_Start
    WHERE (((Format([start date],"m/d/yyyy"))=Date()-1) AND ((dbo_AeXEvt_AeX_Application_Start.User)='ccrpwf') )
    ORDER BY Format([start date],"m/d/yyyy");


    Again, once the data is exported, it's correct.

    fisk

Posting Permissions

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