Question Sort by Month and Day

[rexmorgan]

Hi I have some data that includes records for each day of the year. The records will change from time to time assuming a new record is reached for a particular date. I would like to be able to do a query using two combo boxes 1)cboMonth 2)cboDay and using these two comboboxes do a query using those values. I do not wish the year to be a factor at all. Because these are records for each day and the year is insignificant in the search.

For example If I choose February in cboMonth and 26 in cboDay I want all records that occurred on February 26 regardless of year. The table that the data is stored in is called tblRecords. It includes an autonumber (key), location, record value and record date. The date is in the 1/1/2012 style format.

I also want to be able to search only by month and have been successful and employing a somewhat cumbersome method of doing so. I have a combo box the same one called cboMonth, a text box (hidden) that converts the text value of month to a numeric month value and a command button that runs a query to search. The query SQL for that follows. SELECT tblRecords.StationName, tblRecords.HighMax, tblRecords.HighMaxDate
FROM tblRecords
WHERE (((Month(tblRecords.HighMaxDate))=[forms]![frmqry]![txtMonthNum]));

Thanks in advance for any help on this. I hope that I did not rattle on too long. I can upload a sample if it will help.

 

[Taruz]

Hi..

Try this..:

SELECT StationName, HighMax, HighMaxDate
FROM tblRecords
WHERE Month(HighMaxDate)=[forms]![frmqry]![txtMonthNum]
and
day(HighMaxDate)=[forms]![frmqry]![cboDay]


or more advanced..:

SELECT StationName, HighMax, HighMaxDate
FROM tblRecords
WHERE Month(HighMaxDate)=nz([forms]![frmqry]![txtMonthNum],Month(HighMaxDate))
and
day(HighMaxDate)=nz([forms]![frmqry]![cboDay],day(HighMaxDate))

 

[rexmorgan]

Thanks a million! That worked like a charm. BTW what is the NZ for in the sql?

 

[Taruz]

you're welcome

what is the NZ for in the sql?

if one boxes or both are empty, empty the box ignores..

greetings..