Sorting by month

[Kathi]

I have a field in my database for a surgery date and the month, day, and year
are all part of one field. Now a user wants to be able to query by the
month. It's been too long since I took the class and I don't use it often
enough to know how to write a query to make this happen. Can anyone help me?

 

[John W. Vinson]

I have a field in my database for a surgery date and the month, day, and year
are all part of one field. Now a user wants to be able to query by the
month. It's been too long since I took the class and I don't use it often
enough to know how to write a query to make this happen. Can anyone help me?

Is this an Access Date/Time field? If so, you can use a criterion like

= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:]+1, 1)

Entering 2009 for the year and 5 for the month will get all records in May.

If the date is stored in some other datatype post back with more details and
perhaps an example.

 

[MGFoster]

I have a field in my database for a surgery date and the month, day, and year
are all part of one field. Now a user wants to be able to query by the
month. It's been too long since I took the class and I don't use it often
enough to know how to write a query to make this happen. Can anyone help me?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the Month() function and a Parameter:

Create a Parameter this_month as a Byte.
Create an Expression column: Month(surgery_date).
Set it's Criteria to =this_month.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSjr3T4echKqOuFEgEQLB4ACcDUIh4y4FjNfcI10jRjCnHquEPdgAn0o7
Ad1VlrYzZC8a79abhUOnbYBG
=PibQ
-----END PGP SIGNATURE-----

 

[Kathi]

That almost works. What this person really wants is to be able to see
everyone who had surgery during each month of all the years. So I want to
just put in the month but not the year. I tried deleting the "Enter year"
part of what you gave me and it won't accept it. Can you give me another one
that will just ask for the month?

Thanks. kf

I have a field in my database for a surgery date and the month, day, and year
are all part of one field. Now a user wants to be able to query by the
month. It's been too long since I took the class and I don't use it often
enough to know how to write a query to make this happen. Can anyone help me?

Is this an Access Date/Time field? If so, you can use a criterion like

= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:]+1, 1)

Entering 2009 for the year and 5 for the month will get all records in May.

If the date is stored in some other datatype post back with more details and
perhaps an example.

 

[John W. Vinson]

That almost works. What this person really wants is to be able to see
everyone who had surgery during each month of all the years. So I want to
just put in the month but not the year. I tried deleting the "Enter year"
part of what you gave me and it won't accept it. Can you give me another one
that will just ask for the month?

Hm. They don't care what year, they just want a count summing all records for
August 1998, August 2007, August 2004...?

If so, put a calculated field in the query

SurgeryMonth: Month([datefield])

This will have a value 1 for January ... 12 for December. Put a criterion on
this field of [Enter month number:] and you should be ok.

Note that to see a grid with years down the side, months across the top, and
numbers of patients at the intersection, you could do a Crosstab query using
Year([datefield]) as the Row Header and Month([datefield]) as the Column
Header.