Microsoft Query Unable to Pass String or Date Parameters Back Into Worksheet

Hi All,

I'm running into an issue using Microsoft Query that is not allowing me to pass strings or dates as parameters. However, passing integers as parameters works fine. Say I start with the following dataset that I have retrieved from database using the MS Query:


TIME ERROR_YN RECEIVED_TIME SEQUENCE_NO
4/1/2016 0:15 N 4/1/2016 0:15 270316028
4/1/2016 0:25 Y 4/1/2016 0:25 270313029
4/1/2016 0:35 N 4/1/2016 0:35 270316030
4/1/2016 0:40 Y 4/1/2016 0:40 270313031
4/1/2016 0:40 N 4/1/2016 0:40 270316032
4/1/2016 0:45 N 4/1/2016 0:45 270316033
4/1/2016 1:05 N 4/1/2016 1:05 270313034
4/1/2016 1:10 N 4/1/2016 1:10 270316035
4/1/2016 1:25 N 4/1/2016 1:25 270316036
...
...
...

When I open up MS query and apply criteria I want to filter on (in this case for the ERROR_YN field), I can manually set the error message content to be either 'Y' or 'N' and I will have no problem both preview the data in MS Query and returning the data to the table in the worksheet.

However, if I set that ERROR_YN to an input parameter (filter criteria for ERROR_YN now set to [ERROR]), I am prompted for input and am able to preview the results in MS Query but it will NOT return data upon refreshing it in the worksheet itself. I have tried both direct input from the prompt as well as passing the parameter from another cell in the worksheet but it is never able to pull anything back even though I know both 'Y' and 'N' values exist in the data set. I am, however, able to pass integers through as parameters just fine.

Has anyone ever come across this issue? I searched the forum and did a quick google but I was unable to find any issues directly related to what I'm experiencing.

Thanks!

Andrew

Hi dflak,

Thanks for the response! Answers to your questions below:

Q: Are the Y and N in the database really Y and N or do they have hidden spaces attached to them?
A: They are really Y and N. There are no extra spaces before or after the character.

Q: Are they the same case as the parameters you are trying to pass?
A: Yes. They are all upper case.

Q: Are they really ASCII 78 and 89 respectively?
A: I double checked this and they really are ASCII 78 and 89.

Q: Are you passing the parameter "as is" - that is without the quotes around them?
A: I've been passing them as is. I also tried adding quotes around them just in case but that didn't work either.

Q: What happens if you hard coded a 'Y' or 'N' into the SQL?
A: When I hard code it into the query, I'm able to pull data back.

The strangest thing is that this used to work just fine so I'm wondering if it's possible that there is some sort of clash between a recent Excel update and the ODBC driver I'm using to connect to the data source. The reason I say that is because I set up a dummy Access database and I was able to use that as my data source in Excel just fine with string parameters. I've also tried this on a few of my coworkers machines as well and I'm getting the same outcome. I'll keep digging.