Query data calculation/conversion?

[Guest]

I have a query that pulls from a table of general client information (Name,
address, etc.). Two of the fields in the main table are Birth_Date and Sex
(which is listed as either MALE or FEMALE)

How can I calculate or convert the above data so the query returns the
following:

1) Instead of the actual date of birth, the query returns a number
representing the person's current age?

2) Instead of MALE or FEMALE the query returns either M or F?

Many Thanks,
David

 

[Guest]

Hi,
for the age issue check this:
http://www.utteraccess.com/forums/s...e=m&olderval=&oldertype=#Post176047&bodyprev=

For the Female Male issue, create a new column and use the left function:

YourNewField: Left([YourField],1)

Now this will only return F or M.
Then in the report or form only include that field.
HTH
Good luck

 

[Guest]

Hi Oliver,

Thanks so much. Both suggestions worked great! There's just one small
glitch: in the original table about 1/2 of the records have a blank field
where where the BirthDate should be. When I run the query I get the following
"#Error" . How can just have it leave the Age field blank if there's no date?

Many Thanks
David

Hi,
for the age issue check this:
http://www.utteraccess.com/forums/s...e=m&olderval=&oldertype=#Post176047&bodyprev=

For the Female Male issue, create a new column and use the left function:

YourNewField: Left([YourField],1)

Now this will only return F or M.
Then in the report or form only include that field.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps -

I have a query that pulls from a table of general client information (Name,
address, etc.). Two of the fields in the main table are Birth_Date and Sex
(which is listed as either MALE or FEMALE)

How can I calculate or convert the above data so the query returns the
following:

1) Instead of the actual date of birth, the query returns a number
representing the person's current age?

2) Instead of MALE or FEMALE the query returns either M or F?

Many Thanks,
David

 

[Guest]

Hi,
glad it worked out.
Use the Nz function to remove the error!
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps -

Hi Oliver,

Thanks so much. Both suggestions worked great! There's just one small
glitch: in the original table about 1/2 of the records have a blank field
where where the BirthDate should be. When I run the query I get the following
"#Error" . How can just have it leave the Age field blank if there's no date?

Many Thanks
David

Hi,
for the age issue check this:
http://www.utteraccess.com/forums/s...e=m&olderval=&oldertype=#Post176047&bodyprev=

For the Female Male issue, create a new column and use the left function:

YourNewField: Left([YourField],1)

Now this will only return F or M.
Then in the report or form only include that field.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps -

I have a query that pulls from a table of general client information (Name,
address, etc.). Two of the fields in the main table are Birth_Date and Sex
(which is listed as either MALE or FEMALE)

How can I calculate or convert the above data so the query returns the
following:

1) Instead of the actual date of birth, the query returns a number
representing the person's current age?

2) Instead of MALE or FEMALE the query returns either M or F?

Many Thanks,
David

 

[Guest]

Hey Oliver,

Ooh, I'm a big dummy! I don't know about the Nz function or how to apply it
to the query. Could you help me out?

Many, many thanks,
David

 

[Guest]

Hi,
have a look at this:
http://www.techonthenet.com/access/functions/advanced/nz.php
If you don't want to play with the nz function you could also filter out all
records which do not have a value in the date of birth field by applying this
criteria:

Is Not Null

This way you only get the ones which have a value.
HTH
Good luck