DateAdd question

[jonefer]

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

 

[Wayne Morgan]

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.

 

[jonefer]

"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

 

[SHIPP]

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

"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)
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.

 

[Graham Mandeno]

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.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"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)
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.

 

[Wayne Morgan]

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.

 

[Wayne Morgan]

What data type is StartDate?

 

[Mark via AccessMonster.com]

Try:
Me.StartDate = Me.LastSubmitted + 1

 

[jonefer]

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?

--
Wayne Morgan
MS Access MVP

"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