help with VBA in Access 2000

[midge]

Hi

I am new to using Microsoft Access and understanding VBA.

I hope someone can help me on my two problems with VBA in Access :-

1. I have two fields in my table Age and DateofBirth and what I would like to do is calculate their Age when someone inputs their DateofBirth. The calculation is based on 01/09/2002 minus the entry in the DateofBirth field and the result goes in the Age field. Please note that 01/09/2002 remains constant in this problem. Also could you tell me where to put the code e.g. BeforeUpdate or OnFocus for example.

2. I have two fields one called MemberID and the other called Team. When ever someone inputs the MemberID field then it automatically enters the corresponding Team entry in the field. I have used this example shown below but it does not work.

Dim strMemberID As String

strMemberID = [Member].Text

Select Case MemberID
Case "1"
txtTeam = "Under 8s"
Case "2"
txtTeam = "Under 9s"
Case "3"
txtTeam = "Under 10s"
End Select

 

[Ironis]

Why do yuou want to calculate the age with a constant date.. U coult consider using date functions. Then the age will change on the birthday. Code in After_update. When tabbing to another field, the code will run, and enter the age to the age field.

Furthermore on point 2, you made a typo i guess. I don't know if the code will work after that, but change

Select Case MemberID

Into:
Select Case strMemberID

That might be a good start.
Furthermore put the code in After_Update event. When you are finished typing the field, and tab to another field, the team will appear in the other textbox (when the code is right).

What I don't understand is:
How do you assign the memberID to the team? Do you plan to write code for every member? Or do you have a query or something else for that?

I hope I helped you a bit,

Ironis

 

[midge]

re help in VBA in Access 2000

Thanks for your reply !!

I have managed to solve my 2nd problem but still need to resolve the date calculation. I need to keep the date constant because I need to know the age of the player at a certain date which is 01/09/2002. I have used the following VBA in my database and it does not work !!

Datediff= [Date(01/09/2002)],[DateofBirth]

Any help in solving this problem is much appreciated !!

HAPPY CHRISTMAS !!!!!

 

[shay]

This should do it ...

DateDiff("yyyy", Me.DateofBirth, #9/1/02#) - IIf((Month(Me.DateofBirth) >= 9 And Day(Me.DateofBirth) > 1), 1, 0)

hth

shay

 

[midge]

Date Calculation

Hi Shay,

I have used the VBA that you sent me and unfortunately it does not work. The result was a syntax error.

DateDiff("yyyy", Me.DateofBirth, #9/1/02#) - IIf((Month(Me.DateofBirth) >= 9 And Day(Me.DateofBirth) > 1), 1, 0)

The DateofBirth field is formatted to short date i.e. 10/10/81 if that helps you.

Much appreciated for your help

 

[shay]

What does the error message say? I'm running Access97 here and the code I sent works for me.

shay

 

[midge]

Date Calculation query

Hi Shay,

I am running Microsoft Access 2000 and tried the same code that you sent me and it came up with a compile error : syntax error.

Maybe the code is slightly different in Access 97 to 2000. What a pain in the butt if it is !!

Sorry I couldn't be much help, but I do appreciate the help you have given me.

 

[shay]

Let's take a step back.

The "yyyy" is the interval argument for the Datediff function. It tells the function to work out the difference between the dates in years (rather than in months or days etc). Therefore, as I understand it, it has nothing to do with the way you state the dates when calling the function.

The Datediff function is doing a 'calculation' and so it must be assigned to something - a variable or text box for example. So you will need something like

me.txtAge = DateDiff("yyyy", Me.DateofBirth, #9/1/02#) - IIf((Month(Me.DateofBirth) >= 9 And Day(Me.DateofBirth) > 1), 1, 0)

Sorry I thought it would have been obvious!

shay

 

[shay]

Midge

Had you not assigned the calculation to your Age field/text-box? Do you still have a problem?

shay

 

[midge]

Date Calculations

Hi Shay,

Thanks very much for your help with this matter. The code that you gave me has now worked !!!

Much appreciated for your help !!


HAPPY CHRISTMAS !!!!