Zodiac sign from Date of Birth (DoB)

[Gordoni]

Hi
Within a query I've been trying to calculate a zodiac sign from a DoB field. Am assuming that an array would solve the problem but arrays have been a bit of a long-time mystery to me.
eg: Dob = 12-Jul-1947, Zodiac sign would be Cancer (June 21 to July 22).
Can anyone point me in the right direction please?
Many thanks
Gordoni

 

[KenHigg]

Just do a table - ?

 

[Gordoni]

Many thanks Ken - I'll work on that.
Gordon

 

[nanscombe]

Behind this form is a function, called whichStarSign, which will take a date of birth and return the star sign.

You could store the names of the signs and a number, 1 to 12 (or 13 if you believe in the 13th sign), in a lookup table then change the function to return the number instead.

 

[Gordoni]

To KenHigg and nanscombe
Hi
Your table tip worked a treat Ken - Don't know why I didn't think of this - Must be getting older than I thought.

I'm not a programmer nanscombe but your "case" statement appears to work something like "if-then-else". I'll certainly be having a play with it. That's the beauty of being retired - Plenty of time.

Many, many thanks to both of you. A big help

 

[nanscombe]

Yes, the SELECT CASE construct is very much a "if-then-else" but with a lot less complication.

As I mentioned earlier you could combine the two ideas.

You could have a look up table for the names of the signs and a number and use the function to convert a date to the number, rather than the name itself, for storage in your data table.

 

[KenHigg]

Glad you have it working!

 

[Isskint]

Nanscombe, that is a clever approach to identifying the date ranges.


I have added this to a couple of DB's at work, for Employee data. I added Starsign images also.

 

[nanscombe]

In the past I have used a simlar approach to avoid any ambiguities with dates in queries.

WHERE Format([SomeDate],"yyyymmdd") Between Format([StartDate],"yyyymmdd") AND Format([EndDate],"yyyymmdd")

It's also good for sorting, or grouping dates

ORDER BY Format([SomeDate],"yyyymmdd") ' Order by date - 20121116
GROUP BY Format([SomeDate],"yyyymm") ' Group by year and month (201201 - 201212)
GROUP BY Format([SomeDate],"yyyyq") ' Group by year and quarter (20121 - 20124)
GROUP BY Format([SomeDate],"yyyyww") ' Group by year and week of year (1 - 53) (201201 - 201253)

A list of other formatting options can be found here.