Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Converting string to date and date to string

Posted on 2009-04-09
12
262 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

856 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