By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,406 Members | 2,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,406 IT Pros & Developers. It's quick & easy.

three field conditional HELP!

P: n/a
I have a db I am working on that contains three fields: Name, Time,
Date. I want to make Access bring up a warning box that tells the user
when they have a conflict.... meaning scheduling the same person for
the same day for the same time.

How can I accomplish this?

Aug 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Look for some info on "conditional where" clauses.

bobchabot wrote:
I have a db I am working on that contains three fields: Name, Time,
Date. I want to make Access bring up a warning box that tells the user
when they have a conflict.... meaning scheduling the same person for
the same day for the same time.

How can I accomplish this?
Aug 28 '06 #2

P: n/a
The complexity of the query will depend on how your tables are set up.
Why do you need date and time in separate fields? Any reason you can't
include them in a single date/time field formatted as a general date?
Albert Kallal has several interesting articles on writing scheduling
systems - I had a discussion with him about a reservations project I
worked on several years ago. You might want to look it up, because he
outlines how to identify conflicts. If a person can only be scheduled
for uniform blocks of time, it's pretty easy. You can query for
(Person, TimeBlock), and if you get no matches, it's open. Otherwise,
you get into some really fun date math in your query.

Not to worry, it's a fun brain teaser.

Aug 28 '06 #3

P: n/a
"bobchabot" <bo*******@yahoo.comwrote in
news:11**********************@m73g2000cwd.googlegr oups.com:
I have a db I am working on that contains three fields: Name,
Time, Date. I want to make Access bring up a warning box that
tells the user when they have a conflict.... meaning
scheduling the same person for the same day for the same time.

How can I accomplish this?
You are missing the duration of each appointment.
If you simply all appointments start on the half hour and last
one half hour, then you can simply pass the three parameters to
a dlookup() on the table and if it returns anything except null,
you have a conflict.

add this to the Before Update of the form you use to enter
appointments

Dim Isconflict as boolean
dim WhereClause as string
WhereClause ="Name = """ & me.name & """"
WhereClause = WhereClause & " AND "
WhereClause = WhereClause & "Time = #" & me.time & "#"
WhereClause = WhereClause & " AND "
WhereClause = WhereClause & "Date = #" & me.date & "#"

IsConflict = isnull(Dlookup("Name", "table", WhereClause)

if Isconflict then
Messagebox "No Way Josť!"
me.undo
end if

If you need anything more sophisticated, see
http://allenbrowne.com/appevent.html

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 28 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.