Dateadd on formatted date problem

  • Thread starter Thread starter Tony Girgenti
  • Start date Start date
T

Tony Girgenti

I'm trying to add a number of months to a table date (stored as TEXT,
YYYYMMDD) and format it as MM/DD/YY into a report text box. I used the
following in the control source, but it only appears as "#ERROR" on the
report.

Any help to accomplish this would be greatly appreciated.

Thanks,
Tony

=DateAdd("m",[PS_CTL!RGY_NO_OF_PURCH_MTHS],Format(Mid$([PS_RGY!EVENT_DAT],5,
2) & "/" & Mid$([PS_RGY!EVENT_DAT],7,2) & "/" &
Mid$([PS_RGY!EVENT_DAT],3,2),"mmddyy"))
 
Is there a good reason for storing a date as text? This leads to
over-complication of something that should be quite simple. Try:

=DateAdd("m", RGY_No_On_Purch_Mths, DateSerial(Val(Left(Event_Dat,4)),
Val(Mid(Event_Dat,5,2)), Val(Right(Event_Dat,2))) )
 
You seem to have simplified it quite well.

Thanks Duane.

Duane Hookom said:
Is there a good reason for storing a date as text? This leads to
over-complication of something that should be quite simple. Try:

=DateAdd("m", RGY_No_On_Purch_Mths, DateSerial(Val(Left(Event_Dat,4)),
Val(Mid(Event_Dat,5,2)), Val(Right(Event_Dat,2))) )

--
Duane Hookom
MS Access MVP


Tony Girgenti said:
I'm trying to add a number of months to a table date (stored as TEXT,
YYYYMMDD) and format it as MM/DD/YY into a report text box. I used the
following in the control source, but it only appears as "#ERROR" on the
report.

Any help to accomplish this would be greatly appreciated.

Thanks,
Tony
Click to expand...
=DateAdd("m",[PS_CTL!RGY_NO_OF_PURCH_MTHS],Format(Mid$([PS_RGY!EVENT_DAT],5,
2) & "/" & Mid$([PS_RGY!EVENT_DAT],7,2) & "/" &
Mid$([PS_RGY!EVENT_DAT],3,2),"mmddyy"))
Click to expand...
Click to expand...
 
Hello Duane.

I did try your suggestion and still get the "#ERROR" on the report.

I'm sure you know what you are doing, but will this statement format the
date on the report as "MM/DDYY" ?

BTW, the tables that i am wotking with were not designed by me. They are
externally linked and i have to work with them the way they are.

Thanks,
Tony

Duane Hookom said:
Is there a good reason for storing a date as text? This leads to
over-complication of something that should be quite simple. Try:

=DateAdd("m", RGY_No_On_Purch_Mths, DateSerial(Val(Left(Event_Dat,4)),
Val(Mid(Event_Dat,5,2)), Val(Right(Event_Dat,2))) )

--
Duane Hookom
MS Access MVP


Tony Girgenti said:
I'm trying to add a number of months to a table date (stored as TEXT,
YYYYMMDD) and format it as MM/DD/YY into a report text box. I used the
following in the control source, but it only appears as "#ERROR" on the
report.

Any help to accomplish this would be greatly appreciated.

Thanks,
Tony
Click to expand...
=DateAdd("m",[PS_CTL!RGY_NO_OF_PURCH_MTHS],Format(Mid$([PS_RGY!EVENT_DAT],5,
2) & "/" & Mid$([PS_RGY!EVENT_DAT],7,2) & "/" &
Mid$([PS_RGY!EVENT_DAT],3,2),"mmddyy"))
Click to expand...
Click to expand...
 
Try to troubleshoot this by pasting the control source value into the tag
property of the control to save it. Then simplify the control source to
something like:
=DateSerial(Val(Left(Event_Dat,4)),Val(Mid(Event_Dat,5,2)),
Val(Right(Event_Dat,2)))
Set the format of the control to your desired format.
Does this much look ok?
If so, then add the other parts of the control source back in but use
=DateAdd("m", 1 , DateSerial(Val(Left(Event_Dat,4)),
Val(Mid(Event_Dat,5,2)), Val(Right(Event_Dat,2))) )
If this works then your problem is the RGY_No_On_Purch_Mths field.

