By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

To Compare all dates in a table

P: 13
Dear All,

Can Some body help me on this, im just new to access, and now im trying to develop a database for Human resourses.

The problem im facing is that, i need to check all the passport expiry dates of employees and compare it with now() & if its less than one month must give a mssge box. How can i doo all these, how can i compare all the dates in a table in start up itself and give the Mssge?

Please help me

I have a Table called AdminStaff and the feild i want to check is PassportExpiryDate

Pramod
Mar 8 '08 #1
Share this Question
Share on Google+
31 Replies


ADezii
Expert 5K+
P: 8,597
Dear All,

Can Some body help me on this, im just new to access, and now im trying to develop a database for Human resourses.

The problem im facing is that, i need to check all the passport expiry dates of employees and compare it with now() & if its less than one month must give a mssge box. How can i doo all these, how can i compare all the dates in a table in start up itself and give the Mssge?

Please help me

I have a Table called AdminStaff and the feild i want to check is PassportExpiryDate

Pramod
Assuming your Table Name is Employees, and you have a [LastName] and an [Expiry Date] Field in Employees, the following code placed in the Open() Event of you Main Form will list all the Employees whose Passport Expiration Date will fall within a Month of the Current Date (< 31 days). This information will be listed in a Message Box:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
  2. Dim strBuildString As String
  3.  
  4. strSQL = "Select * From Employees Where Not IsNull([Expiry Date]);"
  5.  
  6. Set MyDB = CurrentDb
  7. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  8.  
  9. Do While Not MyRS.EOF
  10.   If DateDiff("d", MyRS![Expiry Date], Now()) < 31 And DateDiff("d", MyRS![Expiry Date], Now()) > 0 Then
  11.     strBuildString = strBuildString & MyRS![LastName] & " ==> " & MyRS![Expiry Date] & vbCrLf
  12.   End If
  13.   MyRS.MoveNext
  14. Loop
  15.  
  16. MyRS.Close: Set MyRS = Nothing
  17.  
  18. MsgBox Left$(strBuildString, Len(strBuildString) - 2), , "Passport Expiration Dates"
  19.  
NOTE: - A Message Box can only contain a limited amount of Text, so beware that this may not be a good choice if there will be many Employees who have pending Expiration Dates.
Mar 8 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
The following SQL should find the correct records for you :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [AdminStaff]
  3. WHERE [PassportExpiryDate]<DateAdd('m',1,Date())
Don't use Now() for a situation such as this. Date() doesn't include the time element.
Mar 9 '08 #3

P: 13
Assuming your Table Name is Employees, and you have a [LastName] and an [Expiry Date] Field in Employees, the following code placed in the Open() Event of you Main Form will list all the Employees whose Passport Expiration Date will fall within a Month of the Current Date (< 31 days). This information will be listed in a Message Box:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
  2. Dim strBuildString As String
  3.  
  4. strSQL = "Select * From Employees Where Not IsNull([Expiry Date]);"
  5.  
  6. Set MyDB = CurrentDb
  7. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  8.  
  9. Do While Not MyRS.EOF
  10.   If DateDiff("d", MyRS![Expiry Date], Now()) < 31 And DateDiff("d", MyRS![Expiry Date], Now()) > 0 Then
  11.     strBuildString = strBuildString & MyRS![LastName] & " ==> " & MyRS![Expiry Date] & vbCrLf
  12.   End If
  13.   MyRS.MoveNext
  14. Loop
  15.  
  16. MyRS.Close: Set MyRS = Nothing
  17.  
  18. MsgBox Left$(strBuildString, Len(strBuildString) - 2), , "Passport Expiration Dates"
  19.  
NOTE: - A Message Box can only contain a limited amount of Text, so beware that this may not be a good choice if there will be many Employees who have pending Expiration Dates.

Dear ,
"
When i run this i get a error that "Too Few parameters Expeted 1" &

wen i debbug the it highlight this line ''Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)"

Please help me on this

Pramod
Mar 9 '08 #4

P: 13
The following SQL should find the correct records for you :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [AdminStaff]
  3. WHERE [PassportExpiryDate]<DateAdd('m',1,Date())
Don't use Now() for a situation such as this. Date() doesn't include the time element.
Dear,

Can you tell me how i can check all the dates in the table, one by one and give the mssgebox using this?
Mar 9 '08 #5

