Re: Use TBC in a date field

[Allen Browne]

Paul, you *really* need to head Stuart's advice.

The data types are crucially important in database fields. Non-date values
(like the 3 letters TBC) are not valid dates, and cannot be evaluated as
dates.

On the other hand, Null is a valid and very meaningful value. It will be
processed correctly when you do things like counting the number of dates in
the field, averaging, or finding the nearest date. Using a fake date (such
as 11/11/11) will skew those results badly.

Further, you can *format* the Null value to *display* in any way you want -
using the letters TBC if you wish.

If you do have a field that accepts TBC, then you don't have a date/time
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa************ @hotmail.comwro te in message
news:7f******** *************** ***********@r66 g2000hsg.google groups.com...
Sorry, i meant that with that format it would only accept null values?

Paul

On Jul 24, 2:24 pm, "paulquinlan... @hotmail.com"
<paulquinlan... @hotmail.comwro te:

Thanks Stuart

The problem, i have now is that when the user actually does type a
date it says "the value you entered isnt valid for this field" and the
only value it will accept is TBC.

Any ideas
Thanks
Paul

On Jul 24, 2:05 pm, "Stuart McCall" <smcc...@myunre albox.comwrote:

<paulquinlan... @hotmail.comwro te in message

news:e3******** *************** ***********@s50 g2000hsb.google groups.com...

Hi

I have a bound form that displays a whole load of dates, in several of
these i would like to be able to show "TBC" rather than a blank field.
I'm guessing i could store a date in the fields that would clearly not
be relevent eg. 11/11/1111.

But is it possible where a bound text box contains the date 11/11/1111
to get it to show "TBC"? Ideally i'd also like the users to be able to
type TBC and it automatically store the date 11/11/1111.

Thanks for any suggestions.
Paul

Try this: instead of 11/11/1111, use Null. Then set the format property
for
the textbox to:
;;;"TBC"

To store Null when the user types TBC: Put this code in the textbox's
BeforeUpdate event procedure:

If Me.TextboxName. Value = "TBC" Then
Me.TextboxName. Value = Null
End If- Hide quoted text -

- Show quoted text -

[paulquinlan100]

Thanks for all this, but when i set the format of the text box
to ;;;"TBC" it then correctly displays TBC when it contains a null
value, however, when the user trys to type an actual date into the
cell it says it isnt a valid for this field??

Thanks again
Paul

On Jul 24, 2:39*pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

Paul, you *really* need to head Stuart's advice.

The data types are crucially important in database fields. Non-date values
(like the 3 letters TBC) are not valid dates, and cannot be evaluated as
dates.

On the other hand, Null is a valid and very meaningful value. It will be
processed correctly when you do things like counting the number of dates in
the field, averaging, or finding the nearest date. Using a fake date (such
as 11/11/11) will skew those results badly.

Further, you can *format* the Null value to *display* in any way you want-
using the letters TBC if you wish.

If you do have a field that accepts TBC, then you don't have a date/time
field.

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<paulquinlan... @hotmail.comwro te in message

news:7f******** *************** ***********@r66 g2000hsg.google groups.com...
Sorry, i meant that with that format it would only accept null values?

Paul

On Jul 24, 2:24 pm, "paulquinlan... @hotmail.com"

<paulquinlan... @hotmail.comwro te:

Thanks Stuart

The problem, i have now is that when the user actually does type a
date it says "the value you entered isnt valid for this field" and the
only value it will accept is TBC.

Any ideas
Thanks
Paul

On Jul 24, 2:05 pm, "Stuart McCall" <smcc...@myunre albox.comwrote:

<paulquinlan... @hotmail.comwro te in message

>news:e3******* *************** ************@s5 0g2000hsb.googl egroups.com....

Hi

I have a bound form that displays a whole load of dates, in severalof
these i would like to be able to show "TBC" rather than a blank field.
I'm guessing i could store a date in the fields that would clearly not
be relevent eg. 11/11/1111.

But is it possible where a bound text box contains the date 11/11/1111
to get it to show "TBC"? Ideally i'd also like the users to be ableto
type TBC and it automatically store the date 11/11/1111.

Thanks for any suggestions.
Paul

Try this: instead of 11/11/1111, use Null. Then set the format property
for
the textbox to:
;;;"TBC"

To store Null when the user types TBC: Put this code in the textbox's
BeforeUpdate event procedure:

If Me.TextboxName. Value = "TBC" Then
Me.TextboxName. Value = Null
End If- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[Allen Browne]

What happens if you expand the first parts:
mm/dd/yyyy;mm/dd/yyyy;mm/dd/yyyy;"TBC"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<pa************ @hotmail.comwro te in message
news:28******** *************** ***********@a70 g2000hsh.google groups.com...
Thanks for all this, but when i set the format of the text box
to ;;;"TBC" it then correctly displays TBC when it contains a null
value, however, when the user trys to type an actual date into the
cell it says it isnt a valid for this field??

Thanks again
Paul

On Jul 24, 2:39 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

Paul, you *really* need to head Stuart's advice.

The data types are crucially important in database fields. Non-date values
(like the 3 letters TBC) are not valid dates, and cannot be evaluated as
dates.

