How can I change a string to a date in a calculated expression?

[Guest]

I have an expression that I have calculated in a query that returns a string
like "1/1/2005"; however, the expression is a string. How do I convert it to
a date? My current expression is as follows ...

Beginning: Max(IIf([winfas_Book].[FiscYrEnd]=12,"1/1/" &
DatePart("yyyy",[winfas_BookParts].[CurrentLsDeprDate]),[winfas_Book].[FiscYrEnd]+1
& "/1/" &
IIf(DatePart("m",[winfas_BookParts].[CurrentLsDeprDate])>[winfas_Book].[FiscYrEnd],DatePart("yyyy",[winfas_BookParts].[CurrentLsDeprDate]),DatePart("yyyy",[winfas_BookParts].[CurrentLsDeprDate])-1)))

The query is a "Make Table Query" and I want the created table to recognize
it as a date without having to create a procedure that changes the table's
field properties to a date -- it seems there would be a simple expression
that I can add to my formula above to treat the outcome as a date. I guess I
could possibly use a "DateAdd" function to deal with my issue above, but I
would like to be able to see if there is a way to do it with what I have
above.

Thank you --

 

[Wayne Morgan]

Try wrapping the expression in CDate(). Also, you may want to check out the
DateSerial function, it will return a Date value instead of a string. I've
included an example, but you don't say if you're using m/d/y or d/m/y
format. I have assumed m/d/y.

Example:
Beginning: Max(IIf([winfas_Book].[FiscYrEnd]=12, DateSerial(
Year([winfas_BookParts].[CurrentLsDeprDate]), 1, 1), DateSerial(
IIf(Month([winfas_BookParts].[CurrentLsDeprDate])>[winfas_Book].[FiscYrEnd],Year([winfas_BookParts].[CurrentLsDeprDate]),Year([winfas_BookParts].[CurrentLsDeprDate])-1)),
[winfas_Book].[FiscYrEnd]+1, 1))

I think I got all of the parenthesis matched, but I'm not sure.

 

[Guest]

Thank you !!! I need to work with the DateSerial Function a bit -- I like it
because of its similarity to Excel's Date Function. I used the CDate around
my expression and it worked beautifully. Thanks Again

Try wrapping the expression in CDate(). Also, you may want to check out the
DateSerial function, it will return a Date value instead of a string. I've
included an example, but you don't say if you're using m/d/y or d/m/y
format. I have assumed m/d/y.

Example:
Beginning: Max(IIf([winfas_Book].[FiscYrEnd]=12, DateSerial(
Year([winfas_BookParts].[CurrentLsDeprDate]), 1, 1), DateSerial(
IIf(Month([winfas_BookParts].[CurrentLsDeprDate])>[winfas_Book].[FiscYrEnd],Year([winfas_BookParts].[CurrentLsDeprDate]),Year([winfas_BookParts].[CurrentLsDeprDate])-1)),
[winfas_Book].[FiscYrEnd]+1, 1))

I think I got all of the parenthesis matched, but I'm not sure.

--
Wayne Morgan
MS Access MVP

I have an expression that I have calculated in a query that returns a
string
like "1/1/2005"; however, the expression is a string. How do I convert it
to
a date? My current expression is as follows ...

Beginning: Max(IIf([winfas_Book].[FiscYrEnd]=12,"1/1/" &
DatePart("yyyy",[winfas_BookParts].[CurrentLsDeprDate]),[winfas_Book].[FiscYrEnd]+1
& "/1/" &
IIf(DatePart("m",[winfas_BookParts].[CurrentLsDeprDate])>[winfas_Book].[FiscYrEnd],DatePart("yyyy",[winfas_BookParts].[CurrentLsDeprDate]),DatePart("yyyy",[winfas_BookParts].[CurrentLsDeprDate])-1)))

The query is a "Make Table Query" and I want the created table to
recognize
it as a date without having to create a procedure that changes the table's
field properties to a date -- it seems there would be a simple expression
that I can add to my formula above to treat the outcome as a date. I
guess I
could possibly use a "DateAdd" function to deal with my issue above, but I
would like to be able to see if there is a way to do it with what I have
above.

Thank you --