removing zeros

[simon4amiee]

How do I strip the zeros from a Date field.

My export to CSV shows the date as 01/01/2015, I need it to show 1/1/2015

Any ideas

 

[gemma-the-husky]

I struggle to see why it would make a difference?
1/1/2015 is not a valid date as far as most MS apps are concerned. 01/01/2105 is a valid date.

However, if you do want that, you will probably need to change the query that you are exporting to mask the value in the way you want. You may even need to manually generate the csv.

 

[simon4amiee]

I struggle to see why it would make a difference?
1/1/2015 is not a valid date as far as most MS apps are concerned. 01/01/2105 is a valid date.

However, if you do want that, you will probably need to change the query that you are exporting to mask the value in the way you want. You may even need to manually generate the csv.

Totally agree and understand, however unfortunately this is the format it must be in as the CSV get run through a Toolkit and having a valid date there makes it fall over, think the software is from 1066 or something. Either way Im trying Trim, Replace etc and struggling to find the right combination.

 

[JANR]

Have you tried the Format() function?

Format(YourDatefield,"d\/m\/yyyy")
or
Format(yourdatefield,"m\/d\/yyyy")

depended on which "number" is day and which "number" is month

JanR

 

[sneuberg]

Consider creating a function that you could put in your query as an expression that would

1. Convert the date to a string. I think CStr will do that.
2. Using the Split function, splitting on "/"
3 Use the Left function to test the first two array elements for zeros and convert accordingly
4 Concatenate the array elements back to together into a string
5 Return the result

 

[JANR]

@sneuberg

Why go to all that trouble, Format()-function already turns the date into a string.

JanR

 

[sneuberg]

My export to CSV shows the date as 01/01/2015, I need it to show 1/1/2015

I created a table with a date/time field and it shows and exports the dates without these leading zeros. Where are these lead zeros coming from? Do you have a format applied somewhere that is doing it?

 

[arnelgp]

maybe this might help, add a reference to Microsoft Scripting Runtime.

Public Sub subDelLeadingZeroInDate(strFullPathNameAndExtension As String)
    ' open and edit text file
    Dim objFSO As Scripting.FileSystemObject
    Dim objText As Scripting.TextStream
    Dim strContent As String
    Dim strNewText As String
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objText = objFSO.OpenTextFile(strFullPathNameAndExtension, ForReading)
    
    While Not objText.AtEndOfStream
        strContent = objText.ReadLine
        strNewText = strNewText & RemoveLeadingZeroFromDate(strContent) & vbCrLf
    Wend
    
    objText.Close
    
    Set objText = objFSO.OpenTextFile(strFullPathNameAndExtension, forWriting)

    objText.Write strNewText
    objText.Close
    Set objFSO = Nothing

End Sub
Private Function RemoveLeadingZeroFromDate(ByVal strContent As String) As String
Dim i As Integer
For i = 1 To 9
    strContent = Replace(strContent, "0" & i & "/", i & "/")
    strContent = Replace(strContent, "/" & "0" & i & "/", "/" & i & "/")
Next
RemoveLeadingZeroFromDate = strContent
End Function

 

[sneuberg]

@sneuberg

Why go to all that trouble, Format()-function already turns the date into a string.

JanR

I didn't see your post before I posted mine. Since gemma-the-husky didn't suggest using format I didn't think that would solve it.

 

[gemma-the-husky]

I didn't check. I just assumed format (date,"m/d/yyyy") was either invlaid, or would do the same as ("mm/dd/yyyy")

from the foregoing, I assume a 2 digit date/month remain 2 digits.

 

[simon4amiee]

Thanks for all the comments everyone, some really helpful things to work with.

The issue is that although initially a date/time field in the table. When I use the right click Export options to CSV, select Delimited, Comma separated and to include headers. The date/time field loses the zeros in this process.

However what i was trying to do is automate this process without having to right click export all the time, so I created a Macro and used the TransferText option and Export Delimited. This option though included the zero's. This puzzled me as I though both were the same process.

 

[simon4amiee]

Finally got this, was as simple of saving the Spec and using that in the macro, thanks for the support anyway