How do I ask the user to enter the day of the week to search for?

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

Guest

I have a table containing dates of surgery; the field is [Date]. The user
wants a list of all surgeries done on a certain week day, for instance
"Thursday". How can I write a query that gets a list of all surgeries done
on the day of the week that the user enters when the query is run?
 
Penny,

I suggest you take a look at the DatePart( ) function.

Use a combo box on your form to display the days of the week, and hide the
numeric value of the day of the week (use that column as the bound column).
Then, add a criteria (WHERE) clause in your query which is uses the
datepart function and points to the combo box on your form, something like:

WHERE DatePart("w", [SurgeryDate]) = [Forms]![YourForm]![cboWeekDay]

HTH

Dale
 
One method is to use a calculated field

Field: Format([YourDateField],"dddd")
Critera: Like [Enter week day name] & "*"

That would prompt the user for the day name and they could enter W or Wed or
Wednesday. For weekends they could enter S (for Saturday and Sunday). For
Tuesday or Thursday they would need to enter the first two characters.

This should work fairly well with smaller datasets. If you are talking many
thousands of records, a different solution might be required.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
In the field of a query:

DayOfWeek: Format([date],"dddd")

In the Criteria:

[Enter the day of week such as Friday]

Up in Query, Parameter put in [Enter the day of week such as Friday] and in
the next drop down select Text.

Of course you can change [Enter the day of week such as Friday] to whatever
you wish. Just make sure that it matches exactly in both places.

The SQL would look something like:

PARAMETERS [Enter the day of week such as Friday] Text ( 255 );
SELECT tbSurgery.*
FROM tblSurgery
WHERE (((Format([Date],"dddd"))=[Enter the day of week such as Friday]));
 
Back
Top