We help IT Professionals succeed at work.

Adding a number and date using expressions in Access 03

I am creating a form in Access 03 and would like to add, number of days, and a predifined date using an expression.

example:
01-Jan-08 (predifined date) + 30 (number of days) = 31-Jan-08 (answer)

I have tried...
1) =DateAdd("d",[Days Given],[Start Date])
and
2) =[Start Date]+[Days Given]

neither seem to work.
Comment
Watch Question

Commented:
When you say "neither seem to work" do you mean you get an answer different from what you expect or that you get an error?

If an error, change the name of your text control to something that is not the name of a field in your recordsource.

If you are just getting a weird response, I suspect that [Start Date] is a text string not actually a date.  Try =DateAdd("D",[Days Given],CDate([Start Date]))
--
JimFive
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Make sure you're passing in a date. You might use CDate:

DateAdd("d", [Days given], CDate([Start Date]))

Author

Commented:
[Days given] is set as Med Date
[Start date] is set as Gen Number
[End date] is set as Med Date <-- This is the field that should show the new calculated date.

=DateAdd("D",[Days Given],CDate([Start Date])) <-- This didn't work
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Are you sure of this? Days Given should be a number, and Start Date a Date.

Also, the interval should be a lowercase "d" ...

Author

Commented:
[Days given] is set as Gen Number
[Start date] is set as Med Date
[End date] is set as Med Date <-- This is the field that should show the new calculated date.

=DateAdd("d",[Days Given],CDate([Start Date])) <-- This didn't work

Sorry about the typos in the last post. The above correctly reflects what I have.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Are these fields in your recordsource, or are they names of controls on your forms.

Author

Commented:
They are from the form
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Verify that you're getting the correct values. Set a breakpoint in your code, then use the Immediate window to check those values. Type this in the Immediate window:

?Me.[Days Given]

?Me.[Start Date]

?Me.[End Date]

Do each line individually in the Immediate window and verify that they do contain the values you expect.

Author

Commented:
I am not using code, I just use wizards and expressions for my fields. Also, changng pint value to 300.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Still: add a code module in the form's Open event.

1) In the Design view of the form, make sure the Form is selected
2) In the properties dialog, select the Open event, then select the Event Procedure
3) Click the build button - the small button that appears when you place your cursor in the event field
4) Add this to the Open event:

Sub Form_Open()
  Msgbox Me.[Days Given]
  MsgBox Me.[Start Date]
  Msgbox Me.[End Date]
End Sub

Now open the form .... you should get a series of messags.

Author

Commented:
I am not sure exactly what you want so I included a screenshot...is this correct?
Start-Date.png
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
No ... you need to be working in the Form object, and you would want the Open or Load event.

Commented:
> =DateAdd("d",[Days Given],CDate([Start Date])) <-- This didn't work

Can you please define didn't work.

Perhaps try:
=DateAdd("d",[Forms]![MyFormName]![Days Given], CDate([Forms]![MyFormName]![Start Date]))

Replacing [MyFormName] with your form name.
--
JimFive

Author

Commented:
LSM, I get a message box with the correct values for Start Date and for Days Given, but nothing happens for End Date.


Jim, I get a syntax error with your string.
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
I get the impression that you want to set the EndDate when the user sets (a) the StartDate and (b) the Number of Days?

If that's the case, then add this in your Form's code module:

Function SetEndDate()
 If nz(me.[Start Date],"")="" or Nz(me.[Days Given],0)=0 then Exit Function
  Me.[End Date]= DateAdd("d", Me.[Days Given], Me.[Start Date]
End function

Now call this function anywhere you need to update this, for example in the AfterUpdate event of Start Date and Days Given ....
Forced accept.

Computer101
EE Admin

Explore More ContentExplore courses, solutions, and other research materials related to this topic.