Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jul 2009
    Posts
    185

    Question Unanswered: Can't update blank fields

    I have the following code. If the fields have values everything works fine but if they were left blank when entering them I can't get the update to work. I have to go to the form and enter values then everything works.
    Also the if statement doesn't ever work?
    These are date fields.

    if Planes.dDate="" or Planes.ddate=null then
    msgbox " null"
    endif

    [Planes.dlDate] = [Flights.dDate]
    [Planes.dLastTime] = [Flights.dTime]

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's no such thing as = NULL, only Is Null. Easiest thing in VBA (NOT in SQL) to use is a function: IsNull.
    Code:
    if IsNull(Planes.dDate) then
    msgbox " null"
    endif
    Note that "" (or "Zero Length String") is not the same as NULL.
    BTW - it looks like you might be storing text in a date column - say it ain't so.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    185

    Question

    I am only storing dates or time in a date field.
    IsNull works so if I have a IsNull true my update on that field don't work?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Bob2119
    Also the if statement doesn't ever work?
    These are date fields.

    if Planes.dDate="" or Planes.ddate=null then
    msgbox " null"
    endif
    I corrected this - correct?

    As far as the rest of the question, you'll need to give more details for example what you mean by "I can't get the update to work". What does the update consist of?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2009
    Posts
    185
    Thanks for your help this is driving me nuts.

  6. #6
    Join Date
    Jul 2009
    Posts
    185
    The update is simply changing the date and time.
    [Planes.dlDate] = [Flights.dDate]
    [Planes.dLastTime] = [Flights.dTime]
    If they have values the update works find but if it is the first update where the fields are just blank on the form they don't update. IF I put in a dummy date and time then the updates work. They return IsNull true when they are blank.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    what are "they"? The columns in Flight?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2009
    Posts
    185

    Question

    they are date fields in the flights table as they are in the planes table.

    I appear to have the same problem with updating a integer field if it isnull is true. Set on the form to zero and it updates find . Easy I default the field to zero on add. But there is no default value for the date and time field.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    http://www.dbforums.com/microsoft-ac...ate-field.html



    You'll need to check for null SPECIFICALLY anyplace you used null and actually want it to mean "blank".
    oh yeah... documentation... I have heard of that.

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

  10. #10
    Join Date
    Jul 2009
    Posts
    185
    Ok I know how to set a field to null and how use IsNull to detect a null value but now how do I update the null field with a value from code. So far I have to go to the form and add a dummy value. There must be a way to assign a value to a null field.
    I have searched google for null but nothing appears to fix my problem.

  11. #11
    Join Date
    Jul 2009
    Posts
    185
    So I can set my fields always with a default(dummy value) and on a form make the field required so they can't enter a blank(null) value. But this doesn't answer my question just provide a temporary work around.

  12. #12
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Arrow hmmm

    Quote Originally Posted by Bob2119
    Ok I know how to set a field to null and how use IsNull to detect a null value but now how do I update the null field with a value from code. So far I have to go to the form and add a dummy value. There must be a way to assign a value to a null field.
    I have searched google for null but nothing appears to fix my problem.

    I've read all these threads...now let me see if I understand this... you're updating dates and times, and the whole thing goes kerplunk on its face if you run across a null value coming from... I don't know where.

    We've all covered the Isnull() thing... Forgive me i'm trying to understand where what data is coming from and where it's supposed to be going, since I can't look at what you're lookin' at...

    if Planes.dDate="" or Planes.ddate=null then
    msgbox " null"
    endif

    [Planes.dlDate] = [Flights.dDate]
    [Planes.dLastTime] = [Flights.dTime]
    ^ All that stuff is written in some form's VBA module, I assume. What is "Planes", and what is "Flights"? This is a change that just occurs once OnClick, correct? It sure would help if I could see all the stuff surrounding that one bit of code, and if I knew what those "Planes" and "dDate" things were. I'm sorry, I have a hard time visualizing things. <_<

    I'd like to help you though, if I can.... post a bunch of screen shots so I can see what you see.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  13. #13
    Join Date
    Jul 2009
    Posts
    185
    Well I have searched the vb and access help and googled every phrase I can think of and no luck. All kind of setting a null or detecting a null or using nulls in a Sql but nothing that gives me a answer to my question.

  14. #14
    Join Date
    Jul 2009
    Posts
    185

    Question

    The Planes table is a list of airplanes(model) with description etc and DDate which is the last time the plane flew. The flights table is a list of flights showing what battery was used, how long the flight was etc and dDate The date of the flight. When I add a flight I update the date in the Planes table to show the last date the plane flew. If this is the first flight for that plane it has a blank date field and I can't update the field. If however put in a dummy date via the planes's form then I can update the Planes dDate field. It also only updates the Planes's dDate if the current flights's dDate is greater than the existing date. All my updating works as long as the field I am trying to update is not a null or blank field. I now have set defaults for all the fields I wish to update to work around this problem.

    I hope this makes it more clear.

  15. #15
    Join Date
    Jul 2009
    Posts
    185
    Thanks for your help for me to understand this problem.

Posting Permissions

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