[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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.
0
Computrus-User
Asked:
Computrus-User
  • 7
  • 6
  • 2
  • +1
1 Solution
 
JimFiveCommented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Make sure you're passing in a date. You might use CDate:

DateAdd("d", [Days given], CDate([Start Date]))
0
 
Computrus-UserAuthor 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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you sure of this? Days Given should be a number, and Start Date a Date.

Also, the interval should be a lowercase "d" ...
0
 
Computrus-UserAuthor 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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are these fields in your recordsource, or are they names of controls on your forms.
0
 
Computrus-UserAuthor Commented:
They are from the form
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
 
Computrus-UserAuthor Commented:
I am not using code, I just use wizards and expressions for my fields. Also, changng pint value to 300.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Computrus-UserAuthor Commented:
I am not sure exactly what you want so I included a screenshot...is this correct?
Start-Date.png
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No ... you need to be working in the Form object, and you would want the Open or Load event.
0
 
JimFiveCommented:
> =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
0
 
Computrus-UserAuthor 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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ....
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now