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 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?

Here, you can check : https://accessdatabasetutorial.com/ebooks/

Advertisements

MICROSOFT ACCESS DATABASE 2007 TUTORIAL

 

When Microsoft Access first appeared in the early 1990’s it revolutionized the desktop database market. Most versions have been similar to each other. Now with Access 2007, we see the most deep-seated changes yet introduced by Microsoft. When working your way through an MS Access 2007 tutorial, a hardcore traditional user of MS  Access is either going to love or hate these new changes.

 

So what is different about this new version? The interface is completely different and instead of menus and toolbars, there is the Ribbon. The Ribbon is separated into sections and each section holds the relevant commands for designing and running a database.

 

Creating forms has become easier and there are some latest forms to choose from. One example is the split form. The database window has also been replaced with the routing pane. This offers more features and is not as straightforward to use as the old database window.

 

As a fraction of the MS Access 2007 tutorial, you should expect to learn about trusted  locations. When loading a database you will get a warning that it may not be trusted. You can eliminate this warning by assigning a folder to become trusted. If you lay up your database in that folder then the warning will no longer show.

 

Creating tables has also become easier. When creating tables as part of your MS Access 2007 tutorial course, you may use one of the easy creation methods, such as creating columns in datasheet run mode. However, it is better to look at using the design view to create tables as this will give you a better understanding of columns and data types.

 

The built-in wizards will only get you so far. They are great starting points, but if you want to become a master then it is better to try and create the database components manually. By doing this you will learn the intricacies of why things are done. For example, there are reasons why you need to save the ID of a drop-down list of data rather than the data itself.

 

Hunting around the internet for bits and pieces to help you learn is not the way to go. There has to be some structure to the learning and it is better to start from the ground up. There is little point in trying to run before you can walk.

 

When looking for a good MS Access 2007 tutorial, make sure it covers all you want to learn and you gain a good all round understanding of the important concepts and components to creating a working database.

 

Let’s recap what we’ve learned in this article:

 

Access 2007 has a new look interface

There are new form types available

Learning Access 2007 should be done from a structured tutorial series

 

To get started with an MS Access 2007 tutorial, check out the best Microsoft Access tutorial available at http://access-databases.com/ms-access-tutorial/

 

Article Source: http://ezinearticles.com/expert/Paul_Roger_Barnett/279463

 

This article gives a clear and concise overview of what’s new in Access 2007 and how the newer and richer features make it very easy to create tables, queries, forms and reports with a simple click and go actions using the predefined templates. Those migrating from earlier versions may feel a little disorientated at first especially the Ribbon Bar but it won’t take that long to familiarise yourself with this version.

 

Remember, always have a clear plan when designing a database before creating the objects using the templates.

MICROSOFT ACCESS TUTORIAL: HOW TO BUILD ALTERNATIVE ACCESS FORM CONTROLS – THE COMMAND BUTTON

Building Access forms is a time consuming aspect to front-end database designs and in this Microsoft Access tutorial, I want to add some extra aesthetics and alternative control enhancements for you.

There are many Access form controls, formats and properties that can be applied to a form over and above the basics ranging from the more advanced conditional formatting options to the custom built controls including my recent post on coloured tab controls.

So here’s some alternatives for your traditional command button…

Using the standard command button, you can still apply different format effects using the correct event with some Access VBA code or adopt a different control altogether (i.e. a Label) and make this your button click action instead.

Hyperlinks are also another way to apply actions for opening another form or running a report and can exclude VBA code (for basic actions).

Microsoft Access Tutorial: How To Build Alternative Access Form Controls – The Command Button

Let’s work on and illustrate the alternative button option using a Label and some VBA code. Take a look at the following screenshot:

When you hover over an item in the dark blue banner, the item turns a subtle white from the resting light blue resetting all other menu items including the ‘Close‘ item.

Of course, you can set any format by adding a raise, sunken effect or enhancing with underline, bold, change the background or changing font sizes – it’s how creative do you want to be?

I used a Label control and set the default to a non-selected state. In this example, I used the following:

  • Set the Caption property as you may want to change the caption from say ‘On‘ to ‘Off‘.
  • Back Style property to ‘Normal
  • Back Color property to ‘#003366‘ which reflects the dark blue banner colour though could be set as transparent.
  • Border Style property set to ‘Transparent‘.
  • Border Width property set to ‘Hairline‘.
  • Border Color property set to ‘No Color‘.
  • Special Effect property set to ‘Flat‘.
  • Fore Color property set to ‘#7DBEDC‘ (which is light blue colour).

You may want to look at my eBook on How to Build Access Database Forms for more detailed explanation on the above properties and general design techniques for your forms.

I would suggest you also apply some consistency to the names for your Labels that will be acting as a button as you could be clever to group and loop your Access VBA code more effectively. You could also adopt the Tag property to help separate this type of control use with others.

What about the VBA code?

Well, you will probably want a public procedure to reset all controls and individually apply format changes to the selected item.

Therefore, using the main event called ‘On Mouse Move‘ would be advisable. You will still need to add code the ‘On Click‘ event so when users click the item it will execute the action of opening a form or report or whatever it is your want to action (which has been excluded in this article).

I created a public sub procedure called ResetMenuLabels that runs the following code:

(Please ignore the word ‘Private’ in the above code snippet. It should be ‘Public’ as it should really be stored in a module (which this example doesn’t use) making this available to any form).

The above Microsoft Access tutorial code will loop through all controls for your form but only check to first the active control is a Label type and that is had the Tag set as ‘lb‘ (standing for Label Button). Providing you don’t use this convention for any other control, you are pretty safe for this form to reset all label buttons back to the resting style.

