By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Validate date field in form?

P: 4
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
Feb 10 '08 #1
Share this Question
Share on Google+
6 Replies


Jim Doherty
Expert 100+
P: 897
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 :)
Feb 10 '08 #2

P: 4
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?
Feb 10 '08 #3

Jim Doherty
Expert 100+
P: 897
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 :)
Feb 10 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
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)>
Feb 10 '08 #5

P: 4
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?
Feb 11 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. Function CheckDate(ReqDate As Date)
  2. If ReqDate <= Date Then MsgBox "Delivery Date must be In the Future!"
  3. End Function
  4.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub RequiredByDate_AfterUpdate()
  2.   CheckDate (Me.RequiredByDate)
  3. End Sub
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)>
Feb 11 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.