If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I'm going bonkers here
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??? -- Thanks for your time! |
#2
|
|||
|
|||
I'm going bonkers here
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "NC_Sue" wrote in message ... 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??? -- Thanks for your time! |
#3
|
|||
|
|||
I'm going bonkers here
If you are sorting the records using record source, it won't be consistent.
Select * From TableName Order By ... Instead in the reports you should use the sorting and grouping Look at this link http://msdn.microsoft.com/library/de...rInReportS.asp -- Please respond to the group if your question been answered or not, so other can refer to it. Thank you and Good luck "NC_Sue" wrote: 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??? -- Thanks for your time! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
countif problem - am I bonkers? | Lee Harris | Worksheet Functions | 3 | November 16th, 2005 05:43 AM |
Outlook has gone bonkers!! Someone help me please ... | Noneya | General Discussion | 7 | February 3rd, 2005 04:31 PM |