Access 97 help? 1

[hereigns]

Novice user seeking help with Access 97

Database has a table (tblDriver) that I would like to build a Query (or whatever will work) that will automatically fill in a particular field (insurancestatus) with a "pass or fail" record depending on whether or not a particular field (insurance expiration) is past the current date.

I'm not really certain on the best way to accomplish this task - possible a If Then Else statement? If so, any ideas on how I go about setting it up? Help???

 

[kewo99]

An update query should work.
In the Update To box of the insurancestatus field put

=IIF([insurance expiration]>Date(),"Fail","Pass&quot

Ken

 

[hereigns]

Ken,
Thanks for the input but I'm a little confused...

Do I need to build a query in order follow your suggestion? Where will I find the "Update To" box?

Sorry I'm not understanding your suggestion.

 

[dcurtis]

Start a new query. On the tool bar at the top, just to the left of the run (!) button, is the query type button. Click on the pull down menu and select update query. This will add the "Update To" field in the design view. Put kewo99's code, =IIF([insurance expiration]>Date(),"Fail","Pass&quot , (without the leading and following commas).

----
A program is a spell cast over a computer, turning input into error messages.

 

[hereigns]

dcurtis,
Thanks for the help! After adding the query and running it - it added the text "Fail" to ALL of the status records. It looks like I'm missing some other parameter to tell it if the date is before the current date then add "Fail" to the record otherwise add the text "Pass".

 

[dcurtis]

Ok, looking back at it, I think your code should say:

=IIF([insurance expiration]<Date(),&quot;Fail&quot;,&quot;Pass&quot;)

Change the direction of the "carat" from greater than to less than. That should do it.

----
A program is a spell cast over a computer, turning input into error messages.

 

[kewo99]

Hi rlivermore,

If all records are updating to the same thing (Pass or Fail), then the comparison of the current date to the datefield (insurance expiration) is not working correctly.
Make sure that insurance expiration is a date/time data type field and not a text field.

Ken

 

[hereigns]

Ken & dcurtis,

Thank you both VERY MUCH for your assistance and persistance! Both of your replies solved the problem! You guys ROCK!

God Bless,
Rob