felixcw
asked on
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!
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!
>>>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
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
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
Click on the desired text box.
Format - Conditional formatting.
Select Expressing is and [txtExpiryDate]< Date()
Select the formatting you desire.
Scott C
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..
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..
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
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
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?
ASKER
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!
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!
for table names and field names that have spaces, wrapped them in square bracket
[table name]
[table name]
ASKER
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?
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?
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..
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..
ASKER
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?
Any ideas why this would happen?
can you post sample data from the table with field names.
also post the SQL statement of the update query you created.
also post the SQL statement of the update query you created.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Just need to work out if I can automatically run this query when opening the form at the start of each day for instance.
ASKER
Brilliant fast response
you can run the query in the open event of the form, with this codes
docmd.setwarnings false
docmd.openquery "NameOfTheSavedQuery"
docmd.setwarnings true
docmd.setwarnings false
docmd.openquery "NameOfTheSavedQuery"
docmd.setwarnings true
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