Replies

When you go up to Microsoft’s gadget gallery, it does indeed say that ” the Windows website no longer hosts the gadget gallery. ” It also states: “Gadgets installed from untrusted sources can harm your computer”. I guess my questions are: Is Microsoft a “Trusted Source?” Are the 9 gadgets that came with Windows 7, “Safe?” Are we being draconian by eliminating the feature entirely? Note: These are not rhetorical questions, I really would appreciate answers. – Thank you

You might want to Consider this as an alternative:
1) Using Code, create a linked table to the SQL Server database (see: http://support.microsoft.com/kb/892490)
2) Once you have that linked table on your database it is a simple matter to Execute a Make Table Query off that table to create a local table in your Access database with everything you need.
3) Once you are done with the linked table, go ahead and delete it

I would think this would be MUCH faster than open recordsets and looping through records, etc.

Hope that helps

In addition to what has been discussed previously, you might want to review this paper the addresses Query Exectuion Plans and how they can impact performance: http://technet.microsoft.com/en-us/library/ee343986(SQL.100).aspx

Hope that helps

I watch your column with great interest but have never really needed to avoid any updates until recently when my PC wouldn’t install the automatic updates. Thankfully there is some “Rollback” feature in Windows 7 that when an update install fails on reboot, it sidesteps the install and continues to boot up without it. After this type of failure, I go up to Windows update, review your article (comparing the scheduled updates to the update info in your list) and then deselect and hide the offending updates.

Thanks!

Fred,

I normally agree with your columns. You give great advice. So I am puzzled. Did I miss something? Your advice to change my internet settings to protect against Evercookies has caused my browser (IE8) to continually prompt me when I go to a site. Specifically, when I came to Windows Secrets, I think I got prompted 16 times! When I go to My Yahoo page, I got hit with 25 prompts about safe scripts. I am going to get carpel tunnel from all this clicking! So I figure I must have missed something. In this case the solution seems worse than the disease.

Thanks.

Bob Oxford

Corey,

Perhaps you have come to a conclusion with this. If not, here are a couple of things to think about.

First of all, I took your spreadsheet and turned it into a Table in Access 2010 called “Sheet1” and then I built a query. I removed the Formatting from the 2nd Column so that I could see the raw data. The 3rd Column used the Format Properties. The 4th Column used the FormatPercent Function and the 5th Column used Rounding.

SELECT Sheet1.Account, Sheet1.[0to5Rank], Sheet1.[0to5Rank] AS UsingFormat, FormatPercent([0To5Rank],1) AS UsingFormatFunction, Round([0to5Rank],3) AS UsingRounding
FROM Sheet1;

If you do the same, you will notice some interesting results.

1st of all, depending on the version of Access you are using, you might want to avoid the Round() function. Until Access 2007 I believe they used “Banker’s Rounding” which rounds to the nearest EVEN number. A bit different than what you would ecpect if you are used to using Excel. If you want to use Rounding in those Access Versions you should write you own function to do so.

Secondly, if you look at the results in Column 3, everything looks fine until you click in a cell in that column. At which point you will notice that you see the entire number Formatting a column like that changes the way the column LOOKS and not the actual value in the column. If you were to do subsequent calculations using these cells, the ACTUAL value would be used, not the displayed value. This can cause quite a bit of confusion as sometimes things appear to total incorrectly.

Column4 uses the FormatPercent Function. The value that you see will be the value that is used in calculations because the number returned is being processed throuhg a function and giving you a result with only the precision that you specify.

So…depending on what you are going to do with the results of your query will determine how you want to handle the issue. This should give you some additional infforation to make that decision.

Happy holidays!

Bob Oxford

Just a few observations

You can certainly can do an emailing using Word 2003 and Access 2003 and use Outlook 2003 as the email client.

First, create and run a MakeTable query in Access that gets you the records and columns you need and name the query something like: qryAnnualEmailing and have it create tblAnnualEmailing
Next go into word and use the MailMerge Wizard to create an EMail merge using the table (tblAnnualEmailing) that you created in Access as the DataSource
Be sure to fill in the subject and identify the field that contains the email address
Make sure that Outlook is open
Perform the Merge.

Depending on your ISP/Mail Server, it may take a while (Some mail servers only allow a certain number of emails to be sent within a certain time frame (100 per 10 minutes, etc.)) but eventually Oiutlook will take care of sending them all out provide that you leave Outlook open

If you want to automate all of this, you can do so from within Access by opening up an instance of Word, etc…but since you only do it once a year you may find that the effort involved in doing the coding is much more costly than just doing as I have illustrated above.

Now this doesn’t create a separate PDF but I think it accomplishes your goal of getting your emails out. Creating a separate PDF would involve the hassle of a) creating the PDF from withing Office 2003 b) Sending out emails with an attachment. Far more annoying than it sounds I’m afraid.

So, that is how I would probably attack it given the frequency of your mailing.

To simplify my life, when I have repeated mailings I need to do to specific groups I have chosen to use a subscription like Constant Contact. Upload, create and go…You can track it, it let’s you know who opened it, what emails bounced, it is simple to update your list, people can unsubscribe, it puts the appropriate messages on there to comply with emailings, etc….so simple but it does cost a little $$

Hope that helps.

Bob Oxford

Very interesting. I stopped creating/modifying Querydefs through code a while back because it caused major MDB file bloat. Each time you modified a Querydef, MDB file expanded. haven’t tested it since then (Access 2000). I will still do it if I have to in order to create data for export.

