Results 1 to 8 of 8
  1. #1
    Vaslo is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    23

    Imported Query Results returning more than query itself is showing (ignoring a filter)

    Hello,



    I have a query on 8 dimensions of business data. I have a few filters applied. All but one seems to work. The query when run and saved in Access return 10,700 records, but when imported and linked in Excel it returns over 11,000. I have a filter on criteria that seems to work fine in Access but Excel doesn't see it. The criteria is:

    Not Like "9699*"

    I want to omit those codes because they are miscellaneous charges that aren't really operationally relevant to the business. Why would the import to Excel ignore this filter?

    Thanks.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    How exactly are you "importing and linking to Excel"?

    Can you post the SQL code for the query in question (switch to SQL View and copy and paste code here)?

  3. #3
    Vaslo is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    23
    I am using Get External Data under Data in Excel, From Access, then choosing the Query.

    Code:
    SELECT [Transaction Level Detail with Calc Columns Added].[Fiscal Month], tblDate.CalendarMonth, [Transaction Level Detail with Calc Columns Added].[Profit Center Code], [Transaction Level Detail with Calc Columns Added].[Customer Name], [Transaction Level Detail with Calc Columns Added].[Ship To City], [Transaction Level Detail with Calc Columns Added].[Product Number], [Transaction Level Detail with Calc Columns Added].[Base Product Number], Sum([Transaction Level Detail with Calc Columns Added].[Net Invoiced Amount at AFX]) AS [SumOfNet Invoiced Amount at AFX], Sum([Transaction Level Detail with Calc Columns Added].[Net Quantity in GAL]) AS [SumOfNet Quantity in GAL]FROM tblDate INNER JOIN [Transaction Level Detail with Calc Columns Added] ON tblDate.DayOfTheMonth = [Transaction Level Detail with Calc Columns Added].[Invoiced Date]
    GROUP BY [Transaction Level Detail with Calc Columns Added].[Fiscal Month], tblDate.CalendarMonth, [Transaction Level Detail with Calc Columns Added].[Profit Center Code], [Transaction Level Detail with Calc Columns Added].[Customer Name], [Transaction Level Detail with Calc Columns Added].[Ship To City], [Transaction Level Detail with Calc Columns Added].[Product Number], [Transaction Level Detail with Calc Columns Added].[Base Product Number], [Transaction Level Detail with Calc Columns Added].[Fiscal Year]
    HAVING ((([Transaction Level Detail with Calc Columns Added].[Profit Center Code])="3359" Or ([Transaction Level Detail with Calc Columns Added].[Profit Center Code])="3780" Or ([Transaction Level Detail with Calc Columns Added].[Profit Center Code])="3730") AND (([Transaction Level Detail with Calc Columns Added].[Product Number]) Not Like "9699*") AND (([Transaction Level Detail with Calc Columns Added].[Fiscal Year])>2013));

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    That makes no sense to me how the record counts could be different when looking at the same query.
    If you try exporting the query results out to an Excel file or text file, does it have the correct number of records?

    You might want to sort the two different result sets and compare them side-by-side and see where it diverges. Once you find a record that doesn't match, you may see something about it that might shed some light on the issue (i.e. weird, incomplete, or Null data).

  5. #5
    Vaslo is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    23
    Thanks for looking at it - yes it is strange. It is definitely related to the piece of the query on the Not Like "9699*". This works perfectly to filter in Access, but it is just being ignored in the Excel link, even after I refresh. Anything there that may be wrong you think?

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Instead of using the structure:
    Code:
    [Field1] Not Like "9699*"
    try this structure:
    Code:
    Left([Field1],4)<>"9699"

  7. #7
    Vaslo is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    23
    Quote Originally Posted by JoeM View Post
    Instead of using the structure:
    Code:
    [Field1] Not Like "9699*"
    try this structure:
    Code:
    Left([Field1],4)<>"9699"
    Ok so you are an SQL monster, this fixed it. Let me guess - this column is actually text, not a number, because most of the values have a letter in them. When I used 9699 Access was smart enough to know I was trying to filter on a string, but the link in Excel wasn't?

    Please teach a man to fish here, and greatly appreciate your patience with me.

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I think it may more to do with the wildcard "*". I don't know how Excel handles it when being linked from Access.
    Sometimes, it does some funny things along the way, and things don't always work out the way you expect them to, especially when interacting between Excel and Access.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Like query not returning all results
    By robbeh in forum Queries
    Replies: 3
    Last Post: 10-10-2014, 02:32 PM
  2. Query not returning all expected results
    By amenitytrust in forum Queries
    Replies: 6
    Last Post: 11-05-2012, 07:13 AM
  3. Select Query returning no Results
    By Rhemo in forum Access
    Replies: 2
    Last Post: 09-15-2012, 04:11 AM
  4. Query Bug? Not returning consistent results
    By trb5016 in forum Queries
    Replies: 4
    Last Post: 06-15-2010, 12:00 PM
  5. Query not returning all of the results
    By velvettiger in forum Queries
    Replies: 4
    Last Post: 03-11-2010, 06:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums