Stuck on Null Dates

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

Certain criteria requies that I add on 12, 24 or 36 months
to the [Last Review Date]. The [Last Review Date] is
broken out by three fields: [fye_month] [fye_day]
[fye_year]. Some of the accounts do not have a last
review date and the code will not process all of the
records until each date field has something in it.

I've attempted, without success, to use nz to fill in
empty date fields...
last_fye_year: (nz[fye_year],"")


----------------------------------------------------------
I am then trying to add the 12, 24 or 36 months onto the
Last Review Date (if I can ever get past the null dates).
What is wrong with this code?


If [excpt_rate] = 0 Then
[NextReviewDate] = DateSerial(Year(Last_fye_year), _
Month([Last_fye_month]) + 24, Day
([Last_fye_day])
 
Hi Sharon,
You should refer to the form controls with the "me."
keyword, not to the form
s recordset fields. I would suggest the following:

If me.excpt_rate = 0 Then
If Not IsNull(me.Last_fye_month) Then
me.NextReviewDate = DateAdd("m",24,me.Last_fye_month)
End If
End If

I hope that is the correct context you needed.
 
Certain criteria requies that I add on 12, 24 or 36 months
to the [Last Review Date]. The [Last Review Date] is
broken out by three fields: [fye_month] [fye_day]
[fye_year].
Click to expand...

ummm... why? It's a lot easier to take a date apart than to put one
together! Why not make Last Review Date a Date/Time field?
Some of the accounts do not have a last
review date and the code will not process all of the
records until each date field has something in it.

I've attempted, without success, to use nz to fill in
empty date fields...
last_fye_year: (nz[fye_year],"")
Click to expand...

NZ is a *function* - as such, its arguments must be enclosed in
parentheses, not square brackets. Also, you don't want to have a NULL
value replaced by a text string! If [fye_year] is NULL, how would you
want the review date to be calculated? What's the date of 12 months
after ""?

The syntax would be

Last_Fye_Year: NZ([fye_year] said:
----------------------------------------------------------
I am then trying to add the 12, 24 or 36 months onto the
Last Review Date (if I can ever get past the null dates).
What is wrong with this code?


If [excpt_rate] = 0 Then
[NextReviewDate] = DateSerial(Year(Last_fye_year), _
Month([Last_fye_month]) + 24, Day
([Last_fye_day])
Click to expand...

The Year() and Month() functions expect Date/Time field arguments. If
last_fye_year is a number (e.g. 2003) then just leave off the
functions; if all three fields are Date/Time fields the code will work
but it seems VERY strange to store one date in three date/time fields!

I'd just use

DateAdd("m", 24, [Last Review Date])

assuming that Last Review Date is a Date/Time field.
 
John,

Unfortunatly, the day and month fields come from different
databases. The anniversary date for each account comes
from one mainframe database (exp: fye_month: 12, fye_day:
31) and the year comes from a form in the Access database
(exp: fye_year: 2002). Don't ask me, I just work here....

Anyway, with that in mind, would your advice be different?



-----Original Message-----
Certain criteria requies that I add on 12, 24 or 36 months
to the [Last Review Date]. The [Last Review Date] is
broken out by three fields: [fye_month] [fye_day]
[fye_year].
Click to expand...

ummm... why? It's a lot easier to take a date apart than to put one
together! Why not make Last Review Date a Date/Time field?
Some of the accounts do not have a last
review date and the code will not process all of the
records until each date field has something in it.

I've attempted, without success, to use nz to fill in
empty date fields...
last_fye_year: (nz[fye_year],"")
Click to expand...

NZ is a *function* - as such, its arguments must be enclosed in
parentheses, not square brackets. Also, you don't want to have a NULL
value replaced by a text string! If [fye_year] is NULL, how would you
want the review date to be calculated? What's the date of 12 months
after ""?

The syntax would be

Last_Fye_Year: NZ([fye_year] said:
--------------------------------------------------------- -
I am then trying to add the 12, 24 or 36 months onto the
Last Review Date (if I can ever get past the null dates).
What is wrong with this code?


If [excpt_rate] = 0 Then
[NextReviewDate] = DateSerial(Year(Last_fye_year), _
Month([Last_fye_month]) + 24, Day
([Last_fye_day])
Click to expand...

The Year() and Month() functions expect Date/Time field arguments. If
last_fye_year is a number (e.g. 2003) then just leave off the
functions; if all three fields are Date/Time fields the code will work
but it seems VERY strange to store one date in three date/time fields!

I'd just use

DateAdd("m", 24, [Last Review Date])

assuming that Last Review Date is a Date/Time field.


.
Click to expand...
 
The months need to be added to the Last Review date taking
into consideration that the month/day/year fields are
separate:
12/31/2002 + 36 months = 12/31/2005
----------------------------------------------------
This is my current code but it's not calculating correctly:
If [excpt_rate] = 0 Then
[NextReviewDate] = DateSerial(Year(Last_fye_year),
Click to expand...
Click to expand...
_Month([Last_fye_month]) + 24, Day ([Last_fye_day])



-----Original Message-----
Certain criteria requies that I add on 12, 24 or 36 months
to the [Last Review Date]. The [Last Review Date] is
broken out by three fields: [fye_month] [fye_day]
[fye_year].
Click to expand...

ummm... why? It's a lot easier to take a date apart than to put one
together! Why not make Last Review Date a Date/Time field?
Some of the accounts do not have a last
review date and the code will not process all of the
records until each date field has something in it.

I've attempted, without success, to use nz to fill in
empty date fields...
last_fye_year: (nz[fye_year],"")
Click to expand...

NZ is a *function* - as such, its arguments must be enclosed in
parentheses, not square brackets. Also, you don't want to have a NULL
value replaced by a text string! If [fye_year] is NULL, how would you
want the review date to be calculated? What's the date of 12 months
after ""?

The syntax would be

Last_Fye_Year: NZ([fye_year] said:
--------------------------------------------------------- -
I am then trying to add the 12, 24 or 36 months onto the
Last Review Date (if I can ever get past the null dates).
What is wrong with this code?


If [excpt_rate] = 0 Then
[NextReviewDate] = DateSerial(Year(Last_fye_year), _
Month([Last_fye_month]) + 24, Day
([Last_fye_day])
Click to expand...

The Year() and Month() functions expect Date/Time field arguments. If
last_fye_year is a number (e.g. 2003) then just leave off the
functions; if all three fields are Date/Time fields the code will work
but it seems VERY strange to store one date in three date/time fields!

I'd just use

DateAdd("m", 24, [Last Review Date])

assuming that Last Review Date is a Date/Time field.


.
Click to expand...
 
The months need to be added to the Last Review date taking
into consideration that the month/day/year fields are
separate:
12/31/2002 + 36 months = 12/31/2005
----------------------------------------------------
This is my current code but it's not calculating correctly:
If [excpt_rate] = 0 Then
[NextReviewDate] = DateSerial(Year(Last_fye_year),
Click to expand...
Click to expand...
_Month([Last_fye_month]) + 24, Day ([Last_fye_day])
Click to expand...

Well, he code as written is using the Year() function and the Month()
function and the Day() function on INTEGERS. These functions expect
Date/Time arguments. Just omit the function calls:

NextReviewDate = DateSerial([Last_fye_year], [Last_fye_month] + 36,
[Last_fye_day])
 
Back
Top