To Compare all dates in a table

[Pramodpmenon]

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 PassportExpiryD ate

Pramod

[ADezii]

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 PassportExpiryD ate

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:
[CODE=vb]
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
Dim strBuildString As String

strSQL = "Select * From Employees Where Not IsNull([Expiry Date]);"

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecord set(strSQL, dbOpenForwardOn ly)

Do While Not MyRS.EOF
If DateDiff("d", MyRS![Expiry Date], Now()) < 31 And DateDiff("d", MyRS![Expiry Date], Now()) > 0 Then
strBuildString = strBuildString & MyRS![LastName] & " ==> " & MyRS![Expiry Date] & vbCrLf
End If
MyRS.MoveNext
Loop

MyRS.Close: Set MyRS = Nothing

MsgBox Left$(strBuildS tring, Len(strBuildStr ing) - 2), , "Passport Expiration Dates"
[/CODE]
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.

[NeoPa]

The following SQL should find the correct records for you :
[CODE=SQL]SELECT *
FROM [AdminStaff]
WHERE [PassportExpiryD ate]<DateAdd('m',1, Date())[/CODE]
Don't use Now() for a situation such as this. Date() doesn't include the time element.

[Pramodpmenon]

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:
[CODE=vb]
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
Dim strBuildString As String

strSQL = "Select * From Employees Where Not IsNull([Expiry Date]);"

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecord set(strSQL, dbOpenForwardOn ly)

Do While Not MyRS.EOF
If DateDiff("d", MyRS![Expiry Date], Now()) < 31 And DateDiff("d", MyRS![Expiry Date], Now()) > 0 Then
strBuildString = strBuildString & MyRS![LastName] & " ==> " & MyRS![Expiry Date] & vbCrLf
End If
MyRS.MoveNext
Loop

MyRS.Close: Set MyRS = Nothing

MsgBox Left$(strBuildS tring, Len(strBuildStr ing) - 2), , "Passport Expiration Dates"
[/CODE]
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.OpenRecord set(strSQL, dbOpenForwardOn ly)"

Please help me on this

Pramod

[Pramodpmenon]

The following SQL should find the correct records for you :
[CODE=SQL]SELECT *
FROM [AdminStaff]
WHERE [PassportExpiryD ate]<DateAdd('m',1, Date())[/CODE]
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?

[ADezii]

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.OpenRecord set(strSQL, dbOpenForwardOn ly)"

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.

[NeoPa]

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.

[Pramodpmenon]

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$(strBuildS tring, Len(strBuildStr ing) - 2), , "Passport Expiration Dates"

How can i solve this error, please help

[NeoPa]

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.

[Pramodpmenon]

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

Private Sub Form_Open(Cancel As Integer)
    
    Dim MyDB As DAO.Database, MyRS As DAO.Recordset, strSQL As String
    Dim strBuildString As String
 
    strSQL = "Select * From AdminStaff Where Not IsNull([Passport Expiry Date]);"
 
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
 
    Do While Not MyRS.EOF
    If DateDiff("d", MyRS![Passport Expiry Date], Now()) < 31 And DateDiff("d", MyRS![Passport Expiry Date], Now()) > 0 Then
    strBuildString = strBuildString & MyRS![Name] & " ==> " & MyRS![Passport Expiry Date] & vbCrLf
    End If
    MyRS.MoveNext
    Loop
 
    MyRS.Close: Set MyRS = Nothing
 
    MsgBox Left$(strBuildString, Len(strBuildString) - 2), , "Passport Expiration Dates"
   
   
    
End Sub

[ADezii]

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.

[NeoPa]

Could you just try changing line #20 for me to :

MsgBox strBuildString, , "Passport Expiration Dates"

... and seeing what you get. You can restore it afterwards if necessary. this is simply a quick test.

[NeoPa]

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).

[ADezii]

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.

[Pramodpmenon]

Could you just try changing line #20 for me to :

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.