Difference between two dates in Visual Basic

Posted on 2012-09-17
Medium Priority
Last Modified: 2012-09-17
Trying to calculate the difference between two dates. I have tried to use DateDiff like follows: DateDiff("d", Now, StDate) where StDate = 9/22/2012. When I execute this in the immediate window[?SetWeek2(9/22/2012)] I get -41169. I also output the value of StDate. It is 12:00:18 AM. Apparently StDate is not being evaluated as 9/22/2012. I am sure this is something elementary. Thanks in advance for the help.
Question by:StampIT
LVL 19

Accepted Solution

Ken Butters earned 1000 total points
ID: 38406257
I just ran a quick test in Access...:

Sub test()
    Dim x As Variant
    x = DateDiff("d", Now, #9/22/2012#)
End Sub

Open in new window

It assigned a value of 5 to x.

you have setweek(9/22/2012).... maybe you need to surround by # to indicate a date?
LVL 17

Expert Comment

ID: 38406269
In my example I force stNow to be the same format as the date stDate that I am trying to compare

Dim stDate As Date
Dim stNow As Date

    stDate = "9 / 22 / 2012"
    stNow = Format(Now(), "mm/dd/yy")
MsgBox (DateDiff("d", stNow, stDate))
LVL 29

Expert Comment

ID: 38406339
In calling your function SetWeek2(9/22/2012), you are not sending a date value but rather the quotient of 9 divided by 22 divided by 2012.  In order for Access to see this as a date it needs to be enclosed with # symbols just as buttersk mentioned.
LVL 52

Expert Comment

by:Gustav Brock
ID: 38406465
Don't mix up the format with the value.
It should read:

Dim datDate As Date
Dim datNow As Date
    datDate = #9/22/2012#
    datNow = Date


Author Closing Comment

ID: 38406815
It was as simple as that. Thank you.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

864 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