Sabtu, 10 September 2016

RE: [MS_AccessPros] Birthdays Report

 

Hi Robin

So, is your Birthdays report working now?  If so, please share what you did to get it going.

Cheers,
Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Sunday, 11 September 2016 13:42
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Birthdays Report

 

 


Duane,

I am ashamed of the mess that I have made and plan to "TIDY UP" when I am finished. Your advice so far has got me where I need to be. So "Thanks, but No Thanks".

A very appreciative,

Robin Chapple

At 10/09/2016 10:58 PM, you wrote:

 

Can you share your file in the assistance needed folder?

Duane

Get Outlook for iOS




On Sat, Sep 10, 2016 at 3:12 AM +0200, "Robin Chapple robinski@westnet.com.au [MS_Access_Professionals]" < MS_Access_Professionals@yahoogroups.com> wrote:



G'day Graham and Duane,

At 10/09/2016 05:28 AM, you wrote:


First, your qData query contains many calculated fields which are not required for your birthdays report, so it would be much more efficient to pull the data directly from the table like this:

SELECT Data.Display, Data.LastName, Data.fFirstName AS FirstName, Data.FDOB AS DOB
FROM Data
WHERE (Data.ResidentStatus<5) And (Data.fDOB Is Not Null)
UNION
SELECT Data.Display, Data.LastName, Data.mFirstName AS FirstName, Data.mDOB AS DOB
FROM Data
WHERE (Data.ResidentStatus<5) And (Data.mDOB Is Not Null);

It is curious that the query seems to be returning the DOB as text which is then evaluated as an arithmetic expression (day ÷ month ÷ year).  Usually a UNION query will determine the type of each column from the first SELECT statement in the UNION.   Can you please confirm that *both* fDOB and mDOB are date/time fields?


Both are Date/Time format.


Also, as Duane suggested, please tell us whether the DOB column is left- or right-aligned.


The DOB column is right aligned.


You might need to save the above query as, say, "qDOBs" and then use a second query as your RecordSource like this:

SELECT Display, Lastname, FirstName,
CVDate(DOB) as DateOfBirth, Month(DOB) as MM, Day(DOB) as DD
FROM qDOBs;

Then you can have a primary group (with header) on MM and a secondary sort on DD.


I am working on this.

Thanks for your help.

Robin


---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I see there is a union query involved. When you view the data sheet of the Union, are the DOB left or right aligned. I expect they are left which suggests they are being converted to text.  If this is the case you need to convert them back to dates using CDate().

Duane

Get Outlook for iOS




On Thu, Sep 8, 2016 at 9:10 AM +0200, "Robin Chapple robinski@... [MS_Access_Professionals]" < MS_Access_Professionals@yahoogroups.com> wrote:



Thanks Graham,

This is the main query "qData" :

SELECT Data.RecordID, Data.Display, Data.SortOrder, Data.ID, Data.Unit, Data.fFirstName, Data.fInitial, Data.mFirstName, Data.mInitial, Data.Phone, Data.LastName, Data.pLastName, Data.email2, Data.email, Data.Villager, Data.UnitType, Data.mDOB, Data.FDOB, Data.Table, Data.Activity, Data.ConvenorGenderMale, Data.ConvenorBoth, Data.Activity2, Data.Convenor2GenderMale, Data.Convenor2GenderBoth, [fFirstName] & IIf([LastName]=[LastName],""," " & [LastName]) AS fResident, IIf([fFirstName] Is Null And [mFirstName] Is Not Null,[mFirstName] & " " & [LastName],"") AS mResident, IIf([LastName] Is Null,[LastName],IIf([LastName] Is Not Null,[LastName],"")) AS rLastName, Data.ResidentStatus, [Unit] & " Type " & [UnitType] AS NoAndType, IIf([fFirstName] Is Null,"",[fFirstName] & " " & [LastName]) AS fName, IIf([mFirstName] Is Null,"",[mFirstName] & " " & [LastName]) AS mName, Data.LastUpdated, Data.DateOccupied
FROM Data
ORDER BY Data.SortOrder, Data.ResidentStatus;

Then I eliminate records where "ResidentStatus" does not qualify:

