Validate data value within two data Fields

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

Guest

Hi!
Is there any method to do this:
I have one table: A_tab
With two fields: ini_date; end_date

Before I save the new record (with one date value), i want to validate if
the value date is, or isn't between any ini_date and end_date record.
Ex:dd/mm/yyyy
Type1....ini_date.........end_date
1011....31/01/2005 25/02/2005
1032....26/02/2005 27/02/2005
In the form's field I have:
Type: 2020
Inicial Date: 24/02/2005
End Date: 28/02/2005

What I want, is to generate an error because the 'Inicial Date' cannot be
between of any record on the table. Simply, I want that the 'Inicial Date' be
always major than the last period inserted. Can I use the method Dlookup?
 
If what you are saying is that you want the Inicial Date to be more recent
that any date in the table whether it be ini_date or end_date, then rather
than a DLookup, you would need to do a DMax on both fields and compare them.
 
Before I save the new record (with one date value), i want to validate
if the value date is, or isn't between any ini_date and end_date
record.
Click to expand...

DCount("*", "MyTable", _
"Ini_Date <= " & Format(MyDate,jetDateFormat) & _
"AND " & Format(MyDate, jetDateFormat) & " < end_date+1" _
)


If the value is >0 then there is at least one record where the new date
falls between the ini_date and end_date. You'll have to change all the
signs and the operator if you want to find >0 record where the date falls
outside.

Hope that helps


Tim F
 
Tim Ferguson said:
DCount("*", "MyTable", _
"Ini_Date <= " & Format(MyDate,jetDateFormat) & _
"AND " & Format(MyDate, jetDateFormat) & " < end_date+1" _
)


If the value is >0 then there is at least one record where the new date
falls between the ini_date and end_date. You'll have to change all the
signs and the operator if you want to find >0 record where the date falls
outside.
Click to expand...

It might help to indicate that jetDateFormat equals
"\#mm\/dd\/yyyy\#;;;\N\u\l\l", in case the poster isn't aware of that...
 
It might help to indicate that jetDateFormat equals
"\#mm\/dd\/yyyy\#;;;\N\u\l\l", in case the poster isn't aware of that...
Click to expand...

Oooh: I like the NULL bit. Neat trick.

All the best


Tim F
 
Back
Top