query date question

[steve goodrich]

I have a database with 2 date fields on my form

Date from and date to

If I want to view today's list I run a query I have setup with the criteria
as follows



Date from date to

<=date() >=date()



Tomorrows list would be



Date from date to

<=date()+1 >=date()+1



How do I enter the criteria for a parameter query so I can enter any date
when prompted



I thought it would be something like



Date from date to

<=date() [enter date] >=date() [enter date]



But it doesn't work



Any help greatly appreciated



Steve

 

[Steve Schapel]

Steve,

I think it would be like this...

Date from date to
<=[enter date] >=[enter date]

I would not do this myself. I would put an unbound textbox on a form
that would be open when you are doing this, and the user can enter the
date required in the textbox. Then your query criteria is like this...

Date from date to
<=[Forms]![YourForm]![Textbox] >=[Forms]![YourForm]![Textbox]

 

[steve goodrich]

Steve,

I have amended my query as per your suggestion and all works fine. I have
also set a validation rule in my table for both date fields to >=date() so
the user can't enter a past date. How do I set the validation rule on the
"date to" field so a date can't be entered that is before the "date from"
date. I was experimenting and if the user enters the dates the wrong way
round then none of the queries work.

Steve

Steve,

I think it would be like this...

Date from date to
<=[enter date] >=[enter date]

I would not do this myself. I would put an unbound textbox on a form that
would be open when you are doing this, and the user can enter the date
required in the textbox. Then your query criteria is like this...

Date from date to
<=[Forms]![YourForm]![Textbox] >=[Forms]![YourForm]![Textbox]

--
Steve Schapel, Microsoft Access MVP

I have a database with 2 date fields on my form

Date from and date to

If I want to view today's list I run a query I have setup with the
criteria as follows



Date from date to

<=date() >=date()



Tomorrows list would be



Date from date to

<=date()+1 >=date()+1



How do I enter the criteria for a parameter query so I can enter any date
when prompted



I thought it would be something like



Date from date to

<=date() [enter date] >=date() [enter date]



But it doesn't work



Any help greatly appreciated



Steve

 

[Steve Schapel]

Steve,

In design view of the table, select 'Properties' from the View menu.
You can enter a table-level Validation Rule, like this...
[Date To]>[Date From]

 

[steve goodrich]

Steve
Thanks for the information, it does work, just one more question if I may,
I have the validation rule and message for the "date from" field set up in
the field properties section of my table in design view. When the
validation rule is broken I get the validation message as soon as the "date
from" field looses the focus.
I followed your suggestion and set the validation rule from the properties
of the table and it works ok but I don't get the validation warning message
until I try to go to the next record - It allows me to complete the form
before bringing up the warning.
Is there a way to set it up so I get the warning validation message as soon
as the "date to" field looses the focus the same as the "date from" field
Sorry to be a pain, but I'm trying with great difficulty to build this
database for someone who has even less experience than me - if that's
possible.
Many thanks for your time and patience
Steve

Steve,

In design view of the table, select 'Properties' from the View menu. You
can enter a table-level Validation Rule, like this...
[Date To]>[Date From]

--
Steve Schapel, Microsoft Access MVP

Steve,

I have amended my query as per your suggestion and all works fine. I
have also set a validation rule in my table for both date fields to
validation rule on the "date to" field so a date can't be entered that is
before the "date from" date. I was experimenting and if the user enters
the dates the wrong way round then none of the queries work.

 

[Steve Schapel]

Steve,

Yes, a table-level Validation Rule is activated at the point where the
record is saved.

So, in this case, you have two choices:
1. On the After Update event of the Date To control on the form, you
can force a save of the record, using code such as...
DoCmd.RunCommand acCmdSaveRecord
2. Remove the Validation Rule from the table, and put code like this on
the Date To control's Before Update event...
If Me.Date_To < Me.Date_From Then
MsgBox "Date To must be later than Date From"
Cancel = True
End If

 

[steve goodrich]

Steve
That works great.
Many thanks for all your help
Steve