See all newsletters
Tips-FX will be a Bi-Monthly email newsletter that will focus on providing tips, help and information for skilled Microsoft Access users.
In this edition
JAZZ UP YOUR FORMS
ADVANCED GROUP BY
POPULAR ACCESS PAGES ON OUR SITE
DATA MINING AND FAST ACCESS GRAPHS
Add some Explorer like functionality to your command buttons using the following on MouseMove event. CmdOpenForm is an example button on an form.
Private Sub CmdOpenForm_MouseMove(Button As Integer, Shift
As Integer, X As Single, Y As Single)
On Error Resume Next
Only ever use this on your main forms and never use it on any forms where you are managing data as it will cause the update record event to occur. You can be more sophisticated by putting a toggle switch in the code so that focus is only set once and is turned off when the command button looses focus.
Are year 2000 issues going to affect your Access programs ? Microsoft say No for Access 95/97 and Yes for Access 2.
If you run the Total Access Inspector program written by FMS on the Northwind Database, the software identifies 77 High Risk issues that need to be addressed.
So what do you need to worry about and what do you need to fix.
Access and Office uses a shared DLL program for converting dates and time before they are stored in the database. Not every PC can be guaranteed to be using the same DLL so it is possible to have discrepancies in the way dates are entered into the database. Microsoft have an article in their online resource MSDN that outlines these issues
(and contradicts their previous Y2K statements).
To fix these issues, the FMS program says that you need to
change the formats that all your date fields use to force
4 digit date entry. eg "mm/dd/yyyy" and not "Medium Date"
You also need to add to change the input field to force 4 digit date entries and while you are at it add some validation strings.
The other big issues are any code using 2 digit years or "medium date" or "short date" and importing and exporting. I have written up a summary of the FMS year 2000 reporting
software plus the things you can do manually at
Date related information in tables is one area where it would be unusual to analyze
results by consolidating on a groups of data in the raw date form. Usually
want to look at weekly, monthly or quarterly results whilst the actual information would be stored with one or more entries per day.
Using the access format function, the SQL shown below
SELECT DISTINCTROW Format([SalesDate],"yyyy-mm") AS
SalesMonth, Sum(sales) AS TotSales
GROUP BY Format([SalesDate],"yyyy-mm");
producing the following output
So by consolidating the dates into months, we now can start analysing the data to look for possible patterns. Note: 2 important things with this query. Firstly the
Years are shown first followed by the months as numbers. This guarantees that the output will be sorted sequentially. Also when you start building these functions, always use the full 4 digit year or you will be introducing a Year 2000 bug into your code as year 2000 will show as "00" and sort first. Read more about Consolidation Queries at
Popular Access Pages On Our Site
which discusses how to setup Access for Jpegs and the like
We have now established an easier web site address for you to find more of the
great tips, help, lessons and links for Access, VB, ASP and Office that you receive
in Access Unlimited
Search the site at
And to celebrate this momentous occasion, check out this wonderful photo of Bill and the gang at Microsoft in all their 1970's glory (and wonder if you would have invested in Microsoft Mark 1).
Following are some links to good articles that you can download and read
for free. The articles featured this month come from the MSDN site
Add a table of contents page to long reports
Time to starting think about changing to ADO for handling your recordsets rather than DAO. Well try these articles
Note that there is an Microsoft download that will allow you to
start using ADO in your Access 97 databases.
And If you want lots of good code samples, Helen Feddema has plenty
The Toolbox is part of our web site that has been running for last two years
and is focused on providing tips, help and lessons on VB, Access, Frontpage and
Active Server Pages. Have a look around one day or even contribute and we will
make sure to give you good exposure for your efforts. The site has
300+ visitors a day.
A new version of our data mining shareware Graf-FX is now available for download from
The new version features wizards for starting your data mining more efficiently, setting up better grouping queries and generating filters for restricting your data. We have also jazzed up other parts of the program.
Thanks for reading our first newsletter and please send an email if you want to receive the next one. You can do this by clicking the the arrow button and signing up on the newsletter table of contents page.
Get Good Help Here
If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.
About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia