Date changes but shouldn't

  • Thread starter Thread starter annie techwriter
  • Start date Start date
A

annie techwriter

I have a form that is driving me crazy!

I used the expression
=IIf([Status]="Closed",Date()," ")
as a control source in a form. So the [DateClosed] populates with today's
date when the [Status] is set to 'Closed'. It works fine, except the date
changes every day.

For instance, I closed an issue yesterday and the Sept 17 date displayed
correctly. When I opened the form today, the date on the record I closed
yesterday displayed today's date. I reset my computer date to sometime in the
future and the [DateClosed] changed to that date.

I have a [DateOpened] that uses the simple expression "=Date()" as the
default value. Today's date displays when you open the record, and does not
change on subsequent days.

Any ideas? Do I need something in my [DateClosed] expression to keep it from
changing?

Thanks for any input or ideas.
 
Date() is today, not yesterday. Your formula is calculating in present time.
What you want to do is store Date() in the the table field, not just
display.
 
A little trick I use is to have the txtbox on your form with your IIF()
expression. Then put a txtbox on your form - set its property Visable = no,
source = [DateClosed], default it to your txtbox with your expression. You
have your control to see if the record is "closed" and you also, as Karl
pointed out, have the date stored in your table for recall.

Hope this helps.

Jeff

KARL DEWEY said:
Date() is today, not yesterday. Your formula is calculating in present time.
What you want to do is store Date() in the the table field, not just
display.
--
KARL DEWEY
Build a little - Test a little


annie techwriter said:
I have a form that is driving me crazy!

I used the expression
=IIf([Status]="Closed",Date()," ")
as a control source in a form. So the [DateClosed] populates with today's
date when the [Status] is set to 'Closed'. It works fine, except the date
changes every day.

For instance, I closed an issue yesterday and the Sept 17 date displayed
correctly. When I opened the form today, the date on the record I closed
yesterday displayed today's date. I reset my computer date to sometime in the
future and the [DateClosed] changed to that date.

I have a [DateOpened] that uses the simple expression "=Date()" as the
default value. Today's date displays when you open the record, and does not
change on subsequent days.

Any ideas? Do I need something in my [DateClosed] expression to keep it from
changing?

Thanks for any input or ideas.
Click to expand...
Click to expand...
 
Hi Annie,

If you have two fields "Status" and "DateClosed", use some VBA code.

Assuming your Status field is a Combo Box, open the properties and on the
OnClick Event, click Event Procedure, then click on the three dots (...) the
VBA Editor will open and place this code between the Private Sub and End Sub

If Me.Status="Closed" then
Me.DateClosed=Date
End If

If that doesn't work try:

If Me.Status="Closed" then
Me.DateClosed=Date
Else
Me.DateClosed="IsNull"
End If

See if that works but before you do this, MAKE SURE you make a copy first
and not do this on the original.

Good Luck!!
 
Thank you for the suggestions.

Jeff, I followed you up until "default it to your txtbox with your
expression"

I have txtbx A = [DateClosed]. I added my conditional expression as the
control source.

txtbx B = invisible. I named it [close]. The control source is [DateClosed]

not sure what to do after that. Do I need to put the conditional expression
as the default value in the [close] txtbx?

Jeff @ CI said:
A little trick I use is to have the txtbox on your form with your IIF()
expression. Then put a txtbox on your form - set its property Visable = no,
source = [DateClosed], default it to your txtbox with your expression. You
have your control to see if the record is "closed" and you also, as Karl
pointed out, have the date stored in your table for recall.

Hope this helps.

Jeff

KARL DEWEY said:
Date() is today, not yesterday. Your formula is calculating in present time.
What you want to do is store Date() in the the table field, not just
display.
--
KARL DEWEY
Build a little - Test a little


annie techwriter said:
I have a form that is driving me crazy!

I used the expression
=IIf([Status]="Closed",Date()," ")
as a control source in a form. So the [DateClosed] populates with today's
date when the [Status] is set to 'Closed'. It works fine, except the date
changes every day.

