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

Question on how to combine iif() and IsNull?

P: 58
I am working to refine a form that we use to enter quite a bit of data. Basically, I have a datasheet view form in which the following times are entered regarding trucking work:

PlantArrive | PlantLeave | SiteArrive | SiteLeave | ReturnTime

The Return Time for each record is the same as the Plant Arrive time of the next record unless it is the very last trip for that day for that truck. Due to this the data entry person must enter the same value twice most of the time. I consider this wasted effort. I would like to program the form, if possible, to do this instead.

Is it possible to put a iif(IsNull([ReturnTime]), ... type statement in this place? If it is please help me understand how to tell it to get the [PlantArrive] time from the NEXT record, unless there is no next record or unless a time is manually entered (as in the very last record for that day)?

If there is a better way to do this please let me know as well. I am concerned about doing this at a query level because I believe it is wise the for actual data to exist somewhere in the tables in case we want to run different reports or anlysis down the road or if someone else down the road manages this database. (I am open to comments on this rational.)

I hope I have explained this clearly. If not please let me know what part is confusing.
Mar 20 '07 #1
Share this Question
Share on Google+
3 Replies


Denburt
Expert 100+
P: 1,356
There are a few ways you can do this. I am not sure if this will work for your needs but I think the easiest would be to set the default value of the "plant arrive time" field to now() and that will give you the current time.
Mar 20 '07 #2

P: 58
I am confused as to how that would solve the problem. This data is entered manually by a data entry person after the fact. Usually it is 1-2 days later. It seems that the function you suggest would put that time that the data entry person was working rather than the actual return time.

Am I misunderstanding your suggestion?
Mar 20 '07 #3

Denburt
Expert 100+
P: 1,356
Try this and see if that is a little better.


Private Sub Text0_AfterUpdate()
Dim MyVal
MyVal = Me!ReturnTime
DoCmd.GoToRecord , , acNewRec
Me!PlantArrive = MyVal
End Sub
Mar 20 '07 #4

Post your reply

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