Data Validation of Dates

  • Thread starter Thread starter Tanya Shepherd
  • Start date Start date
T

Tanya Shepherd

I have two tables. One is member information and the
second contains Member Stay Information. On the second
table, I would love to make the primary key the Member ID
and Start Date. A member can have mulitple (stays) start
dates as long as the start dates for the member are not
duplicated. However, because of the way I need to set up
the form for user input this is not possible. The users
want to see the member info (member ID, name, etc) once
(main form) and all of the start date and associated info
in a continous form below (subform - linked by member
ID). So the primary key option is not working for me.
I've been trying to use data validation in the form but it
doesn't seem to work.

When I put in the following as the Validation Rule for the
Start Date field in the form, it accepts all data
including duplicates.

Not In (SELECT [Stay Data].[Stay Start]
FROM [Stay Data]
WHERE ((([StayData].[Mem #])=[Forms]![Member Stay Form]!
[Mem #])))

I know my problem is not the "IN" Statement because if I
type in the following, my validation works.

Not In (#01/01/2003#,#02/01/2003#,#03/01/2003#)

I think the problem is the way the SELECT statement is
returning the dates but I'm not sure. I know that field
validation rules cannot contain references to other fields
that is why I'm putting this validation on the control in
the form. Is it possible that MS ACCESS won't allow me to
reference the data element in the validation and considers
this a circular reference?

Any help would be greatly appreciated.

Tanya
 
What do you get when you try the SELECT statement all by itself in a query?
Is it returning the records that you expect?
 
OK - my error. You cannot use an SQL statement in a control's validation
rule. However, you can do what you want by using VBA code that runs on the
OnEnter event of the textbox and that sets the validation rule for you.

Put code similar to this on the textbox's Enter event:

Private Sub Start_Date_Enter()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strList As String

strList = ""
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [Stay Start] " & _
"FROM [Stay Data] WHERE [Mem #]=" & _
Me.[Mem #] & ";", dbOpenDynaset, dbReadOnly)
If rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
strList = strList & ("#" + CStr(rst![Stay Start]) + "#") & ";"
rst.MoveNext
Loop
strList = Left(strList, Len(strList) - 1)
If strList <> "" Then strList = "Not In (" & strList & ")"
End If
Me.[Start Date].ValidationRule = strList
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub


--
Ken Snell
<MS ACCESS MVP>

Tanya Shepherd said:
Ken,

The [Stay Start] field is a date/time field with a short
date format in the form. I did try to use the General
Date format to see if that made a difference and it did
not.

Tanya
-----Original Message-----
Based on what you've said works and what doesn't, then I'm going to guess
that the [Stay Start] field is not formatted as a date/time field. Assuming
that, try this statement in place of what you've been using:

Not In (SELECT "#" & [Stay Data].[Stay Start] & "#" FROM [Stay Data] WHERE
((([StayData].[Mem #])=[Forms]![Member Stay Form]![Mem #])))

--
Ken Snell
<MS ACCESS MVP>

Tanya Shepherd said:
Ken,

Yes, I am getting the expected results. In fact as a
double check to ensure that there were no syntax errors, I
created a query and copied the SQL statement from it once
I had the expected results.

I have also tried using this logic in the condition of a
macro:

[Stay Start] Not In (SELECT [Stay Data].[Stay Start]
FROM [Stay Data]WHERE ((([StayData].[Mem #])=[Forms]!
[Member Stay Form]![Mem #])))

When I try to exit the condition area, I am told by Access
that it cannot parse the expression. but if I type in

[Stay Start]Not In (#01/01/2003#,#02/01/2003#,#03/01/2003#)

this is accepted by Access.

If the values returned were strings then I would think I
might need to include quotes somewhere in the expression.
Should I be trying to include "#"s in the list and if so,
how?

Thanks,
Tanya

-----Original Message-----
What do you get when you try the SELECT statement all by
itself in a query?
Is it returning the records that you expect?

--
Ken Snell
<MS ACCESS MVP>

I have two tables. One is member information and the
second contains Member Stay Information. On the second
table, I would love to make the primary key the Member
ID
and Start Date. A member can have mulitple (stays)
start
dates as long as the start dates for the member are not
duplicated. However, because of the way I need to set up
the form for user input this is not possible. The users
want to see the member info (member ID, name, etc) once
(main form) and all of the start date and associated
info
in a continous form below (subform - linked by member
ID). So the primary key option is not working for me.
I've been trying to use data validation in the form but
it
doesn't seem to work.

When I put in the following as the Validation Rule for
the
Start Date field in the form, it accepts all data
including duplicates.

Not In (SELECT [Stay Data].[Stay Start]
FROM [Stay Data]
WHERE ((([StayData].[Mem #])=[Forms]![Member Stay Form]!
[Mem #])))

I know my problem is not the "IN" Statement because if I
type in the following, my validation works.

Not In (#01/01/2003#,#02/01/2003#,#03/01/2003#)

I think the problem is the way the SELECT statement is
returning the dates but I'm not sure. I know that field
validation rules cannot contain references to other
fields
that is why I'm putting this validation on the control
in
the form. Is it possible that MS ACCESS won't allow me
to
reference the data element in the validation and
considers
this a circular reference?

Any help would be greatly appreciated.

Tanya


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top