Unbound date field giving me fits!

[Gina Maylone]

Hello all,



I have an unbound form that I use for processing loan payments. I have an unbound "datepaid" field, formatted as Short Date. I have a button to process the various types of payments (full, short, etc) and...

 
If vamountpaid > 0 And Format (datepaid, "short date") <= DueDate And amountpaid > 0 And PaidDate <> Null Then

In debug mode, no matter what I do, (on hover) the datepaid shows 12:00:00 AM, on debug.print its 12/30/1899. I've used the calendar control, NOT used the calendar control, etc. Obviously, my if statement does not process.

What is going on here? TIA!!

[ranman256]

12/30/1899 12:00:00 AM is the start of all dates numerically. Usu when its blank it will convert to this (but not null)
which will always be < duedate

[Gina Maylone]

Thank you ranman256, but it is not blank. There will always be a date in there.

[orange]

You have fields PaidDate and DatePaid----is this correct or a typo?

This Format (datepaid, "short date") <= DueDate
is comparing a text value to a date/time datatype (if DueDate is a Date/time datatype)

[Gina Maylone]

Hi Orange,

DatePaid is the unbound field on my form, paiddate is the actual paid date in the table.

Thanks for the info!

[Gina Maylone]

Ok, so I've tried using the conjetdate like this:

datepaid = Format(datepaid, conJetDate)

and

If vamountpaid > 0 And Format([Forms]![NewPaymentForm]!datepaid, conJetDate) <= DueDate And amountpaid > 0 And PaidDate <> Null Then

and I'm getting a type mismatch, because the datepaid is still 12:00:00 AM in the code window. How do I just get that unbound field formatted as a date??

Again, thanks all!

[hapm]

fyi

PaidDate <> Null

This expression will never be True, but will always evaluate to Null. The same goes for Anything And Null, so your if will never evaluate to True at all. Do some tests in the direct window with the following expressions:

?True <> Null
?1 <> Null
?"A String"<>Null
?True And Null
?Null = Null

It's a common mistake. The solution is to use

Not IsNull(PaidDate)

PS: the following evaluates to True:

True Or Null

[hapm]

For the problem in the op, as ranman256 stated:

12/30/1899 12:00:00 AM is the start of all dates numerically. Usu when its blank it will convert to this (but not null)
which will always be < duedate

This doesn't directly mean that the field on the form is blank. Another reason would be that you misspelled the controlname or the control isn't directly accessible from the context of your code (only possible from inside the controls form module, or explicitly by having a reference to the form object). To avoid errors like that use datepaid.Value instead of datepaid or use Option Explicit at the top of your code modules (best practice anyway).

[orange]

Have you tried removing the Format function from datePaid?
Can you post a copy of your database---just enough data to show the problem?
Zip format.

[Gina Maylone]

Thank you all for your help! I changed PaidDate<>Null to not isnull([paiddate]), and removed the format function. And all is well now! I have another question that I am posting in a new thread. Again, THANK YOU ALL!!