warranty expire (1 Viewer)

[kabir_hussein]

Hi

I am creating a database where i store information on all computer and printers we have. The printers have warranties usually lasting 2/3 years

Is there a way i can set a function where when a printer warranty is expiring within 30 days a reminder is displayed on the main menu

Many thanks in advance

Kabir

 

[freakazeud]

re:

Hi,
sure...I'm not sure what your setup is like but normally you would just create a query to do the calculation and you can then display these filtered records or give warnings by checking if the query holds records with the dcount() function. Lets say you have a DatePurchased and WarrentyAmount fields in your table. DatePurchased is of type Date/Time holding a true date value and WarrentyAmount is of type number holding warrenty times in month.
You can then use an expression to calculate the expiration date in a query e.g.:

DateExpire: DateAdd("m",[WarrentyAmount],[DatePurchased])

You can then use criteria on that to show everything which will expire soon e.g.:

BETWEEN DateAdd("d",-30,Date()) AND Date()

You can now display these records by binding a form to this query or check at any point (maybe everytime the application opens) if the query holds values e.g.

If Dcount("*","YourQueryName") > 0 Then
MsgBox("Items will expire soon...want to look at them?")
End If

If you have different warrenty time frames e.g. years/month/days...then you could of course additionally add another field to your table which identifies what interval the entered number value should be used in, but of course storing everything in a uniform manner e.g. just month makes live much easier.
HTH
Good luck

 

[kabir_hussein]

Hi

Many thanks for the reply

what i am proposing to do is have a warranty section for all computers, printers, but the warranty will be different for each comp, printer so i would need a seperate field for each hardware.

How ever

if i had a field with warranty expire(in the following format (i.e. 07 - Sep - 06) what query would i use to tel me a item was running out of warranty in less than 30 days or even a month.

Many thanks

 

[freakazeud]

re:

Hi,
I'm not sure if you should store the date the warrenty actually expires...it is a calculated value and can be calculated at runtime as shown earlier. You can store the components which make up this calculation like the date purchased and the interval of month/days/years till the warrenty expires. However, if you really want to approach it in your manner then it would be very similar to my suggestion. Just add criteria to your ExpirationDate field to show you all records which are 30 days out e.g.:

BETWEEN DateAdd("d",-30,Date()) AND Date()

HTH
Good luck

 

[kabir_hussein]

Hi

i have tried the query you suggested and it has not worked

here is the table i am trying to query

SELECT Printer.[Printer ID], Printer.Printer, Printer.Location, Printer.[Host name], Printer.Warranty, Printer.[Date Expires]
FROM Printer;


the Date Expires will hold a date (i.e. 10 sept 06) which i am trying my query to show when it is less than 30 days.

Any idea how this would be done

MAny thanks

 

[freakazeud]

re:

Where did you specify the criteria I gave you? I'm not seeing it...right now you have a regular select query...without any criteria in place.
BTW...you should avoid spaces in field/object/control...names...they are not valid characters and can cause problems.
HTH
Good luck

 

[kabir_hussein]

Hi

I have got this working now using the following query

Many thanks for all your help

SELECT Printer.[Printer ID], Printer.Printer, Printer.Location, Printer.[Host name], Printer.[Printer Type], Printer.[Serial Number], Printer.[IP Address], Printer.Department, Printer.Usage, Printer.Server, Printer.Warranty, Printer.[Date Expires]
FROM Printer
WHERE (((Printer.[Date Expires]) Between DateAdd("d",30,Date()) And DateAdd("d",-30,Date())));

 

[freakazeud]

re:

Glad you got it sorted out.
Good luck on future projects!

 

[kabir_hussein]

Hi

Sorry to reopen this thread but i have tried to reuse the criteria on another table of mine thinking it should be simply by just adding the query to another table

But for some reason i do not get any data returned

Any idea where i am going wrong

Below is the query that is working

SELECT Printer.[Printer ID], Printer.Printer, Printer.Location, Printer.[Host name], Printer.[Printer Type], Printer.[Serial Number], Printer.[IP Address], Printer.Department, Printer.Usage, Printer.Server, Printer.Warranty, Printer.[Date Expires]
FROM Printer
WHERE (((Printer.[Date Expires]) Between DateAdd("d",30,Date()) And DateAdd("d",-30,Date())));


And here is the query where i get no data

SELECT Computers.[Machine ID], Computers.[Machine Type], Computers.User, Computers.Department, Computers.[Installed Applications], Computers.[Serial Tag], Computers.[OS Installed], Computers.[Pc Brought]
FROM Computers
WHERE (((Computers.[Pc Brought]) Between DateAdd('d',30,Date()) And DateAdd('d',-30,Date())));

Many thanks for all help recieved