SELECT qData.Display, qData.LastName, qData.fFirstName, qData.mFirstName, qData.FDOB, qData.mDOB, qData.pLastName
FROM qData
WHERE (((qData.ResidentStatus)<5))
ORDER BY qData.LastName;

Then this is the last query from which I hope to make my birthday lists:

SELECT Birthdays.Display, Birthdays.LastName, Birthdays.fFirstName AS FirstName, Birthdays.FDOB AS DOB
FROM Birthdays
WHERE (((Birthdays.fFirstName) Is Not Null))

UNION SELECT Birthdays.Display, Birthdays.LastName, Birthdays.mFirstName AS FirstName, Birthdays.mDOB AS DOB
FROM Birthdays
WHERE (((Birthdays.mFirstName) Is Not Null));

I must admit the now that I have had to explain the construction it does seem complicated!

Many thanks,

Robin


At 8/09/2016 11:44 AM, you wrote:

 

Hi Robin

Can you please post the SQL of the query you are using as the RecordSource for your report?  Also, If the birth date in that query comes from another query(ies) then please post that SQL also.

Cheers,

Graham

 

From: MS_Access_Professionals@yahoogroups.com [ mailto:MS_Access_Professionals@yahoogroups.com]

Sent: Thursday, 8 September 2016 11:51

To: MS_Access_Professionals@yahoogroups.com

Subject: Re: [MS_AccessPros] Birthdays Report

 

 

John,

The field is a Date/Time data type.

Robin

At 7/09/2016 11:16 PM, you wrote:

 

Duane and Robin-

Or the field he's using isn't really a date/time data type.  The local format doesn't matter if you pass a true date/time to the Month function.  If it's text or something else, then that's the problem.

John Vie scas, Author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

http://www.viescas.com/

(Paris, France)

On Sep 7, 2016, at 3:14 PM, Duane Hookom duanehookom@... [MS_Access_Professionals] < MS_Access_Professionals@yahoogroups.com> wrote:

Robin,

The reason why December and January are significant is because

?Month(11/01/12) =  12

?Month(31/01/04) = 1

?Month(28/02/01) = 1

whereas:

