Derive birthday from field and pop-up.

[Hartkoorn]

Our company has a employee numbering system like 65.01.20.HAR this is also the primary key that I use in the table.
The employee number is build up like this:65=year 01=jan 20=day HAR=first three letters of last name, so 65.01.20.HAR is me Hartkoorn born on 20-01-1969.
Furthermore I have FirstName and LastName in that same table as well.
Now, here's the thing:
I want a pop-up when starting the database notifying the people that have their birthday that week like:

Paul Hartkoorn 20 jan: 41 years
Pete Whatever 24 jan 26 years
etc

Any way to achieve this?
Thanks in advance

 

[Matt Greatorex]

As somewhere to begin, you could set a form to open on startup. This form could display a listbox containing the information you need.

To find the data, you would add code to the form's OnOpen event.

Dim str_SQL as String
Dim ld_Date as Date

ld_Date = Now

str_SQL = "SELECT [Employee Number], [First Name]" & " " & "[Last Name] as Employee, " & _
              "Left([Employee Number],2) as Age" & _
              "FROM [I]table name[/I] " & _
              "WHERE [Employee Number] = #" & Format(ld_date,"YY.MM.DD") & "#;"

[Forms]![[I]form name[/I]]![listbox name].Rowsource = str_SQL
[Forms]![form name]![listbox name].Requery

That should give you a list of any people, along with their ID numbers and their age in years. From this, you should be able to play with the format and display the data in whichever way you want.

 

[Hartkoorn]

Thanks for the fast reply, there's just one minor thing:
As I open the form the listbox shows:
SELECT [Werknemernummer], [Naam] [VoorNaam] as Employee, Left([Werknemernummer],2) as AgeFROM Persoons_Gegevens WHERE [Werknemernummer] = #06
09
01#
I added a record with a person who has its birthday today
Any thoughts?
Here is my code:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
Dim str_SQL As String
Dim ld_Date As Date

ld_Date = Now

str_SQL = "SELECT [Werknemernummer], [Naam]" & " " & "[Voornaam] as Employee, " & _
              "Left([Werknemernummer],2) as Age" & _
              "FROM Persoons_Gegevens " & _
              "WHERE [Weknemernummer] = #" & Format(ld_Date, "YY.MM.DD") & "#;"

[Forms]![FormVerjaardag]![Verjaardag].RowSource = str_SQL
[Forms]![FormVerjaardag]![Verjaardag].Requery

End Sub

Ofcourse the name of the listbox is called Verjaardag and the form is called FormVerjaardag

 

[Matt Greatorex]

Oops! I gave you slightly wrong syntax.

There was a space needed after 'Age' and the gap between 'Naam' and 'Voornaam' should have been within the string, rather than outside of it.
I just tested this against a dummy table and it seems to work okay.

str_SQL = "SELECT [Werknemernummer], [Naam] & ' ' & [Voornaam] as Employee, " & _
              "Left([Werknemernummer],2) as Age " & _
              "FROM Persoons_Gegevens " & _
              "WHERE [Weknemernummer] = #" & Format(ld_Date, "YY.MM.DD") & "#;"

One last thing - make sure the listbox has the right number of columns. It shouldn't error if it hasn't, but you won't see all of the data so it will look like something's wrong. Sorry if I'm telling you what you already know.

Good luck.

 

[Hartkoorn]

LOL so even VB programmers are human after all
Thanks for the quick response mate!

 

[Hartkoorn]

Sorry to nag you again Matt,
But the listbox keeps on returning the SELECT statement.
Must be going bonkers here, did I miss something in the listbox properties?
I attached a screenshot of the form in view mode and the VB code window, maybe I'm missing something here.

 

[Matt Greatorex]

Originally, you used the line:

[Forms]![FormVerjaardag]![Verjaardag].RowSource = str_SQL

to refere to the control in question.

In your snapshot, it appears that you have changed this to:

[Forms]![Verjaardag]![Verjaardag].RowSource = str_SQL

Is this a valid reference? If not, then running the script wouldn't have changed the value from the one you originally had the problem with.