I’ve only used a simple format here for illustrative purposes and of course would expect you take it to the next step and apply your chosen styles – simple fill in between the loop structure.

The added element now is to call this procedure for the individual control’s ‘On Mouse Move‘ event with the additions for highlighting this control standing out from all others.

Here’s an example for the ‘Close‘ button label:

So now this Microsoft Access tutorial on how to use alternative buttons opens the mind a little further.

The downside unfortunately is this will add to the time consuming task for your Access form designs!

I would love to hear from you perfectionists out there on what alternative buttons you have adopted for you Access forms in the reply box below.

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.

Microsoft Access

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.

MICROSOFT ACCESS DATABASE: WHERE’S THE SWITCHBOARD MANAGER TOOL?

If you have been using Microsoft Access Database for quite some time and been through a few upgrades over the years and ended up with the latest version (currently Access 2016/Office 365), then you will have noticed many changes, newer look and feel, new features and older utilities and tools being dropped (deprecated).

One example is the Switchboard Manager tool which up until version 2007 was the easiest way to create custom built menu forms to allow users to easily navigate to other objects (typically forms and reports).

With later releases of MS Access, a newer tool was added and replaced the older tool called Navigation Forms which can be found under the ‘Create’ tab on the ribbon bar.

Microsoft Access Database

However, to keep the seasoned user happy, you can actually still get access to the older tool as it’s not really gone but just hidden from view!

Also note, that If you were a dab hand at using the keyboard shortcuts in Access and knew the ALT + T + I sequence that used to call the tool pop-up but alas, that’s actually gone too.

Microsoft Access Database

So, here’s how to gain access to it:

1. You need to customise the ribbon bar and add the ‘Switchboard’ icon back via the Access OptionsCustomize Ribbon section (which can be found in the Backstage).
2. In the Access Options screen, locate the section and select ‘All Commands’ to view all icons available. You can create a new group under the current ‘Database Tools’ group and call it ‘Administrator’ (or anything you like). You can optionally set an icon for it too.

You can now drag n drop the icon called ‘Switchboard Manager’ over to the new group.

Microsoft Access Database

Microsoft Access Database

Microsoft Access Database

3.  Close and return back to your main Microsoft Access database application window and view the ‘Database Tools’ tab and notice your new section and icon.

Microsoft Access Database

4.  You can now run the tool and as expected, the same functionality and checks will operate as before.

Microsoft Access Database

Microsoft Access Database

There you have it, the old actually is still there (and makes perfect to keep it going).

I guess newbie users will lean towards the new Navigation Forms for building easy to use navigation with more and richer options to choose and leave us old-fogies alone!

Ultimately, if you are a Microsoft Access VBA programming , you may create a hybrid and real customised interface instead but that just shows you how powerful and flexible Microsoft Access databases can be.

For more informations – https://accessdatabasetutorial.com/

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.

Ms Access Query

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 building access databases of this nature.

There are many other types of queries too and the more you know about Access queries, the more flexible your database will be and produce more effective reports.

I have a couple eBooks about MS Access queries which may be of interest – why not check these and other Microsoft Access database eBooks out!

MICROSOFT ACCESS QUERIES AND HOW IMPORTANT THEY ARE IN YOUR DATABASE

The heart of any MS Access database system resides with the queries and I’m often asked to give new users a quick overview of this powerful application – here’s an example…

A Microsoft Access Database is a desktop application which stores large amounts of data and is able to recognize relationships between those data. It consists of data and tools that enable users to manipulate that data.

Microsoft Access Database

One of these tools are the queries. Microsoft Access Queries are used specifically to organize data on the Access database.

A database can hold multiple tables. Each of the tables can have hundreds of thousands of records. How the user extracts a specific section of the table or tables is by using the queries.

Displaying data in simple or complex ways is the typical function of a query. One simple way is by getting the names of all the customers in a table. But you can modify your query to include only a list of customers residing in London sorted by last name, and this is a more complex search.

Microsoft Access Queries and How Important They Are in Your Database

The main types of queries in Microsoft Access are select, action, parameter and aggregate.  How Many Types Do You Know And Use In Your Access Database?

Select Query

The select query is the simplest among them. It is used to retrieve data from tables. The data that will be displayed is dependent on the criteria of the user.

Action Query

Not only can a query be used in retrieving data, it is also used in deleting or updating records as well as creating tables and inserting data into tables. The type of query that does these is called the action query.

There are four kinds of action queries and they are as follows:

  • Append Query – gets the results of a query and add them to an existing table.
  • Delete Query – deletes the records in a table according to the set results of a query.
  • Make Table Query – creates a table based on the query set results.
  • Update Query – updates one or more fields in a table.

Parameter Query

A parameter query is a modified select query. It is either a value or a condition. This type of query functions along with other types of queries to get specific results that a user may want to retrieve. It basically refines the search by instructing the other query exactly what you want it to do.  An example of a parameter query is a specific date (3/30/2015) or a date in between start (3/1/2015) and end date (3/30/2015).

Aggregate Query

Like parameter query, aggregate query also works with other types of query. It chiefly generates an aggregated result from the selected groups. Some examples are SumAvgMinMaxGroup ByCountWhereFirst, and Last.

Obtaining data from a table consisting of a large amount of records without having to probe the table is the main importance of queries.

I have a couple eBooks about MS Access queries which may be of interest – why not check these and other Microsoft access tutorial eBooks out!