If the user wants a PDF of their Report after they Print Preview it, in 2010 they can just do a File Save & Publish and send it to PDF. BUT…yours is a nice solution if you want to Print directly to PDF without Previewing and without user intervention.

Bob Oxford

PMJI

I see you have a couple of solutions. I would have to agree wth John that in most cases the best way is to use the WhereCondition of the OpenReport method when running the report rather than basing a report on a Query that has parameters. i thought I would try to explain why you got the reults you did.

If I understand what you were trying to do. You want the third argument to place “>([Forms]![frmReports]![cmbYears]) into the criteria of the query and replace [Forms]![frmReports]![cmbYears] with the value in the combo box. That is not exactly what happens.

The Query design grid is only there to help you write a SQL Statement. Utimately what gets processed is the SQL Statement (A Text String) so it can help to examine the SQL that gets created by the designer.

I have created a very basic example using the Northwind sample database to illustrate.

I created a Form called frmTest with a single comboBox on it named: cboInvoiceDate.

Because in the Northwind Database they used the time portion of the Date/Time field when entering data and I only wanted the Date Portion and unique date values, I entered the following in the RowSource for the ComboBox is the invoice numbers from the Invoice Table:
SELECT DISTINCT CDate(FormatDateTime([Invoice Date],2)) AS InvDate FROM Invoices;

I created a Query based on the Invoices table to include [Invoice Date] and [Amount Due]

As you did, I placed the following in the Criteria under the [Invoice Date]:
IIf(IsNull([Forms]![frmTest]![cboInvoiceDate]),Date(),>([Forms]![frmTest]![cboInvoiceDate]))

Here is the SQL statement that was built as a result:
SELECT Invoices.[Invoice Date], Invoices.[Amount Due]
FROM Invoices
WHERE (((Invoices.[Invoice Date])=IIf(IsNull([Forms]![frmTest]![cboInvoiceDate]),Date(),(Invoices.[Invoice Date])>([Forms]![frmTest]![cboInvoiceDate]))));

We can break down the WHERE Clause when the Combo box is NOT Null assuming the cboInvoiceDate value is 3/24/2006 and on the First Record the [Invoice Date] = 3/22/2006

1st Step –
WHERE invoices.[Invoice Date] = (Invoices.[Invoice Date])>([Forms]![frmTest]![cboInvoiceDate])

2nd Step
WHERE 3/22/2006 = (3/22/2006)>(3/24/2006)

3rd Step
Look at the right side of the Equal sign and evaluate that first
(3/22/2006)>(3/24/2006)
Of Course this is false then you get the following

4th Step
Now you have
WHERE 3/22/2006 = False
Of Course that is False (And it will NEVER be TRUE regardless of the data)

5th Step
So Now you Have
WHERE False

Now let’s plug that back into the original statement:
SELECT Invoices.[Invoice Date], Invoices.[Amount Due]
FROM Invoices
WHERE False

Of course now it won’t display any records

I hope this helps you see why it does not return any records

Bob Oxford

Sure…

One way is to set up a Tab Control with a different page for each situation. Put the controls in places that you want them and then just choose the desired page of the tab depending on the scenario.

Another way is to move the controls around on the form by setting the Left and Top Properties as needed.

Also, Controls can overlay each other in design view and you can just set the Visible property of each control depending on the situation. Lots of ways to work this out.

Hope that helps.

Bob Oxford

I think a better way for you to approach this would be to examine your reasoning for not creating a persistant form and showing, hiding and populating controls etc as needed. Creating a form “On the fly” in design view seems to me to be frought with potential disaster.

Is there a reason you are not creating a persistant form?

Bob Oxford

I think what you will want to do is manipulate 2 of the Properties for the Control displaying the Long Time:

Input Mask: 99:00:00;;_
Format: hh:nn:ss

Input Mask – will control how you input the data
Format – will control how it is displayed once the data has been entered

Hope that helps.

Bob Oxford

Oh yes…I certainly agree about your source of business. Quite a number of my clients start with Excel, think they can use Access but never learn how to use it, bringing their Excel paradigm to Access. They dig themselves a large hole from which there is no internal escape so the come to me!

I have never really had to use the Macro converter either but I tried it because I teach this stuff and need to at least know it is there. When I tried the Macro Converter in 2010…it just fails. Dug a little deeper and found the admission of the bug that no one seems to be planning to fix.

A LONG time ago, I used the code wizards to help me start learning about code and Access. I could write an addin but there are others who have more time to do that type of stuff! I gave up on using purchased tools a while ago. I used FMS Access tools which were good but It was just another application to keep updated. I did like them though. I’ll have to check out the MZ Tools.

I guess we’ll just keep helping people out of the holes they find themselves in, eh?

Thanks for your comments.

Here is an article from MSDN which might be of some help

http://msdn.microsoft.com/en-us/library/aa217449(office.11).aspx

Assuming the following table structures

Complete_Report
ID (Long Integer)
WriterName (Text)

For_Processing
ID (Long Integer)

The Query Should read:

SELECT For_Processing.ID, Complete_Report.WriterName
FROM Complete_Report INNER JOIN For_Processing ON Complete_Report.ID = For_Processing.ID

Pay particular attention to the Data Types in the tables as the data types on the Join Fields need to be the same.

Bob Oxford