Excel 中建立 Query 企图从 Access 获得数据。没有报错,但是某些数据无法获得。 RRS feed

  • 问题

  • 首先我在Access里面link进来几张表,基于这几张表,我设计了一个select查询,其中添加了一个计算字段 alpha


    UCase(SAPCostFact.[Name]) Like '*AAA*','A类型',

    UCase(SAPCostFact.[Name of offsetting account]) Like '*BBB*','B类型',

    (SELECT DISTINCT TravelAgency.[Name] FROM TravelAgency WHERE UCase(SAPCostFact.[Name of offsetting account]) Like '*' & TravelAgency.[Name] & '*') IS NOT Null,'E类型',

    UCase(SAPCostFact.[Name]) Like '*CCC*','C类型',


    结果Access里面执行查询 可以得到Alpha字段下面 A B C D E 类型的标志。到这里都没有问题。

    但是从Excel里面利用External Data里面data connection, Query的方式去拿数据的时候仅仅得到了ABCD四种类型。经检查E类型的条目被合并到D当中去了。


    2011年2月21日 15:29


  • 解决方法找到,详见原文:


    Hi difuk,


    So I'm not certain that I got everything exactly the same as your environment, but I'm getting results that I believe are fairly similar to what you're seeing. Here's my setup:


    1. I created FactTable with fields Name and Alias and populated it with data
    1. I created Vendor with the field Name and populated it with data that matches my Alias field in FactTable
    1. I set up a query with a join between Alias and Name and added the field Name from FactTable to the output
    1. I created an expression that uses your Switch function


    In Access, wherever I have a field with a value BBB111 or BBB222 and a match between the two tables, I'm getting a value of 'B' back in the expression field. That tells me that Access is correctly evaluating the subquery in the function correctly and returning B as a result.


    So far so good I think, but after that is where I'm not sure I'm following your exact process . . .


    My next step was to open an Excel sheet and click on Data and choose the Microsoft Query option. I didn't see a builder there that allows me to create an expression, so I just used my Access query as the source data. In that case, for the records where I see a 'B' returned by the switch function on the Access side, this time I'm getting 'D' returned to me. The other fields continue to evaluate as they did on the Access side. That seems to indicate that somehow, the second expression in the function does not evaluate as true in this case.


    In testing a little further we found that if we make a slight change to the function on the Access side, we get the desired results in Excel. In your function, simply change all of the '*' characters to '%', then refresh the data on the Excel side. I should note that if you just make that change, the query won't work in Access because it doesn't understand '%' as a wild card character.

    Assuming we're seeing the same things up to this point, that would leave you with two options:

    1. Create one query for viewing on the Access side that keeps the '*' character as a wild card and one that uses '%' for opening with Excel
    1. Change the following setting in Access: File>Object Designers>Query Design>SQL Server Compatible Syntax (ANSI 92)>This database = yes

    Changing the ANSI 92 option may have other unintended consequences so I like the first option, but that's your call.

    Why Excel only cares about those wild card characters in the subquery is beyond me.


    I hope that helps!


    Kirk Beller

    Microsoft Online Community Support


    • 已标记为答案 difuk 2011年3月2日 15:21
    2011年3月2日 15:20