On the other hand, Null is a valid and very meaningful value. It will be
processed correctly when you do things like counting the number of dates
in
the field, averaging, or finding the nearest date. Using a fake date (such
as 11/11/11) will skew those results badly.

Further, you can *format* the Null value to *display* in any way you
want -
using the letters TBC if you wish.

If you do have a field that accepts TBC, then you don't have a date/time
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<paulquinlan... @hotmail.comwro te in message

news:7f******** *************** ***********@r66 g2000hsg.google groups.com...
Sorry, i meant that with that format it would only accept null values?

Paul

On Jul 24, 2:24 pm, "paulquinlan... @hotmail.com"

<paulquinlan... @hotmail.comwro te:

Thanks Stuart

The problem, i have now is that when the user actually does type a
date it says "the value you entered isnt valid for this field" and the
only value it will accept is TBC.

Any ideas
Thanks
Paul

On Jul 24, 2:05 pm, "Stuart McCall" <smcc...@myunre albox.comwrote:

<paulquinlan... @hotmail.comwro te in message

>news:e3******* *************** ************@s5 0g2000hsb.googl egroups.com...

Hi

I have a bound form that displays a whole load of dates, in several
of
these i would like to be able to show "TBC" rather than a blank
field.
I'm guessing i could store a date in the fields that would clearly
not
be relevent eg. 11/11/1111.

But is it possible where a bound text box contains the date
11/11/1111
to get it to show "TBC"? Ideally i'd also like the users to be able
to
type TBC and it automatically store the date 11/11/1111.

Thanks for any suggestions.
Paul

Try this: instead of 11/11/1111, use Null. Then set the format
property
for
the textbox to:
;;;"TBC"

To store Null when the user types TBC: Put this code in the textbox's
BeforeUpdate event procedure:

If Me.TextboxName. Value = "TBC" Then
Me.TextboxName. Value = Null
End If- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[paulquinlan100]

Thanks a lot guys, thats done the trick!

On Jul 24, 3:40*pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

What happens *if you expand the first parts:
* * mm/dd/yyyy;mm/dd/yyyy;mm/dd/yyyy;"TBC"

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.<paulquinla [e-mail] mwrote in message

news:28******** *************** ***********@a70 g2000hsh.google groups.com...
Thanks for all this, but when i set the format of the text box
to ;;;"TBC" it then correctly displays TBC when it contains a null
value, however, when the user trys to type an actual date into the
cell it says it isnt a valid for this field??

Thanks again
Paul

On Jul 24, 2:39 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

Paul, you *really* need to head Stuart's advice.

The data types are crucially important in database fields. Non-date values
(like the 3 letters TBC) are not valid dates, and cannot be evaluated as
dates.

On the other hand, Null is a valid and very meaningful value. It will be
processed correctly when you do things like counting the number of dates
in
the field, averaging, or finding the nearest date. Using a fake date (such
as 11/11/11) will skew those results badly.

Further, you can *format* the Null value to *display* in any way you
want -
using the letters TBC if you wish.

If you do have a field that accepts TBC, then you don't have a date/time
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<paulquinlan... @hotmail.comwro te in message

news:7f******** *************** ***********@r66 g2000hsg.google groups.com....
Sorry, i meant that with that format it would only accept null values?

Paul

On Jul 24, 2:24 pm, "paulquinlan... @hotmail.com"

<paulquinlan... @hotmail.comwro te:

Thanks Stuart

The problem, i have now is that when the user actually does type a
date it says "the value you entered isnt valid for this field" and the
only value it will accept is TBC.

Any ideas
Thanks
Paul

On Jul 24, 2:05 pm, "Stuart McCall" <smcc...@myunre albox.comwrote:

<paulquinlan... @hotmail.comwro te in message

news:e3******** *************** ***********@s50 g2000hsb.google groups.com...

Hi

I have a bound form that displays a whole load of dates, in several
of
these i would like to be able to show "TBC" rather than a blank
field.
I'm guessing i could store a date in the fields that would clearly
not
be relevent eg. 11/11/1111.

But is it possible where a bound text box contains the date
11/11/1111
to get it to show "TBC"? Ideally i'd also like the users to be able
to
type TBC and it automatically store the date 11/11/1111.

Thanks for any suggestions.
Paul

Try this: instead of 11/11/1111, use Null. Then set the format
property
for
the textbox to:
;;;"TBC"

To store Null when the user types TBC: Put this code in the textbox's
BeforeUpdate event procedure:

If Me.TextboxName. Value = "TBC" Then
Me.TextboxName. Value = Null
End If- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

[Stuart McCall]

"Allen Browne" <Al*********@Se eSig.Invalidwro te in message
news:48******** *************** @per-qv1-newsreader-01.iinet.net.au ...

Clarification, Stuart:

These are numeric values, so the 3rd one is zero (not a zls.)

Dates can be zero (Dec 30 1999) or negative (before that.)

Right you are. Makes sense now you mention it. Thankyou.

However, after running a few tests, it seems that the middle two sections
are ignored when the data type is Date. At least I can't make it happen...