Combo Boxes for Dates

[LinusIT]

Hi all

I have a form where you select a customer, then type in the first day of the month you require and again for the end date. The problem is the user has to know how many days there are in the month which not everyone can remember. It's also a long winded way.

I have an idea but not sure how to implement:

Have two combo boxes on the form, one where you select the year and the other one for the month.

The report uses a query for it's data. The query get's the date information using:

Between [Forms]![f_account_statements]![BeginningDate] And [Forms]![f_account_statements]![EndingDate]

My idea is the user selects the year and then the month, then using on AfterUpdate function it populates two unbound text boxes with say:

Year: 2009
Month: September

BeginningDate: 01/09/2009
EndingDate: 30/09/2009

I hope this makes sense and someone can help.

 

[Access_guy49]

I think the much esier solution is to use the calender control.

You set up a calender on your form. When the user clicks it the first time, it populates the combobox with the "start date" then when they click the calender a second time (hopefully on the ending date, it will populate it's respective combo box.

to add a calender control there are alot of good sites that have pictures. but basically on your tool bar, you click on the additional tools button and select calender control.
It's near the top of the list.

Then on the "on click" event, you will place code something like this.

if isnull (me.startdate) then
me.startdate = calender1.value
else
me.enddate = calender1.value
endif

hope that gets you on the right track.

 

[SOS]

How about letting them choose what MONTH from a combo box where the month number is the bound column and the name displays and we do it for them like this:


Between DateSerial(Year(Date()), Forms!YourFormName.cboMonth, 1) And DateSerial(Year(Date()), Forms!YourFormName.cboMonth + 1, 0)

That will get you the first of the month to the end of the month (regardless of which month it is).

 

[Access_guy49]

How about letting them choose what MONTH from a combo box where the month number is the bound column and the name displays and we do it for them like this:


Between DateSerial(Year(Date()), Forms!YourFormName.cboMonth, 1) And DateSerial(Year(Date()), Forms!YourFormName.cboMonth + 1, 0)

That will get you the first of the month to the end of the month (regardless of which month it is).

That is so crazy....it just might work!

 

[SOS]

That is so crazy....it just might work!

Yeah, I use it for several things. DateSerial can be extremely handy. And the method for getting the end of the month is even more handy. You just add one to the current month and then use day 0 which backs it up one from the first of the month. Quite nifty really and I think I originally learned it from a post by RuralGuy.

 

[Rich]

Why not just give them a list of dates within the db from a combo box which is grouped by month and year anyway?

 

[SOS]

How about letting them choose what MONTH from a combo box where the month number is the bound column and the name displays and we do it for them like this:


Between DateSerial(Year(Date()), Forms!YourFormName.cboMonth, 1) And DateSerial(Year(Date()), Forms!YourFormName.cboMonth + 1, 0)

That will get you the first of the month to the end of the month (regardless of which month it is).

Actually, have to change the code a little because of the year selection. Just put the form reference in for the year where it says Year(Date()) and it should be good to go.

 

[LinusIT]

Fantastic, it works! Thank you so much, the end user are people who aren't computer literate so the easier I can make it, the better for them and myself.

You mentioned it needs changing so they can select the year aswell, can you help me out with this aswell please, I know a bit but obviously not enough

Thanks again, I was beginning to scrap the idea and go with the calendar which I wanted to avoid.

 

[Rich]

Whats to stop users from entering dates for periods where there is no data?

 

[LinusIT]

If they choose a month/year where there is no data then I've used the following to show an error message.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

 

[vbaInet]

If they choose a month/year where there is no data then I've used the following to show an error message.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Just a little note. I tend to count the records instead (using DCount) and prompt if necessary. Useful if you're using one form to open several reports.

 

[Rich]

If they choose a month/year where there is no data then I've used the following to show an error message.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Yes but you'll also have to trap the error 2501 you cancelled the previous operation etc. I've always worked on the principle that prevention is better than the cure and in this case its easier to apply the Prevention rather than start filling the db with code

 

[LinusIT]

Yes but you'll also have to trap the error 2501 you cancelled the previous operation etc. I've always worked on the principle that prevention is better than the cure and in this case its easier to apply the Prevention rather than start filling the db with code

I know this is off topic but could you explain a little further about the prevention or provide a link, anything for me to look at really. I see exactly what you mean, I've ended up putting more code in to hide error messages etc.

 

[vbaInet]

With my suggestion you don't open the report unless it has records and in effect you won't need to trap the 2501 error (for that purpose).

 

[Rich]

By prevention I mean that if they only had the option to select the month/year from records that already exist then there would be no problem having to grope around in the dark looking for them, as it were. A combo box based on a query would do that for you

 

[Rich]

Here's a very quick and dirty example of how to fill a combo based on existing dates, again preventing any further need to check the existence of records by code or slow domain functions

 

[LinusIT]

Thanks Rich, that's pretty handy. Doing it this way would only populate the combo box with available data based on the months

Next idea but again, don't know how to code.

You select a customer from the drop down menu, then it populates the Month & Year Combo boxes with whatever data is available from t_sales table.

How would I go about doing this please?

 

[Brianwarnock]

How about adding a where clause to the queries for the month and Year Combo source with the criteria based on the result of the customer combo.

Brian

 

[LinusIT]

The month and year combo boxes are just lists, they don't use queries at present.

Can anyone help with the SQL for the date combo box.

At a guess it would be something like:

select saleID, date from t_sales where custid = me.accountcustomer

I know that the above is far from correct but hopefully someone can help.

 

[LinusIT]

I have a similar problem with a different form now but along the same lines so didn't want to start a new thread.

I have a form with Jan - Dec buttons, these apply a form filter based on what button is pressed.

I'm trying to get the buttons to work using the same concept as previously mentionned but am having trouble.

If (.Month = 9) Then
            DoCmd.ApplyFilter "", "[MOT Due] Between DateSerial(cboYear, 9, 1) And DateSerial(cboYear, 9 + 1, 0)"

I can get it to work using:

DoCmd.ApplyFilter "", "[MOT Due] Between DateSerial(Year(Now), 9, 1) And DateSerial(Year(Now), 9 + 1, 0)"

But this only works for the current year, I'd like the user to be able to select the year using the Year combobox.

I have tried the following but to no avail

Me.cboYear
[cboYear]

Also if anyone can help with my previous post, that would be great. Thanks