A phone number or extension. Our water truck drivers are not issued phones the trucks are and the phones stay in the trucks at all times, the drivers rotate to different trucks at any given time. The eight conference rooms have extension assigned to them and have always been listed on our phone list.
Tiffany Atchley
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, November 3, 2011 11:22 AM
Subject: RE: [MS_AccessPros] Customize report
Tiffany-
Um. I think of "contacts" as people, not trucks and rooms! What defines a water truck or conference room in your table?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Thursday, November 03, 2011 6:15 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Customize report
John,
Is there a way to group just specific records at the end of the report? I want all the conference rooms and water trucks listed at the end of the phone list.
Tiffany Atchley
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, November 3, 2011 10:32 AM
Subject: RE: [MS_AccessPros] Customize report
Tiffany-
Check the definition of the Office Phone and MobilePhone fields in your table. If Allow Zero Length is set to Yes, that could be the problem. There could be a "blank" instead of a Null in the field. Either change that property or try this:
SELECT Contacts.ID, Contacts.LastName, Contacts.FirstName, Contacts.EmailAddress, Contacts.Department, Contacts.[Office Phone], Contacts.MobilePhone, Contacts.FaxNumber, Contacts.Supervisor
FROM Contacts
WHERE ((Len(Contacts.[Office Phone] & "") > 0) OR (Len(Contacts.MobilePhone & "") >0 ));
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Thursday, November 03, 2011 5:20 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Customize report
Thanks, John
It worked on my Query perfect. I still have a problem though... On my report it is still showing names with no phone numbers at all??? I refreshed the report.
Tiffany Atchley
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, November 3, 2011 8:59 AM
Subject: RE: [MS_AccessPros] Customize report
Tiffany-
You didn't step down one line for the second Is Not Null. The SQL should look like:
SELECT Contacts.ID, Contacts.LastName, Contacts.FirstName, Contacts.EmailAddress, Contacts.Department, Contacts.[Office Phone], Contacts.MobilePhone, Contacts.FaxNumber, Contacts.Supervisor
FROM Contacts
WHERE (((Contacts.[Office Phone]) Is Not Null) OR ((Contacts.MobilePhone) Is Not Null));
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Thursday, November 03, 2011 3:57 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Customize report
John:
Now it is selecting just records with both an extension and phone. But not either/or. I need it to select all records that have a phone number and/or an ext.
Here is the SQL view:
SELECT Contacts.ID, Contacts.LastName, Contacts.FirstName, Contacts.EmailAddress, Contacts.Department, Contacts.[Office Phone], Contacts.MobilePhone, Contacts.FaxNumber, Contacts.Supervisor
FROM Contacts
WHERE (((Contacts.[Office Phone]) Is Not Null) AND ((Contacts.MobilePhone) Is Not Null));
Tiffany Atchley
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, November 3, 2011 1:33 AM
Subject: RE: [MS_AccessPros] Customize report
Tiffany-
What Duane means is open your query in Design view, then switch to SQL view. You probably need to put the Is Not Null on two separate lines - one on Criteria and the other one step down on the first Or line. When you switch to SQL view, it'll look something like:
WHERE (([PhoneNumber]) Is Not Null) Or (([Extension]) Is not Null)
You basically want the row if EITHER field is not null.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Thursday, November 03, 2011 1:01 AM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Customize report
Regarding #1, please post back with your report's record source SQL view.
Duane Hookom
MS Access MVP
To: MS_Access_Professionals@yahoogroups.com
From: cc0623@yahoo.com
Date: Wed, 2 Nov 2011 15:42:05 -0700
Subject: Re: [MS_AccessPros] Customize report
Thanks Bill,
The "Is not Null" was close but, it excluded all records if it had a nothing in the field. I need it to show the records IF they have an extension or phone number. Some people have phone numbers but no extensions and vice versa.
I played with the margins and it is obvious that appears to be the problem, when I look at print preview all the columns show up. The problem is it skips pages, I need to tell it to only add 8 columns to the page and then go to the next page.
Tiffany Atchley
________________________________
From: Bill Mosca <wrmosca@comcast.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, November 2, 2011 4:21 PM
Subject: Re: [MS_AccessPros] Customize report
Tiffany
I'm nearing going-home time, but I can maybe help with a couple things.
1. Your report should be based on a query. If it has a table as its Recordsource change it to a query. Add criteria in the query design view. Put: Is Not Null in both of those fields. That way, any records where they are null will not be on the report.
2. Anytime a report hs blank pages it means the report is wider than the margin settings so the extra width gets carried over to the next page. Columns are sometimes difficult to work with. try adjusting margins under page settings. Also see if the number of columns is too wide for the page.
If you can't get it to work upload a zipped sample to our Assistance needed folder. One of us is sure to help.
3. As far as I know, the only view that will show you all columns is preview, but I'm not sure about that. I'd have to see the report.
Bill
--- In MS_Access_Professionals@yahoogroups.com, cc0623 <cc0623@...> wrote:
>
> Bill -
> Â
> Can you help me with these last three problems? Then my report will be finished.
> Â
> 1. I would like to exclude any names from the report that does not contain an extension or phone number.
>
> 2. My report is not showing all the names. It says there are three pages but on page one the last name on the page starts with a C and then page two jumps to the R's and page three is completely blank.
>
> 3. I can only see the first four columns in all views other than print preview. In print preview it shows all eight columns. I would like to see all the columns in the other views as well if possible.
> Â
> Thanks,
>
>
> Tiffany Atchley
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Wednesday, November 2, 2011 11:26 AM
> Subject: Re: [MS_AccessPros] Customize report
>
>
> Â
> Tiff -
> Do you know how to use Conditional Formatting?
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, cc0623 <cc0623@> wrote:
> >
> > Acces 2010
> >
> > ÂÂ
> >
> > Tiffany Atchley
> > PO Box 1003
> > Fort Duchesne, UT 84026
> > 435-722-7833
> > WWW.ODYSSEYMINISHETLANDS.NET
> >
> >
> > ________________________________
> > From: Bill Mosca <wrmosca@>
> > To: MS_Access_Professionals@yahoogroups.com
> > Sent: Wednesday, November 2, 2011 11:23 AM
> > Subject: Re: [MS_AccessPros] Customize report
> >
> >
> > ÂÂ
> > Tiffany
> >
> > What version of Access are you using? 2003 and older has report conditional formatting just like forms.
> >
> > If that won't do it for you, use the section's format event like this:
> >
> > Select Case MyDept
> > Case 'Admin'
> > txtDept.BackColor = 255
> > Case 'Facil'
> > txtDept.BackColor = 12224
> > Case Else
> > txtDept.BackColor = vbwhite
> > End Select
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com, cc0623 <cc0623@> wrote:
> > >
> > > Okay here is the problem. I created a report off of a table. Tables and queries do not accept color formatting right? I do have a datasheet form colored the way I want it but it appears that I cannot create a report off a datasheet form. Is this correct? So how do I color code the depts on my form?
> > >
> > >
> > > Tiffany Atchley
> > >
> > > ________________________________
> > > From: Bill Mosca <wrmosca@>
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Sent: Wednesday, November 2, 2011 10:12 AM
> > > Subject: Re: [MS_AccessPros] Customize report
> > >
> > >
> > > ÂÂÂ
> > > Tiffany
> > >
> > > Are the colors coming from Conditional Formatting or are they ones you set in the form's design?
> > >
> > > Regards,
> > > Bill Mosca, Founder - MS_Access_Professionals
> > > http://www.thatlldoit.com
> > > Microsoft Office Access MVP
> > > https://mvp.support.microsoft.com/profile/Bill.Mosca
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, cc0623 <cc0623@> wrote:
> > > >
> > > > Duane,
> > > > How do I get it to carry over the color from the form? I have some of the records color coded. Also it was continuing into the next row like I wanted but then I moved the column titles up to the header and it stopped. I moved them back but it still does not carry over. I have hit refresh several times.
> > > >
> > > > Tiffany Atchley
> > > >
> > > >
> > > > ________________________________
> > > > From: Duane Hookom <duanehookom@>
> > > > To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> > > > Sent: Tuesday, November 1, 2011 5:20 PM
> > > > Subject: RE: [MS_AccessPros] Customize report
> > > >
> > > >
> > > > Tiffany,
> > > > Use the page setup menu options to create a multi-column report. I think you can even use the label report wizard.
> > > >
> > > > Duane Hookom
> > > > MS Access MVP
> > > >
> > > >
> > > >
> > > >
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > From: cc0623@
> > > > Date: Tue, 1 Nov 2011 22:57:10 +0000
> > > > Subject: [MS_AccessPros] Customize report
> > > >
> > > >
> > > > ÂÂÂ
> > > >
> > > >
> > > >
> > > > 1. The data is repeating itself in the second set of columns. I would like it to just continue with the data and not repeat the same data over again.
> > > >
> > > > The report is a contact list. There are 4 columns with data, I copied these four columns, so there are now 8 columns. I did this to maximize the space on the page. I would like the data to flow down the page and then back up to the next set of columns and back down again and continue on the next page until complete.
> > > >
> > > > Can someone help me with this?
> > > >
> > > > Thanks,
> > > > Tiffany
> > > >
> > > >
> > > >
> > > >
> > > >                 ÂÂÂ
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar