-
May 14th, 2006, 11:56 PM
#1
Thread Starter
Fanatic Member
[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
-
May 15th, 2006, 12:09 AM
#2
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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.
-
May 15th, 2006, 12:23 AM
#3
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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
-
May 15th, 2006, 12:38 AM
#4
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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.
-
May 15th, 2006, 02:54 AM
#5
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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
-
May 15th, 2006, 02:58 AM
#6
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
try Date() it is system date
-
May 15th, 2006, 03:34 AM
#7
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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
-
May 15th, 2006, 03:42 AM
#8
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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..
The Difference between a Successful person and others is not a Lack of Knowledge,
But rather a Lack of WILL
-
May 15th, 2006, 04:04 AM
#9
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
try this
select empname,licNo,ExpDate from employees where expdate-date()<=30 and expdate-date() > 0)
-
May 15th, 2006, 06:29 AM
#10
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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
-
May 15th, 2006, 11:37 AM
#11
-
May 15th, 2006, 11:01 PM
#12
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
The second part is to control dont display expired licenses other withwise query will always display all expired licenses which is useless and bothring.
-
May 15th, 2006, 11:12 PM
#13
Re: Alert by showing the Expiry date of a License on a pop up message box ....
Code:
"select empname,licNo,ExpDate from employees where expdate - #" & date() & "# <= 30 and expdate - #" & date() & "# > 0"
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read.
Please Help Us To Save Ana
-
May 15th, 2006, 11:50 PM
#14
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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
Last edited by seema_s; May 15th, 2006 at 11:55 PM.
-
May 16th, 2006, 12:24 AM
#15
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
well u started a good quiz!
its not clear, pleas explain it.
-
May 16th, 2006, 01:02 AM
#16
-
May 16th, 2006, 02:35 AM
#17
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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
-
May 16th, 2006, 02:41 AM
#18
-
May 16th, 2006, 02:52 AM
#19
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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
-
May 16th, 2006, 03:22 AM
#20
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
-
May 16th, 2006, 03:24 AM
#21
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
VB Code:
Dim days as integer
days = datediff("d", ExpiryDate, Date)
txtTotalDaysLeftForExpiry.text = days
or is it days = datediff("d", Date, ExpiryDate)
anyway modify it to your needs.....same basic principal txtTotalDaysAfterExpiry.text
-
May 16th, 2006, 05:05 AM
#22
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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)
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
-
May 16th, 2006, 05:11 AM
#23
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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?
-
May 16th, 2006, 05:21 AM
#24
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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
-
May 16th, 2006, 05:27 AM
#25
Hyperactive Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
VB Code:
Private Sub cmdCalculateDays_Click()
Dim days As Integer
days = DateDiff("d", rs!ExpiryDate, Date)
txtTotalDaysLeftForExpiry.Text = days
End Sub
-
May 16th, 2006, 05:43 AM
#26
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
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.
-
May 16th, 2006, 05:46 AM
#27
Thread Starter
Fanatic Member
Re: Alert by showing the Expiry date of a License on a pop up message box ....
Hi guys!
Thanks to all of you for the help.
Regards.
Seema_S
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|