By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Re: Use TBC in a date field

P: n/a
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.comwrote in message
news:7f**********************************@r66g2000 hsg.googlegroups.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.comwrote:
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...@myunrealbox.comwrote:
<paulquinlan...@hotmail.comwrote in message
news:e3**********************************@s50g2000 hsb.googlegroups.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 -
Jul 24 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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...@SeeSig.Invalidwrote:
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.comwrote in message

news:7f**********************************@r66g2000 hsg.googlegroups.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.comwrote:
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...@myunrealbox.comwrote:
<paulquinlan...@hotmail.comwrote in message
>news:e3**********************************@s50g200 0hsb.googlegroups.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 -
Jul 24 '08 #2

P: n/a
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.comwrote in message
news:28**********************************@a70g2000 hsh.googlegroups.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...@SeeSig.Invalidwrote:
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.comwrote in message

news:7f**********************************@r66g2000 hsg.googlegroups.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.comwrote:
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...@myunrealbox.comwrote:
<paulquinlan...@hotmail.comwrote in message
>news:e3**********************************@s50g200 0hsb.googlegroups.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 -
Jul 24 '08 #3

P: n/a
Thanks a lot guys, thats done the trick!

On Jul 24, 3:40*pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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.<paulquinlan...@hotmail.comwrote in message

news:28**********************************@a70g2000 hsh.googlegroups.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...@SeeSig.Invalidwrote:
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.comwrote in message
news:7f**********************************@r66g2000 hsg.googlegroups.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.comwrote:
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...@myunrealbox.comwrote:
<paulquinlan...@hotmail.comwrote in message
news:e3**********************************@s50g2000 hsb.googlegroups.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 -
Jul 24 '08 #4

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalidwrote 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...
Jul 24 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.