Simpler syntax for date conversion

[levinepw]

I can convert a yymmdd to (yymmdd - 1 day)
What I do is take the existing yymmdd string, convert it to a
mm/dd/yyyy date and subtract a day from it & then format it back into
yyymmdd.

But the expression is a monster beacuse I have to include the DateAdd()
for each part of the formatting.

Exp_date:
Right(DateAdd("d",-1,CDate(Mid([dupe_openTerms_1].[Exp_Date],3,2) & " "
& Right([dupe_openTerms_1].[Exp_Date],2) & " " &
Left([dupe_openTerms_1].[Exp_Date],2))),2) &
Left(DateAdd("d",-1,CDate(Mid([dupe_openTerms_1].[Exp_Date],3,2) & " "
& Right([dupe_openTerms_1].[Exp_Date],2) & " " &
Left([dupe_openTerms_1].[Exp_Date],2))),2) &
Mid(DateAdd("d",-1,CDate(Mid([dupe_openTerms_1].[Exp_Date],3,2) & " " &
Right([dupe_openTerms_1].[Exp_Date],2) & " " &
Left([dupe_openTerms_1].[Exp_Date],2))),4,2)

It would be prettier with a variable instead of having to repeat
DateAdd(). So I tried that. I created an expression Exp_Date1 in a
sperate non-displayed column that just did the DateAdd(). But when I
tried to reference [Exp_Date1] to format it, ACCESS prompted me for the
value. So that's why I use the big ugly.

Any ideas on slimming the expression down? Is there a way to define a
variable in ACCESS expressions?

[Larry Linson]

So, why are you keeping a date as a string value? Sometimes I get
string-value-dates as input, but it doesn't make any sense to keep them as
strings in your database.

It's possible, perhaps, to expend time and effort to do this more
efficiently, but I don't like to encourage what may be ill-advised practices
(such as dates in strings).

Larry Linson
Microsoft Access MVP

<le******@yahoo.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...

I can convert a yymmdd to (yymmdd - 1 day)
What I do is take the existing yymmdd string, convert it to a
mm/dd/yyyy date and subtract a day from it & then format it back into
yyymmdd.

But the expression is a monster beacuse I have to include the DateAdd()
for each part of the formatting.

Exp_date:
Right(DateAdd("d",-1,CDate(Mid([dupe_openTerms_1].[Exp_Date],3,2) & " "
& Right([dupe_openTerms_1].[Exp_Date],2) & " " &
Left([dupe_openTerms_1].[Exp_Date],2))),2) &
Left(DateAdd("d",-1,CDate(Mid([dupe_openTerms_1].[Exp_Date],3,2) & " "
& Right([dupe_openTerms_1].[Exp_Date],2) & " " &
Left([dupe_openTerms_1].[Exp_Date],2))),2) &
Mid(DateAdd("d",-1,CDate(Mid([dupe_openTerms_1].[Exp_Date],3,2) & " " &
Right([dupe_openTerms_1].[Exp_Date],2) & " " &
Left([dupe_openTerms_1].[Exp_Date],2))),4,2)

It would be prettier with a variable instead of having to repeat
DateAdd(). So I tried that. I created an expression Exp_Date1 in a
sperate non-displayed column that just did the DateAdd(). But when I
tried to reference [Exp_Date1] to format it, ACCESS prompted me for the
value. So that's why I use the big ugly.

Any ideas on slimming the expression down? Is there a way to define a
variable in ACCESS expressions?

[levinepw]

The values are kept as strings in the database because of a documented
bug in ACCESS that won't allow me to import the yymmdd data as dates
from a fixed width text file.

[Larry Linson]

<le******@yahoo.com> wrote

The values are kept as strings in the database
because of a documented bug in ACCESS that
won't allow me to import the yymmdd data as
dates from a fixed width text file.

Bug? Sounds like "working as designed" to me. But, so what?

Once they are imported, it's not rocket science to convert them. You'll only
have to do it once after you input them and it will make your life easier*.

(Example, you won't have to put up with replies
similar to "It can be done but I won't do anything
to encourage bad practices.")

Larry Linson