Retaining zero's in conversion of date to text to date 2

[AlexCuse]

Hello All,

A co-worker grabbed me this morning for help on a project, and her question has puzzled the (heck) out of me. I've tried everything I could think of, and to no avail. Here is what she's trying to do:

1. Start with fields maildate1 & maildate2 (these fields have input masks set to show zeros ie (08/06/2006 rather than 8/6/2006)

2. Concatenate these fields to a date range (08/06/2006-08/08/2006) in a make table query.

3. The resulting table is the recordsource of a form, and the date range can be updated by the user. This input then needs to be used to update the original date fields. I recommended she achieve this using the following syntax, as regular formatting functions didn't seem to be working:

CDate(Left([maildate_1],2) & "/" & Mid([maildate_1],4,2) & "/" & Mid([maildate_1],7,4))

And while trying to get this to work, we realized that no matter what she tries to do, it is not retaining the zeroes when being converted to text, and therefore causing the above statement to error when either month or year is represented by a 1 digit number.

Is there any way we can get these zero's to display in the text output of the query in step 2, and allow for this update? Any advice would be GREATLY appreciated, this is driving her (and now me) nuts!

Thanks a lot,

Alex




It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[SkipVought]

Hi,

Format([maildate1],"mm/dd/yyyy") & Format([maildate2],"mm/dd/yyyy")

Skip,

_[red][/red]

 

[billy71]

How is your date set up in the Computer?. In the regional settings check the Format.

 

[SkipVought]

The settings only have to do with the DISPLAY of the DATE.

DATE/TIME values are store as NUMBERS, like right now in Dallas, its 8/31/2006 12:54 which has a DATA/TIME value of 38960.53789, and it can be FORMATTED in accordance with your display settings.


Skip,

_[red][/red]

 

[barny2006]

you can use the split command with / separator and make each component any digit that you want. like this

parts = split(the_date, "/")
if len(parts(1))< 2 then
parts(1) = "0" & parts(1)
end if
if len(parts(2)) < 2 then
parts(2) = "0" & parts(2)
end if
new_date = parts(1) & "/" & parts(2) & parts(3)

 

[barny2006]

the last line should be:
new_date = parts(1) & "/" & parts(2) & "/" & parts(3)
my bad!

 

[SkipVought]

Barney,

The "/" is only in the DISPLAY, which, of course, is a STRING. But WHY?

Skip,

_[red][/red]

 

[AlexCuse]

Thanks all,

Barney - I like your solution. I was wondering if there was a charindex type of function for Access queries. That is good to know for the future.

Skip - Yours is good as well, although I kind of want to smack myself for not thinking of that. I guess I am too preoccupied on my own projects

Also, Barney's solution would have worked well to convert the date back from text.

Unfortunately, the people requesting this project have succumbed to logic and decided to 'accept' having the two dates in separate fields side by side. Thanks for the great info though guys!

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[barny2006]

skip,
actually, that's not true. the data type for date is #date#. and when you look at this data type as text, you will see mm/dd/yyyy or m/d/yyyy which can be parsed to extract components using split function with "/", check the length of each component and add a "0" if needed to format a text field of fixed size. 08/01/2006 instead of 8/1/2006

cheers

 

[SkipVought]

barny

DATE/TIME is a NUMBER, like right now is 9/1/2006 13:14 in Dallas which the DATE/TIME value is 38961.55184. This NUMBER can be FORMATTED an number of ways, like
[tt]
September 9 2006
[/tt]
which has no slashes or dashes. It just happens that the compiler "helps" you out by performing a NUMBER to TEXT conversion to the FORMAT in the regional settings.

#date#, as you refer to as a type, does a CONVERSION from an assumed STRING within the #....#, in the format of regional settings, to a DATA VALUE NUMBER.

Bottom Line: You can do as you suggested, but it ASSUMES that some things will happen. I believe that it is much safer to consider a DATE as a NUMBER, and use the DATE Functions rather than STRING Functions which forces the complier to do an extra assumed conversion.

Skip,

_[red][/red]

 

[SkipVought]

FYI...

How to store, calculate, and compare Date/Time data in Microsoft Access

Skip,

_[red][/red]