Date Validation Problem

[papic1972]

On a main form I have date field which in its default value I have set to NOW().

I have a subform linked to this mainform that allows me to input a delivery date. In this field I have placed this bit of code in the validation rule:
Is Null Or >=[Forms]![frmOrderInput].[OrderEntryDate]
In the validation text I have: "This date occurs in the past. Please correct."

Everything works fine EXCEPT if I enter today's date in the delivery date field. Then it gives me the validation text error.

How do I correct my validation rule so it allows me to enter today's date?

 

[willir]

papic1972

NOW() returns the date and time. Date is the interger part. The time is the decimal part.

Try using [blue]INT(NOW())[/blue] as your default. This will set the date to the current date at midnight.

Richard

 

[papic1972]

Hey willir,

I tried your suggestion, but unfortunately it is still giving me the same error. Can you think why this would be?

 

[willir]

papic1972

Okay, lets go back to the basics...

Me.OrderEntryDate = int(now())

Say 09/24/2003

Me.DeliveryDate = manually entered date

Say 09/28/2003

Noe grab the text from your post...
Is Null Or >=[Forms]![frmOrderInput].[OrderEntryDate]

It seems you are saying

If Me.DeliveryDate >= Me.OrderEntryDate Then YourMsg

Since the delivery date is 09/28 and the order entry date is 09/24, this is a true statement.

Should your comparision be...

If isnull([DeliveryDate]) or ([DeliveryDate] [blue]< [/blue][Forms]![frmOrderInput].[OrderEntryDate])

rather than greater or equal to???

Richard

 

[papic1972]

willir,

I understand what you are saying but I think you are missing what I'm asking for. I want to be able to enter today's date in the delivery date field. I want only those dates entered that are less than NOW()to produce the error msg. Therefore using >= (i.e. greater than or equal to) should suffice shouldn't it? It seems logical but access won't let me do it though. I'm stuck.

Thanks for your help by the way!

 

[Fahy]

Use Now() -1

 

[PeteJohnston]

If you only key in a date with no Time part, the field will hold 25-Sep-2003 00:00:00 as Richard said. If you do a compare against Now it will hold 25-Sep-2003 11:32:00 (it is currently 11:32 AM UK time) so the <= compare will be true and it will display the message. You need to decide just how you are going to have the user enter the delivery date, ie. as just date or as date and time. If you go for the first you have no way of telling whether you have entered for an AM delivery when it is already the afternoon. If you go for the second then they have to enter both date and time which might make them whine (like many of my users) about the extra key strokes.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[papic1972]

Thank you Fahy. What you suggested works beautifully!

I owe you a beer!