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!
felixcwAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
0
 
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"
  else


end if

end sub
0
 
jefftwilleyCommented:
>>>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

J
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
clarkscottCommented:
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
 
0
 
Rey Obrero (Capricorn1)Commented:
jeff,

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..
0
 
jefftwilleyCommented:
Cap,
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.

J
0
 
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?
0
 
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!
0
 
Rey Obrero (Capricorn1)Commented:
for table  names and field names that have spaces, wrapped them in square bracket

[table name]  
0
 
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?
0
 
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..
0
 
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?
0
 
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.
0
 
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?

Regards

Marc
0
 
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.
0
 
felixcwAuthor Commented:
Brilliant fast response
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.