[RESOLVED] Alert by showing the Expiry date of a License on a pop up message box ....

Hi,

I have a database (MS Access) to store Driving License details. I need to remind the user of the License which are going to expire in next month. So, when I start the program it should check the expiry date of license and if it finds any license which is going to expire in a month it should show the employee name and the license number by popping up with an alert message.

txtEmployeeName
txtLicenseExpiryDate

The above two boxes hold the data which is required by a pop up message.

Regards.


Seema_S

Now what u need help?. Are u asking what should be the query to check the expiry date, i think expiration alert is concerned with number of days, add 30 days in current date and check it with license expiray date. If any licesence holder lies in this check print name and other information.

Quote:

---

Originally Posted by zubairkhan

Now what u need help?. Are u asking what should be the query to check the expiry date, i think expiration alert is concerned with number of days, add 30 days in current date and check it with license expiray date. If any licesence holder lies in this check print name and other information.

---

Yes, Mr. Zubair. you got the point which I could not explain you clrearly. How it can be done?

Employee.mdb
Table = Employees
Adodb connection = db
Adodb Recordset = rs

Fields are:

EmployeeName
LicenseNumber
ExpiryDate

Please help me out.

Seema_S

If you are using SQL query language, you should apply this

rs.open "select empname,licNo,ExpDate from employees where expdate-30 = date"


this will filter all employees those license expires withen thirty days.

Quote:

---

Originally Posted by zubairkhan

If you are using SQL query language, you should apply this

rs.open "select empname,licNo,ExpDate from employees where expdate-30 = date"


this will filter all employees those license expires withen thirty days.

---

Getting the following error on trying the above querry:

"No value given for one or more required parameter"

VB Code:

rs.Open "Select * from Employees where ExpiryDate - 30 [B]= date[/B]", db, adOpenDynamic, adLockOptimistic

Could you please let me know in the above code "= date" which is not available in the Employees table, from where did we get this one?

Seema_S

try Date() it is system date

Quote:

---

Originally Posted by zubairkhan

try Date() it is system date

---

Thanks for your help, Zubair. Now, I have two new problems.

1. When there is no ExpiryDate that matches the querry getting this error: "Either BOF or EOF is true....."

2. It shows the records only when it is exactly 30 days from the date of ExpiryDate. But what I need is something like this: 30,29,28,27,,,,1 (It should show even it is 30 days left for the expiry or 1 day left).

Seema_S

after opening the recordset try to check wether it is EOF or BOF...

VB Code:

rs.open(sql statement)
if not rs.eof or not rs.bof
msgbox "expired"
end if

and instead of "=date()" use "<=date()"

hope this will wipes of the problem..

try this
select empname,licNo,ExpDate from employees where expdate-date()<=30 and expdate-date() > 0)

Thanks a lot to both of you (Mr. Zubair Khan & Mr. Naga Srikanth).

In the following statement the First part of statement is working fine while the Second part shows an error:

First part : select empname,licNo,ExpDate from employees where expdate-date()<=30

Second part : and expdate-date() > 0)

Seema_S

U r most welcome..

but i too didn't get the logic of the secondpart...

Khan can explain this a bit better...

The second part is to control dont display expired licenses other withwise query will always display all expired licenses which is useless and bothring.

Code:

---

"select empname,licNo,ExpDate from employees where expdate - #" & date() & "# <= 30 and expdate - #" & date() & "# > 0"

---

Hi,

I have another querry as follows:

I want to put two textboxes:

1. txtTotalDaysLeftForExpiry
2. txtTotalDaysAfterExpiry

First txtbox will start counting days as soon as the expiry date will be exact 30 days and daily updates and shows how many days left for expiry...29 days, 28 days, 27 days ..........1 day.

While the other textbox will start counting days as soon as the date expired and counts like .....1 day, 2 days, 3 days ........so on....

How to write an sql statements for both?

Thanks in advance.

Seema_S

well u started a good quiz!

its not clear, pleas explain it.

well Mr..zubairkhan, You are right..seems a bit heavy...

If Im right,What seema_s want is to display no.of days laps by expiry date..After Selecting the Licence No....

Is it Seema_s???

Quote:

---

Originally Posted by nagasrikanth

well Mr..zubairkhan, You are right..seems a bit heavy...

If Im right,What seema_s want is to display no.of days laps by expiry date..After Selecting the Licence No....

Is it Seema_s???

---

Hi, guys!

Just we will change the first textbox to work like this:

First textbox (txtTotalDaysLeftForExpiry) shows the total days remaining for expiry.

Ex: ExpiryDate - CurrentDate = txtTotalDaysLeftForExpiry

Second textbox (txtTotalDaysAfterExpiry) shows the total days after the license has expired.

Ex: It should start when ExpiryDate=CurrentDate and start showing the days that "Your License has expired and it is the 5 th day now" something like this..

Regards.

Seema_S

come-on seema...

when would u like 2 see the textboxes updated...like by pressing the button or by updating the licence no like that..

or u would like to see for all the records...

sorry 2 say that "Im not getting u r exact idea "

Quote:

---

Originally Posted by nagasrikanth

come-on seema...

when would u like 2 see the textboxes updated...like by pressing the button or by updating the licence no like that..

or u would like to see for all the records...

sorry 2 say that "Im not getting u r exact idea "

---

Whenever I open the project it should show the updated data.

Seema_S

actually easy:

VB Code:

Dim days as integer

VB Code:

Dim days as integer
 
days = datediff("d", ExpiryDate, Date)
txtTotalDaysLeftForExpiry.text = days

or is it days = datediff("d", Date, ExpiryDate) :ehh:

anyway modify it to your needs.....same basic principal txtTotalDaysAfterExpiry.text

Quote:

---

Originally Posted by whythetorment

VB Code:

Dim days as integer
 
days = datediff("d", ExpiryDate, Date)
txtTotalDaysLeftForExpiry.text = days

or is it days = datediff("d", Date, ExpiryDate) :ehh:

anyway modify it to your needs.....same basic principal txtTotalDaysAfterExpiry.text

---

Hi, Getting error: Variable not defined and it highlights the following:
ExpiryDate

Seema_S

how do you call ExpiryDate from your database? thats what you should put in there....normally i call my fields as rsx!ExpiryDate....depending on what you use to call queries. how do you assign database fields to textboxes?
e.g. txt1.text = rsx!ExpiryDate?

Quote:

---

Originally Posted by whythetorment

how do you call ExpiryDate from your database? thats what you should put in there....normally i call my fields as rsx!ExpiryDate....depending on what you use to call queries. how do you assign database fields to textboxes?
e.g. txt1.text = rsx!ExpiryDate?

---

It is also not working.

VB Code:

Private Sub cmdCalculateDays_Click()
Dim days As Integer
ExpiryDate.Text = rs!ExpiryDate & ""
days = DateDiff("d", ExpiryDate, Date)
txtTotalDaysLeftForExpiry.Text = days
End Sub

VB Code:

Private Sub cmdCalculateDays_Click()
      Dim days As Integer
      days = DateDiff("d", rs!ExpiryDate, Date)
      txtTotalDaysLeftForExpiry.Text = days
End Sub

Quote:

---

Originally Posted by whythetorment

VB Code:

Private Sub cmdCalculateDays_Click()
      Dim days As Integer
      days = DateDiff("d", rs!ExpiryDate, Date)
      txtTotalDaysLeftForExpiry.Text = days
End Sub

---

Thanks for the help.

Hi guys!

Thanks to all of you for the help.

Regards.

Seema_S