Date converting

J

Jeff

Hi Everyone,

I am building a page about Horoscopes. The user key in a day (in Date
datatype), then it automatically displays a Horoscopes. There must be
something like:

If InputDate Between Nov 23 And Dec 21 Then
txtHoro = "Sagittarius"
ElseIf InputDate Between Dec 22 And Jan 19 Then
txtHoro = "Capricorn"
...................................
End If

Could someone help me write this in codes? No idea how to convert just month
& day in Date type. Thank you.
 
V

vanderghast

Place the data into a table. The easiest way it to have a StartingDate and
EdingDate for each Sign. Supply a fix year, say 2000, to have a complete
date, with a year, in addition to the month and day.



HoroscopeTable ' table name
StartingDate EndingDate Sign ' fields name
2000.11.23 2000.12.21 Sagitarus
2000.12.21 2000.12.31 Capricorn
2000.01.01 2000.01.19 Capricorn
2000.01.20 2000..... .... ' data





Then use a DLookup:


DLookup("Sign", "HoroscopeTable", Format(CDate(DateSerial( 2000,
yourMonth, yourDay), "/#mm-dd-yyyy/#" ) & " BETWEEN startingDate AND
endingDate" )




(note that Capricorn,in the table, is broken in two parts, to get the
BETWEENess correctly considered).

I assume you don't need astronomical precision, ie, Sagitarus always end the
21st of December for EACH and EVERY year.


Vanderghast, Access MVP
 
J

John Spencer MVP

In VBA code

If IsDate(InputDate) then

Select Case Month(InputDate)*100 + Day(InputDate)
Case >=12022 or <=119 'Capricorn

Case >=11023 and <=12021 'Sagittarius

Case >= ???? and <=11022 'some other sign
...
End Select

End If

Actually if you carefully arranged the values from Largest to smallest you
would only need to check the >= side and would not need the Less Than except
for Capricorn since it splits the year

Case <= 119
'Capricorn
Case >= 12022
'Capricorn
Case >= 11023
'Sagittarius
Case >= ???
'????
End Select

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

John W. Vinson

Hi Everyone,

I am building a page about Horoscopes. The user key in a day (in Date
datatype), then it automatically displays a Horoscopes. There must be
something like:

If InputDate Between Nov 23 And Dec 21 Then
txtHoro = "Sagittarius"
ElseIf InputDate Between Dec 22 And Jan 19 Then
txtHoro = "Capricorn"
..................................
End If

Could someone help me write this in codes? No idea how to convert just month
& day in Date type. Thank you.

As a former astrologer I had to chuckle... and wince a bit. A sun sign is NOT
a horoscope, and makes up only a tiny fraction of one. In addition, the Sun
moves from sign to sign at a specific moment of time which varies quite a bit
from year to year; November 23 might be in Sagittarius one year and in Scorpio
the next, so your cusp values are likely to be wrong.

What's the context, though? Do you have an Access database? If so what tables
do you have in it? Where is this pseudocode going to be?

Access stores dates as a number, a count of days from midnight, December 30,
1899. What you could do to get the sunsigns is create a little thirteen-row
table Ephemeris with two fields: StartDate and Sign, with values like

1/1/1900 "Capricorn"
1/21/1900 "Aquarius"
2/22/1900 "Pisces"
....
12/22/1900 "Capricorn"

Create a Form with two textboxes, Birthdate and Sign. You could then use an
expression

=DLookUp("Sign", "Ephemeris", "[Startdate] <= " & DateSerial(1900,
Month([Birthdate]), Day([Birthdate]))

as the Control Source of the Sign textbox.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top