none
Despite workarounds, why am I still getting this message when I run this query - Error! Invalid use of Null

    Question

  • Good day everyone!

    I have this query:

    SELECT GUEST_PLAYER.GAME_ID, GUEST_PLAYER.GAME_CODE, GUEST_PLAYER.SCORE, (DateSerial(CInt(Left([GUEST_PLAYER].[PLAY_DATE],4)),CInt(Mid([GUEST_PLAYER].[PLAY_DATE],5,2)),CInt(Right([GUEST_PLAYER].[PLAY_DATE],2))))
    AS PLAYING_DATE, GUEST_PLAYER.PLAY_DESC FROM GUEST_PLAYER WHERE (NOT EXISTS(SELECT GAME_ID FROM MEMBER_PLAYER WHERE GUEST_PLAYER.GAME_ID = MEMBER_PLAYER.GAME_ID)) AND GUEST_PLAYER.PLAY_DATE IS NOT NULL AND ((GUEST_PLAYER.GAME_CODE) IN ("A101XV", "A102XV", "A103XV", "A104XV", "A105XV"));

    The dates are from a linked table and formatted like this: 20120414 and so I used the DateSerial to convert the date to: 04/14/2012

    The problem is I need to specify certain dates, example: get PLAY_DATE greater than some date but regardless of what I try I get the Invalid use of null error.

    I tried using HAVING or removing all Null values but nothing is working.

    Why and how can I workaround or remedy this?

    Thanks everyone!

    Synth

    Saturday, April 14, 2012 8:04 PM

