Solved

Converting string to date and date to string

Posted on 2009-04-09
12
258 Views
Last Modified: 2013-11-27
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
Comment
Question by:proffittware
  • 5
  • 4
  • 3
12 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24109036
dateserial is a good way to create dates

dateserial (yy, mm, dd) - args in numbers
0
 

Author Comment

by:proffittware
ID: 24109056
Let me try and let you know.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24109062
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
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 24109115
"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
 

Author Comment

by:proffittware
ID: 24109208
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24109249
No, u cant use 0's. Did u see my earlier post? I suggestd using null
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 65

Expert Comment

by:rockiroads
ID: 24109281
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
 

Author Comment

by:proffittware
ID: 24109434
Thanks...try ideas. Be Back.
0
 

Author Comment

by:proffittware
ID: 24109745
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24109751
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24109825
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24109840
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now