Access validation rule for a column that could be: a date, a specific text string, or null

Question

I have a column that can either be:

1. A short date: DD/MM/YYYY
2. The string: "N/A"
3. Null

Can I use the Validation rule field in the table to enforce this? If so how do i do it?

Answer 1

It might be a bit fussy to try and do the validation with a single validation rule at the field level, but since you are using Access 2010 you could do it with a Before Change data macro:

The XML source code for that macro is

<?xml version="1.0" encoding="utf-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
  <DataMacro Event="BeforeChange">
    <Statements>
      <Action Collapsed="true" Name="SetLocalVar">
        <Argument Name="Name">dateStringIsValid</Argument>
        <Argument Name="Value">False</Argument>
      </Action>
      <ConditionalBlock>
        <If>
          <Condition>IsNull([dateString])</Condition>
          <Statements>
            <Action Collapsed="true" Name="SetLocalVar">
              <Argument Name="Name">dateStringIsValid</Argument>
              <Argument Name="Value">True</Argument>
            </Action>
          </Statements>
        </If>
        <ElseIf>
          <Condition>UCase([dateString])=&quot;N/A&quot;</Condition>
          <Statements>
            <Comment>force to uppercase for consistency</Comment>
            <Action Collapsed="true" Name="SetField">
              <Argument Name="Field">dateString</Argument>
              <Argument Name="Value">UCase([dateString])</Argument>
            </Action>
            <Action Collapsed="true" Name="SetLocalVar">
              <Argument Name="Name">dateStringIsValid</Argument>
              <Argument Name="Value">True</Argument>
            </Action>
          </Statements>
        </ElseIf>
        <ElseIf>
          <Condition>Len([dateString])=10</Condition>
          <Statements>
            <Action Collapsed="true" Name="SetLocalVar">
              <Argument Name="Name">ddStr</Argument>
              <Argument Name="Value">Mid([dateString],1,2)</Argument>
            </Action>
            <Action Collapsed="true" Name="SetLocalVar">
              <Argument Name="Name">mmStr</Argument>
              <Argument Name="Value">Mid([dateString],4,2)</Argument>
            </Action>
            <Action Collapsed="true" Name="SetLocalVar">
              <Argument Name="Name">yyyyStr</Argument>
              <Argument Name="Value">Mid([dateString],7,4)</Argument>
            </Action>
            <Action Collapsed="true" Name="OnError">
              <Argument Name="GoTo">Next</Argument>
            </Action>
            <Action Collapsed="true" Name="SetLocalVar">
              <Argument Name="Name">newDate</Argument>
              <Argument Name="Value">DateSerial(Val([yyyyStr]),Val([mmStr]),Val([ddStr]))</Argument>
            </Action>
            <Action Collapsed="true" Name="OnError">
              <Argument Name="GoTo">Fail</Argument>
            </Action>
            <ConditionalBlock>
              <If>
                <Condition>[MacroError].[Number]=0</Condition>
                <Statements>
                  <Comment>make sure DateSerial() hasn&#39;t converted an invalid date to a valid one (e.g. 32/01/2014 -&gt;
                  01/02/2014)</Comment>
                  <ConditionalBlock>
                    <If>
                      <Condition>(Year([newDate])=Val([yyyyStr])) And (Month([newDate])=Val([mmStr])) And
                      (Day([newDate])=Val([ddStr]))</Condition>
                      <Statements>
                        <Comment>reassemble to ensure consistent separators</Comment>
                        <Action Collapsed="true" Name="SetField">
                          <Argument Name="Field">dateString</Argument>
                          <Argument Name="Value">[ddStr] &amp; &quot;/&quot; &amp; [mmStr] &amp; &quot;/&quot; &amp;
                          [yyyyStr]</Argument>
                        </Action>
                        <Action Collapsed="true" Name="SetLocalVar">
                          <Argument Name="Name">dateStringIsValid</Argument>
                          <Argument Name="Value">True</Argument>
                        </Action>
                      </Statements>
                    </If>
                  </ConditionalBlock>
                </Statements>
              </If>
            </ConditionalBlock>
          </Statements>
        </ElseIf>
      </ConditionalBlock>
      <ConditionalBlock>
        <If>
          <Condition>[dateStringIsValid]=False</Condition>
          <Statements>
            <Action Collapsed="true" Name="RaiseError">
              <Argument Name="Number">1</Argument>
              <Argument Name="Description">dateString is not valid.</Argument>
            </Action>
          </Statements>
        </If>
      </ConditionalBlock>
    </Statements>
  </DataMacro>
</DataMacros>

(For information on how to transfer data macro XML code into and out of an Access database, see the question here.)

---

By the way, further to my answer to your earlier question here, the following are just a couple of reasons why storing your date/text information in a single (text) field might still not be such a great idea, even with all of this fancy validation:

1. By storing the date values as text you are committing yourself to converting the text back to an actual date value any time you want to use the field value as a date (beyond just printing it verbatim). Not only is this a nuisance, but it could have significant performance implications (see point #3 below).

2. By "hard-wiring" the date format as dd/mm/yyyy you are forcing it on your users. These days people expect applications to respect their preferences, so if someone decides that they want to use, say, the yyyy/mm/dd format – and they have specified that in the Windows Control Panel – then they expect their applications to use it.

3. Perhaps most importantly, by storing the dates as dd/mm/yyyy text you essentially guarantee that any searching you do beyond a single-day literal dd/mm/yyyy value will not be sargable and will require a table scan. If you had settled on yyyy/mm/dd you could have at least done date range searches by straight string comparisons, but with dd/mm/yyyy (or even mm/dd/yyyy) strings your search performance will suffer.