Data Validation at Form Level Please (1 Viewer)

Lilly420 · Mar 15, 2018

Hello,

I have an input form in Access 2010 and I would like to put data validation on a few of the date fields (at form level). This form use to be in Excel.

I am in Design view of the form, in the Properties of the field, Validation Rule. I have tried several attempts but it is not working, as far as I can get is >=Date() which is today and/or greater than today or just =Date() today. Nothing else works even if I try to add 30 Days to today.

What I need is the following:

The field I want the validation on is called [LTARRecDate] so I need to write something that looks at the Access field [LTStartDate] and whatever that date is negative -15 from that and then Today's date and plus 30 days.

The Excel data validation looks like the below:

Allow: Date
Data: Between
Start Date: References the Cell Number where the date lives =B2-15
End Date: =Today()+30

I need this in Access format for the same type of data validation. Is this possible?

Any help is so appreciated. Thank you.

Lilly

:banghead:

RuralGuy · Mar 15, 2018

I'm not sure where you are having an issue but the code would go in the AfterUpdate event of the control where you are entering a date.

Lilly420 · Mar 15, 2018

OK, thank you but I am still unsure how to write this? Would someone be able to help me with that?

Thank you.

Lilly

RuralGuy · Mar 15, 2018

Are you familiar with VBA? Have you looked at the DateAdd() function?

Lilly420 · Mar 15, 2018

I have done a few things with VBA but you would say I am a beginner at best and am unsure how to write this. I did see the DateAdd() function and could look more at that but I need to look at two different things...1st look at the LTStartDate and -15 from that date and then today's date plus 30...I just am unsure how to write that.

Thank you.

Lilly

arnelgp · Mar 15, 2018

if [LTStartDate] is also in the form you
can do the Validation on [LTARRecDate] on its
BeforeUpdate Event:

Code:

Private Sub LTARRecDate_BeforeUpdate(Cancel As Integer)
If Trim([LTARRecDate] & "") <> "" Then
	If [LTStartDate]-[LTARRecDate] < -15 Or
		[LTARRecDate] > Date() + 30 Then
		Cancel = True
		Msgbox "Date is out of range"
	End If
End If
End Sub

RuralGuy · Mar 15, 2018

Where do you want the calculations to go?

Lilly420 · Mar 15, 2018

Hi,

Thank you both for the help. It is date validation on the [LTDateARRec] field on my form and yes, the [LTStartDate] is on the form.

I did try your code (thank you) and I needed to make a couple of tweaks (the field name was wrong-sorry) and it works except it won't allow a date -15 days from the [LTStartDate] so say that start date is 2/28/18 and I try to type in in 2/27/18 it won't allow it and that part I need...the other date part works, entering today and any date 30 days after that and won't allow a date 30 days out.

This is what I have (thanks to you): Do you know what I am doing wrong?

Private Sub LTDateARRec_BeforeUpdate(Cancel As Integer)
If Trim([LTDateARRec] & "") <> "" Then
If [LTStartDate] < -15 Or [LTDateARRec] > Date + 30 Then
Cancel = True
MsgBox "Date is out of range"
End If
End If
End Sub

Thank you so much.

arnelgp · Mar 15, 2018

Im a little bit confused on the first criteria.
Maybe change the first test:

If [LTDateARRec] < [LTStartDate]+15 Or ...

isladogs · Mar 15, 2018

Code:

[LTStartDate] < -15 Or [LTDateARRec] > Date + 30

Today is 15/03/2018

Your code means that the date is out of range and the code cancels IF:
a) LTStartDate is BEFORE 28/02/2018
OR
b) TDateARRec is AFTER 14/04/2018

Tomorrow, both dates shift forward one day to 01/03/2018 & 15/04/2018
Is that what you want?

Lilly420 · Mar 15, 2018

