Access form data comparison

Hi I am quite new to Access and was wondering if someone could help me out with a small issue.

Basically I have a database which keeps reocrds of computer information. One field keeps a record of the warranty expiry date, and another field (warranty status) displays whether it is in warranty or not. At the moment both fields are manually entered but I would like change it so that the warranty status field changes automatically depending on today's date. Ie, if I enter a warranty expiry date of 01/01/2007, then the warranty status field would change automatically to "Out of Warranty".

this is probably a simple thing and can be done with some form of macro, but I am new to building macro's and expressions! Please help!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
are there existing records in your table?
if there are you will need an update query to update the warranty status field.

in the form..
you will use the after update event of the control for expiry date.

private sub txtExpiryDate_afterupdate()

if me.txtExpiryDate< Date() then
  me.txtStatus=  "Out of Warranty"

end if

end sub
>>>I would like change it so that the warranty status field changes automatically depending on today's date

Today's date is relative in so much as Cap has stated, there must be some event which kicks off to make the update work.

Automatic in this sense may mean that you write a small update procedure that runs when you open your form for example.

currentdb.Execute "Update YourTable Set [Status] = 'Out of Warranty' Where [ExpiryDate] <= Date()", dbseechanges + dbfailonerror

You could also use Conditional Formatting (if all you need is an alert).  You could use the same formula (per capricorn1), but instead of 'OUT OF WARRANTY' you could make the box RED background with bold text.
Click on the desired text box.
Format - Conditional formatting.
Select Expressing is   and   [txtExpiryDate]< Date()
Select the formatting you desire.

Scott C
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Rey Obrero (Capricorn1)Commented:

the update query i mentioned is not related to the information entered in the form.

it is a separate operation, to keep all records updated..
I understood that...that's the reason I pasted in the users quote to clarify.
ie. >>>I would like change it so that the warranty status field changes automatically depending on today's date

Along with your correct assessment of his immediate need, there is also the ongoing update of data. Automating that would require either some kind of timed event, or a simpler method is obviously to use the entry form's own open event to ensure that the data is updated whenever the user gets into it.

That's all.

Stephanie HudsonBusiness Intelligence DeveloperCommented:
Why even store the status?  You could make the status appear on the form, but I don't see why you need to store it?
felixcwAuthor Commented:
Sorry all for the delay in replying, but I've been unable to get any of the suggestions working.
I am a acomplete novice when it comes to writing scripts etc for Access, so I think I'm just not setting this up correctly. I presume that I will need to amend your scripts with the actual field names etc, which I have done, but I still can't seem to get this to work. I have tables and field names which contain spaces and I noticed that your code examples don't. Would this cause an issue?

Send2steph - I need to store the status as it makes it easier for me to run reports aginst it.

Any help would be greatly appreciated!
Rey Obrero (Capricorn1)Commented:
for table  names and field names that have spaces, wrapped them in square bracket

[table name]  
felixcwAuthor Commented:
OK, I have got the first part working where it automatically updates the Warranty Status filed depending on the Warranty Expiry field, but I can't get the update info when opening the form section to work.

Here's the code I have :-

Private Sub Form_Load()

CurrentDb.Execute "Update [Computer Data] Set Warranty_Status = 'Out of Warranty' Where Warrant_Expiry <= Date()", dbSeeChanges + dbFailOnError

End Sub

Can anybody see what's wrong here?
Rey Obrero (Capricorn1)Commented:
you only need to run the Update query once. this is just to keep your table updated.

create a query with this SQL statement

Update [Computer Data] Set Warranty_Status = 'Out of Warranty' Where Warrant_Expiry <= Date()

run the query and your done..
felixcwAuthor Commented:
Ok, I have setup a query to update the table I think, but it asks me to enter parameter values for Warranty_Status and Warrant_Expiry.

Any ideas why this would happen?
Rey Obrero (Capricorn1)Commented:
can you post sample data from the table with field names.

also post the SQL statement of the update query you created.
felixcwAuthor Commented:
Sorry for the delay in replying. How can I post sample data?

Here's the SQl statement :-
UPDATE [Computer Data] SET Warranty_Status = 'Out of Warranty'
WHERE Warrant_Expiry <= Date()

There are also blank entires in some of the equipments warranty date field which I like to ignore if possible. Do you think for the script to work, it needs a date in every field entry?


Rey Obrero (Capricorn1)Commented:
are you certain that the fields Warranty_Status and Warrant_Expiry  have underscore and not space?

UPDATE [Computer Data] SET Warranty_Status = 'Out of Warranty'
WHERE Warrant_Expiry <= Date() And Warrant_Expiry <> null

if fields have spaces.

UPDATE [Computer Data] SET [Warranty Status] = 'Out of Warranty'
WHERE [Warrant Expiry] <= Date() And [Warrant Expiry] <> null

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
felixcwAuthor Commented:
Brilliant, that's it now working. I was because of the missing y on the Warrant Expiry plus the additional NULL command.

Just need to work out if I can automatically run this query when opening the form at the start of each day for instance.
felixcwAuthor Commented:
Brilliant fast response
Rey Obrero (Capricorn1)Commented:
you can run the query in the open event of the form, with this codes

docmd.setwarnings false
docmd.openquery "NameOfTheSavedQuery"
docmd.setwarnings true
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.