Sat 30 Dec 1899 12.00am, shows when Blank!

  • Thread starter Thread starter Bob V
  • Start date Start date
B

Bob V

This part of my code is making the blank fields [DayNo] come up with this
date: Sat 30 Dec 1899 12.00am
Addition_ItMdt.Fields, Day No is blank in my Tables but when I use this code
to Distribute the Invoices to there clients I am getting this date error,
The fields that have a date in them work properly....Please any suggestions
to what I can do......Bob
This is the only part of the code that has "DayNo"

Etc Etc
Do While Not recAddition_ItMdt.EOF = True
With recAdditionCharge
.AddNew
.Fields("InvoiceID") = lngInvoiceID
.Fields("ChargeID") =
Nz(recAddition_ItMdt.Fields("ChargeID"), 0)
.Fields("HorseID") =
Nz(recAddition_ItMdt.Fields("HorseID"), 0)
.Fields("DayNo") = Nz(recAddition_ItMdt.Fields("DayNo"),
0)
.Fields("AdditionCharge") =
Nz(recAddition_ItMdt.Fields("AdditionCharge"), "")
.Fields("AdditionChargeAmount") =
Nz(recAddition_ItMdt.Fields("AdditionChargeAmount"), 0)
.Update
End With
EtcEtc
 
The Date 0 is Sat 30 Dec 1899 12.00am ;-)
Use Null for "Empty" Dates (Or todays date if that's better)

..Fields("DayNo") = recAddition_ItMdt.Fields("DayNo")
..Fields("DayNo") = Nz(recAddition_ItMdt.Fields("DayNo"), Now())

HTH

Pieter
 
Bob,

Ran into a similar problem the other day, working on a database that one of
the guys in my office had developed. He had set the date fields datatype to
number, with a default value of zero. Then, in the form, he formatted the
control as a short date, so every time we went to a new record, or a record
in which he had not put a value, the date showed up as you indicate. Check
to make sure that the fields data type is actually set to date/time, and that
the default is set to NULL

HTH
Dale
 
Thanks guys I will check it out, its actually giving me a 12.00am result for
blank date but when formatted in to my report is showing 30 Dec 1999
Thanks for all you help.....Bob
Dale Fye said:
Bob,

Ran into a similar problem the other day, working on a database that one
of
the guys in my office had developed. He had set the date fields datatype
to
number, with a default value of zero. Then, in the form, he formatted the
control as a short date, so every time we went to a new record, or a
record
in which he had not put a value, the date showed up as you indicate.
Check
to make sure that the fields data type is actually set to date/time, and
that
the default is set to NULL

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Bob V said:
This part of my code is making the blank fields [DayNo] come up with this
date: Sat 30 Dec 1899 12.00am
Addition_ItMdt.Fields, Day No is blank in my Tables but when I use this
code
to Distribute the Invoices to there clients I am getting this date error,
The fields that have a date in them work properly....Please any
suggestions
to what I can do......Bob
This is the only part of the code that has "DayNo"

Etc Etc
Do While Not recAddition_ItMdt.EOF = True
With recAdditionCharge
.AddNew
.Fields("InvoiceID") = lngInvoiceID
.Fields("ChargeID") =
Nz(recAddition_ItMdt.Fields("ChargeID"), 0)
.Fields("HorseID") =
Nz(recAddition_ItMdt.Fields("HorseID"), 0)
.Fields("DayNo") =
Nz(recAddition_ItMdt.Fields("DayNo"),
0)
.Fields("AdditionCharge") =
Nz(recAddition_ItMdt.Fields("AdditionCharge"), "")
.Fields("AdditionChargeAmount") =
Nz(recAddition_ItMdt.Fields("AdditionChargeAmount"), 0)
.Update
End With
EtcEtc
Click to expand...
Click to expand...
 
DayNo which is a date/time field in my tblAddition_ItMdt stores the blank
records as blanks but at the end of the month when I distribute them to
there owners to tblAdditionCharge they appear (Blanks)in the new table under
DayNo as (12:00:00 a.m.)
The funny thing about it if I distribute one Invoice at a time I get a
correct blank, but if I Distribute (Batch) I get this error (12:00:00 a.m.)
Thanks if someone can help me I could post the code to someone if they are
interested at looking at it...Bob

Bob V said:
Thanks guys I will check it out, its actually giving me a 12.00am result
for blank date but when formatted in to my report is showing 30 Dec 1999
Thanks for all you help.....Bob
Dale Fye said:
Bob,

Ran into a similar problem the other day, working on a database that one
of
the guys in my office had developed. He had set the date fields datatype
to
number, with a default value of zero. Then, in the form, he formatted
the
control as a short date, so every time we went to a new record, or a
record
in which he had not put a value, the date showed up as you indicate.
Check
to make sure that the fields data type is actually set to date/time, and
that
the default is set to NULL

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Bob V said:
This part of my code is making the blank fields [DayNo] come up with
this
date: Sat 30 Dec 1899 12.00am
Addition_ItMdt.Fields, Day No is blank in my Tables but when I use this
code
to Distribute the Invoices to there clients I am getting this date
error,
The fields that have a date in them work properly....Please any
suggestions
to what I can do......Bob
This is the only part of the code that has "DayNo"

Etc Etc
Do While Not recAddition_ItMdt.EOF = True
With recAdditionCharge
.AddNew
.Fields("InvoiceID") = lngInvoiceID
.Fields("ChargeID") =
Nz(recAddition_ItMdt.Fields("ChargeID"), 0)
.Fields("HorseID") =
Nz(recAddition_ItMdt.Fields("HorseID"), 0)
.Fields("DayNo") =
Nz(recAddition_ItMdt.Fields("DayNo"),
0)
.Fields("AdditionCharge") =
Nz(recAddition_ItMdt.Fields("AdditionCharge"), "")
.Fields("AdditionChargeAmount") =
Nz(recAddition_ItMdt.Fields("AdditionChargeAmount"), 0)
.Update
End With
EtcEtc
Click to expand...
Click to expand...
Click to expand...
 
I seemed to have fixed it does this seem ok???..Added Null to DayNo..Thanks
Bob
With recAdditionCharge
.AddNew
.Fields("InvoiceID") = lngInvoiceID
.Fields("ChargeID") =
Nz(recAddition_ItMdt.Fields("ChargeID"), 0)
.Fields("HorseID") =
Nz(recAddition_ItMdt.Fields("HorseID"), 0)
.Fields("DayNo") = Nz(recAddition_ItMdt.Fields("DayNo"),
Null)'********Changed to Null
.Fields("AdditionCharge") =
Nz(recAddition_ItMdt.Fields("AdditionCharge"), "")
.Fields("AdditionChargeAmount") =
Nz(recAddition_ItMdt.Fields("AdditionChargeAmount"), 0)
.Update
End With

Bob V said:
DayNo which is a date/time field in my tblAddition_ItMdt stores the blank
records as blanks but at the end of the month when I distribute them to
there owners to tblAdditionCharge they appear (Blanks)in the new table
under DayNo as (12:00:00 a.m.)
The funny thing about it if I distribute one Invoice at a time I get a
correct blank, but if I Distribute (Batch) I get this error (12:00:00
a.m.)
Thanks if someone can help me I could post the code to someone if they are
interested at looking at it...Bob

Bob V said:
Thanks guys I will check it out, its actually giving me a 12.00am result
for blank date but when formatted in to my report is showing 30 Dec 1999
Thanks for all you help.....Bob
Dale Fye said:
Bob,

Ran into a similar problem the other day, working on a database that one
of
the guys in my office had developed. He had set the date fields
datatype to
number, with a default value of zero. Then, in the form, he formatted
the
control as a short date, so every time we went to a new record, or a
record
in which he had not put a value, the date showed up as you indicate.
Check
to make sure that the fields data type is actually set to date/time, and
that
the default is set to NULL

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:



This part of my code is making the blank fields [DayNo] come up with
this
date: Sat 30 Dec 1899 12.00am
Addition_ItMdt.Fields, Day No is blank in my Tables but when I use this
code
to Distribute the Invoices to there clients I am getting this date
error,
The fields that have a date in them work properly....Please any
suggestions
to what I can do......Bob
This is the only part of the code that has "DayNo"

Etc Etc
Do While Not recAddition_ItMdt.EOF = True
With recAdditionCharge
.AddNew
.Fields("InvoiceID") = lngInvoiceID
.Fields("ChargeID") =
Nz(recAddition_ItMdt.Fields("ChargeID"), 0)
.Fields("HorseID") =
Nz(recAddition_ItMdt.Fields("HorseID"), 0)
.Fields("DayNo") =
Nz(recAddition_ItMdt.Fields("DayNo"),
0)
.Fields("AdditionCharge") =
Nz(recAddition_ItMdt.Fields("AdditionCharge"), "")
.Fields("AdditionChargeAmount") =
Nz(recAddition_ItMdt.Fields("AdditionChargeAmount"), 0)
.Update
End With
EtcEtc
Click to expand...
Click to expand...
Click to expand...
 
.Fields("DayNo") = Nz(recAddition_ItMdt.Fields("DayNo"),
Null)'********Changed to Null
Click to expand...

Rather than using NZ to turn a Null into a Null, just use

..Fields("DayNo") = recAddition_ItMdt!DayNo

If it's null it will stay null; if it's not it will be copied as is.

John W. Vinson [MVP]
 
John your not inferring about my country NZ to turn to null ;)
Click to expand...

The only NULL I can imagine is "the set of things I don't like about New
Zealand"... <g>

(well, other than the distance from Idaho)

John W. Vinson [MVP]
 
Back
Top