Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    Bristol, UK
    Posts
    2

    Question Unanswered: Validating a field that can be date or text

    I have a field called StartDate that can be either a date or the text string "TBC".

    I want to create a validation rule which checks this.

    I've tried the rule:
    "IsDate([StartDate]) or "TBC"
    and this allows "TBC" but doesn't allow a date.

    ANy ideas?

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    Make this try
    Code:
    If IsDate(Texte3) Or Texte3 = "TBC" Then
    MsgBox "toto"
    End If

  3. #3
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    Can you format the field as a date? If the date is unknown, you can enter an obvious fake, such as "9/9/9999".

    I've used this before, and in queries, I'd create an expression field to use in reports.

    Expr1: Iif ([StartDate] = #9/9/9999#,"TBC",[StartDate])

  4. #4
    Join Date
    Oct 2004
    Location
    Bristol, UK
    Posts
    2

    Solution found

    Thanks for replies.

    I wanted to keep the validation rule as part of the database engine (i.e. I wanted it to be in the "Validation Rule" Property of the field)So neither of 1st two replies sorted my problem.

    With trial and error, I've discovered while my original rule doesn't work:
    IsDate([StartDate]) Or ([StartDate]="TBC")

    The following does do what I want:
    (IsDate([StartDate])=True) Or ([StartDate]="TBC")

    This allows either a valid date or "TBC" to be entered.

    Strange but my problem is sorted.
    Thanks

Posting Permissions

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