I'm going bonkers here

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm a newbie to access. Have a contacts db. In table for contacts include 2
BD fields - one mm/dd/yyyy, one mmmm/dd (don't know years for all contacts &
don't need to know age for most contacts). In all records where mm/dd/yyyy is
known, have included mmmm/dd in the second field (separate entry from
mm/dd/yyyy & did NOT enter year). Then developed a query which sorted
ascending on mmmm/dd and NO other sort fields. Then developed a report which
groups on month only (that part works fine) & then should sort on mmmm/dd.
Within a given month, it doesn't sort by anything consistently - not mmmm,
dd, first name, last name, or age. I want it to sort on date and it just
WON'T. Any ideas why?

Please help???
 
It's not necessary (nor appropriate) to have two separate fields like that.

What data type is the mmmm/dd field, and how is it being populated? If it's
a Date/Time field, be aware that Date/Time fields can only contain complete
dates (under the covers, they're 8 byte floating point numbers, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day). If you've been assigning the complete date field to that mmmm/dd
field, and are thinking that it's been changed to only contain the month
name and day, you're mistaken.

Since the mmmm/dd field will also have year in it, that would probably
explain why you can't sort it properly.

For your situation, it's probably most appropriate to use three separate
fields, YearOfBirth, MonthOfBirth and DayOfBirth, and only populate those
fields you know.

Then, create a query with computed fields representing the Date Of Birth for
those contacts for whom you know all 3:

DateOfBirth: IIf(IsNull([YearOfBirth]), Null, DateSerial(YearOfBirth,
MonthOfBirth, DayOfBirth))

and another computed field for Birthday this year:

BirthdayThisYear: DateSerial(Year(Date()), MonthOfBirth, DayOfBirth)

Sorting on the BirthdayThisYear computed field should then work.
 
Back
Top