0

Using an Excel xlsx file with 50k+ rows exported from elsewhere. Trying to deal with nulls in msquery I have tried CASE statements with every combination I could imagine (and I am pretty sure it was not the syntax that was the problem) these are pretty simple statements

I am concatenating dimensional measurements to display a string for labels. for instance...

'H: '+xtract.Height+' x  W:'+xtract.Width AS [Dimensions]

nulls cause a failure in every type of CASE statement I have tried and just did not work.

What surprised me was that I could use the '&' as a concatenator rather than '+' which can be used in Excel formulas and prepend an empty string to get the CSTR function to work here.

'H: '+CStr(''&xtract.Height+' x  W:'+CStr(''&xtract.Width ) AS [Dimensions]

If this was all conventional SQl syntax it would be fairly straight forward, but it seems MSQUERY has peculiar syntax that allows me to solve the issue this way.

This is an infrequent task, so I am not so concerned with performance. This works.

So I am torn between moving on, and spending time insisting on a CASE statement that I can never get the syntax correct for when this works.

Are there other problems with this approach I am missing?

datatoo
  • 2,019
  • 2
  • 21
  • 28

1 Answers1

0

Try using ISNULL function.

Like H: '+ISNULL(xtract.Height,'')+' x W:'+ISNULL(xtract.Width,'') AS [Dimensions]

Ram
  • 3,092
  • 10
  • 40
  • 56
  • Thanks. I tried that earlier and again now. I get ERROR [4200] [Microsoft][ODBC Excel Driver] Wrong number of arguments used with function in query expression.... I am pretty sure this is specific to using excel as a data source and thus functions are not all usable as might be expected. – datatoo May 29 '13 at 04:13
  • 1
    I guess the conventional SQL syntax doesn't work for MS Query with Excel file as source as it has a different driver and version based on the type of excel file like mentioned [here](http://www.exceluser.com/explore/msquery1_2.htm). As it uses a different driver I guess I has a different rule set and syntax for queries related to it. – Ram May 30 '13 at 18:31
  • yes that is why it seems so wrong to do things this way. But it is for temporary data exchange, so I can live with it for now. Thanks for your input – datatoo May 31 '13 at 20:06