ADezii
Expert 5K+
P: 8,597
Dear ,
"
When i run this i get a error that "Too Few parameters Expeted 1" &

wen i debbug the it highlight this line ''Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)"

Please help me on this

Pramod
Make sure you substitute your own Table Name for [Employees] and your own Date for [Expiry Date]. The code has been tested and works fine, but remember that you are limited to 1,024 characters within the Message Box. Another option may be to have a List Box on the Form which will display the First Name, Last Name, and Expiration Dates of all Employees who meet the Date Criteria. With this approach there would be no limitations, but the List Box would have to be set up either manually or in code. The code would also have to write the values to the List Box.
Mar 9 '08 #6

NeoPa
Expert Mod 15k+
P: 31,186
Dear,

Can you tell me how i can check all the dates in the table, one by one and give the mssgebox using this?
You take the SQL I gave you, you put it into a string then you use code similar to what ADezii has already provided in this thread to process through the recordset and throw a MsgBox for each record.

All you need is here already for this.

If you have any more (particular) questions, or are stuck after trying to implement this, then please come back for more guidance.
Mar 9 '08 #7

P: 13
Make sure you substitute your own Table Name for [Employees] and your own Date for [Expiry Date]. The code has been tested and works fine, but remember that you are limited to 1,024 characters within the Message Box. Another option may be to have a List Box on the Form which will display the First Name, Last Name, and Expiration Dates of all Employees who meet the Date Criteria. With this approach there would be no limitations, but the List Box would have to be set up either manually or in code. The code would also have to write the values to the List Box.
Hii,

I changed the table Name & Expiry Date as in my Table, soo that error doesnt comes now,
Now the problm is it show other error "Run time error 5"," Invalid procedure call or Argument" in line

MsgBox Left$(strBuildString, Len(strBuildString) - 2), , "Passport Expiration Dates"

How can i solve this error, please help
Mar 10 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
Please show the code leading up to this (the whole procedure if possible). It may be that the contents of strBuildString are too short (empty?), but it's hard to say in isolation.
Mar 10 '08 #9

P: 13
Please show the code leading up to this (the whole procedure if possible). It may be that the contents of strBuildString are too short (empty?), but it's hard to say in isolation.
Hii,

this is the code that i have used
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3.     Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
  4.     Dim strBuildString As String
  5.  
  6.     strSQL = "Select * From AdminStaff Where Not IsNull([Passport Expiry Date]);"
  7.  
  8.     Set MyDB = CurrentDb
  9.     Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  10.  
  11.     Do While Not MyRS.EOF
  12.     If DateDiff("d", MyRS![Passport Expiry Date], Now()) < 31 And DateDiff("d", MyRS![Passport Expiry Date], Now()) > 0 Then
  13.     strBuildString = strBuildString & MyRS![Name] & " ==> " & MyRS![Passport Expiry Date] & vbCrLf
  14.     End If
  15.     MyRS.MoveNext
  16.     Loop
  17.  
  18.     MyRS.Close: Set MyRS = Nothing
  19.  
  20.     MsgBox Left$(strBuildString, Len(strBuildString) - 2), , "Passport Expiration Dates"
  21.  
  22.  
  23.  
  24. End Sub
Mar 10 '08 #10

ADezii
Expert 5K+
P: 8,597
The code itself appears to be sound, I think the problem lies in strSQL.
  1. Is your Table named AdminStaff?
  2. Is your Date Field named [Passport Expiry Date]?
  3. Do you actually have a [Name] Field in AdminStaff?
  4. The Field Names that I gave you were just meant to be markers so that you couold insert your own Field Names.
Mar 10 '08 #11

NeoPa
Expert Mod 15k+
P: 31,186
Could you just try changing line #20 for me to :
Expand|Select|Wrap|Line Numbers
  1. MsgBox strBuildString, , "Passport Expiration Dates"
... and seeing what you get. You can restore it afterwards if necessary. this is simply a quick test.
Mar 10 '08 #12

