Date Field Not Null

  • Thread starter Thread starter Jani
  • Start date Start date
J

Jani

What is the default code for this type of situation:
If 'Actual Delivery Date' is blank, default to the data in 'Sched Delivery';
if 'Actual Delivery Date' is not blank, leave the data as is. My latest code
is as shown below but it's not working:
=IIf(IsNull([Actual Delivery Date]),[Sched Delivery],[Actual Delivery Date])

Thanks so much, Jani
 
Jani

"not working" is not very descriptive.

Any chance there are "blank" values in some of the date fields? Those
wouldn't be caught by your IsNull() test...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
What is the default code for this type of situation:
If 'Actual Delivery Date' is blank, default to the data in 'Sched Delivery';
if 'Actual Delivery Date' is not blank, leave the data as is. My latest code
is as shown below but it's not working:
=IIf(IsNull([Actual Delivery Date]),[Sched Delivery],[Actual Delivery Date])

Thanks so much, Jani

At the moment a new record is created, *neither* field has a value. What's the
context? At what point do you want to assign a value to [Actual Delivery
Date]? What if there's no value in [Sched Delivery] at that point in time?

And for that matter... why would you want to enter data in an [Actual Delivery
Date] field until you knew the actual delivery date??
 
I think I understand your question. When the form is opened for a specific
record, there is a 'Sched Delivery' date in the record; there is no 'Actual
Delivery Date.' Almost always the 'Actual Delivery Date' is the same as the
'Sched Delivery' date, which is why we want it to default to the 'Sched
Delivery Date.' However, once in awhile the 'Actual Delivery Date' is
changed and is, therefore, no longer the same as the 'Sched' date... so when
reviewing that record subsequently, the changed 'Actual' date must stay and
not default to the 'Sched' date. Hope I'm making myself clear! Jani

John W. Vinson said:
What is the default code for this type of situation:
If 'Actual Delivery Date' is blank, default to the data in 'Sched Delivery';
if 'Actual Delivery Date' is not blank, leave the data as is. My latest code
is as shown below but it's not working:
=IIf(IsNull([Actual Delivery Date]),[Sched Delivery],[Actual Delivery Date])

Thanks so much, Jani

At the moment a new record is created, *neither* field has a value. What's the
context? At what point do you want to assign a value to [Actual Delivery
Date]? What if there's no value in [Sched Delivery] at that point in time?

And for that matter... why would you want to enter data in an [Actual Delivery
Date] field until you knew the actual delivery date??
 
I think I understand your question. When the form is opened for a specific
record, there is a 'Sched Delivery' date in the record; there is no 'Actual
Delivery Date.' Almost always the 'Actual Delivery Date' is the same as the
'Sched Delivery' date, which is why we want it to default to the 'Sched
Delivery Date.' However, once in awhile the 'Actual Delivery Date' is
changed and is, therefore, no longer the same as the 'Sched' date... so when
reviewing that record subsequently, the changed 'Actual' date must stay and
not default to the 'Sched' date. Hope I'm making myself clear! Jani

In that case, I'd suggest just setting the value of the actual delivery date
in the AfterUpdate event of the scheduled date:

Private Sub Sched_Delivery_AfterUpdate()
If IsNull(Me![Actual Delivery Date]) Then
Me![Actual Delivery Date] = Me![Sched Delivery]
End If
End Sub
 
So close. It works as far as if the field already has a date, it does not
default to the Sched Delivery Date field but if blank doesn't default to the
Sched Delivery Date. Many thanks, Jani

John W. Vinson said:
I think I understand your question. When the form is opened for a specific
record, there is a 'Sched Delivery' date in the record; there is no 'Actual
Delivery Date.' Almost always the 'Actual Delivery Date' is the same as the
'Sched Delivery' date, which is why we want it to default to the 'Sched
Delivery Date.' However, once in awhile the 'Actual Delivery Date' is
changed and is, therefore, no longer the same as the 'Sched' date... so when
reviewing that record subsequently, the changed 'Actual' date must stay and
not default to the 'Sched' date. Hope I'm making myself clear! Jani

In that case, I'd suggest just setting the value of the actual delivery date
in the AfterUpdate event of the scheduled date:

Private Sub Sched_Delivery_AfterUpdate()
If IsNull(Me![Actual Delivery Date]) Then
Me![Actual Delivery Date] = Me![Sched Delivery]
End If
End Sub
 
So close. It works as far as if the field already has a date, it does not
default to the Sched Delivery Date field but if blank doesn't default to the
Sched Delivery Date. Many thanks, Jani

Jani, I've read that sentence three times and I'm still baffled. If it has a
date, it doesn't NEED a default. If it doesn't have a date...what???

At what point in time does the Sched Delivery Date get set?
At what point do you want the delivery date default to be applied? Right then,
or at some later point in time (e.g. when you open the form)?
 
John - so sorry, I don't mean to be confusing. The Sched Delivery Date is
always in the record and displayed when the form is opened. The Actual
Delivery Date may or may not be in the record. If when the form is opened,
there is no Actual Delivery Date, then it should default to the Sched
Delivery Date. Geez, I hope I'm still not being confusing!
 
