greater than or equal to date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I would like to restrict [Date2] in the following way:
[Date2] is greater than or equal to [Date1].
How does one do this?

Thank you,
Diana
 
If Date1 and Date2 are fields in the same table, you can do it with a
table-level validation rule. To create a table-level validation rule, open
the table in design view, and choose Properties from the View menu. Enter an
expression such as ...

[Date2] >= [Date1] in the Validation Rule field, and enter the text you want
to display when the user attempts to enter data that would break this rule
in the Validation Text field.
 
Hi,

I tried your suggestion: [ResDateDepart]>=[ResDateArrive]
but I get the following error:

"Invalid SQL syntax--cannot use multiple columns in a column-level CHECK
constraint."

Is there another place where I can enter validation rules?

Thank you,
Diana

Brendan Reynolds said:
If Date1 and Date2 are fields in the same table, you can do it with a
table-level validation rule. To create a table-level validation rule, open
the table in design view, and choose Properties from the View menu. Enter an
expression such as ...

[Date2] >= [Date1] in the Validation Rule field, and enter the text you want
to display when the user attempts to enter data that would break this rule
in the Validation Text field.

--
Brendan Reynolds (MVP)


DianaS said:
Hello,

I would like to restrict [Date2] in the following way:
[Date2] is greater than or equal to [Date1].
How does one do this?

Thank you,
Diana
Click to expand...
Click to expand...
 
Please re-read my suggestion. The error message indicates that you tried to
create a field-level validation rule, and not a table-level validation rule
as I suggested.

BTW: If either or both of the fields may be Null, you'll need to modify the
validation rule to allow for that ....

([Date1]>=[Date2]) Or ([Date1] Is Null) Or ([Date2] Is Null)

--
Brendan Reynolds (MVP)

DianaS said:
Hi,

I tried your suggestion: [ResDateDepart]>=[ResDateArrive]
but I get the following error:

"Invalid SQL syntax--cannot use multiple columns in a column-level CHECK
constraint."

Is there another place where I can enter validation rules?

Thank you,
Diana

Brendan Reynolds said:
If Date1 and Date2 are fields in the same table, you can do it with a
table-level validation rule. To create a table-level validation rule,
open
the table in design view, and choose Properties from the View menu. Enter
an
expression such as ...

[Date2] >= [Date1] in the Validation Rule field, and enter the text you
want
to display when the user attempts to enter data that would break this
rule
in the Validation Text field.

--
Brendan Reynolds (MVP)


DianaS said:
Hello,

I would like to restrict [Date2] in the following way:
[Date2] is greater than or equal to [Date1].
How does one do this?

Thank you,
Diana
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Thank you for the correction. I made the table-level validation rule.
However, it does not seem to be working.

([ResDateDepart]>=[ResDateArrive]) Or ([ResDateArrive] Is Null) Or
([ResDateDepart] Is Null)

I entered incorrect data into the table to test the rule, but nothing
happened. The database accepts the incorrect data.

I appreciate your help. If you have any suggestions I will try them.

Diana

Brendan Reynolds said:
Please re-read my suggestion. The error message indicates that you tried to
create a field-level validation rule, and not a table-level validation rule
as I suggested.

BTW: If either or both of the fields may be Null, you'll need to modify the
validation rule to allow for that ....

([Date1]>=[Date2]) Or ([Date1] Is Null) Or ([Date2] Is Null)

--
Brendan Reynolds (MVP)

DianaS said:
Hi,

I tried your suggestion: [ResDateDepart]>=[ResDateArrive]
but I get the following error:

"Invalid SQL syntax--cannot use multiple columns in a column-level CHECK
constraint."

Is there another place where I can enter validation rules?

Thank you,
Diana

Brendan Reynolds said:
If Date1 and Date2 are fields in the same table, you can do it with a
table-level validation rule. To create a table-level validation rule,
open
the table in design view, and choose Properties from the View menu. Enter
an
expression such as ...

[Date2] >= [Date1] in the Validation Rule field, and enter the text you
want
to display when the user attempts to enter data that would break this
rule
in the Validation Text field.

--
Brendan Reynolds (MVP)


Hello,

I would like to restrict [Date2] in the following way:
[Date2] is greater than or equal to [Date1].
How does one do this?

Thank you,
Diana
Click to expand...
Click to expand...
Click to expand...
 
Hi,

The problem has been solved. Thank you for your help. Visual basic code
had to be changed so that the field would be checked after losing focus
(rather than at the time of saving database).

Diana

Brendan Reynolds said:
Please re-read my suggestion. The error message indicates that you tried to
create a field-level validation rule, and not a table-level validation rule
as I suggested.

BTW: If either or both of the fields may be Null, you'll need to modify the
validation rule to allow for that ....

([Date1]>=[Date2]) Or ([Date1] Is Null) Or ([Date2] Is Null)

--
Brendan Reynolds (MVP)

DianaS said:
Hi,

I tried your suggestion: [ResDateDepart]>=[ResDateArrive]
but I get the following error:

"Invalid SQL syntax--cannot use multiple columns in a column-level CHECK
constraint."

Is there another place where I can enter validation rules?

Thank you,
Diana

Brendan Reynolds said:
If Date1 and Date2 are fields in the same table, you can do it with a
table-level validation rule. To create a table-level validation rule,
open
the table in design view, and choose Properties from the View menu. Enter
an
expression such as ...

[Date2] >= [Date1] in the Validation Rule field, and enter the text you
want
to display when the user attempts to enter data that would break this
rule
in the Validation Text field.

--
Brendan Reynolds (MVP)


Hello,

I would like to restrict [Date2] in the following way:
[Date2] is greater than or equal to [Date1].
How does one do this?

Thank you,
Diana
Click to expand...
Click to expand...
Click to expand...
 
VBA code will work as long as users only enter data via your forms. It can't
prevent users from entering incorrect data directly into the tables, or
importing incorrect data from another source, or entering data by any other
method that by-passes your forms, and therefore your code.

I tested the validation rule that I posted before I posted it, and I have
just double-checked it again. It works.

--
Brendan Reynolds (MVP)


DianaS said:
Hi,

The problem has been solved. Thank you for your help. Visual basic code
had to be changed so that the field would be checked after losing focus
(rather than at the time of saving database).

Diana

Brendan Reynolds said:
Please re-read my suggestion. The error message indicates that you tried
to
create a field-level validation rule, and not a table-level validation
rule
as I suggested.

BTW: If either or both of the fields may be Null, you'll need to modify
the
validation rule to allow for that ....

([Date1]>=[Date2]) Or ([Date1] Is Null) Or ([Date2] Is Null)

--
Brendan Reynolds (MVP)

DianaS said:
Hi,

I tried your suggestion: [ResDateDepart]>=[ResDateArrive]
but I get the following error:

"Invalid SQL syntax--cannot use multiple columns in a column-level
CHECK
constraint."

Is there another place where I can enter validation rules?

Thank you,
Diana

:

If Date1 and Date2 are fields in the same table, you can do it with a
table-level validation rule. To create a table-level validation rule,
open
the table in design view, and choose Properties from the View menu.
Enter
an
expression such as ...

[Date2] >= [Date1] in the Validation Rule field, and enter the text
you
want
to display when the user attempts to enter data that would break this
rule
in the Validation Text field.

--
Brendan Reynolds (MVP)


Hello,

I would like to restrict [Date2] in the following way:
[Date2] is greater than or equal to [Date1].
How does one do this?

Thank you,
Diana
Click to expand...
Click to expand...
Click to expand...
 
Back
Top