Placing conditions on Combo Box

[goaksmith]

I have a database that is being used to setup payroll deductions from an employee's account. I have a text box [CallInDate] that marks when the employee calls in to set up a deduction. The deductions can only start on the 1st and the 16th of the month. So, I have a combo box [cboStartDate] that is based on a table where I have stored the available dates (ie. 8/1/03 8/16/03 9/1/03..). The combo box is limited to the list that is in the table. This works fine. I would like the values in the combo box to change be greater than what the [CallInDate] is without having to change the table.

For Example.

[CallInDate] = 7/18/03
[cboStartDate] = 8/1/03, 8/16/03, 9/1/03...

but if

[CallInDate] = 8/18/03
[cboStartDate] = 9/1/03, 9/16/03, 10/1/03...

I hope I have explained this well enough. Any questions or suggestions would be appreciated.

 

[ghudson]

The DateAdd Function should do what you want. Try this for a simple test...

Dim sDate As Date
sDate = "08/15/2003"
MsgBox "Date = " & sDate
MsgBox "New Date = " & DateAdd("m", 1, sDate) 'add one month

Check the help files for the DateAdd Function for more info.

HTH

 

[goaksmith]

The DateAdd Function will only give me one option for the combo box correct? Because the [cboStartDate] can be any date in the future as long as it is on the 1st or the 16th of the month and it is after the [CallInDate]. Will a DateAdd Function still work in this case?

 

[goaksmith]

Let me see if I can state this another way. I need the [cboStartDate] to be a date that is after [CallInDate]. I also need [cboStartDate] to be either the 1st or the 16th of the month. But other than that there are no limitations on [cboStartDate].

So if [CallInDate] = 8/7/03 then
[cboStartDate] can equal 9/16/03 or 10/1/04 or 11/16/05

I have a table [tblStartDates] right now that holds the values for a year in advance. And the combo box pulls the values from this table. The problem with that is that I do not know how to limit the combo box to show only values in the future.

For example, with the example above I need 7/16/03 and 8/1/03 to be taken out as options in the combo box because the are before the [CallInDate] even though they are listed in the table.

There might be a better way of doing this that I am not thinking of. I am still at the beginning stages of designing and am willing to explore other options. Any help/advice would be greatly apprecitaed. Thank!

 

[ghudson]

Check out my attached sample. I am building the row source for the combo box with code. I am taking the date value keyed into the tbDate text box and adding a month to the month value. You will have to add some conditions for when it rolls into a new year but this should get you started.

HTH

 

[goaksmith]

Thanks, I will look at the example and see if I can get it to work. My knowledge of VBA is limited...But I will play around with it....Thanks again.

 

[goaksmith]

Where clause

After looking at everything I ended up adding a where clause to the row source and it appears to be working great. Thanks for your help.


SELECT DISTINCTROW [tblEffectiveDate].[EffectiveDate] FROM [tblEffectiveDate] WHERE [EffectiveDate]>[tblPayrollTable].[RequestDate];