• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Converting string to date and date to string

I have declared a variable Private under Option Explicit etc.

I am trying to keep track of when a change occurs.

I have not been able to complete my testing because I get errors.

Private oseasonal as date

       If IsNull(Me.[seasonal_updated]) Or Nz(Me.[seasonal_updated]) = "" Then
          oseasonal_updated = CDate("00/00/00")
       Else
          oseasonal_updated = Me.seasonal_updated
       End If

I get a type mismatch on this line:           oseasonal_updated = CDate("00/00/00")
I get a type mismatch when I have tried           oseasonal_updated = " "     also.

I have not gotten to the section below that takes the date and converts to string:
If oseasonal <> Me.seasonal Then
   midno = mid
   blastupdate = olast_updated
   bupdateby = oupdated_by
   bchange = "Seasonal Updated was " & month(oseasonal) & "/" & day(oseasonal) & "/" & year(oseasonal)
   lastupdate = Me.LAST_UPDATED
   updateby = Me.UPDATED_BY
   change = "Seasonal Updated is " & & month(me.seasonal) & "/" & day(me.seasonal) & "/" & year(me.seasonal)
End If
I do not know if I have approach the date to string correctly.

Can anyone help ?
0
proffittware
Asked:
proffittware
  • 5
  • 4
  • 3
1 Solution
 
rockiroadsCommented:
dateserial is a good way to create dates

dateserial (yy, mm, dd) - args in numbers
0
 
proffittwareAuthor Commented:
Let me try and let you know.
0
 
rockiroadsCommented:
now there is no such thing as a 0/0/0 date, can u not store null instead?

Private oseasonal as variant

If IsNull(Me.[seasonal_updated]) Or Nz(Me.[seasonal_updated]) = "" Then
          oseasonal_updated = null
       Else
          oseasonal_updated = Me.seasonal_updated
       End If


now check to see if its null (isnull) before working on it
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GRayLCommented:
"00/00/00" has no meaning and will create an error.  CDate(0) does.  What is your private variable?  you do not need to juggle dates converted to strings and vice versa.

change = "Seasonal Updated is " & & month(me.seasonal) & "/" & day(me.seasonal) & "/" & year(me.seasonal)

should be:

change = "Seasonal Updated is " & Me!Seasonal

0
 
proffittwareAuthor Commented:
Well... the type mismatch was eliminated. But when I use
DateSerial(0, 0, 0) I get 11/30/1999.

I have tried twice and get the same result. This is Access 2000.
0
 
rockiroadsCommented:
No, u cant use 0's. Did u see my earlier post? I suggestd using null
0
 
rockiroadsCommented:
also you can use the format command either short date (to take sttings from control panel) or MM/DD/YYYY


Private oseasonal As Variant

       If IsNull(Me.[seasonal_updated]) Or Nz(Me.[seasonal_updated]) = "" Then
          oseasonal_updated = Null
       Else
          oseasonal_updated = Me.seasonal_updated
       End If

I get a type mismatch on this line:           oseasonal_updated = CDate("00/00/00")
I get a type mismatch when I have tried           oseasonal_updated = " "     also.

I have not gotten to the section below that takes the date and converts to string:
If IsNull(oseasonal) = False Then
    If oseasonal <> Me.seasonal Then
       midno = Mid
       blastupdate = olast_updated
       bupdateby = oupdated_by
       bchange = "Seasonal Updated was " & Format(oseasonal, "Short Date")
       lastupdate = Me.LAST_UPDATED
       updateby = Me.UPDATED_BY
       Change = "Seasonal Updated is " & Format(oseasonal, "Short Date")

0
 
proffittwareAuthor Commented:
Thanks...try ideas. Be Back.
0
 
proffittwareAuthor Commented:
rockiroad - the errors were gone, but the code did not pick up a difference between the old and the new if the old was set to Null using your example. But thanks.

GrayL - the Cdate(0) is the solution - it gives 12:00am as the old date but that is acceptable.
With the other examples - thanks.
0
 
GRayLCommented:
Try describing what you want to happen without the code.  Dates are always stored as a number, and it so happens that in the date algorithm used by Access, a value of 0 is  midnight 30 Dec 1899.
0
 
rockiroadsCommented:
I simply got that logic wrong

from this

If IsNull(oseasonal) = False Then
    If oseasonal <> Me.seasonal Then

to this will do it

If IsNull(oseasonal) = True or (IsNull(oseasonal) = False && oseasonal <> Me.seasonal) Then

I know you have already accepted it, I just thought I would tell you.

Did you at the very least find the use of format handy?


0
 
GRayLCommented:
Thanks, glad to help.  For further, press Ctl+G to get to the VBA Editor window and in Help type - DateSerial Function -  a good explanation of why you got 1999-11-30.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now