Formula to give result of date in form

[trackmedic]

I am trying to write a formula to stick in a form that gives the result of a date. Basically what I would like to do is use a statement (IF?) to figure out if a date is over 31 days old. As an example please follow below.

fire drill 01/01/07 DRILL PAST
fire drill 10/01/07 DRILL IN RANGE

I am not to good at writing statements so any help would be appreciated.

Thanks in advance and everyone be safe!

- Trackmedic

 

[rainman89]

try the dateadd function
so iff(dateadd("d",31,"[yourdatefield])<Date(), "Drill Past", "In range")

 

[trackmedic]

Still not working

Sorry, but I do not follow.

I am comparing a field named "fire drill" to the "date" field. If the "fire drill" field is more than 31 days old, I want a response of Drill required to pop up. If it is in the 31 day window from the "Fire drill" date, I want the response to stay blank.

thanks in advance.

 

[rainman89]

ok put this as the control source of another box

=iff(dateadd("d",31,"[fire drill]")<Date(), "Drill Past", "")

 

[trackmedic]

nope

I see what you are trying to do and it makes sense. I am still getting a #Name? error.

 

[rainman89]

what is the name of the field that has the date stored in it? is it firedrill?

 

[trackmedic]

Field Name

The name of the field is "Last Fire Drill". I did change it in the example and it still does not work.


- Thanks in advance.

PS It is little stuff like this that drives me batty!

 

[rainman89]

well i messed it up again. its iif not iff. i always do that!

 

[trackmedic]

Hmmmmm

Ok,

well I tried that and changed the name to my field and I get a #error sign.

 

[rainman89]

Check out this little example i made. i put the code in the oncurrent event of the form. and also created a query to show you how it is done in that part!

 

[trackmedic]

I am not having any luck. It just will not work on my end. I would really like to have this work as a reminder to my guys when the fire drills are over due. I looked at your example and it seems to work, I do not know what the issue is.

Thanks anyway!

 

[rainman89]

can you strip down your db and post it up here. ill take a look at it and see what i can do for you

 

[trackmedic]

here you go!

If you look at your example, thats what I would like. The database stripped is to large to send even stripped down.

The date it compares to is not always going to be the current date. It would be another field named date. I did not mention that before.

So in a nutshell, I am trying to compare a field named "last fire drill" to a field named "date" and if the difference is over 31 days, I would like to fill an unbound box with the words Drill required".

thanks!

 

[rainman89]

ok just an fyi you shouldnt use the word date as a field. you should use something else because it is a reserved word.

other than that. the code that i have given u should work for your system. check to make sure the names are correct and make sure it is in the oncurrent event of the form that is loaded.

 

[trackmedic]

Meaning???

I did not know that date is a reserved word. Also, what are you talking of when you mention oncurrent event?

If I change the Date range to inspection date, how would I syntax the formula?

Just checking on this. I would make a unbound box, click and stick the formula directly in the box? or would I go through properties of the box and stick it somewhere else?

since the fire drill box is in brackets, should'nt the box I am comparing it to be in brackets?

One other thing, once I place the drill date in the box and hit tab to go to the next field, I would want an instant reading. Would that be done in properties?


Again, thanks for all the help

 

[rainman89]

dont put the code directly in the box. do it this way.
when you go to the form right click and go to the properties tab.
in there you will find events.
in the on current event click the dropdown and select event procedure,
hit the ... next to that.
there is where you will put the code that i showed you.
changing the name of the me.text4 to the name of your unbound textbox.

=iff(dateadd("d",31,"[fire drill]")<[inspectiondate], "Drill Past", "")

 

[trackmedic]

Duh !

I tried your example and I have not had any success. Just to get it right, You want me to click on the properties of the form and not the control box.....correct?

 

[rainman89]

correct!! then navigate to the oncurrent event and place

me.yourunboundtxtboxname = the code i gave you before

 

[trackmedic]

ok

Here is the way I have it in the event procedure block

Private Sub Form_Current()

Me.Text60 = IIf(DateAdd("d", 31, "[Last Fire Drill]") < [Inspection Date], "Drill Past", "")

End Sub

It is giving me a run time error!?!?!?!?!?!

- I think I will skip this and get a cup of coffee!

 

[ajetrumpet]

Track,

If I may interject here...I think I am looking at another case of quotation marks confusing the "***beep*** out of people. Here is another short sub that you can try...

Private Sub Form_Current()
  
with Me.Text60

  If DateDiff("d", "DateFieldYouAreQuestioning", "FieldThatHoldsTheFutureDate") > 31
  Then .Value = "The Questioned Date is Over 31 Days Old"
    Else: .Value = "The Questioned Date is Not That Old Yet"

  End If
  End With

End Sub