Date comparison issue

[Malcolmneill]

Hi,

I'm having another "Date" issue. I am trying to compare 2 dates to check that the "Sale Date" does not exceed the "Expiry Date". I have tried various ways to do this using a reference to the fields in the loaded form and a table, tried using a TempVar but none work! I think it's probably due to differences in the data type although both are defined as date. Here's an example

[CODE}

If [TempVars]![batch_date] > [Forms]![Items - Sales Details Update]![Expiry Date] Then

MsgBox strmsg, vbOKOnly, "Sale Date Exceeds Expiry date - Not allowed"

The "TempVar" is set up in thepreviously loaded form

[CODE}

If MsgBox("Correct Date Selected?", vbOKCancel + vbQuestion, "Batch Date Check") = vbCancel Then
Me.Undo

Else
TempVars.Add "Batch_update", "[Forms]![Batch Items by Sold Date]![Form Select date]"

I tried using the field directly from the table i.e.

If [Tables]![Operators]![ Form Select Date] > etc.

But that gave me an error Name not defined. Does it matter that the DB is an external one?

Anyway, some pointers/help would be appreciated.

Thanks
Malcolm
:banghead:

 

[Minty]

Add a Debug.Print TempVars![Batch_update] to see if your tempvar is being set correctly.

When you say "Does it matter that the DB is an external one?" Are you saying the form is in another database ???

Also just noticed you use

TempVars.Add "Batch_update" - but are checking for a value stored in
[TempVars]![batch_date]

Spot the difference !

 

[arnelgp]

Then which part on your code that does not work?

Definitely this one won't work:

TempVars.Add "Batch_update", "[Forms]![Batch Items by Sold Date]![Form Select date]"

It is saving the literal string.
Should be:

TempVars.Add "Batch_update", [Forms]![Batch Items by Sold Date]![Form Select date]


Also the way you reference a table is not correct.
You should link the table and use Dlookup to return the value you want.

 

[Mark_]

To aid you in the future, you should get in the habit of always showing what you are comparing when giving the end user a warning.

If MsgBox("Correct Date Selected?", vbOKCancel + vbQuestion, "Batch Date Check")

would make much more sense if you had included both sales date and expiration date. You would also catch that there is a problem much sooner.

 

[Malcolmneill]

Thanks Guys, sometimes when you have been staring at a screen for 4 + hours you miss the obvious!!

Thanks Malcolm