Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Unanswered: Set Null to space

    Hey, I have done some searching and couldnt find anything about setting the null values to spaces.

    Meaning if there was a blank in my data cell, for my text-field, with the help of a query have it transfer to a single space.


    thanks,
    BillyJ
    Last edited by BillyJay23; 01-16-07 at 14:04.

  2. #2
    Join Date
    Nov 2006
    Posts
    13
    its easy when you do it with an update query

    Code:
    Update To: " "
    Criteria: Is Null

  3. #3
    Join Date
    Jan 2007
    Posts
    7
    perfect thanks

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SELECT this, that, nz(potentialNULL, " ") FROM thisTable

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmm - crossed posts.

    think twice before the UPDATE - (those values got into your table for some reason, so make sure you know why they are there before you remove them)

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jan 2007
    Posts
    7
    izy, i tried your method also, it gave me an error and then changed all of data in the field to "potentialNULL".

    I think its ok to do the update because there is no data in the first place in the cell, so if it gives it a space instead of a blank, i think it would be ok.
    Thanks anyways

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by BillyJay23
    izy, i tried your method also, it gave me an error and then changed all of data in the field to "potentialNULL".

    I think its ok to do the update because there is no data in the first place in the cell, so if it gives it a space instead of a blank, i think it would be ok.
    Thanks anyways
    Right... Izy isn't asking whether you're going to be destroying data. He's asking if there's a really good reason to convert NULL to " ".

    NULL is a special value that tends to exist for a reason. Do you know the reason?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jan 2007
    Posts
    7
    No i dont.

    I just wanted to get my "blank/empty" (null?) cells to have a character in them to align my data. Of course i do know how many spaces to add depending on how far i need need to push the data for each field.

    I originally had zeros to align my data, but now zeros are going to mean something for the future. Having a space, i thought, would help me indicate that the information might exist but is not currently known instead of a NULL where a blank value indicates that the field does not apply to a particular record.

  9. #9
    Join Date
    Nov 2006
    Posts
    13
    ughh, sorry i guess i gave you wrong code. That was the only way that made sense to me from what you wanted. I guess ill shutup around here, and leave it to the pros.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    UPDATE and nz() are both good answers - depends on the problem.

    NULLs get into tables - that's life!

    one solution is to UPDATE them all to something else (to " " as discussed above) ...considerations: why the NULL got there in the first place (user error; application badly designed; or maybe the NULL has a 'special' meaning)

    another solution is to preserve the NULLs in the tables (at least until you have fully understood how they got there in the first place) and cover the formatting stuff with the query ...via nz()

    possibly my shorthand notation was too short!
    this, that, potentialNULL are supposed to be your real fieldnames, with potentialNULL being a field that might turn up to be NULL.

    the nz() function replaces any NULL value in the first parameter (fieldname in this case) with the value in the second parameter (" " space-string)

    if fieldX is the one you are talking about with possible NULLs,
    SELECT nz(fieldX, " ") FROM myTable
    is what i am talking about.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by BillyJay23
    No i dont.

    I just wanted to get my "blank/empty" (null?) cells to have a character in them to align my data. Of course i do know how many spaces to add depending on how far i need need to push the data for each field.

    I originally had zeros to align my data, but now zeros are going to mean something for the future. Having a space, i thought, would help me indicate that the information might exist but is not currently known instead of a NULL where a blank value indicates that the field does not apply to a particular record.

    This is a good example of where you should consider separating your data layer from your presentation layer. The data in your table should in no way be effected by spacing requirements for a report. I would STRONGLY urge you to move your formatting logic to the form/report/query that's pulling data out of the table as opposed to storing it in the table itself.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Jan 2007
    Posts
    7
    im actually exporting my data to a text file, its a not a report within Access. I had that info before but i edited it out once i got an answer.sorry. Thanks izy, i understand the logic now and was able to get it working.
    Last edited by BillyJay23; 01-16-07 at 16:23.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "exporting data to a text file" is generally going to involve using a query. You can place formatting logic in a query.

    For example, I have a few processes that run on a monthly basis where text files are sent to three different vendors. Each pulls the exact same data from the exact same tables. Each needs the data formatted differently.

    Is it easier to create a query and apply appropriate formatting, or attempt to manage synchronization of three different tables just to provide the same functionality?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Jan 2007
    Posts
    7
    ...im using an update query to provide the formation...your right, I am "destroying" my original data, but then again im only destroying my blank cells by adding spaces to them, I am doing no other logic or mathematical calculation with my other queries that involve NULL value. I have only one table so its easy to manage and then after im done updating it, it gets archived. Once I am done writing all my update queries, i will never have to do anything manually again when I keep on getting new tables added.

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    *shrug*

    Suit yourself.

    There's a reason I suggested what I suggested. Maybe you'll be lucky and won't have to discover that reason for yourself, the hard way.
    Last edited by Teddy; 01-16-07 at 17:25.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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