If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date changes but shouldn't
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. |
#2
|
|||
|
|||
Date changes but shouldn't
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" wrote: 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. |
#3
|
|||
|
|||
Date changes but shouldn't
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" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
Date changes but shouldn't
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!! "annie techwriter" wrote: 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. |
#5
|
|||
|
|||
Date changes but shouldn't
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" wrote: 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" wrote: 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" wrote: 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. |
#6
|
|||
|
|||
Date changes but shouldn't
On Thu, 18 Sep 2008 07:33:01 -0700, annie techwriter
wrote: 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. 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. 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. 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? 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. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Date changes but shouldn't
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" wrote: On Thu, 18 Sep 2008 07:33:01 -0700, annie techwriter wrote: 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. 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. 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. 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? 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. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Date changes but shouldn't
On Thu, 18 Sep 2008 10:25:01 -0700, annie techwriter
wrote: 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. Sorry! Should have added that check: Private Sub Status_AfterUpdate() If Me.Status = "Closed" Then Me![DateClosed] = Date End If End Sub -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|