none
Importing data from access to sql using a query RRS feed

  • Question

  • I am trying to import data from access to sql using the query editor. I would like to include the isnull or any equivalent statements into the query since there is some null data that must be handled before bringing it over if not it raises an error. 

    I tried 

    SELECT JOURNAL, INVOICE, AMOUNT, IIF([DATE] = NULL OR [DATE] = "", #1/1/1900#) FROM APPLIED

    and it didn't give and it was a valid statement, but it gives an error when import it because it says you can't insert a null value into the date column. I thought I handled for that. 


    Debra has a question

    Thursday, April 14, 2016 5:09 PM

Answers

  • There are a number of problems with your query:

    1.  Nothing can equal NULL.  NULL is the absence of a value, so any equality operation involving NULL will evaluate to NULL, Neither TRUE nor FALSE.  You must use IS NULL.

    2.  The IIF function has three arguments.  The first is a logical expression which evaluates to TRUE or FALSE; the second is the return value if the expression evaluates to TRUE; the third is the return value if it evaluates to FALSE.

    3.  You appear to be testing a column of date/time data type for a zero-length string.  A column of date/time data type will either be a value representing a date/time or NULL.  You can return a value in place of a NULL by calling the NZ function.

    So putting all this together the query would be:

    SELECT journal, invoice, amount,
    NZ(applied.date,#1/1/1900#) AS [date]
    FROM applied;

    Date is a bad choice for a column name, however, as it is the name of a built in function in Access, so as a 'reserved word' should not be used as an object name.

    Ken Sheridan, Stafford, England

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:16 AM
    • Marked as answer by David_JunFeng Monday, April 25, 2016 4:09 AM
    Thursday, April 14, 2016 9:08 PM
  • To pile on to Ken's excellent reply: 1/1/1900 represents a "magic value" which is a REALLY BAD IDEA. Did you know that Excel's day zero is 12/30/1899 (which is another magic value)?

    If NULL values raise an error the answer is NOT to use 1/1/1900 or any other "magic value" to satisfy the requirement. Say this date is an OrderDate. Then the underlying data should be able to supply an accurate OrderDate, or the OrderDate in the database should allow for nulls to indicate "I don't know". To use 1/1/1900 or any other literal value for "I don't know" is a cop-out.


    -Tom. Microsoft Access MVP

    • Marked as answer by David_JunFeng Monday, April 25, 2016 4:09 AM
    Friday, April 15, 2016 3:31 AM
  • The NZ function does not parse as a valid query in the import wizard from access to sql, but the idea you gave me for the iif function worked. You need to do AS DATE inorder for it to know which column to insert it into in sql. This query worked SELECT JOURNAL, INVOICE, AMOUNT, IIF([DATE] IS NULL, #1/1/1900#, [DATE])AS [DATE] FROM APPLIED.


    Debra has a question

    Monday, May 2, 2016 3:39 PM

All replies

  • There are a number of problems with your query:

    1.  Nothing can equal NULL.  NULL is the absence of a value, so any equality operation involving NULL will evaluate to NULL, Neither TRUE nor FALSE.  You must use IS NULL.

    2.  The IIF function has three arguments.  The first is a logical expression which evaluates to TRUE or FALSE; the second is the return value if the expression evaluates to TRUE; the third is the return value if it evaluates to FALSE.

    3.  You appear to be testing a column of date/time data type for a zero-length string.  A column of date/time data type will either be a value representing a date/time or NULL.  You can return a value in place of a NULL by calling the NZ function.

    So putting all this together the query would be:

    SELECT journal, invoice, amount,
    NZ(applied.date,#1/1/1900#) AS [date]
    FROM applied;

    Date is a bad choice for a column name, however, as it is the name of a built in function in Access, so as a 'reserved word' should not be used as an object name.

    Ken Sheridan, Stafford, England

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:16 AM
    • Marked as answer by David_JunFeng Monday, April 25, 2016 4:09 AM
    Thursday, April 14, 2016 9:08 PM
  • Hi, Debra

    I aggree with Ken Sheridan's reply, you could follow Ken Sheridan's suggestion. Otherwise if your [Date] column type is string, you could use DateValue function to convert string to Date.

    For more information, click here to refer about DateValue Function

    Friday, April 15, 2016 2:41 AM
  • To pile on to Ken's excellent reply: 1/1/1900 represents a "magic value" which is a REALLY BAD IDEA. Did you know that Excel's day zero is 12/30/1899 (which is another magic value)?

    If NULL values raise an error the answer is NOT to use 1/1/1900 or any other "magic value" to satisfy the requirement. Say this date is an OrderDate. Then the underlying data should be able to supply an accurate OrderDate, or the OrderDate in the database should allow for nulls to indicate "I don't know". To use 1/1/1900 or any other literal value for "I don't know" is a cop-out.


    -Tom. Microsoft Access MVP

    • Marked as answer by David_JunFeng Monday, April 25, 2016 4:09 AM
    Friday, April 15, 2016 3:31 AM
  • The NZ function does not parse as a valid query in the import wizard from access to sql, but the idea you gave me for the iif function worked. You need to do AS DATE inorder for it to know which column to insert it into in sql. This query worked SELECT JOURNAL, INVOICE, AMOUNT, IIF([DATE] IS NULL, #1/1/1900#, [DATE])AS [DATE] FROM APPLIED.


    Debra has a question

    Monday, May 2, 2016 3:39 PM