Display an error if date entered is equal to or in between 2 dates

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

Guest

I have created an assignments form in which Training IDs are being reserved.
There is a StartDate and EndDate field. When a user enters a StartDate that
is equal to a StartDate in a porevious record OR the StartDate is in between
the StartDate and EndDate of a previous record, a message box displays that
the StartDate is invalid and a different StartDate needs to be entered. The
message box should appear after entering the StartDate and before moving on
to the next field.

Any help is greatly appreciated!
 
Create a query.
in your query criteria of the for the start date put
= [Forms]![NameOfForm]![startdate]
Click to expand...
and in the criteria for the enddate in the query put
<= [Forms]![NameOfForm]![stardate]
Create a macro. In the conditions put
DCount("*", "queryname") > 0
In the action put
Msgbox
in the message put "This date is taken"

Jim
 
Jim,

Thanks! I gave it a go and it didn't work. I double checked spellings and
syntax and I'm good there. I went to the StartDate control box properties
and told the macro to run OnExit and I also tried AfterUpdate.

Here's more info:

I have a Form with 2 subforms. The main Form shows the Environment used.
The first SubForm contains the IDs within the Environment and the 2nd SubForm
is to assign the IDs to a particular user and the dates (Start and End) the
IDs will be reserved.

The Query I created is based only on the Assignments table that contains the
date fields. in the same criteria row I entered the criteria as you said.
In my case I typed >=[Forms]![tblAssignments Subform]![StartDate] for
StartDate field
and <=[Forms]![tblAssignments Subform]![StartDate] for EndDate field. Do
you think the space in the subform name is the problem?

For the macro, I typed DCount("*","qryDateCheck")>0 in Conditions and
Actions I chose the Msgbox.

Thanks for anymore insight. This has been stumping me for a week now...

Jim/Chris said:
Create a query.
in your query criteria of the for the start date put
= [Forms]![NameOfForm]![startdate]
Click to expand...
and in the criteria for the enddate in the query put
<= [Forms]![NameOfForm]![stardate]
Create a macro. In the conditions put
DCount("*", "queryname") > 0
In the action put
Msgbox
in the message put "This date is taken"

Jim
Click to expand...
 
I did not know the date was in a subform. Try this in your
query criteria for the start date
= [Forms]![Mainformname]![subformname].[Form]![startdate]
Click to expand...
and in the criteria for the enddate in the query put
<= [Forms]![Mainformname]![subformname].[Form]![startdate]s
I like to use the expression builder to locate the
startdate. It prevents typing errors.

Jim
-----Original Message-----
Create a query.
in your query criteria of the for the start date put
= [Forms]![NameOfForm]![startdate]
Click to expand...
and in the criteria for the enddate in the query put
<= [Forms]![NameOfForm]![stardate]
Create a macro. In the conditions put
DCount("*", "queryname") > 0
In the action put
Msgbox
in the message put "This date is taken"

Jim
-----Original Message-----
I have created an assignments form in which Training IDs are being reserved.
There is a StartDate and EndDate field. When a user enters a StartDate that
is equal to a StartDate in a porevious record OR the StartDate is in between
the StartDate and EndDate of a previous record, a message box displays that
the StartDate is invalid and a different StartDate needs to be entered. The
message box should appear after entering the StartDate and before moving on
to the next field.

Any help is greatly appreciated!
Click to expand...
.
Click to expand...
 
Jim,

Thanks again. I re-did the query and in the subform, chaged the AfterUpdate
event to run the macro. I opened the form and typed in a date that would
conflict and it accepted it, with no message box. In the expression, >=
[Forms]![Mainformname]![subformname].[Form]![startdate], is .[Form] literal
or am I supposed to put another form name here?

My main form is frmEnvironments-ASSIGN and my two subforms are
sfrmMegaTrainIDs-ASSIGN and sfrmAssignments-ASSIGN. The Assignments subform
contains the start and end date fields. This is how I typed the expression:
= [Forms]![frmEnvironments-ASSIGN]![sfrmAssignments-ASSIGN].[Form]![StartDate]
Click to expand...

Does this look right to you? Thanks again for your help!

Jim/Chris said:
I did not know the date was in a subform. Try this in your
query criteria for the start date
= [Forms]![Mainformname]![subformname].[Form]![startdate]
Click to expand...
and in the criteria for the enddate in the query put
<= [Forms]![Mainformname]![subformname].[Form]![startdate]s
I like to use the expression builder to locate the
startdate. It prevents typing errors.

Jim
-----Original Message-----
Create a query.
in your query criteria of the for the start date put
= [Forms]![NameOfForm]![startdate]
Click to expand...
and in the criteria for the enddate in the query put
<= [Forms]![NameOfForm]![stardate]
Create a macro. In the conditions put
DCount("*", "queryname") > 0
In the action put
Msgbox
in the message put "This date is taken"

Jim
-----Original Message-----
I have created an assignments form in which Training IDs are being reserved.
There is a StartDate and EndDate field. When a user enters a StartDate that
is equal to a StartDate in a porevious record OR the StartDate is in between
the StartDate and EndDate of a previous record, a message box displays that
the StartDate is invalid and a different StartDate needs to be entered. The
message box should appear after entering the StartDate and before moving on
to the next field.

Any help is greatly appreciated!
Click to expand...
.
Click to expand...
Click to expand...
 
