Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dateadd

Posted on 2016-11-19
3
Medium Priority
?
50 Views
Last Modified: 2016-11-19
Experts,

How would I add 4 months to [ValueDateDD]
[ValueDateDD]=DLookUp("ValueDate","tblDraws_Details1","ID = " & [DrawIDrpmt])

add 4 months:
=[ValueDateDD]+DateAdd("m",4,DLookUp("ValueDate","tblDraws_Details1","ID = " & [DrawIDrpmt]))
=[ValueDateDD]+DateAdd("m",4,[ValueDateDD])
both of those add many years and not 4 months.
0
Comment
Question by:pdvsa
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 41894085
try

[ValueDateDD]=DateSerial(Year([ValueDateDD]),Month([ValueDateDD])+4,Day([ValueDateDD]))
0
 

Author Closing Comment

by:pdvsa
ID: 41894091
nice.  thank you Rey..
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41894120
Your error is that you add ValueDate to itself plus four months.
That, of course, results in a date about 2016 + (2016 - 1899) = about 2136.

So your first expression is correct - just leave out [ValueDateDD]:

    =DateAdd("m",4,DLookUp("ValueDate","tblDraws_Details1","ID = " & [DrawIDrpmt] & ""))

However, if [ValueDateDD] holds the same value as you would look up - which I suspect it does - it is even simpler, as in your second expression:

    =DateAdd("m",4,[ValueDateDD])

So, as Rey would know if he had had his morning coffee, no need for a deroute via DateSerial.

/gustav
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question