Date Range (year from entered date)

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

Guest

In an Access query,

I have used
between [message 1] and [message 2]
to prompt users for a particular date range.

Now I need to prompt for a year's worth of data from a date they select,
without having them enter two dates.

Even better would be to allow this to happen automatically based on a date
tied to a drop down window in an exisiting form.

(The end result is a series of calculations that will appear in a form near
the data they've entered for that date.)
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

PARAMETERS Forms!frmCriteria!cboDate Date;
SELECT *
FROM table_name
WHERE date_column BETWEEN DateAdd("y", -1, Forms!frmCriteria!cboDate)
And Forms!frmCriteria!cboDate

Change the form name "frmCriteria" and control name "cboDate" to
whatever your form and control names are. Change the table & column
names to whater you are using.

See the Access Help articles on PARAMETERS for more info on form
references in queries.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQbeLxoechKqOuFEgEQIilwCgmbkgYN1P59MV3HfsjSJ1nzNKTOsAnR4C
y1WLVto0KT2l88zJgrK4zX3Q
=uZ82
-----END PGP SIGNATURE-----
 
In an Access query,

I have used
between [message 1] and [message 2]
to prompt users for a particular date range.

Now I need to prompt for a year's worth of data from a date they select,
without having them enter two dates.

Even better would be to allow this to happen automatically based on a date
tied to a drop down window in an exisiting form.

(The end result is a series of calculations that will appear in a form near
the data they've entered for that date.)
Click to expand...

Try

BETWEEN CDate([Enter date:]) AND DateAdd("yyyy", 1, CDate([Enter
date:]))

Instead of [Enter Date:] you could use

[Forms]![NameOfForm]![NameOfCombobox]

where the combo box contains the valid dates.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
You can use the dateadd function to get the end date.

Assuming
Form named "YourForm"
ComboBox name "ComboDates"

your criteria would look something like the following in the query grid

Field: YourDateField
Criteria: >= Forms![YourForm]![ComboDates] And < DateAdd("yyyy",1,Forms![YourForm]![ComboDates])
 
Back
Top