I am sorry if I am confusing you, I want the person to be able to enter a date the is negative 15 days from the LTStartDate (say the start date is 2/28/18 and I want them to be able to enter a date of 2/27/18--15 days prior to this start date) OR enter today's date and 30 days from today's date...does that make sense? The code below only works on the today's date and 30 days out but won't allow me to enter any date prior to 2/28/18. Thank you for any help.

jdraw · Mar 15, 2018

Lilly,

Seems to be a lot of confusion surrounding your requirement.

There is an old saying
If you can't describe it, no one can build it.

How about giving an example where:
you show a starting record; and perhaps a date, and
then show us which records should be returned/reported.

seems like EnteredDate must be Between LTStartDate -16 And Date +30

I made it 16 since Today -15
?date-15
28-Feb-18

and Between is an inclusive concept

isladogs · Mar 15, 2018

I'm more confused than before.
All I know is the code you have isn't what you want

So do you want them to be able to enter any start date that is AT LEAST 15 days before today i.e. BEFORE 28/02/2018 .... BUT NOT after that date

Lilly420 · Mar 15, 2018

Hi, OK I will try to explain.

I have a field called [LTStartDate] = 2/28/18

The field I want the code on is [LTDateARRec], this is the date the user will be filling in but we only want to allow them the below options.

I want the user to be able to enter the [LTStartDate] of 2/28/18 or negative 15 days meaning they may also enter 2/27, 2/26, 2/25, 2/24, 2/23, etc. (15 days prior to LT start date) OR be able to enter today's date which is 3/15 and 30 days into the future from today...like 3/16, 3/17, 3/18, ...but not be able to go beyond 4/14 (in this example). Does this make sense?

I appreciate all the help you are all giving me and I apologize for not being clearer.

Lilly

jdraw · Mar 15, 2018

And do any of these conditions change tomorrow, or next week?
Your [LTStartDate] = #2/28/18# is a constant??

Lilly420 · Mar 15, 2018

Once the LTStartDate is entered into that record for that customer, it does not change. But every month, we have a new record for that customer with a new LTStartDate for example, in March, that customer will have a new record and the LTStartDate will be 3/31/18 and they will also have the 2/28/18 record. We are required to get data each month from our customers and this is how we track it.

jdraw · Mar 15, 2018

Lilly,

Can you tell us in plain English what you are trying to do?
It seems you have a Date on a record related to a Client.
There is significance to the Date, such that when ??someone?? enters a date to see/review that Client record, there are constraints on the Date entered.

It also seems there is a time period where a specific range of Date values is acceptable, and then (for some business reason), LTStartDate is updated with the Date related to the last day of the month???

We don't know the significance of having multiple LT...recs. Or why that date changes.
We don't know your business nor how things were done (and why) in Excel.

Maybe I'm just slower than the others, but we seem to be getting deeper into a cloud of increasing uncertainty. I think it's time to restate the requirement.

arnelgp · Mar 16, 2018

Change again the first part to:

If [LTDateARRec] <= [LTStartDate]-15 Or ..

Lilly420 · Mar 16, 2018

OH my goodness, thank you so much, by making that adjustment to the code, it worked...below is what I have...again, thank you all so very much, I so appreciate you taking the time to help me out. And I apologize if I was not clear...I tried to explain it the best I could.

Private Sub LTDateARRec_BeforeUpdate(Cancel As Integer)

If Trim([LTDateARRec] & "") <> "" Then
If [LTDateARRec] <= [LTStartDate] - 15 Or [LTDateARRec] > Date + 30 Then
Cancel = True
MsgBox "The date is out of range."
End If
End If
End Sub

Lilly

arnelgp · Mar 16, 2018

Youre welcome!

Data Validation at Form Level Please (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

..forever waiting... waiting for jellybean!

AWF VIP

Registered User.

..forever waiting... waiting for jellybean!

MVP / VIP

Registered User.

Super Moderator

MVP / VIP

Registered User.

Super Moderator

Registered User.

Super Moderator

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread