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

[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!

 

[Guest]

Would doing the check in the LostFocus event work?

Marvin

 

[Guest]

I suppose, but I need help creating the check. That is what i am stuck on...

 

[Jim/Chris]

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

 

[Guest]

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...

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

 

[Jim/Chris]

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!

.

 

[Guest]

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]

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

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!

.

 

[Jim/Chris]

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:

=

[Forms]![frmEnvironments-ASSIGN]![sfrmAssignments-ASSIGN].[Form]![StartDate]

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

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
.

.

.

 

[Guest]

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.

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:

=

[Forms]![frmEnvironments-ASSIGN]![sfrmAssignments-ASSIGN].[Form]![StartDate]

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

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
.

.

.

 

[Jim/Chris]

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