Left Function for Dates 1

[jt463]

I have had a hard time finding a thread that speaks to my question exactly.

I have a date field that is formatted like:

10/1/2006

I am trying to extract everything to the left of the /2006.

I have tried the following:

ActDate = Left("" & day2rst!sch_current_ & "", InStr("" & day2rst!sch_current_ & "", "/200"))

This always pulls back the '/' before the 2006

I also tried:

ActDate = Left("" & day2rst!sch_current_ & "", 4)

Which worked great until I had a 10/11/2006 date. This returns '10/1'

I would like to tell the code to return everything to the left of the right 5 characters - which would always work since the date has a yyyy format.

I got lost when I tried to do a Left function with a nested Right function!

Any help would be greatly appreciated!

 

[Golom]

ActDate = Left(day2rst!sch_current_, InStrRev(day2rst!sch_current_,"/") - 1)

or

ActDate = Format(CDate(day2rst!sch_current_),"mm/dd")

Assuming that day2rst!sch_current_ is a TEXT string containing the formatted date.

If it is really a Date/Time field then

ActDate = Format(day2rst!sch_current_,"mm/dd")

should work.

 

[kjv1611]

Could you use the Split function, and split it into an array of 3?

Dim strDate() as String
strDate = Split(day2rst!sch_current_, "/")
'Then do what you want with the results..  something like..

strMonth = strDate(0)
strDay = strDate(1)
strYear = strDate(2)

'etc etc..

What do you think?

 

[fdcusa]

Hope this helps:

Option Compare Database
Option Explicit

'----------------------------------------------------------------
' Purpose: Parse the MM/DD portion from a Date Value.
'
' Use: ActDate = Date2md(txtDate):  Return MM/DD from given date
'      ActDate = Date2md()       :  Return MM/DD for current date
'
' Test in Intermediate Window    :  ?date2md("10/14/2006")
'                                   ?date2md("10/1/2006")
'                                   ?date2md("1/1/2006")
'                                   ?date2md("1/1/2")
'----------------------------------------------------------------
Public Function Date2md(Optional txtDate As String)
    If txtDate = "" Then txtDate = Now()
    Date2md = Format(txtDate, "mm/dd")
End Function

 

[missinglinq]

ActDate = Left(txtDate, Len(txtDate) - 5)

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[CaptainD]

Why not use Format(Date,"mm/dd/")

 

[missinglinq]

Actually, Format(Date,"mm/dd") he doesn't want the second "/". I threw in the use of Left because he asked about that originally, and thought it'd show how to use if for other applications as well.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[CaptainD]

Then this will work without the second /: Format(Date,"mm/dd")

 

[missinglinq]

Uh...isn't that what I said? In bold?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[WildHare]

IF this is a DATE-type field, then the MONTH and DAY functionswill work just as well:

Month("10/11/2006") returns "10"
Day("10/11/2006") returns "11"

Works just as well with "10/6/06" or "4/31/06" or "4/1/2006" or any combination thereof..



"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[jt463]

Thank you all for your suggestions. While I am sure that there were several that would have worked, I tried Golom's first suggestion:

ActDate = Format(day2rst!sch_current_, "mm/dd")

and it worked beautifully!

One note: when I left 'ActDate' defined as a 'Date', it still pulled back the entire date (including the year), but when I changed it to a 'String', it pulled back what I wanted.

Thanks again for your time.

 

[SkipVought]

Hi,

[red][b]ActDate[/b][/red] = Format(day2rst!sch_current_, "mm/dd")

raises a RED FLAG in my book.

I'd breath alot easier if it were coded...

[b]str[/b]ActDate = Format(day2rst!sch_current_, "mm/dd")

since it is a STRING and not a REAL DATE. You really need to understand and define strings and dates so thaqt there is absolutely no question of what you are working with. I would strongly advise against using string functions on dates, as REAL DATES are NUMBERS, and when you use STRING functions on REAL DATES, behind the scenes, it causes a conversion from DATE to STRING in accordance with your Regional Settings and then does the parsing. Why cause all that overhead, when you have other function that will do stuff directly?

Skip,

_[red][/red]