NeoPa
Expert Mod 15k+
P: 31,186
While I'm looking at this can I make a couple of suggestions for your code :
  1. Never put more than one command on a line (unless it's one-time-only code) (See line #18).
  2. Never use Now() for checking dates. The correct function to use here is Date() (See Line #12).
    Now() should only ever be used when times are also involved.
  3. Try to put Dim lines for separate types on separate lines (See Line #3).
Mar 10 '08 #13

ADezii
Expert 5K+
P: 8,597
While I'm looking at this can I make a couple of suggestions for your code :
  1. Never put more than one command on a line (unless it's one-time-only code) (See line #18).
  2. Never use Now() for checking dates. The correct function to use here is Date() (See Line #12).
    Now() should only ever be used when times are also involved.
  3. Try to put Dim lines for separate types on separate lines (See Line #3).
Hello NeoPa, just for curiosity, aren't suggestions 1 and 3 just a matter of Programming Style? Multiple Declarations and Commands on a single line will in no way impair the efficiency of the code, nor will they make any difference to the Compiler. They will actually shorten code length, though. Just curious.
Mar 11 '08 #14

P: 13
Could you just try changing line #20 for me to :
Expand|Select|Wrap|Line Numbers
  1. MsgBox strBuildString, , "Passport Expiration Dates"
... and seeing what you get. You can restore it afterwards if necessary. this is simply a quick test.
Hii,

When i changed as wat u said it gives a Mssge Box with Caption Passport Expiry dates
But there is no dates or names message in it.
Mar 11 '08 #15

P: 13
The code itself appears to be sound, I think the problem lies in strSQL.
  1. Is your Table named AdminStaff?
  2. Is your Date Field named [Passport Expiry Date]?
  3. Do you actually have a [Name] Field in AdminStaff?
  4. The Field Names that I gave you were just meant to be markers so that you couold insert your own Field Names.

HII,

Yes the Table Name & feild names are all currect.
Mar 11 '08 #16

Expert Mod 2.5K+
P: 2,545
...Now the problm is it show other error "Run time error 5"," Invalid procedure call or Argument" in line

MsgBox Left$(strBuildString, Len(strBuildString) - 2), , "Passport Expiration Dates"

How can i solve this error, please help
Hi all. The run-time error is arising when strBuildString is an empty string, which happens when the inner part of the IF is not performed (the datediff test not met). When this is so Len(strBuildString)-2 becomes -2, and Left$(strBuildString, -2) generates the run-time error. Some rethinking needed on what goes into strBuildString, or a test for zero length before doing the msgbox bit...

-Stewart
Mar 11 '08 #17

P: 13
Hi all. The run-time error is arising when strBuildString is an empty string, which happens when the inner part of the IF is not performed (the datediff test not met). When this is so Len(strBuildString)-2 becomes -2, and Left$(strBuildString, -2) generates the run-time error. Some rethinking needed on what goes into strBuildString, or a test for zero length before doing the msgbox bit...

-Stewart
Hii All,

if this is the case what changes should i make 4 this to work
Mar 11 '08 #18

ADezii
Expert 5K+
P: 8,597
Hii All,

if this is the case what changes should i make 4 this to work
First of all, thank you Stewart for your insight into this matter. You assistance is appreciated. Pramodpmenon, try the following revisions to the code:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
  2. Dim strBuildString As String
  3.  
  4. strSQL = "Select * From AdminStaff Where Not IsNull([Passport Expiry Date]);"
  5.  
  6. Set MyDB = CurrentDb
  7. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  8.  
  9. Do While Not MyRS.EOF
  10.   If DateDiff("d", MyRS![Passport Expiry Date], Date) < 31 And DateDiff("d", MyRS![Passport Expiry Date], Date()) > 0 Then
  11.     strBuildString = strBuildString & MyRS![Name] & " ==> " & MyRS![Passport Expiry Date] & vbCrLf
  12.   End If
  13.     MyRS.MoveNext
  14. Loop
  15.  
  16. MyRS.Close: Set MyRS = Nothing
  17.  
  18. If Len(strBuildString) > 0 Then
  19.   MsgBox Left$(strBuildString, Len(strBuildString) - 2), , "Passport Expiration Dates"
  20. Else
  21.   MsgBox "There are no Passport Expirations to report!", , "No Passport Expirations"
  22. End
NOTE: Notice the substitution of Date() for Now(), as correctly suggested by NeoPa.
Mar 11 '08 #19

NeoPa
Expert Mod 15k+
P: 31,186
Hii,

When i changed as wat u said it gives a Mssge Box with Caption Passport Expiry dates
But there is no dates or names message in it.
That's exactly what I was expecting. See Stewart's post (#17) as he has interpreted this correctly and provided an answer.

PS I will respond to all posts when time allows. Just catching up with easiest first ;)
Mar 11 '08 #20

ADezii
Expert 5K+
P: 8,597
Hii All,

if this is the case what changes should i make 4 this to work
An oversight on my part, sorry. On Line #10 I only made one Date for Now() substitution, you need to make the other also.
Mar 11 '08 #21

NeoPa
Expert Mod 15k+
P: 31,186
While I'm looking at this can I make a couple of suggestions for your code :
  1. Never put more than one command on a line (unless it's one-time-only code) (See line #18).
  2. Never use Now() for checking dates. The correct function to use here is Date() (See Line #12).
    Now() should only ever be used when times are also involved.
  3. Try to put Dim lines for separate types on separate lines (See Line #3).
Hello NeoPa, just for curiosity, aren't suggestions 1 and 3 just a matter of Programming Style? Multiple Declarations and Commands on a single line will in no way impair the efficiency of the code, nor will they make any difference to the Compiler. They will actually shorten code length, though. Just curious.
That rather depends on your definition of style. Using GoTos is heavily frowned upon, but the style of some programmers is to use GoTos. In that sense yes.
However, in commercial and professional environments it is important for code to be as readable and understandable as possible, and both points #1 & #3 make code harder to both read and understand. They were advised against by the tutor giving a coding course when I started using VBA seriously. It seems clear to me why.

On the other hand, I can't deny that multi-statement lines in a semi-interpreted language does have a performance advantage, although that would not easily be measurable.
Mar 11 '08 #22

NeoPa
Expert Mod 15k+
P: 31,186
An oversight on my part, sorry. On Line #10 I only made one Date for Now() substitution, you need to make the other also.
I've changed the post now to read as you intended it ADezii.

PS. You know I have a great deal of respect for your coding abilities ADezii, so I hope you won't feel my answer of your question was intended to sound critical.
Mar 11 '08 #23

ADezii
Expert 5K+
P: 8,597
I've changed the post now to read as you intended it ADezii.

PS. You know I have a great deal of respect for your coding abilities ADezii, so I hope you won't feel my answer of your question was intended to sound critical.
Not at all, NeoPa. You know that I have the utmost respect for you and that your opinions are always welcome. It was, just as I stated, a matter of curiosity, and from this point on, I will actually be using your approach. I looked at several of Allen Browne's code examples, and he does in fact use your technique as it relates to Variable Declarations and Multiple Single-Line Statements. I think Mr. Browne is an exceptional individual, and if he agrees with you, who am I to stand in the way of progress. (LOL). Take care NeoPa, see your around.
Mar 11 '08 #24

NeoPa
Expert Mod 15k+
P: 31,186
Marvellous!
Take care ADezii :)
Mar 12 '08 #25

Expert 5K+
P: 8,435
Subscribing .
Mar 12 '08 #26

P: 13
Hii All,

At last wat should i do for it to work?? As im just a starter to access i m not able to follow you fully plss help me in this.

Pramod
Mar 12 '08 #27

P: 13
Dear All,

THXX its working now, but now i need little more help so that with Passport expiry date i need to check also Visa Expiry date & Labour card expiry Date , these fields are also in the same table.

Regards

Pramod
Mar 12 '08 #28

ADezii
Expert 5K+
P: 8,597
Dear All,

THXX its working now, but now i need little more help so that with Passport expiry date i need to check also Visa Expiry date & Labour card expiry Date , these fields are also in the same table.

Regards

Pramod
This really complicates matters and should definately been mentioned a long time ago when it could easily have been incorporated into the code. When I have some spare time, I'll look into it.
Mar 12 '08 #29

ADezii
Expert 5K+
P: 8,597
Dear All,

THXX its working now, but now i need little more help so that with Passport expiry date i need to check also Visa Expiry date & Labour card expiry Date , these fields are also in the same table.

Regards

Pramod
  1. The Msgbox approach can no longer be utilized given your new requirements. All results will now be written to a Table named tblExpirationDates in the Open() Event of your Form.
  2. Create a Table named tblExpirationDates with the following Fields:
    1. [ID] - (AutoNumber) - {Primary Key}
    2. [Name] - (TEXT)
    3. [Expire Date] - (DATE/TIME)
    4. [Type] - (TEXT) - will contain the Values Passport, Visa, or Labour
  3. Create the following Private Sub-Routine in your Form's Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Private Function PopulateExpirationTable(strName As String, dteDate As Date, strType As String)
    2. Dim MyDB As DAO.Database
    3. Dim MyRS As DAO.Recordset
    4.  
    5. Set MyDB = CurrentDb
    6. Set MyRS = MyDB.OpenRecordset("tblExpirationDates", dbOpenDynaset)
    7.  
    8. With MyRS
    9.   .AddNew
    10.     ![Name] = strName
    11.     ![Expire Date] = dteDate
    12.     ![Type] = strType
    13.   .Update
    14. End With
    15.  
    16. MyRS.Close
    17. Set MyRS = Nothing
    18. End Function
  4. Copy and Paste the following code to the Open() Event of your Form replacing all the previous code that I had given you:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim MyRS As DAO.Recordset
    3. Dim strSQL As String
    4. Dim strBuildString As String
    5. Dim strSQL2 As String
    6.  
    7. strSQL = "Select * From AdminStaff;"
    8. strSQL2 = "Delete * From tblExpirationDates"
    9.  
    10. DoCmd.SetWarnings False
    11.   DoCmd.RunSQL strSQL2
    12. DoCmd.SetWarnings True
    13.  
    14. Set MyDB = CurrentDb
    15. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    16.  
    17. Do While Not MyRS.EOF
    18.   If Not IsNull(MyRS![Passport Expiry Date]) Then
    19.     If DateDiff("d", MyRS![Passport Expiry Date], Date) < 31 And DateDiff("d", MyRS![Passport Expiry Date], Date) > 0 Then
    20.       Call PopulateExpirationTable(MyRS![Name], MyRS![Passport Expiry Date], "Passport")
    21.     End If
    22.   End If
    23.   If Not IsNull(MyRS![Visa Expiry Date]) Then
    24.     If DateDiff("d", MyRS![Visa Expiry Date], Date) < 31 And DateDiff("d", MyRS![Visa Expiry Date], Date) > 0 Then
    25.       Call PopulateExpirationTable(MyRS![Name], MyRS![Visa Expiry Date], "Visa")
    26.     End If
    27.   End If
    28.   If Not IsNull(MyRS![Labour Card Expiry Date]) Then
    29.     If DateDiff("d", MyRS![Labour Card Expiry Date], Date) < 31 And DateDiff("d", MyRS![Labour Card Expiry Date], Date) > 0 Then
    30.       Call PopulateExpirationTable(MyRS![Name], MyRS![Labour Card Expiry Date], "Labour")
    31.     End If
    32.   End If
    33.     MyRS.MoveNext
    34. Loop
    35.  
    36. MyRS.Close
    37. Set MyRS = Nothing
  5. Now, when your Form Opens, any Passport, Visa, or Labour Expiration Dates that are within 30 days from the Current Date will be written to tblExpirationDates along with the Name and Type of Expiration Date(Passport, Visa, Labour).
  6. Make any necessary Name substitutions where necessary.
  7. I tested the code on March 11, 2008 with sample data and the results are posted below.
  8. I won't bore you with explanations, so if there are any questions, please feel free to ask.
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. ID      Name           Expire Date      Type
  2. 171      Davolio        3/5/2008       Passport
  3. 172      Fuller            3/1/2008          Visa
  4. 173      Fuller            2/20/2008      Labour
  5. 174      Leverling        2/22/2008         Passport
  6. 175      Leverling        2/28/2008         Visa
  7. 176      Peacock        2/23/2008       Passport
  8. 177      Peacock        2/16/2008       Labour
  9. 178      Suyama            2/15/2008      Visa
  10. 179      King            3/9/2008        Labour
  11. 180      Callahan        3/2/2008        Labour
  12. 181      Dodsworth      2/18/2008       Passport
  13.  
Mar 13 '08 #30

P: 13
Dear All,


Thxxx for all the help. Its working now.


Pramod.
Mar 13 '08 #31

ADezii
Expert 5K+
P: 8,597
Dear All,


Thxxx for all the help. Its working now.


Pramod.
Glad you got it working.
Mar 13 '08 #32

Post your reply

Sign in to post your reply or Sign up for a free account.