Date Fields

[Roger Bell]

I have three seperate fields; namely Day, Month, Year. When a certificate
is produced, the user has to enter the day, press Enter, then the Month, and
then the Year, so that they appear in the correct place. This is built on a
Parameter Query.
Is there any way the three fields can be combined in the Query so that the
user only has to type for eg: 1st January 2008 and then Enter to produce the
certificate?

Thanks for any help

 

[John W. Vinson]

I have three seperate fields; namely Day, Month, Year. When a certificate
is produced, the user has to enter the day, press Enter, then the Month, and
then the Year, so that they appear in the correct place. This is built on a
Parameter Query.
Is there any way the three fields can be combined in the Query so that the
user only has to type for eg: 1st January 2008 and then Enter to produce the
certificate?

Thanks for any help

Sure. Your table should contain ONLY a date/time field. The user can type "1
January 2008" or "1/1" or "Jan 1 08" or any of a plethora of other
recognizable date/time values.

On your Certificate report you would have three textboxes with control sources
all the same - this date/time field - with format properties "d", "mmmm", and
"yyyy" respectively to display 1, January, 2008.

 

[John Spencer]

If you can't change the structure of your table/query then you could use one
parameter and get parts of it using expressions like the following.

Day(CDate([Enter Date]))
Month(CDate([Enter Date]))
Year(CDate([Enter Date]))

If you want the Month formatted as a full month Name then
Format(CDate([Enter Date]),"mmmm")

The user will be prompted to Enter Date one time.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ken Sheridan]

You can easily change you table so that it includes a single column for the
date by adding a new column of date/time data type, CertificateDate say
(don't use Date as a column name, it’s the name of a built in function, as
are Day, Month, Year in fact, so these also are best avoided), and then
running the following update query:

UPDATE [YourTable]
SET CertificateDate =
CDATE([Year] & "-" & [Month] & "-" & [Day])
WHERE [Year] IS NOT NULL;

Testing for [Year] IS NOT NULL means any rows in the table without a date
entered will be ignored; otherwise an error would occur when the CDate
function is called. If you can't change the table design for any reason then
another way of querying by means of a singe parameter would be:

PARAMETERS [Enter date:] DATETIME;
SELECT * FROM [YourTable]
WHERE CDATE([Year] & "-" & [Month] & "-" & [Day])
= [Enter date:];

Note that it’s a good idea to always declare date/time parameters as if the
user enters a date in short date format as the parameter value then it could
be interpreted as an arithmetical expression rather than a date, and give the
wrong results.

Ken Sheridan
Stafford, England