calculation to change value from DOB to age in VB, not what u thnk

[access_learner]

hey hey.

i posted this in the general section ut didnt get any responses.

now ive been thinking about it and i think it could be done in the coded area.

I am generating statistics from my table data.
gender, ethnicity etc etc.
i want to add Age to this.
however because im not storing Age, im calculating it from DOB i cant do it.

this is the code im using to generate the stats

Private Sub cboFields_AfterUpdate()
   Dim iTotalStudents As Integer
   Dim SQL As String
   
   iTotalStudents = DCount("*", "Students")
   
   SQL = "Select nz([Students].[" & Me.cboFields & "]," & _
            IIf(Me.cboFields = "Completed College", _
               "'Not Completed'", "'Not Specified'") & ")" & _
          ", Format(Count(*)/" & iTotalStudents & ",'Percent')" & _
          " from [Students]" & _
          " Group By [" & Me.cboFields & "]"
   
   Me.lblStatistics.Caption = Me.cboFields
   Me.lstStatistics.RowSource = SQL
   
End Sub

im thinking that i could add something to this code like

if (field selected = DOB){
then =DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))
}

i dont know VB, nor how i would code this.

but is my theory correct ?

when someone selects DOB from the list, because it is a stored value, and then could i run DOB through the same Age forumla i use to calculate the Age and display the differnt DOB's but as an Age. ??

i hope someone can help me with this.

cheers.

ACCESS_LEARNER

 

[WayneRyan]

ACCESS_LEARNER,

Pat gave you some very good advice in the General Section. I like code, but
this is not an instance for it.

For your DOB problem, it is very easy to add a new field to a query:

Age: DateDiff("y", [DOB], Date())

Then on whatever form/report you have, just include the Age field in it.
You'll see it on the dropdown lists, it's "part" of your data.

The other topics such as gender:

Select Gender, Count(*)
From   YourTable
Group By Gender

You really need to post your table structure and what you are trying to do.

Wayne

 

[access_learner]

ok.

well i made the Age query.

heres the link http://www.access-programmers.co.uk/forums/showthread.php?t=74237 to my first post about making stats from my database, maybe useful to explain what im trying to do, also has a sample which someone did for me (very nice person )

i have 1 form which is showing all the fields of my student database.
This form also includes the calculated field of Age which is calculated from the stored value or DOB.

I then have another form.statistics.

this form has a drop down box from which a user can select things like Ethnicity, Gender, Qualification etc.

When the user selects what they want a text box to the right of the drop down box is populated with statistics from this selection.

eg
User selects gender from the drop down box.

text box

Results for Gender | Percentage
_________________________________
|Male %50.00
|Female %50.00
|
|
|________________________________


and thats it.
now i want to do the same with the Age.
I want the user to be able to selcect age from the drop down and the text field to display all the Ages that are in the database. and because is not being stored but calculated from DOB i can not get it to work.

now that i have created this query i still dont know how to use this data to help me generate these stats.

 

[Jon K]

You can build a query qryStudentWithAge from the Students table:

SELECT [Gender],
DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")) AS Age,
[Completed High School]
FROM Students;


and then base the code on the query:

   SQL = "Select nz([[b]qryStudentWithAge[/b]].[" & Me.cboFields & "]," & _
            IIf(Me.cboFields = "Completed High school", _
               "'Not Completed'", "'Not Specified'") & ")" & _
          ", Format(Count(*)/" & iTotalStudents & ",'Percent')" & _
          " from [[b]qryStudentWithAge[/b]]" & _
          " Group By [" & Me.cboFields & "]"

.

 

[access_learner]

thanks for the reply Jon.

im having a bit of trouble getting the query to wrok though.

i keep getting syntax errors after putting
SELECT [Gender],
DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")) AS Age,
[Completed High School]
FROM Students;

into the build section of the query.

'The syntax of the usb query in this expression is incorrect
Check the sub queries syntax and enclose the subquery in parentheses'

also, should it be selecting Gender from Students or DOB ??, cos thats kinda confusing me too.

thanks

 

[Jon K]

SELECT [Gender],
DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")) AS Age,
[Completed High School]
FROM Students;

is the query in query SQL View.


To put the Age field in query Design View, you can put the expression in the field row like this:-
Age: DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd"))


Gender and DOB are two separate fields.

I have modified the sample database. The table now contains a DOB field. And a query "qryStudentWithAge" is added.
.

 

[access_learner]

ah ok i understand now.

why is it when i try create or view my query in design view its 'normal' but with yours its just a straight code page ??

i think thats why i couldnt get it to work, cos i couldnt get that code page, ive never seen it beofre in access.

thanks alot mate. youve been a great help

 

[May Parry22]

A query has three views: Design, SQL and Datasheet.

You can switch the query amongst these three views from the View menu or from the drop down list of the button on the toolbar (normally it's the first button on the Query Design toolbar unless you have customized it.)


When you start a new query, Access presents you with the Design View. But you can build (and save) a query in Design and/or SQL View. When you reopen a query by clicking on the Design button, Access will open the query in the view in which it was last saved.


After linking the tables and selecting the fields in query Design View, many people switch to SQL View to type their long expressions and save the query there.


Some queries, such as a Union query, cannot be built in Design View and can only be created in SQL View.