469,342 Members | 6,455 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

Is it possible to generate an "end date" after entering start date and term length?

Hello all,

I'm hoping to find an answer to a question I have using forms in Access 2007.

I have a form (source: table) that has three fields:

Start Date
Term (number of months)
End Date

Currently the user has to enter a start date, select a term from a drop down combo box, and then enter an end date. Is it possible to have it set up where after the term is selected in the combo box the end date is automatically generated?

Any help would be greatly appreciated! thanks!
Oct 29 '10 #1

✓ answered by NeoPa

This is perfectly possible, but the idea of storing values that are calculable from existing data is not good (See Database Normalisation and Table structures for more).

The code to achieve this is :
Expand|Select|Wrap|Line Numbers
  1. DateAdd('m', [Term], [Start Date])

13 4729
NeoPa
32,182 Expert Mod 16PB
This is perfectly possible, but the idea of storing values that are calculable from existing data is not good (See Database Normalisation and Table structures for more).

The code to achieve this is :
Expand|Select|Wrap|Line Numbers
  1. DateAdd('m', [Term], [Start Date])
Oct 29 '10 #2
Is there a certain way I should format the input for the start date or for the term in the combo box? The current format is:

Start Date

Data Type: Date/Time
Input Mask: 00/00/"20"00;;_

Term

Data Type: Text
lookup box with the terms manually entered, not linked to another table (e.g. "1 month", "3 months", "12 months" and so on...)

when i pasted your code into the control source for the end date field it returned "#ERROR" in the field, but I'm assuming there is something wrong with the two fields mentioned above.

thanks again!
Oct 29 '10 #3
NeoPa
32,182 Expert Mod 16PB
Tim, if you need a numeric value there - store a numeric value. You can probably extract a numeric value from the strings if you know exactly how they're formatted, but I can't as you haven't shared enough information with your three example values.

PS. The date as a date is fine and exactly as expected.
Oct 29 '10 #4
I got it to work! Obviously though there are issues with the data being stored, which you pointed out. I'm assuming there is no way to have it store that data?
Nov 12 '10 #5
NeoPa
32,182 Expert Mod 16PB
There is nothing to stop you storing the data, except the understanding that it will not help you, and will likely involve you in extra complications. There will be no benefit from it for you.

I'm sorry. I guess that's not what you want to hear, but it's truth so I have to say it.
Nov 13 '10 #6
No worries, I came across this issue earlier in the database but figured I would ask again...

Thanks again for the help!
Nov 16 '10 #7
TheSmileyCoder
2,321 Expert Mod 2GB
I would HIGHLY recommend changed your term text box to a combobox, in which the user can select a number of months. Having users manually typing things where precision is required is always a bad idea.
While it may be easy to write code to parse values such as "3 months" you will run into problems when you have users typing stuff such as "3", "3 mnths","3-6 months","3 months","7mngts" and so on.
Nov 17 '10 #8
NeoPa
32,182 Expert Mod 16PB
That's a good idea Smiley. I'd take it a step further though, and separate the value out from the units. It can be stored as a single text field if required, but the ComboBox would be better off simply allowing the selection of the units without any numerical values included.

Good thinking :-)
Nov 17 '10 #9
colintis
255 100+
As from the example Tim provided earlier, there should be a fixed amount of choices for the term. If so, its better off to make selection with a combo box or option radio buttons.
Nov 19 '10 #10
NeoPa
32,182 Expert Mod 16PB
You're right. Tim did start with a ComboBox rather than a TextBox control. Also, if there really is just a limited list of possible values, then a single ComboBox would be more appropriate. Separating them out would only be called for, for a large or unlimited number of possible values.
Nov 19 '10 #11
I do have the number of month choices set specifically in a combo box, however the resulting end date is too important with what we do to not have it stored, so I've gone ahead and removed the "auto-generate" function.

Numerous queries that use the end date as some sort of parameter that we need won't work (without more intense behind the scenes coding), so I think I'm going to leave it as a manual entry.

Thanks to everyone that replied, I really appreciate it!
Nov 23 '10 #12
Actually I take that back... I simply replaced the EndDate column headers in each of the queries with

Expand|Select|Wrap|Line Numbers
  1. EndDate: DateAdd('m',[Term],[StartDate])
and now they all work perfectly. Obviously as mentioned above having the data like this isn't the best way of doing it, however I feel that this is the best way to prevent the great deal of potential human error that could arise from manually computing the end date.

Thanks again guys!
Nov 23 '10 #13
NeoPa
32,182 Expert Mod 16PB
Tim Mullin:
Obviously as mentioned above having the data like this isn't the best way of doing it,
obviously doesn't really work in this context. In fact the statement is wholly insupportable. Not only is that way the best way of doing it, it is also the only way that makes sense, unless all the brains that worked on the concepts of database normalisation are wrong and don't know what they're talking about. I won't be putting any money on that one ;-)
Nov 24 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by sandy | last post: by
17 posts views Thread by David C. Ullrich | last post: by
reply views Thread by bruce | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.