Subtract #Days from combo box that lists dates in mm/dd/yyyy format

[threadgills]

I have an unbound field set to a combo box (named txt_week) on a form that refers to a table called tbl_WE. The choices are 11/16/03, 11/23/03, etc. I have another unbound field I want to be able to set to a week before this date based upon the users choice. i.e., 11-16-03 - 7 = 11-9-03. I've tried to use =[txt_week]-7 but it gives me #error.

Any help is appreciated.

 

[DreamerZ]

Try this:

=DateSerial(DatePart("yyyy",[text74]),DatePart("m",[text74]),DatePart("d",[text74])-7)

It basically separates each part of the date selected, subtracts 7 from the day part and then puts it back in date format (mm/dd/yyyy).

You'll have to change the [text74] to the listbox selection index.

Hope that helps.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[threadgills]

Thank you DreamerZ!! I've never used the DateSerial or DatePart before. I understand where you've broken mm/dd/yyyy down individually and it worked like a charm!

 

[CajunCenturion]

There is also the DateAdd function, which subtracts as well simply by putting a negative number for the interval.

NewDate = DateAdd("d", -7, [txt_week])

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein