Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Date issue

  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Date issue

    I have a form with a field Called DateExited

    The field is a short date with an input mask of 99/00/0000;0;_

    I have the following code in order to check that the date exited into the field is not greater than today's date:

    If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then
    Call MsgBox("The exit date cannot be greater than the today's date." _
    & vbCrLf & "" _
    & vbCrLf & "Enter a date less than today's date." _
    , vbExclamation, "EXIT DATE GREATER THAN CURRENT DATE")
    Me.DateExited.Undo
    End If


    The message box will be shown if I do not have 'If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then'

    But if I leave the 'If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then' in the code the message box will not be displayed.

    What is wrong with the code?

    Thanks heaps for any assistance.

    KD

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am not sure I fully follow the description of the problem you have (where is the code running?), but I would use this code

    If Me.DateExited > Date Then

    or

    If [DateExited] > Date Then

    I don't know if that will cure the problem, but I think it is worth a try!


    MTB

  3. #3
    Join Date
    May 2002
    Posts
    157

    Date issue

    thanks for the suggestion but I had already tried that and others and it does not work.

    What I have is a form with a calendar button on it. After clicking on the calendar button the date is inserted into the DateComm field. If you click on the same button, the code checks to see if the DateComm field is NULL if not then the date has to be put into the ExitDate field. I want to check that the date entered into the ExitDate field is not greater than today's date. If it is then a message box should be displayed telling the db-user. Unfortunately the message box using the following code will not be displayed (although it will display if I remove 'If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then')

    The code I have in full is:


    Private Sub cmdCalDate_Click()
    On Error GoTo Err_cmdCalDate_Click

    If IsNull(Me.[DateComm]) Then
    'Enter todays date into the [DateComm] field
    Call CalendarFor([DateComm], "Set the start/end date")

    ElseIf IsNull(Me.[DateExited]) Then
    Call CalendarFor([DateExited], "Set the start/end date")
    If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then
    Call MsgBox("The exit date cannot be greater than the today's date." _
    & vbCrLf & "" _
    & vbCrLf & "Enter a date less than today's date." _
    , vbExclamation, "EXIT DATE GREATER THAN CURRENT DATE")
    Me.DateExited.Undo
    Else
    'Nothing
    End If
    Else
    'Nothing
    End If

    Exit_cmdCalDate_Click:
    Exit Sub

    Err_cmdCalDate_Click:
    MsgBox Err.Description
    Resume Exit_cmdCalDate_Click
    End Sub

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Karen,

    The easiest way to handle this problem is:
    Code:
    If CLng(DateToCheck) > CLng(Now) Then
    Have a nice day!

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    OK

    So we can find out what is going on, add this line

    Code:
    Call CalendarFor([DateExited], "Set the start/end date")
    
    MsgBox [DateExited], & " : " & Date & "  :  " & [DateExited] > Date
    
    If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then
    and let us know what it displays (if it doesn't fail due to Null value(s))?


    MTB

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I think the problem with the OP's code is that

    If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then

    is comparing a Date

    DateExited

    with a String

    Format(Now(), "dd/mm/yyyy")

    because Format() will always return a String, regardless of the original Datatype of the entity being formatted.

    There's really no reason to format Now(). A simple

    If Me.DateExited > Now() Then

    should work!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by Missinglinq View Post
    I think the problem with the OP's code is that

    If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then

    is comparing a Date

    DateExited

    with a String

    Format(Now(), "dd/mm/yyyy")

    because Format() will always return a String, regardless of the original Datatype of the entity being formatted.

    There's really no reason to format Now(). A simple

    If Me.DateExited > Now() Then

    should work!

    Linq ;0)>
    Yes, thats what I thought, also if DateExited is 'to-day' then Now() is always goining to be greater than DateExited (perhaps why the need to format as a date only) although you could use Int(Now()), which is what I used to do a long, long time ago, before I discovered the the Date function, but that is confusing the issue!


    MTB

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You're right, Mike! I'm in the middle of simultaneously rehabbing/re-installing Windows on two PCs and got distracted!

    Now() really should always be avoided unless there is a clear need for the time component.

    Date() = Now()

    is only true at Midnight on a given day.

    Linq ;0)>
    is only true at
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    May 2002
    Posts
    157

    Date issue

    Hi Everyone, thanks for responding so quickly to my call for help.

    I had problems with all of your suggestions, so had to sleep on the issue.

    Remember the field is called DateExited (short date with an input mask of 99/00/0000;0;_)

    Then I realised that back in the table I had entered a validation rule of:
    Is Null or <= Date()
    so I removed this and tried to test all over again.

    I cant beleive that I have wasted so much time on this and of course your time. I don't want you to work on this any more because I am now spoilt for choice as to which code I shall use, but am giving you the following for your information. I have found the following works:

    FIRST VERSION:
    Call CalendarFor([DateExited], "Set the start/end date")
    If Me.DateExited > Now() Then
    Call MsgBox("The exit date cannot be greater than the today's date." _
    & vbCrLf & "" _
    & vbCrLf & "Enter a date less than today's date." _
    , vbExclamation, "EXIT DATE GREATER THAN CURRENT DATE")
    Me.[DateExited].Value = Null

    SECOND VERSION:
    Call CalendarFor([DateExited], "Set the start/end date")
    If CLng([DateExited]) > CLng(Now) Then
    Call MsgBox("The exit date cannot be greater than the today's date." _
    & vbCrLf & "" _
    & vbCrLf & "Enter a date less than today's date." _
    , vbExclamation, "EXIT DATE GREATER THAN CURRENT DATE")
    Me.[DateExited].Value = Null

    THIRD VERSION:
    Call CalendarFor([DateExited], "Set the start/end date")
    'If Me.DateExited > Now() Then
    'If CLng([DateExited]) > CLng(Now) Then
    If Me.DateExited > Date Then
    Call MsgBox("The exit date cannot be greater than the today's date." _
    & vbCrLf & "" _
    & vbCrLf & "Enter a date less than today's date." _
    , vbExclamation, "EXIT DATE GREATER THAN CURRENT DATE")
    Me.[DateExited].Value = Null


    The following does not work - I beleive for the reasons explained by Missinglinq and MikeTheBike:

    Call CalendarFor([DateExited], "Set the start/end date")
    If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then
    Call MsgBox("The exit date cannot be greater than the today's date." _
    & vbCrLf & "" _
    & vbCrLf & "Enter a date less than today's date." _
    , vbExclamation, "EXIT DATE GREATER THAN CURRENT DATE")
    Me.[DateExited].Value = Null


    and the following does not work as I keep on getting a visual basic compile error - Expected Expression (MsgBox....... line shows in red font)
    :
    Call CalendarFor([DateExited], "Set the start/end date")
    MsgBox [DateExited], & " : " & Date & " : " & [DateExited] > Date
    If Me.DateExited > Format(Now(), "dd/mm/yyyy") Then


    THANKS HEAPS FOR ALL YOUR HELP
    ALL THE BEST
    KD

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Karen

    Glad you now have it working, but just for information I believe this line fails

    MsgBox [DateExited], & " : " & Date & " : " & [DateExited] > Date

    because I left a rogue comma in it, should be

    MsgBox [DateExited] & " : " & Date & " : " & [DateExited] > Date

    Provided DateExited is not Null it should be OK.


    MTB

  11. #11
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I can't help but think that the DateExited is not a date at all, but a string that's formatted to read like a date. Remember, dates can have math performed on them; formatted strings cannot.

    SL

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's a few calendar type examples from different authors in the code bank:

    http://www.dbforums.com/6274797-post25.html (by Nick)
    http://www.dbforums.com/6274828-post28.html (by pkstormy)
    http://www.dbforums.com/6274874-post30.html (by pkstormy)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    May 2002
    Posts
    157

    Thanks PK for the additional examples

    Thanks PK for the additional examples

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Karen Day View Post

    Remember the field is called DateExited (short date with an input mask of 99/00/0000;0;_)
    if its a datetime value then the format is irrelevant, as the underlying datastorage mechanism will store it as a datetime value. the format is used for displaying the date to the user.

    however if you have stored a date as a string value then you are asking for trouble.

    Access VBA knows about datetime.. the method used is a real number where the interger portion is the number fo days since (IIRC 01/01/1900), and the decimal part is the proportion of the day to proovided the time bit
    so xxx.5 = middday, .75=18:00 and so on 1/(24*60*60) equates to one second
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Aug 2012
    Posts
    19
    Date field Need Help for dates
    I want to put the restriction that if a date is greater than 1 week or less that 1 week from today date then a message should appear that the date is greater than one week or less than one week. In the form date will not be acceptable if date is> 1 week from today and same < than 1 week from today date

    Private Sub Date_BeforeUpdate(Cancel As Integer)
    If "Date" >= "Date()" Or "Date" <= "Date()" Then (no result only current date is acceptable)
    if date + 7 and date -7 than (no this wrong, I tried but failed)
    MsgBox "Wrong Date", vbOKCancel
    Cancel = True
    End If
    'Exit Sub
    End Sub

    Messange is correct. I tried my best but I could not get the result which I explained above. I request the seniors to help me like before. I need syntax of dates with both conditions that is less that 7 days from now or greater than from now. I hope that seniors will understand what is problem I am facing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •