DateAdd question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jonefer - 13 Jan 2005 19:41 GMT

I want to add a day to a date and it seems like the following should work

LastSubmitted date is 1/12/2005
I want to return 1/13/2005 in StartDate

using
Me.StartDate = DateAdd("d", 1, Me.LastSubmitted)

but I get 12/31/1899

Reply to this Message

Wayne Morgan - 13 Jan 2005 19:51 GMT

What is the data type of the LastSubmitted field (this will be set in the
table's design)? It appears that you are adding 1 to zero. The base (zero)
date is 12/30/1899.

You may also try
Me.StartDate = DateAdd("d", 1, "#" & Me.LastSubmitted & "#")

The # is a date delimiter, sort of like using quotes to delimit a string.

Signature

Wayne Morgan
MS Access MVP

>I want to add a day to a date and it seems like the following should work
>
[quoted text clipped - 5 lines]
>
> but I get 12/31/1899

Reply to this Message

jonefer - 13 Jan 2005 20:07 GMT

"Last submitted" is based on a formula from a query recordset
=Max([TurnInToAcct])

[TurnInToAcct] is a date

I tried the "#" & me.lastsubmitted & "#"
but still get a type mismatch error

> What is the data type of the LastSubmitted field (this will be set in the
> table's design)? It appears that you are adding 1 to zero. The base (zero)
[quoted text clipped - 14 lines]
> >
> > but I get 12/31/1899

Reply to this Message

SHIPP - 13 Jan 2005 20:21 GMT

Try format(Max([TurnInToAcct]),"mm/dd/yy")

> "Last submitted" is based on a formula from a query recordset
> =Max([TurnInToAcct])
[quoted text clipped - 22 lines]
> > >
> > > but I get 12/31/1899

Reply to this Message

Graham Mandeno - 13 Jan 2005 20:57 GMT

Hi Jonefer

DateAdd should automatically convert the 3rd argument to a Date data type,
so the "#" signs will not help as you have discovered.

As Wayne said, the result is consistent with LastSubmitted having a value of
zero, or more likely, Null. Try putting:
MsgBox Me.LastSubmitted
just before the Me.StartDate= line to see what the value is. It's possible
as it's a calculated control that the value has not yet been calculated.

You could try adding:
Me.LastSubmitted.Requery
and possibly
DoEvents
to allow the calculation to occur before you use the result.

Signature

Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

> "Last submitted" is based on a formula from a query recordset
> =Max([TurnInToAcct])
[quoted text clipped - 24 lines]
>> >
>> > but I get 12/31/1899

Reply to this Message

Wayne Morgan - 13 Jan 2005 21:24 GMT

I understand that the # signs didn't work. Let's try this instead.

Me.StartDate = DateAdd("d", 1, CDate(Me.LastSubmitted))

What I suspect is happening is that the date is being taken as a division
problem. For example, today is January 14, 2005. Well, 1/14/2005 would be 1
divided by fourteen divided by 2005. This gives 3.56e-5 or almost 0. What
needs to be done is to make sure that Access uses the value as a date. Also,
verify that the value isn't Null.

Signature

Wayne Morgan
MS Access MVP

> "Last submitted" is based on a formula from a query recordset
> =Max([TurnInToAcct])
[quoted text clipped - 3 lines]
> I tried the "#" & me.lastsubmitted & "#"
> but still get a type mismatch error

Reply to this Message

Wayne Morgan - 13 Jan 2005 21:25 GMT

What data type is StartDate?

Signature

Wayne Morgan
MS Access MVP

> "Last submitted" is based on a formula from a query recordset
> =Max([TurnInToAcct])
[quoted text clipped - 3 lines]
> I tried the "#" & me.lastsubmitted & "#"
> but still get a type mismatch error

Reply to this Message

jonefer - 15 Jan 2005 01:55 GMT

Actually, Graham nailed it.
I put a calculation in LastSubmitted and was trying to populate the day
after in the "open" event. Forgetting that the value isn't even there until
after the form has opened.

What I did instead of DoEvents or any other thing is that I made sure the
calculations happened altogether. I did that by puting what I used to have
in LastSubmitted in the Open event.

like this:
Me.LastSubmitted = DMax("[TurnInToAcct]", "qryJobLog")
Me.StartDate = DateAdd("d", 1, DMax("[TurnInToAcct]", "qryJobLog"))
Me.EndDate = Format(Now(), "mm/dd/yyyy")

Thanks for your help.

> What data type is StartDate?
>
[quoted text clipped - 5 lines]
> > I tried the "#" & me.lastsubmitted & "#"
> > but still get a type mismatch error

Reply to this Message

Mark - 15 Jan 2005 00:32 GMT

Try:
Me.StartDate = Me.LastSubmitted + 1

Reply to this Message