The best way is to use the Expression wizard.
Have the fors open
go to the database and open that query in design mode
Click on the criteria and select the expression wizard.(the
magic wand)
Click the plus sign on forms on the ledft side
click the plus sign on loaded forms
Click the plus sign on the form with the subforms
select the startdate filed on the right
The whole reference should appear at top
select ok
repeat for end date

What you typed may look right but this way will guarantee it.
Test the query with data that meets all options

Jim
-----Original Message-----
Jim,

Thanks again. I re-did the query and in the subform, chaged the AfterUpdate
event to run the macro. I opened the form and typed in a date that would
conflict and it accepted it, with no message box. In the expression, >=
[Forms]![Mainformname]![subformname].[Form]![startdate], is .[Form] literal
or am I supposed to put another form name here?

My main form is frmEnvironments-ASSIGN and my two subforms are
sfrmMegaTrainIDs-ASSIGN and sfrmAssignments-ASSIGN. The Assignments subform
contains the start and end date fields. This is how I typed the expression:
=
Click to expand...
[Forms]![frmEnvironments-ASSIGN]![sfrmAssignments-ASSIGN].[Form]![StartDate]

Does this look right to you? Thanks again for your help!

Jim/Chris said:
I did not know the date was in a subform. Try this in your
query criteria for the start date
= [Forms]![Mainformname]![subformname].[Form]![startdate]
Click to expand...
and in the criteria for the enddate in the query put
<= [Forms]![Mainformname]![subformname].[Form]![startdate]s
I like to use the expression builder to locate the
startdate. It prevents typing errors.

Jim
-----Original Message-----
Create a query.
in your query criteria of the for the start date put
= [Forms]![NameOfForm]![startdate]
and in the criteria for the enddate in the query put
<= [Forms]![NameOfForm]![stardate]
Create a macro. In the conditions put
DCount("*", "queryname") > 0
In the action put
Msgbox
in the message put "This date is taken"

Jim

-----Original Message-----
I have created an assignments form in which Training IDs
are being reserved.
There is a StartDate and EndDate field. When a user
enters a StartDate that
is equal to a StartDate in a porevious record OR the
StartDate is in between
the StartDate and EndDate of a previous record, a message
box displays that
the StartDate is invalid and a different StartDate needs
to be entered. The
message box should appear after entering the StartDate and
before moving on
to the next field.

Any help is greatly appreciated!

--
GEP
.

.
Click to expand...
Click to expand...
.
Click to expand...
 
I did that and then added the > and < respectively, still doesn't work. I
appreciate your help and effort though :)

FYI: I tried the AfterUpdate and OnExit events, neither triggered.

Jim/Chris said:
The best way is to use the Expression wizard.
Have the fors open
go to the database and open that query in design mode
Click on the criteria and select the expression wizard.(the
magic wand)
Click the plus sign on forms on the ledft side
click the plus sign on loaded forms
Click the plus sign on the form with the subforms
select the startdate filed on the right
The whole reference should appear at top
select ok
repeat for end date

What you typed may look right but this way will guarantee it.
Test the query with data that meets all options

Jim
-----Original Message-----
Jim,

Thanks again. I re-did the query and in the subform, chaged the AfterUpdate
event to run the macro. I opened the form and typed in a date that would
conflict and it accepted it, with no message box. In the expression, >=
[Forms]![Mainformname]![subformname].[Form]![startdate], is .[Form] literal
or am I supposed to put another form name here?

My main form is frmEnvironments-ASSIGN and my two subforms are
sfrmMegaTrainIDs-ASSIGN and sfrmAssignments-ASSIGN. The Assignments subform
contains the start and end date fields. This is how I typed the expression:
=
Click to expand...
[Forms]![frmEnvironments-ASSIGN]![sfrmAssignments-ASSIGN].[Form]![StartDate]

Does this look right to you? Thanks again for your help!

Jim/Chris said:
I did not know the date was in a subform. Try this in your
query criteria for the start date
= [Forms]![Mainformname]![subformname].[Form]![startdate]
and in the criteria for the enddate in the query put
<= [Forms]![Mainformname]![subformname].[Form]![startdate]s
I like to use the expression builder to locate the
startdate. It prevents typing errors.

Jim

-----Original Message-----
Create a query.
in your query criteria of the for the start date put
= [Forms]![NameOfForm]![startdate]
and in the criteria for the enddate in the query put
<= [Forms]![NameOfForm]![stardate]
Create a macro. In the conditions put
DCount("*", "queryname") > 0
In the action put
Msgbox
in the message put "This date is taken"

Jim

-----Original Message-----
I have created an assignments form in which Training IDs
are being reserved.
There is a StartDate and EndDate field. When a user
enters a StartDate that
is equal to a StartDate in a porevious record OR the
StartDate is in between
the StartDate and EndDate of a previous record, a message
box displays that
the StartDate is invalid and a different StartDate needs
to be entered. The
message box should appear after entering the StartDate and
before moving on
to the next field.

Any help is greatly appreciated!

--
GEP
.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Gus I am at a loss as to why it is not working. If you
want to send me a stripped down copy of your db I would be
glad to look at it. You can send it to, (Remove the spaces)
g a n d h a w k @ g m a i l . c o m

Jim
 
Back
Top