Conference bump::msaccess

Art,

the answer is staring you in the face. The cvdate() function is what you are
after.

Here's an example:

    Dim DateVar As Variant

    'Assign DateString to Date Variable
    DateVar = CVDate(#4/03/97 12:20:27#)
    'Manipulate date i.e. add one year to date
    Debug.Print DateAdd("yyyy", 1, DateVar)

Hope this helps.

Dennis.
[Posted by WWW Notes gateway]

    Re: <<< Note 1637.1 by NETRIX::"laodennis@mail.dec.com" "Dennis Lao" >>>

Dennis,

Thanks for the quick response. Unfortunately it really doesn't solve my
particular problem - unless I am missing something.

What you did, and what I did try and was successful at (see the base
note) is assign the literal date string to a variable and convert it to a
date and use it as such. Problem comes when I extract what appears to be
a legitimate date string from a file, assign that string to a variable
and then try to do the convert that variable to a date. It is at that
time I get the Type Mismatch error.

Again, logic is as follows:

1st Line of text file being read is "File Date - ",#1997-03-03 12:20:27#.
Read it and assign it to a variable TextLine ie. Line Input #1,TextLine

Using Mid$ function extract #1997-03-04 12:20:27# and assign to a
variable DateVar ie. DateVar = Mid$(textline, 16, 21)

Now, do a CVDate(DateVar) and you will get the Type Mismatch error.

Art

    
    	Should you be including the #'s in your variable assignment?
    
    	IE, maybe mid$(textline,17,19) instead?
    
    
    	But if the #'s should be there, what do they do?  Looks like
    	their only purpose would be to "enclose" the date so that
    	there is no gap in the field [created by the space between
    	the date and time].
    

From the MS Access help file:

date literal

Any sequence of characters with a valid format that is surrounded by number
signs (#).  Valid formats include the date format specified by the locale
settings for your code or the universal date format.
For example, #12/31/92# is the date literal that represents December 31, 1992
where English-U.S. is the locale setting for your application.  Use date
literals to maximize portability across national languages.

    
    	Thanks, I sort of figured.
    

Hope this is not too late...

> 1st Line of text file being read is "File Date - ",#1997-03-03 12:20:27#.
> Read it and assign it to a variable TextLine ie. Line Input #1,TextLine
>
> Using Mid$ function extract #1997-03-04 12:20:27# and assign to a
> variable DateVar ie. DateVar = Mid$(textline, 16, 21)
> 
> Now, do a CVDate(DateVar) and you will get the Type Mismatch error.

This is because Access does not like the yyyy-mm-dd format. You will need to
massage TextLine so that the date portion is in the format mm-dd-yyyy before
passing it on to CVDate.

Regards,
Dennis.
[Posted by WWW Notes gateway]

        Re:      <<< Note 1637.6 by NETRIX::"laodennis@mail.dec.com" "Dennis Lao" >>>

Dennis,

I have worked around what I consider an inconsistency - so not solved my
original problem, just come at it a different way.

Indeed the format yyyy-mm-dd is legal and can be processed as such. The
issue is one of how you process it. 

If I use the LINE INPUT # statement and parse the line to extract the
date, I can't get the darn thing converted to a date format.

If I use the LINE # statement, in which I am forced to pre-dimension that
part of the line being read as a Variant, then the same information gets
'input' as a date.

Again, I have worked around the problem by changing my code to the
latter. I am still curious as to why the former parsing and converting of
a string to a date variant doesn't work.

Art