For instance, I closed an issue yesterday and the Sept 17 date displayed
correctly. When I opened the form today, the date on the record I closed
yesterday displayed today's date. I reset my computer date to sometime in the
future and the [DateClosed] changed to that date.

I have a [DateOpened] that uses the simple expression "=Date()" as the
default value. Today's date displays when you open the record, and does not
change on subsequent days.

Any ideas? Do I need something in my [DateClosed] expression to keep it from
changing?

Thanks for any input or ideas.
Click to expand...
Click to expand...
Click to expand...
 
I have a form that is driving me crazy!

I used the expression
=IIf([Status]="Closed",Date()," ")
as a control source in a form. So the [DateClosed] populates with today's
date when the [Status] is set to 'Closed'. It works fine, except the date
changes every day.
Click to expand...

That's exactly what you're asking it to do: when the form is opened to a
record, it checks the value of Status; if it is "Closed" then it displays
today's date in the textbox.
For instance, I closed an issue yesterday and the Sept 17 date displayed
correctly. When I opened the form today, the date on the record I closed
yesterday displayed today's date. I reset my computer date to sometime in the
future and the [DateClosed] changed to that date.
Click to expand...

Exactly. That's what you're asking it to do - display whatever the computer's
current date.
I have a [DateOpened] that uses the simple expression "=Date()" as the
default value. Today's date displays when you open the record, and does not
change on subsequent days.
Click to expand...

That's because you're *storing a value in the table* rather than just
*displaying it on a form*.
Any ideas? Do I need something in my [DateClosed] expression to keep it from
changing?
Click to expand...

Store the date in your table. To do so, use the AfterUpdate event of the
Status control. Open the form in design view; select the status control, and
view its properties. Click the ... icon by the "After update" line on the
Events tab and choose "Code Builder". The VBA editor will open with a Sub and
End Sub line already there; just add one line -

Private Sub Status_AfterUpdate()
Me![DateClosed] = Date
End Sub

The control named DateClosed should be a textbox, and its Control Source
should be the name of a field in the table in which you want that date stored.
 
Thank you! that seems to have solved my Date Changing issue... but now the
date displays no matter what status I select in the [Status] combo box. I
would like it to only display the status=closed.

Can I do that?

John W. Vinson said:
I have a form that is driving me crazy!

I used the expression
=IIf([Status]="Closed",Date()," ")
as a control source in a form. So the [DateClosed] populates with today's
date when the [Status] is set to 'Closed'. It works fine, except the date
changes every day.
Click to expand...

That's exactly what you're asking it to do: when the form is opened to a
record, it checks the value of Status; if it is "Closed" then it displays
today's date in the textbox.
For instance, I closed an issue yesterday and the Sept 17 date displayed
correctly. When I opened the form today, the date on the record I closed
yesterday displayed today's date. I reset my computer date to sometime in the
future and the [DateClosed] changed to that date.
Click to expand...

Exactly. That's what you're asking it to do - display whatever the computer's
current date.
I have a [DateOpened] that uses the simple expression "=Date()" as the
default value. Today's date displays when you open the record, and does not
change on subsequent days.
Click to expand...

That's because you're *storing a value in the table* rather than just
*displaying it on a form*.
Any ideas? Do I need something in my [DateClosed] expression to keep it from
changing?
Click to expand...

Store the date in your table. To do so, use the AfterUpdate event of the
Status control. Open the form in design view; select the status control, and
view its properties. Click the ... icon by the "After update" line on the
Events tab and choose "Code Builder". The VBA editor will open with a Sub and
End Sub line already there; just add one line -

Private Sub Status_AfterUpdate()
Me![DateClosed] = Date
End Sub

The control named DateClosed should be a textbox, and its Control Source
should be the name of a field in the table in which you want that date stored.
Click to expand...
 
Thank you! that seems to have solved my Date Changing issue... but now the
date displays no matter what status I select in the [Status] combo box. I
would like it to only display the status=closed.
Click to expand...

Sorry! Should have added that check:

Private Sub Status_AfterUpdate()
If Me.Status = "Closed" Then
Me![DateClosed] = Date
End If
End Sub
 
Back
Top