Post reply

How to convert "zero-length string" filter from Access 2010 to SQL 2008 R2

  • February 4, 2014 at 12:03 pm

    #285144

    I'm trying to convert an Access query to SQL and I've run into a few problems. Here is the first, I need to exclude records when a particular field is blank. The code in Access SQL view looks like this:

    HAVING ((([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "DEALER" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "EDI" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "EXCORR" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "INTERCO" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "FXD" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like ""));

    When I try to validate the code in the SQL query I'm getting an error on the very last part, the double quote which represents zero-length string. How should that be represented in SQL? The field is blank, not null.

    Thank you!

  • February 4, 2014 at 12:07 pm

    #1685976

    service 87594 (2/4/2014)

    I'm trying to convert an Access query to SQL and I've run into a few problems. Here is the first, I need to exclude records when a particular field is blank. The code in Access SQL view looks like this:

    HAVING ((([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "DEALER" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "EDI" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "EXCORR" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "INTERCO" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like "FXD" And ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) Not Like ""));

    When I try to validate the code in the SQL query I'm getting an error on the very last part, the double quote which represents zero-length string. How should that be represented in SQL? The field is blank, not null.

    Thank you!

    AND ([dbo_Mepps$Cust_ Ledger Entry].[Order Type]) != ''

    You want to exclude empty non-null strings, the above does it easily.

    Thanks

    John.

  • February 4, 2014 at 12:25 pm

    #1685982

    Thank you John, will give that a test soon as I get past problem two.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply