DATE CRITERIA MAY NOT ALWAYS WORK WITH MS ACCESS QUERY

Learning about ms access query is the means to a good database management system as it is the heart of any database application.

There are many ways and questions to ask a database using queries and mastering the

special conventions and criteria will pay the dividend and avoid silly mistakes, the illogical recordset results, and even errors.

One of the more commonly used criteria and prone to…

Learning about ms access query is the means to a good database management system as it is the heart of any database application.

There are many ways and questions to ask a database using queries and mastering the

special conventions and criteria will pay the dividend and avoid silly mistakes, the illogical recordset results, and even errors.

One of the more commonly used criteria and prone to errors (if misunderstood) is the date/time data type and its conventions.

Take a look at the ms access query below-showing orders before the year 2016 and the intend

of the query which suggested a date range from 1st January 2016 to 31st December 2016.

The criterion for the above shows >=#01/01/2016 < #31/12/2016# which will not actually return the correct range and instead show what’s known as logical errors (dates outside the range).

It’s missing the ‘And’ operator and better still adding the ‘Between’ hand will capture the correct range too. The # (hash sign) is the exact convention and good check to ensure it’s the right data type too (date/time in this case).\

A revised example is shown below:

If the field is a actual date/time data type, you do not need to enter the # (hash sign) as it will automatically fill this in for you when moving the cursor elsewhere in the restrain (another convention checker for you).

If however, you see double-quotes (“ “) wrapped around the date expression, this suggests either you have chosen the wrong field or the date field is actually a text data type and it will simply not work as expected.

One final point to consider and even check for you ms access query database and those

queries are the regional setting of the operating system you are using. UK versus USA setting often throws up confusion and inaccuracies to so ensure you are using the correct date/time formats.

MS Access query is very powerful indeed and there are many permutations available – take

a look at my eBook Microsoft Access 2016 Database Queries.

Do you need for eBook relating to Access Database?

How To Run Microsoft Access On A Mac PC Using CrossOver Software – Alternative Options

Firstly, let’s clarify that Microsoft Office for Mac does not include the microsoft access database application which is why we have to take such steps.

The alternatives and work around could include:

1. Using the free runtime version for an exectuable file only or a third party web app which is now available for the iPad so you can load Access as a viewer only should you need to just read…

Firstly, let’s clarify that Microsoft Office for Mac does not include the microsoft access database application which is why we have to take such steps.

The alternatives and work around could include:

1. Using the free runtime version for an exectuable file only or a third party web app which is now available for the iPad so you can load Access as a viewer only should you need to just read information.

2. Installing VMWare (or similar) which is a virtual image software tool that creates a separate instance on your Mac PC and therefore can have several versions as a quick way to restore a default environment. However, it will require you having a licenced copy of Microsoft Windows operating system whereas CrossOver has this already included

3. Using some sort of remote access software like LogMeIn or SplashTop (which there is a free version. There are others too but the two mentioned I have used are very effective indeed but will require a live internet connection to a Windows PC.

4. Using a web based system like Microsoft SharePoint services (or similar) which basically means just using a browser off the Mac PC but be careful to not use Safari and opt for either FireFox or Chrome which will render better for SharePoint objects.

5. You could of course install a separate or boot split partition of a Microsoft Windows instance directly onto a Mac providing you have the latest Intel chip version (from 2006) and enough RAM (at least 8MB). This would be for regular heavy usage and not just for one application!

So there you have it, how to run Microsoft Access on a Mac. When I’m travelling and don’t have a strong enough internet connection, at least I can still carry out some Access work without the need to carry my favoured Mac Book Pro and a separate Windows laptop and keep my  machines optimised too.

When I first started to use Microsoft Access (back in 1993) which was then version 1.1 and quickly moved to the newer version of 2.0, you had various options available that could be set to change the behaviour of how you viewed and ran the basic objects. It probably took you no more than 15 minutes to discover, read (in the help files) and apply all settings that Microsoft Access databases had to offer – job done, never having to return to this area again!

Move forward 20 plus years and working with Access version 2013 you now have to either employ an individual to study and constantly refine the permutations of how Microsoft Access will work, behave and look. Okay, I may have exaggerated a little about employing and individual but your get the point! You need to spend a lot more time to understand the individual settings in isolation followed by how they are affected when coupled with other settings to get the best or at least the balance of how Microsoft Access database applications will serve you (and your business).

Microsoft Access Database: 5 Settings To Avoid With Microsoft Access Options Causing You Headaches – continued

I personally do not like all the settings that users can choose as I believe they cause performance problems and some instances causes system failures too which are called bugs though the developers at Microsoft may call them ‘characteristics of the system‘.

However, the following options and settings may need to be understood and at best either left alone or perhaps enforced to help handle unknown and performance problems that this application could challenge you with.

I have listed 5 settings for your perusal – you can make the decision. The following are found (using Microsoft Access 2013) via ‘File‘ backstage tab, under the Options command in the backstage area of your application:

Compact on Close – which can be found in the Current Database section when enabled will automatically compact and repair your database when closing the ACCDB file. Personally, do not set it! You can always compact and repair this manually which is better as it will help increase the application’s performance and more importantly you should only run this when in either ‘Exclusive‘ mode or you are the only person using the database file and have taken back-ups first.

AutoIndex on Import/Create – which can be found in the Object Designers section. It is automatically enabled and populated with four keywords; id, key, code and num. Leave this enabled and even add more generic keywords to it that you feel would serve as a unique name identifier. This feature will automatically assign an index key to it which will speed up your reporting namely those queries.

Confirm – which can be found in Advanced section. Never, never switch all three options off; leave them enabled. There are three settings; Record changes, Document deletions and Action queries which are designed to prompt the user before you delete records, objects and run action queries as once executed cannot be reversed.

No locks – found in the Advanced section too and should be left like this as it will allow appending or importing data into various tables more freely and unless there are a large number of concurrent users on the system then this keeps the system free from preventing changes and restricting record and page conflicts.

Set Trusted locations – which is found in the Trust Center Settings (button) is where you can manage and prevent the horrible pop-up banner users see when starting a database file other than from the default location on your pc. You simply train the system by adding locations (paths) with the option of specifying sub-folders included to avoid the disabling of any macro and module based code that is lurking in the database file.

Of course I could add another 5 options that I believe should be left alone and really only controlled via VBA code with the design intent of users being aware of how they wish to control an Access database.

Final point is that I have used Microsoft Access 2013 in this article and the above will also be applicable to earlier versions but located in different places if not slightly worded differently too.

Another Tip for you! Talking of VBA (Visual Basic for Applications), you can really take full control and dynamically set options with various events to help handle performance with functionality making the best of the Microsoft Access application. You call the main keyword Application.SetOption followed by the string value assigned normally to a numeric or boolean value i.e. Application.SetOption “Show Status Bar”, True

You use this type of query to change data in tables, saving the need to manually edit records or use any of the manual tools namely, Find andReplace. Examples for this action could be to reset data, change prices, increase volume and correct data migration and population issues.

One of the more commonly used examples is to carry out some sort of calculation and change prices for a range of products.

The above example increases all products for UK suppliers by 10% using the [Unit Price] field containing the original price value multiplied by 1.1 (an increase by 110%).

Just remember, this is an action query which means changes to any data values cannot be reversed once actioned which is why you should normally see a warning first.

However, it is always a good idea to take a copy or back up of your data files before running Microsoft access tutorial of this nature.