+ Reply to Thread
Results 1 to 13 of 13

Query to change null/blanks to a number 0

  1. #1
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    180

    Query to change null/blanks to a number 0

    HI,

    I have a table called Module Data. Within this table there is a column called STUDENTS_TAKING.

    I want to run a query that amends this column so that any field that is either Null or Blank has a number 0.

    Can anyone please help? I'm a pretty novice Access user!

    Thanks
    Davinia

  2. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Query to change null/blanks to a number 0

    You can do this by Update query with Update to : "0" ; Criteria: Is Null
    Attached Images Attached Images
    Last edited by AliGW; 11-27-2017 at 12:45 PM. Reason: Unnecessary quotation removed.
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  3. #3
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Query to change null/blanks to a number 0

    Hi,

    Thank you for you reply but I don't think is a Null value. I need to cover all bases e.g. IsEmpty, "" etc

    Thanks

  4. #4
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Query to change null/blanks to a number 0

    You want to convert all "0" as empty?!

  5. #5
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Query to change null/blanks to a number 0

    Hi,

    No I have a column that contains numbers ranging from 0 to huundreds. However some are also BLANK. I.e. appear to not have anything in them. I want to enter a 0 in these blank fields so that it is recognised as a number and can be queried.

    Thanks

  6. #6
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Query to change null/blanks to a number 0

    Hi,
    Then you can use "" instead of "Is Null" ??

  7. #7
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Query to change null/blanks to a number 0

    Hi,

    I've used:- "", "IsNull", and "IsEmpty". None of them work.

    Thanks

  8. #8
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Query to change null/blanks to a number 0

    Is there no way to write some SQL to replace anything that looks blank as a 0? I know blanks are difficult when you import from Excel to Access but surely there is a way to automate this somehow?

  9. #9
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Query to change null/blanks to a number 0

    You said import from Excel, did you import the filed as text?
    If yes then "" will work on text field.

  10. #10
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Query to change null/blanks to a number 0

    It comes into Access as text yes but "" doens't work so whatever is in that field must be something else :-(

  11. #11
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Query to change null/blanks to a number 0

    can you share few lines in access db & the file which you try to import?
    Last edited by than_gold; 11-29-2017 at 05:51 AM.

  12. #12
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Query to change null/blanks to a number 0

    Hi,

    Unfortunately the data is rather sensitive and too large to attach. I have ended up with a temporary solution by amending the data manually in Excel before importing it. Not ideal but I am time bound so will have to revisit this another time although having looked up a lot online I'm not sure there is a solution.

    Thank you for you help.

  13. #13
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Query to change null/blanks to a number 0

    Ok...see you soon

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Null values get matched in Full Outer Join query. Can we stop this?
    By XLn3wb in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-26-2016, 04:32 AM
  2. [SOLVED] Access Query shows (Blanks) but no blanks in data!
    By MissDB in forum Access Tables & Databases
    Replies: 0
    Last Post: 09-08-2016, 07:16 AM
  3. How to remove null values from several columns with Power Query?
    By toblju in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2016, 11:08 AM
  4. [SOLVED] Help Needed to Create Query to eliminate Is Null Records
    By krjoshi in forum Access Programming / VBA / Macros
    Replies: 4
    Last Post: 09-05-2014, 07:45 PM
  5. [SOLVED] How can Access form run a query if field is not null?
    By jgomez in forum Access Tables & Databases
    Replies: 10
    Last Post: 01-14-2013, 02:35 PM
  6. Replies: 0
    Last Post: 03-08-2012, 05:02 PM
  7. Countifs with null or blanks
    By shuddle in forum Excel General
    Replies: 3
    Last Post: 10-14-2011, 02:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1