Is there any possibility that these field values will be null?

--
Duane Hookom
MS Access MVP


Tony Girgenti said:
Hello Duane.

I did try your suggestion and still get the "#ERROR" on the report.

I'm sure you know what you are doing, but will this statement format the
date on the report as "MM/DDYY" ?

BTW, the tables that i am wotking with were not designed by me. They are
externally linked and i have to work with them the way they are.

Thanks,
Tony

Duane Hookom said:
Is there a good reason for storing a date as text? This leads to
over-complication of something that should be quite simple. Try:

=DateAdd("m", RGY_No_On_Purch_Mths, DateSerial(Val(Left(Event_Dat,4)),
Val(Mid(Event_Dat,5,2)), Val(Right(Event_Dat,2))) )
Click to expand...
=DateAdd("m",[PS_CTL!RGY_NO_OF_PURCH_MTHS],Format(Mid$([PS_RGY!EVENT_DAT],5,
2) & "/" & Mid$([PS_RGY!EVENT_DAT],7,2) & "/" &
Mid$([PS_RGY!EVENT_DAT],3,2),"mmddyy"))
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your brilliant debugging techniques.

Tony

Duane Hookom said:
Try to troubleshoot this by pasting the control source value into the tag
property of the control to save it. Then simplify the control source to
something like:
=DateSerial(Val(Left(Event_Dat,4)),Val(Mid(Event_Dat,5,2)),
Val(Right(Event_Dat,2)))
Set the format of the control to your desired format.
Does this much look ok?
If so, then add the other parts of the control source back in but use
=DateAdd("m", 1 , DateSerial(Val(Left(Event_Dat,4)),
Val(Mid(Event_Dat,5,2)), Val(Right(Event_Dat,2))) )
If this works then your problem is the RGY_No_On_Purch_Mths field.

Is there any possibility that these field values will be null?

--
Duane Hookom
MS Access MVP


Tony Girgenti said:
Hello Duane.

I did try your suggestion and still get the "#ERROR" on the report.

I'm sure you know what you are doing, but will this statement format the
date on the report as "MM/DDYY" ?

BTW, the tables that i am wotking with were not designed by me. They are
externally linked and i have to work with them the way they are.

Thanks,
Tony
Click to expand...
=DateAdd("m",[PS_CTL!RGY_NO_OF_PURCH_MTHS],Format(Mid$([PS_RGY!EVENT_DAT],5,
2) & "/" & Mid$([PS_RGY!EVENT_DAT],7,2) & "/" &
Mid$([PS_RGY!EVENT_DAT],3,2),"mmddyy"))
Click to expand...
Click to expand...
Click to expand...
 
Just lots of experience debugging.

--
Duane Hookom
MS Access MVP


Tony Girgenti said:
Thanks for your brilliant debugging techniques.

Tony

Duane Hookom said:
Try to troubleshoot this by pasting the control source value into the tag
property of the control to save it. Then simplify the control source to
something like:
=DateSerial(Val(Left(Event_Dat,4)),Val(Mid(Event_Dat,5,2)),
Val(Right(Event_Dat,2)))
Set the format of the control to your desired format.
Does this much look ok?
If so, then add the other parts of the control source back in but use
=DateAdd("m", 1 , DateSerial(Val(Left(Event_Dat,4)),
Val(Mid(Event_Dat,5,2)), Val(Right(Event_Dat,2))) )
If this works then your problem is the RGY_No_On_Purch_Mths field.

Is there any possibility that these field values will be null?

--
Duane Hookom
MS Access MVP


used
the on
the
Click to expand...
=DateAdd("m",[PS_CTL!RGY_NO_OF_PURCH_MTHS],Format(Mid$([PS_RGY!EVENT_DAT],5,
2) & "/" & Mid$([PS_RGY!EVENT_DAT],7,2) & "/" &
Mid$([PS_RGY!EVENT_DAT],3,2),"mmddyy"))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top