John - so sorry, I don't mean to be confusing. The Sched Delivery Date is
always in the record and displayed when the form is opened. The Actual
Delivery Date may or may not be in the record. If when the form is opened,
there is no Actual Delivery Date, then it should default to the Sched
Delivery Date. Geez, I hope I'm still not being confusing!

I'm seeing a bit through the fog but I still don't understand:

At what point is the Sched Delivery Date entered?

It can't be "always in the record" because when a record is initially created
it's EMPTY. There's NOTHING in the field at the instant the record is created.

If the [sched delivery date] field has a Default value, what is that value and
why can't you just use it as the default for Delivery Date?

If the [sched delivery date] is manually assigned my code should work. Doesn't
it?

If the [sched delivery date] comes from somewhere else, could you explain?

What does OPENING THE FORM have to do with the assignment of values to fields
in the table? Opening a form is like looking out a window: you don't usually
call in the landscaping crew to plant a new tree every time you look out the
window!

All that said... you could put code in the Form's Current event to put data
into the Sched Delivery Date when the user navigates to each record:

Private Sub Form_Current()
If IsNull(Me![Actual Delivery Date]) Then
Me![Actual Delivery Date]) = Me![Sched Delivery Date])
End If
End Sub

but this still seems wierd to me.
 
Oh my, but you are patient with me! Let me start over and see if I do any
better. There is a main form on which one enters in an order, a button is
pressed that brings up the second form where the record can be edited, if
necessary. The record source for the 'edit' form is a query built from a SQL
table and includes only the data for the order entered on the main form. The
data from the query populates the fieldson the edit form and already has a
Sched Delivery Date - however, it may or may not have an Actual Delivery
Date. If there is no Actual Delivery Date, then the Actual Delivery field
should default to the Sched Delivery Date; if there is an Actual Delivery
date, that date should not change/should not default to the Sched Delivery
Date. (my fingers are crossed.)

John W. Vinson said:
John - so sorry, I don't mean to be confusing. The Sched Delivery Date is
always in the record and displayed when the form is opened. The Actual
Delivery Date may or may not be in the record. If when the form is opened,
there is no Actual Delivery Date, then it should default to the Sched
Delivery Date. Geez, I hope I'm still not being confusing!

I'm seeing a bit through the fog but I still don't understand:

At what point is the Sched Delivery Date entered?

It can't be "always in the record" because when a record is initially created
it's EMPTY. There's NOTHING in the field at the instant the record is created.

If the [sched delivery date] field has a Default value, what is that value and
why can't you just use it as the default for Delivery Date?

If the [sched delivery date] is manually assigned my code should work. Doesn't
it?

If the [sched delivery date] comes from somewhere else, could you explain?

What does OPENING THE FORM have to do with the assignment of values to fields
in the table? Opening a form is like looking out a window: you don't usually
call in the landscaping crew to plant a new tree every time you look out the
window!

All that said... you could put code in the Form's Current event to put data
into the Sched Delivery Date when the user navigates to each record:

Private Sub Form_Current()
If IsNull(Me![Actual Delivery Date]) Then
Me![Actual Delivery Date]) = Me![Sched Delivery Date])
End If
End Sub

but this still seems wierd to me.
 
Oh my, but you are patient with me! Let me start over and see if I do any
better. There is a main form on which one enters in an order, a button is
pressed that brings up the second form where the record can be edited, if
necessary. The record source for the 'edit' form is a query built from a SQL
table and includes only the data for the order entered on the main form. The
data from the query populates the fieldson the edit form and already has a
Sched Delivery Date - however, it may or may not have an Actual Delivery
Date. If there is no Actual Delivery Date, then the Actual Delivery field
should default to the Sched Delivery Date; if there is an Actual Delivery
date, that date should not change/should not default to the Sched Delivery
Date. (my fingers are crossed.)

Thanks. So you have nothing to do with assigning the scheduiled date, eh?

Ok, use the second form's Load event:

Private Sub Form_Load()
If IsNull(Me![Actual Delivery Date]) Then
Me![Actual Delivery Date]) = Me![Sched Delivery Date])
End If
End Sub

This will fire whether you're opening the second form from the first *or*
opening it independently - if that's a problem post back.
 
Finally got back to this and it's working great. Thanks John!

John W. Vinson said:
Oh my, but you are patient with me! Let me start over and see if I do any
better. There is a main form on which one enters in an order, a button is
pressed that brings up the second form where the record can be edited, if
necessary. The record source for the 'edit' form is a query built from a SQL
table and includes only the data for the order entered on the main form. The
data from the query populates the fieldson the edit form and already has a
Sched Delivery Date - however, it may or may not have an Actual Delivery
Date. If there is no Actual Delivery Date, then the Actual Delivery field
should default to the Sched Delivery Date; if there is an Actual Delivery
date, that date should not change/should not default to the Sched Delivery
Date. (my fingers are crossed.)

Thanks. So you have nothing to do with assigning the scheduiled date, eh?

Ok, use the second form's Load event:

Private Sub Form_Load()
If IsNull(Me![Actual Delivery Date]) Then
Me![Actual Delivery Date]) = Me![Sched Delivery Date])
End If
End Sub

This will fire whether you're opening the second form from the first *or*
opening it independently - if that's a problem post back.
 
Back
Top