Disallowing Specific Dates from being entered. 1

[CharlieT302]

Hi Folks,

I have a form where deliveries are scheduled. Specific vendors may not be able to deliver on certain dates (the dates will change per vendor). I need the dbase to disallow an entry if the entered date is one of those disallowed.

My thought:
Currently, I have a table that stores each vendor's ID and disallowed dates. It's a relational table; multiple dates can be entered per vendor. I would like Access to check the entire date set for a particular ID to decide if it can accept a date.

Users need to be able to alter the disallowed dates each year, so the "Validation Rule" property is not a good match unless it can somehow be tied to a table.

Am I on the right track?

 

[SkipVought]

Hi,

A table is a very good approch. Check out the Not In (list) construct.

Skip,

_[red][/red]

 

[CharlieT302]

Thanks Skip,

But...I can't seem to locate this. Is it a function, property???

 

[PHV]

It was a SQL tip.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

Thanks PHV,

I found an SQL reference for this topic, but it is presented from the standpoint of retrieving...although I do not work with SQL that much.

I need the user to enter a date in a date field and have Access check a list of dates in another table. If the date exists in the table; disallow it. From there I could come up with some error message, which is not a big deal.

Both tables, currently have a VenID field in common.

 

[PHV]

Something like this ?
If Not IsNull(DLookUp("VenID", "yourTable", "VenID=" & Me![VenID] & " AND DateField=#" & Me![DateControl] & "#")) Then
MsgBox "some error message"
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

I think this is what I want, except that I keep getting a "data type mismatch in criteria expression" error.

The data types of both fields (in each table) matches. I believe my code is fine:

If Not IsNull(DLookup("Site_ID", "tbl_disallow_dates", "Site_ID=" & Me![Site_ID] & " AND Date=#" & Me![Date] & "#")) Then
MsgBox "Services are not provided this date"

End If

 

[PHV]

I guess that Site_ID is NOT numeric, so try this:

If Not IsNull(DLookup("Site_ID", "tbl_disallow_dates", "Site_ID=[!]'[/!]" & Me![Site_ID] & "[!]'[/!] AND [Date]=#" & Me![Date] & "#")) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

That was it. Thank much, this was a great help.