Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Unanswered: Crazy Request: "N/A" in Date Field!

    My users input into several date fields on a form. Some fields are left blank, others are not. The designer who created the db used Date data types which is a shame as the users now want to somehow record blanks: their preference being "n/a".

    Obviously the text isn't going to work and I can't change the data type either so do any of you bright sparks have an idea of what I can do to get around this problem?

    All suggestions welcome!


    Cheers - Andy

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Crazy Request: "N/A" in Date Field!

    Originally posted by randycarpet
    My users input into several date fields on a form. Some fields are left blank, others are not. The designer who created the db used Date data types which is a shame as the users now want to somehow record blanks: their preference being "n/a".

    Obviously the text isn't going to work and I can't change the data type either so do any of you bright sparks have an idea of what I can do to get around this problem?

    All suggestions welcome!


    Cheers - Andy
    Leave the date field empty and when you test for it assign "N/A" ...

  3. #3
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Re: Crazy Request: "N/A" in Date Field!

    Originally posted by M Owen
    Leave the date field empty and when you test for it assign "N/A" ...
    The users want a visible marker that the field was left intentionally blank.

    I was thinking of changing the colour of the field if double-clicked but this still means I have to record that action in the record somewhere and I'm trying to avoid the need to create an associated field (to record the colour change) for every date field.


    Andy

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Crazy Request: "N/A" in Date Field!

    Originally posted by randycarpet
    The users want a visible marker that the field was left intentionally blank.

    I was thinking of changing the colour of the field if double-clicked but this still means I have to record that action in the record somewhere and I'm trying to avoid the need to create an associated field (to record the colour change) for every date field.


    Andy
    Okay ... Which way is this going? Are you trying to save data or are you trying to pull data?

    Can you clarify what you're trying to do?

  5. #5
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Re: Crazy Request: "N/A" in Date Field!

    Originally posted by M Owen
    Okay ... Which way is this going? Are you trying to save data or are you trying to pull data?

    Can you clarify what you're trying to do?
    I'm saving data that is input on a form. There are many date fields on the form (of date data type) and a lot are intentionally left blank. The users want to type in 'n/a' but that's not possible.

    I was thinking of toggling the colour of the field to indicate if the field is intentionally blank but of course I still need to record this action and I don't really want to have to create one of these fields for every date field that could be left intentionally blank.

    Is there a way perhaps to change the value of a date field from Null to Empty and back?

    A thought - thinking out loud: maybe I could record these actions in a separate table and pull that into the form record source???


    Regards - Andy

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Crazy Request: "N/A" in Date Field!

    Originally posted by randycarpet
    I'm saving data that is input on a form. There are many date fields on the form (of date data type) and a lot are intentionally left blank. The users want to type in 'n/a' but that's not possible.

    I was thinking of toggling the colour of the field to indicate if the field is intentionally blank but of course I still need to record this action and I don't really want to have to create one of these fields for every date field that could be left intentionally blank.

    Is there a way perhaps to change the value of a date field from Null to Empty and back?

    A thought - thinking out loud: maybe I could record these actions in a separate table and pull that into the form record source???


    Regards - Andy
    How are you saving? Is it thru the form itself (i.e. bound controls)? Or are you doing it thru code?

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    instead of storing a n/a value in a table, why dont u dynamically update the form based on the field's date value?

    e.g

    recordsource of textbox , iif(Me.textbox.Value = "" OR me.textbox.Value = "0", Me.textbox.Value = "n/a", )

    and to change the color of the textbox if its null

    then

    in the form's activate function, add this;

    if (me.textbox.value = "n/a") then
    me.textbox.backcolor = 65280 //green
    else
    me.textbox.backcolor = 16777215 //white
    end if

  8. #8
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Originally posted by Jerrie
    instead of storing a n/a value in a table, why dont u dynamically update the form based on the field's date value?

    e.g

    recordsource of textbox , iif(Me.textbox.Value = "" OR me.textbox.Value = "0", Me.textbox.Value = "n/a", )

    and to change the color of the textbox if its null

    then

    in the form's activate function, add this;

    if (me.textbox.value = "n/a") then
    me.textbox.backcolor = 65280 //green
    else
    me.textbox.backcolor = 16777215 //white
    end if
    Of course! The controls are unbound so this will work very well indeed... I'm embarrassed that I didn't see that myself!

    Cheers.

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    hehe np!

Posting Permissions

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