?Month(#28/02/01#) = 2

There first three calculations (in the debug window) divide the first number by the second by the third rather than assuming these are dates.  Using this type of calculation sugg ests there will be no values that return a month between February and November. You apparently are located in a location where dates are not m/d/y.

Regards,

Duane


To: MS_Access_Professionals@yahoogroups.com

From: MS_Access_Professionals@yahoogroups.com

Date: Wed, 7 Sep 2016 10:23:50 +1000

Subject: RE: [MS_AccessPros] Birthdays Report

Duane,

Thanks fir your patience.

At 6/09/2016 08:39 PM, you wrote:

 

To get to the debug window press Ctrl+G. Then paste:

? Month(Date )

and press enter. You should get: 9

Yes, I get a 9

 It seems like you might be passing the day of the month into your grouping rather than an actual date.

 

Can you share your some sample values of your birthdates? I assume 1/01/1 900 is for a January 1st birthday.

1/03/1936, 24/12/1924   5/03/1923, 6/12/1927, 29/12/1928, 16/08/1935,   17/02/1932, 31/01/1930, 14/08/1936, 9/08/1933, 26/04/1939, 28/11/1931, 6/03/1927, 26/01/1938, 30/11/1929, 7/05/1942     , 11/09/1939, 11/02/1943, 26/01/1931, 17/08/1930, 22/01/1930, 5/08/1943

Yes we are residents in a retirement vilage!

 Try add the month and day into your report's record source by adding a columns

 

Mth: Month([Birthdate])

DayNum: Day([Birthdate])

 

You should see the actual months and days based on the birthdate column. In your report you could group by Mth and sort by DayNum.

I had already done that as I needed September dates as a matter of urgency.

Regards,

Robin


To: MS_Access_Professionals@yahoogroups.com

From: MS_Access_Professionals@yahoogroups.com

Date: Tue, 6 Sep 2016 10:34:14 +1000

Subject: RE: [MS_AccessPros] Birthdays Report

Thanks Duane,

My apology for the delayed response. I have been tied up with another problem

A t 31/08/2016 05:24 PM, you wrote:

 

Robin,

Just December and January definitely helps with troubleshooting.

 

I don't believe you answer the question/email about if you have another function named Month(). He also asked you to open the debug window (press Ctrl+G) and enter

? Month(Date)

 

I do not have a function named Month(). Where do I start from for the debug window?

I believe someone asked if the date field is actually a date. Since you have only December and January, I expect you aren't storing an actual date value like Mar-21-1980 or Aug-2-2001.

 

The date field is actually a date. The date is stored as 1/01/1900.

I hope that helps

Regards,

Robin

 

If you don't want to store the actual year of birth, at lea st sto re them all as the year 1900 or similar since the birthday report discards the year.

 

Regards,

Duane


To: MS_Access_Professionals@yahoogroups.com

From: MS_Access_Professionals@yahoogroups.com

Date: Wed, 31 Aug 2016 07:22:18 +1000

Subject: RE: [MS_AccessPros] Birthdays R eport

Duane,

I am still perplexed. The grouping shows January Birthdays first with a December header and all remaining in a group with a January header.Most odd.

Cheers,

Robin

At 31/08/2016 06:42 AM, you wrote:

 

Robin,

Has this been resolved? If so, I think I missed the email.

Regards,

Duane


To: MS_Access_Professionals@yahoogroups.com

From: MS_Access_Professionals@yahoogroups.com

Date: Tue, 30 Aug 2016 09:29:17 +0200

Subject: Re: [MS_AccessPros] Birthdays Report

OK, that makes more sense!  If the day sort is below the group on month, then the records should be sorted correctly.

John Viescas, Author

Effecti ve SQL

SQL Queries for Mere Mortals

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

http://www.viescas.com/

(Paris, France)

On Aug 30, 2016, at 8:37 AM, Robin Chapple robinski@... [MS_Access_Professionals] < MS_Access_Professionals@ yahoogroups.com> wrote:

OOOPS! Sorry that  Was a typo !!!!  It should be 10. All of the other entries return 10. Yes ten.

Red faced!

 Robin

At 30/08/2016 01:48 PM, you wrote:

 

Robin-

The Month function should return values 1 to 12.  It should never return 19!  Is birthdate a Date / Time field?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Offic e Access 20 07 Inside Out

Building Access Applications

http://www.viescas.com/

On Aug 29, 2016, at 23:48, Robin Chapple robinski@... [MS_Access_Professionals] < MS_Access_Professionals@yahoogroups.com> wrote:

Duane,

The month provides numeral 1 for the first group and numeral 19 for th e second group. There are no other groups.

The day gives the day date for each entry.

I hope that means something to you!

Thanks,

Robin

At 29/08/2016 03:42 PM, you wrote:

 

Robin,

Add a text box to the month gro up header with a control source of:

=Month([birthdate])

Add a control to the detail section with a control source of:

=Day([birthdate])

What do you see when you run the report?

Duane Hookom


To: MS_Access_Professionals@yahoogroups.com

From: MS_Access_Professionals@yahoogroups.com

Date: Mon, 29 Aug 2016 08:24:37 +1000 < /dd>

Subject: RE: [MS_AccessPros] Birthdays Report

Thanks Duan e,

I have now done that and the first group shows January as the first month and lists birthdays in ascending order.

The next group header is May with all the remaining birthdays in random order. There is not another month group header

What have I missed?

Regards,

Robin

At 26/08/2016 08:17 PM, you wrote:

 

Robin,

 

Display the report sorting and group. Then select to group by the expression:

  Month([birthdate])&nbs

p;

Then add another sorting expression of:

  Day([birthdate])

You can format the [birthdate] text boxes in your report sections to display whatever you want.

 

Regards,

Duane

 

> From: MS_Access_Professionals@yahoogroups.com

> Date: Fri, 26 Aug 2016 14:02:03 +1000

>  ;

> I have a members database which includes their birthdays. I plan to

> make a report that groups birthdays on the month an d then sorts them

> by the day.

>

> How do I do that?

>

> Many thanks,

>

> Robin Chapple





 





 

 

__._,_.___

Posted by: "Graham Mandeno" <graham@mandeno.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (36)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Poskan Komentar