All replies

  • You could create a query that selects the records from GUEST_PLAYER where PLAY_DATE is filled in:

    SELECT *
    FROM GUEST_PLAYER
    WHERE PLAY_DATE Is Not Null

    Save this as - say - qryGUEST_PLAYER, and then use qryGUEST_PLAYER instead of GUEST_PLAYER in your macro.

    Regards, Hans Vogelaar

    Saturday, April 14, 2012 8:28 PM
  • You could create a query that selects the records from GUEST_PLAYER where PLAY_DATE is filled in:

    SELECT *
    FROM GUEST_PLAYER
    WHERE PLAY_DATE Is Not Null

    Save this as - say - qryGUEST_PLAYER, and then use qryGUEST_PLAYER instead of GUEST_PLAYER in your macro.

    Regards, Hans Vogelaar


    I tried that but still get an error, this is painful. :(
    Saturday, April 14, 2012 8:52 PM
  • Null is not the same as an empy string.  The database has a cell in every row and every column so the object isn't a null.  You have a cell with an empty string.  So you need to look for an empty string.  So simply use two sets of double quotes like this : ""

    jdweng

    Saturday, April 14, 2012 9:43 PM
  • Try:

    SELECT GUEST_PLAYER.GAME_ID, GUEST_PLAYER.GAME_CODE,
    GUEST_PLAYER
    .SCORE,
    CDate(Format[GUEST_PLAYER].[PLAY_DATE], "0000\-00\-00")) AS PLAYING_DATE, GUEST_PLAYER.PLAY_DESC
    FROM GUEST_PLAYER WHERE (NOT EXISTS(SELECT GAME_ID FROM MEMBER_PLAYER WHERE GUEST_PLAYER.GAME_ID = MEMBER_PLAYER.GAME_ID))
    AND GUEST_PLAYER.PLAY_DATE IS NOT NULL
    AND ((GUEST_PLAYER.GAME_CODE) IN ("A101XV", "A102XV", "A103XV", "A104XV", "A105XV"


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Sunday, April 15, 2012 3:55 AM
  • Joel Engineer wrote:

    Null is not the same as an empy string. 

    Agreed.

    The database has a cell in every row and every column so the object isn't a null.  You have a cell with an empty string.  So you need to look for an empty string.  So simply use two sets of double quotes like this : ""

    Databases don't have cells.  Excel has cells. 
    Fields in databases can be null.

    Testing for two sets of double quotes only works for text fields which
    are empty.   And that happens very seldom as Access automatically
    updates form controls with no data as fields with null fields.   Indeed,
    as far as I know, the only way to have a field in a table with an empty
    string is to use a SQL Update/Insert query.  But I could be wrong.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Sunday, April 15, 2012 8:12 PM
  • Tony: I don't want to argue, but all "TABLES have CELLS".  Fields in Databases are equivalent to Columns in Excel.  Fields and Columns are veritical divisions of tables, like row are horizontal divisions of tables.


    jdweng

    Sunday, April 15, 2012 8:21 PM
  • Joel,

    I think there is some confusion on your part. The field names are in the similar position and fasion as columns in Excel however they function entirely differently. Excel has definitions writing all field positions as empty until filled by the user while Access has null value until the value is assigned. This is an inherent nature database structure. You can actually run code to test for null cells in a db and get a real result. There are many articles written on how to deal with "nulls" in a db. The reason is simply because many calculations fail with null values. Since null values can and do exist in Access, how do you explain their existence when the builder does not put null fields in? If you assume that null fields are created via data entry then explain this, please.

    Thank you.


    Chris Ward

    Sunday, April 15, 2012 11:22 PM
  • What happens in access when a new row is added?  Do all the fields in a row get added.  I think you are getting confused between fields in tables which are cells and links between tables.  Yes you can have NULL links, but I still think when a row gets added all the cells in the row also get added.  There is also confusion with tables that are created as a results of SQL statements (especially with JOIN).  The Join table has rows with "all the cells" in the rows, but some fields are empty with Null pointers to the source data.

    Now I was just refering to Access which is a binary database.    There is another whole story when you start talking about a database like SQL Server which stores the data in XML format.


    jdweng

    Monday, April 16, 2012 8:38 AM
  • And how do you explain a simple situation like the CheckBox where the default value is Null unless you change it to 0 or 1. This is not an empty but a NULL.

    Chris Ward

    Monday, April 16, 2012 1:05 PM
  • The checkbox exists which means the cell was created.  The data inside the checkbox has three possible values

    1) Zero

    2) One

    3) Null

    I have worked with ListViews in visual studio and what really happens when you add a new column to a table with checkboxes and column is added and a cell is create for each row.  Each cell points to the same default checkbox object that is null.  When the check box is set for the first time a new check object is created for the cell so it no longer points to the default object.

    Monday, April 16, 2012 1:08 PM
  • Interesting however 2 things,

    1) We are not talking about pointing to diferent objects only 1 field in a Table at the row level.

    2) If a new value is not selected, the value remains NULL.

    There is no code that tells it otherwise and you can still run sql to return NULL values in the Table.

    You have not addressed these facts


    Chris Ward

    Monday, April 16, 2012 1:39 PM
  • Chris: I said the following which started this conversation.

    Null is not the same as an empy string.  The database has a cell in every row and every column so the object isn't a null.  You have a cell with an empty string.  So you need to look for an empty string.  So simply use two sets of double quotes like this : ""

    I did not talk about links between tables.  Yes SQL statements can have NULLS.  I just can't tell in this situation if the cell is a source table that contains data or a linked table that contains pointers.

    The person who started this posting said the following

    I tried using HAVING or removing all Null values but nothing is working.

    Here is the SQL

    SELECT GUEST_PLAYER.GAME_ID, GUEST_PLAYER.GAME_CODE,
    GUEST_PLAYER
    .SCORE,
    (DateSerial(CInt(Left([GUEST_PLAYER].[PLAY_DATE],4)),CInt(Mid([GUEST_PLAYER].[PLAY_DATE],5,2)),CInt(Right([GUEST_PLAYER].[PLAY_DATE],2))))
    AS PLAYING_DATE, GUEST_PLAYER.PLAY_DESC

    Note: PLAYING_DATE is an alias.

    You need to create a row in the table for each item that is returned from the query to be able to create the alias column.  You can't delete a row that is a result of the query. It will reappear every time the query is executed.  The solution to this request is to eliminate the alias.




    jdweng

    Monday, April 16, 2012 2:01 PM
  • Have a good day Joel.

    Synthologic, Sorry I should not have hi-jacked your thread I didn't realize the direction the discourse would take. Some of us just have different understandings that don't really relate to your issue at hand.

    Do you have the ability to breakdown your query and test for the individual field results and pairs of results to see if something else your looking at could be confounding your query?

    Did you have the opportunity to try Doug's suggestion?

    In the Alias, Are these combo box controls you are looking at? If so are you specifying the column to get the date from? [Play_Date],4...[Play_Date],5,2...[Play_Date],2...If any of those return a Null value, then they may fail to calculate before it reaches your Where clause. Sometime Access cannot calculate on multiple levels so you may need to deal with the Nulls before the Alias.

    Again I apologize for going off on a tangent and I hope you find your answer soon.


    Chris Ward

    Monday, April 16, 2012 2:23 PM
  • Try this:

    SELECT DISTINCT GUEST_PLAYER.GAME_ID,
    GUEST_PLAYER.GAME_CODE,
    GUEST_PLAYER.SCORE,
    Format(Format(GUEST_PLAYER.PLAY_DATE,"0000-00-00"),"mm/dd/yyyy") AS PLAYING_DATE
    FROM GUEST_PLAYER LEFT JOIN MEMBER_PLAYER
    ON GUEST_PLAYER.GAME_ID = MEMBER_PLAYER.GAME_ID
    WHERE GUEST_PLAYER.GAME_CODE IN ("A101XV", "A102XV", "A103XV", "A104XV", "A105XV")
    AND MEMBER_PLAYER.GAME_ID IS NULL;

    This will not raise an error if PLAY_DATE is NULL as the column is not being converted to a date only formatted as such.

    If you need to do restrict the query on the PLAY_DATE column then you can do so on its original value as this sorts, e.g. you could include a criterion in the WHERE clause which uses a parameter:

    PLAY_DATE > Format([Enter play date:],"yyyymmdd")

    The parameter value can then be entered in the local date format.

    Ken Sheridan, Stafford, England

    Monday, April 16, 2012 11:11 PM
  • Joel Engineer wrote:

    Tony: I don't want to argue, but all "TABLES have CELLS".  Fields in Databases are equivalent to Columns in Excel.  Fields and Columns are veritical divisions of tables, like row are horizontal divisions of tables.

    I do want to argue.  The proper terminology in Access is fields.  Not
    cells.   
    Superficially Access in datasheet view and Excel appear alike.  However
    there are many differences especially in having field types in Access
    such as integer, current and string thus ensuring at least some data
    uniformity in Access.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Wednesday, April 18, 2012 4:33 AM
  • Joel Engineer wrote:

    What happens in access when a new row is added?  Do all the fields in a row get added. 

    Yes, empty place holder fields are added to the Access database file
    when the record is initially created on a form.  Then once the data is
    saved on the form it is checked for consistency such as required fields
    or parent records and, if all such are valid, the record is saved.

    You can see this yourself by going to a tables datasheet view and
    starting to add a record.   You will also notice the autonumber value,
    if you are using such, is allocated.

    This is different behavior than SQL Server which only writes the record
    once you save the record.

    I think you are getting confused between fields in tables which are cells and links between tables.  Yes you can have NULL links, but I still think when a row gets added all the cells in the row also get added.  There is also confusion with tables that are created as a results of SQL statements (especially with JOIN).  The Join table has rows with "all the cells" in the rows, but some fields are empty with Null pointers to the source data.

    The term you mean instead of links is joins which are used in queries.

    Now I was just refering to Access which is a binary database.    There is another whole story when you start talking about a database like SQL Server which stores the data in XML format.

    SQL Server does not store data in XML format as standard practice.  You
    can choose to do so if desired but that's not done very often.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Wednesday, April 18, 2012 4:42 AM
  • Tony Toews - Access MVP <tony@granite.ab.ca> wrote:

    Joel Engineer wrote:

    What happens in access when a new row is added?  Do all the fields in a row get added. 

    Yes, empty place holder fields are added to the Access database file
    when the record is initially created on a form. 

    Actually that should be slightly rephrased.    An empty record
    containing null fields is created in the Access database file as soon as
    you enter any character either in datasheet view or a form.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Wednesday, April 18, 2012 4:46 AM
  • Tony: You can't use the same word for two different items.   if everybody in the world was called Tony, we wouldn't know who anybody was talking about.  In Access a column is a field and a field constists of many cells.  Just like in a word table the individual boxes are cells.

    jdweng

    Wednesday, April 18, 2012 5:03 AM
  • Joel Engineer wrote:

    Tony: You can't use the same word for two different items.   if everybody in the world was called Tony, we wouldn't know who anybody was talking about.  In Access a column is a field and a field constists of many cells.  Just like in a word table the individual boxes are cells.

    A column is not the same as a field.   A field does not consist of many
    cells.  A record consists of one or more fields.     The individual
    fields are not called cells.  That is not database terminology.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Wednesday, April 18, 2012 6:19 AM
  • A record consists of one or more fields.
    Strictly speaking records and fields are terms of a file system database in which data is addressable by position.  A table in a relational database has rows and columns.  Data is held at column positions in rows and in the relational model is addressable solely by name.  However, I'm not unduly uncomfortable with the use of the terms record and field in Access, but I entirely agree that to talk of cells in the context of any relational database management system is nonsensical.

    For a short but authoritative explanation of the differences between tables and files, and to some extent spreadsheets, I'd recommend chapter 1 of Joe Celko's SQL for Smarties.  For a more in depth discussion of just what a table is, and how in differs from a relation one can't do better than to consult Date.


    Ken Sheridan, Stafford, England

    Wednesday, April 18, 2012 10:45 AM
  • Ken Sheridan wrote:

    I'd recommend chapter 1 of Joe Celko's/SQL for Smarties/. 

    Ah, Celko.  He didn't like one of my ideas in a posting years ago.  Oh
    well.  I'm still right.  <smile>

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Sunday, April 22, 2012 12:55 AM
  • Ah, Celko.  He didn't like one of my ideas in a posting years ago
    There are many things Joe doesn't like, including MS Access per se!  We used to get some interesting posts from him in the old CompuServe MsDevApps forum, which really exasperated some people at times, but he knows his stuff, and his books and articles are a mine of useful SQL techniques.  I corresponded directly with him on a few occasions and, despite the fact that he looks like someone who loves small children so much he has one for breakfast each day, always found him very courteous and willing to help.

    Ken Sheridan, Stafford, England

    Sunday, April 22, 2012 11:06 AM
  • The older we get the more experience we get. From our experiences we find lots of thinks that we don't like.  I have similar issue to Joe in the fact I find lots of things I don't like including eating little children.  Although, I like to teach these inexperience children a thing or two.

    jdweng

    Sunday, April 22, 2012 12:09 PM
  • Ken Sheridan wrote:

    Ah, Celko.  He didn't like one of my ideas in a posting years ago <http://www.autofeupdater.com/>

    There are many things Joe doesn't like, including MS Access per se!  We used to get some interesting posts from him in the old CompuServe MsDevApps forum, which really exasperated some people at times, but he knows his stuff, and his books and articles are a mine of useful SQL techniques.  I corresponded directly with him on a few occasions and, despite the fact that he looks like someone who loves small children so much he has one for breakfast each day, always found him very courteous and willing to help.

    Heheheh

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Sunday, April 22, 2012 9:36 PM