DateDiff and order of calculation

[Suepoo]

I have two dates I would like to compare.

Basically if one date is older than the other date - display an error message

Otherwise, continue on.

I have been doing this for at least two days now (and today is my birthday).

I've been researching the DateDiff function as my other method I was trying didn't work as I wish. Does the DateDiff take the right date from the left date so that in some cases a negative answer might result? If not, I will revert back to the other method I was attempting and try going from that angle......

------------------------------------------
Private Sub injection_due_date_Exit(Cancel As Integer)

TestDate

End Sub

---------------------------------------------

Function TestDate()

Runquery

DoCmd.OpenTable "dobcheck", acNormal
Dim dob
Dim datecheck

If DateDiff("d", dob, datecheck) > 0 Then

MsgBox "Incorrect date!"

Else

DoCmd.close acTable, "dobcheck", acSaveNo

End If

End Function

-------------------------------------------
Sub Runquery()

stDocName = "val_injectionduedate"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

-------------------------------------------------

It's not overly important, just a nicety I'm doing as I know the user is prone to typos but would really appreciate a point in the right direction.

 

[boblarson]

If you look in the Access VBA help file (which is a wonderful, yet often forgotten source of information), you would possibly see:

Syntax
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

[...snipped for brevity]

If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.

 

[Suepoo]

Thanks heaps for that. Over the last couple of days I have accessed so many places for help (including sifting through this site and the Access VBA help (and even a copy of VB for dummies ). I think my brain was starting to turn into mush from the information overload.

Your info looks like it will set me off on the correct path. Will give it a go and let you know how I get on.

Fingers crossed.....

 

[ansentry]

=DateDiff("d", StartDate, EndDate)

If StartDate was 12/2/2007 and EndDate was 12/2/2007 then the days would be 0.
If you leave EndDate static and change StartDate to 13/2/2007 then the result will be -1.

If the StartDate was 11/2/2007 with the static EndDate then the result would be 1

If DateDiff("d", dob, datecheck) > 0 
MsgBox "Incorrect date!"

The message box would run if the datecheck was before the dob.

Basically if one date is older than the other date - display an error message

Which one dob or datecheck?

 

[Suepoo]

Big sigh....

Okay, Bob, I'm still playing around with DateDiff....

John, the problem I had with my code is that it either displays the error message for all results (or if I played with the structure) it didn't display the message at all.

Basically, I have an alpaca table which contains the basic information such as name, dob, gender, etc.

I have various tables that are related to the alpaca number (an auto number). In this case, my form alpaca_injection allows the user to choose an alpaca and enter the due date for an injection. I want a validation so that this date can not be before the alpaca's dob.

My very first attempt at this was just a basic validation in the properties box for the injection_due_date: >=[Forms]![alpaca_main]![dob]

Even though I didn't type this in manually (I used the expression builder) Access tells me it cannot locate the form alpaca_main. Hence, I've been trying other ways around this problem.....

I'm starting to think I should have a break and give my brain a rest. I might continue on with finishing off the db so it can be used and nut out these little issues afterwards. I'm just a perfectionist and it frustrates me when I can't work out something that I initially thought would be simple

Thanks heaps both of you

PS I do usually get my "<" and ">" back to front, but during testing they get corrected.

 

[boblarson]

So is this on a main form or a subform? Also, where are you attempting this validation?

 

[Suepoo]

Hi Bob,

No, I haven't used subforms. There are a lot of forms in this db. I have one main form (Welcome) which consists of buttons that open the forms (which in turn contain buttons to other forms). The tables are all pretty much linked back through to the main alpaca table.

I was trying this validation code for the injection_due_date field in the Injections form (clicking properties and using the expression builder to insert the criteria in "validation rule"). I've done this for other validations that work fine (but are reading other fields within the same form/table). The trouble seems to be when the validation is based on a different form.

Cheers,
Sue

 

[boblarson]

I never use the validation rule in the controls. I just use the BeforeUpdate event of the form and there it is easy to validate and if it isn't right, just set Cancel=True and then setfocus back to the offending control.

 

[Suepoo]

Cheers for that. I will give it a go