Selecting Year & Month

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

Guest

Ok the end of the year is coming and I'm getting requests to alter my query.
Right now I have a query that selects the starting month, ending month, and
year from a form. How would I be able to select the starting month & year and
the ending month & year? This could be very simple but for some reason I
can't figure it out. I've attached the SQL from my current query below;

PARAMETERS [Forms]![frmCustomerSelect]![Text2] Long,
[Forms]![frmCustomerSelect]![Text4] Long;
SELECT tblRMA.Customer, tblRMA.RMA, tblRMA.SalesRep, tblRMA.[WO#-Line#],
tblRMA.[Date Notified], tblRMA.[Discrepant Qty], tblRMA.[U/M],
tblRMA.[Discrepancy Description], tblRMA.[Customer Expectation],
tblRMA.[Discrepancy Code], tblRMA.[Date Disposition Made],
tblRMA.[Disposition Code], tblRMA.[Credit Amount], tblRMA.[Raw Mat'l Cost],
tblRMA.[Labor Cost], tblRMA.[In / Out Frt Cost], tblRMA.[Misc Cost],
tblRMA.DateClosed, [Raw Mat'l Cost]+[Labor Cost]+[In / Out Frt Cost]+[Misc
Cost] AS [Total Cost], Year([Date Notified]) AS YearNum, Month([Date
Notified]) AS MonthNum
FROM tblRMA
WHERE (((tblRMA.Customer)=Forms!frmCustomerSelect!CustomerSelect) And
((Year([Date Notified]))=Forms!frmCustomerSelect!Text6) And ((Month([Date
Notified])) Between Forms!frmCustomerSelect!Text2 And
Forms!frmCustomerSelect!Text4))
ORDER BY Year([Date Notified]), Month([Date Notified]);
 
Dear Squirrel:

I would suggest you could use two combo boxes, one for the starting month
and year, and the other for the ending month and year.

Create a table with one column which is a date/time datatype. Put a date in
for the first day of each month. Use a query to extract these dates from
the table as the rowsource of the combo boxes. Use a custom format of the
date/time value that selects only the month and year portions. Order this
select query by the date/time value.

Use the Month and Year functions in the filters of your query.

Tom Ellison
 
Why don't you create two text boxes
txtStartDate
txtEndDate
Not only will you have unlimited flexibility, your controls will have names
that make sense.
 
Back
Top