Validate date field in form?

[emma82]

Hi, I'm completely new to Access and VBA so what I'm asking is probably really simple, anyway:

I have an Order form which has two date fields, one for the date the order is placed (today) and one for when the order is required by. I have to write a function to validate the second so that the user gets a warning if the required by date is earlier than or equal to today.

And I'm stumped. Could anyone offer any advice?

Thanks

[Jim Doherty]

Hi, I'm completely new to Access and VBA so what I'm asking is probably really simple, anyway:

I have an Order form which has two date fields, one for the date the order is placed (today) and one for when the order is required by. I have to write a function to validate the second so that the user gets a warning if the required by date is earlier than or equal to today.

And I'm stumped. Could anyone offer any advice?

Thanks

Have a look at the DATEDIFF function in Access help or use the search facility on here its fully documented in Access help and will tell you the difference between one and another by which you can then in VBA introduce a simple IF statement to test the logic ie:

IF DateDiff("d", [OrderDate], [ShippedDate]) =3 THEN
MsgBox "There are 3 days between OrderDate and Shipped Date", vbinformation," System Message"
END IF

Regards

Jim :)

[emma82]

Have a look at the DATEDIFF function in Access help or use the search facility on here its fully documented in Access help and will tell you the difference between one and another by which you can then in VBA introduce a simple IF statement to test the logic ie:

IF DateDiff("d", [OrderDate], [ShippedDate]) =3 THEN
MsgBox "There are 3 days between OrderDate and Shipped Date", vbinformation," System Message"
END IF

Regards

Jim :)

thanks a lot, I had tried various searches on 'date' etc, didn't know about DateDiff, searching on that has given lots of things that look relevant :)

what does the "d" mean though? that seems to be in every example I can find from a search on here, so presumably I don't need to change that to anything else?

[Jim Doherty]

thanks a lot, I had tried various searches on 'date' etc, didn't know about DateDiff, searching on that has given lots of things that look relevant :)

what does the "d" mean though? that seems to be in every example I can find from a search on here, so presumably I don't need to change that to anything else?

It means the Day emma

Jim :)

[missinglinq]

Are you trying to prevent this from happening, in all instances, or merely warn the user when it occurs?

Will the Order Date always be today?

Welcome to The Scripts!

Linq ;0)>

[emma82]

by default when you place a new order the order date fills itself in as today

I should probably explain: I have been given a half completed database created by my tutor and I have to finish it off with certain specified features, so I haven't designed the way the existing fields etc are set!

I'm not sure the above solution will be what he's after as he says the function must be within a module and then be called from within the form, the idea being to demonstrate that I understand functions etc... to be honest I think I do until I try and apply it!

Is there a way of doing it that way or should I still use DateDiff but in a public function and call it in the form?

[missinglinq]

In the future, please state up front if you are working on a school project! We're not averse to helping students, in fact many of us actually enjoy doing so, but the manner in which we offer help frequently differs when working with formal students.

Since you've stated that the OrderDate is always today, the general syntax for checking to see if the RequiredByDate is equal to or before the OrderDate, and popping up a warning is this is true, would be something like:

If RequiredByDate <= Date Then MsgBox "Delivery Date must be In the Future!"

I assume you know that to create a public function, available throughout the database, you go to the database window (Objects Dialog Box) and click on Modules. Then either open an existing module or create a new module. Here's a simple function using the syntax from above:

[CODE=vb]Function CheckDate(ReqDa te As Date)
If ReqDate <= Date Then MsgBox "Delivery Date must be In the Future!"
End Function
[/CODE]

The general syntax for calling this function would be

CheckDate(ReqDate)

where ReqDate is the date you're checking.

So, in the code module for your form, to call the function, assuming your date field is named RequiredByDate, you'll need this:
[CODE=vb]Private Sub RequiredByDate_ AfterUpdate()
CheckDate (Me.RequiredByD ate)
End Sub[/CODE]

You should be set now. One important thing to note; if you created a new module to place the CheckDate function in, when asked to save and name it, do not name it CheckDate! If you do so, you'll confuse the Access gnomes no end, and the function won't run! Name it something else descriptive, like modValidation or modDataCheck.

Good Luck!

Linq ;0)>