Meet Our Authors

Nov

Making Date Fields Optional in Access Queries

By Bob S

If you would like to give a person optional choices in a date parameter, and don’t want to use if statement logic, the NZ function is the way to go. Recently in Access, I had to write a query that was getting criteria from a form called frmChoice. I wanted to give my database user the option to enter a starting date (in a field called dtmStartDate) and/or an ending date (in a field called dtmEndDate). If they chose not to enter a date, I wanted to use a start that was well beyond the first date in the data, and if they chose not to enter an ending date, I wanted to automatically supply the current date as the ending date. With non-optional dates, the criteria line would look something like this:

Between [forms]![frmChoice]![dtmStartDate] and [forms]![frmChoice]![dtmEndDate]

However, if no date was entered on the form, the above statement will return an error.

Enter the NZ function. In the help, Microsoft describes the NZ function this way:

”You can use the Nz function to return zero, a zero-length string (” “), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.”

Syntax: Nz(variant , valueifnull] )

So the criteria statement becomes:

Between NZ( [forms]![frmChoice]![dtmStartDate], #01/01/2000#) and NZ([forms]![frmChoice]![dtmEndDate], Date())

In this case, the NZ function will force the starting date to be January 1, 2000 if left blank, and force the ending date to be the current date if left blank on the form by the user.

The NZ function can also be used to make a maximum or minimum value an optional choice. Suppose you recorded meeting attendance in a database. Sometimes, you wanted to find all the meetings where more than 15 people attended, but sometimes, you just wanted to see all of the meetings regardless of attendance. In the criteria line of the query you could enter:

>=NZ([forms]![frmChoice]![lngAttendance], 0)

In this case, if someone enters a number, you will use that figure as your minimum. If they leave the field blank, zero will be used as the minimum.

Filed Under Microsoft Access, Office Applications |

Print this post

Nov

A Better Way to Create Lookup Fields

By Bob S

Image via Wikipedia

You might be tempted, when thinking about creating a Lookup field in Microsoft Access, to use the Wizard.

Don’t.

It involves too much cleanup work after you are done. For example, if you use the Lookup Wizard, you must destroy any existing relationship between the two tables. During the running of the wizard, you will see a recommendation to hide the key column. This will also cause confusion, because if you do this, the field stored and the field displayed will be two different items. And finally, if you did have a relationship between the two tables with referential integrity set up, you must go back into the relationships after completing the wizard and reset the integrity.

Why do all of this?

Why not just change the properties of the field on the Lookup tab so that the field becomes a lookup field. It is faster and easier if you know which tabs to change. The list below shows how each property on the Lookup tab should be set. Simply follow these suggestions and you will have Lookup field without the bother of correcting and resetting properties.

Command	Setting
Display Control	Select Combo Box
Row Source Type	Choose Table/Query
Row Source	Create a query that displays all the fields you would like to see in the dropdown area.
Bound column	1
Column Count	Match the number of columns in your query
Column Heads	Leave this as No
Column Widths	Set the width of each column in the drop down area and separate them with semicolons
List Rows	Set the number of items to be displayed when the dropdown button is selected
List Width	Sum the column widths in the previous property
Limit to List	If set to Yes, no new entries are allowed, and auto-complete is enabled

Leave the rest of the properties alone.

Filed Under Microsoft Access, Office Applications |

Print this post

Aug

Can You Wrap Text in an Access List Box?

By Jane H

Recently I was asked if you can wrap text in a list box on an Access form. The short answer is no.

However, you could try putting an unbound text box near the list box. In the text box properties set the Control Source to point back to the list box property. In the image below, I’ve set the control source to =[lstCharity].[column](0). In this example, 0 is one less than the column number I want to display in the text box (My list only had one column). If your list had two columns, you would use 0 to display the first column, 1 to display the second column.

WrapText1

The results, as you can see in the following image, allows you to have a list box with long entries and still be able to read them.

WrapText

Filed Under Microsoft Access |

Print this post

Jul

Benefits of Training

By Sheri T

People are either thrilled or upset when their company tells them they have to attend a training. I do understand that when you are already very busy, it just makes your day more stressful to be away from your desk for several hours. BUT, when you take the time to learn the most efficient way of performing task, you will be more efficient. Training.com states that you will actually be 230% more effective than someone who has not been trained. This means that the few hours you give up in a training class, you will make up for in efficiency multiple times over!

In today’s economy the best thing you can do for your career is to be up-to-date with the latest technology as well as being an efficient user of that technology.

Don’t consider training a burden on your day. Consider it a benefit, one that no one can take from you (even if you do have to change jobs). It is reassuring that your company is spending their money investing in your skills.

Filed Under Certifications, Excel, Microsoft Access, Office Applications, Outlook, PowerPoint, Visio, Word |

Print this post

Jun

Using form value to run a query (or report)

By Jane H

Access queries are a wonderful way to extract specific information out of your database. You can run the same query over and over, but if you want different information, you need to reconfigure the criteria on your query. You can create a Parameter query, which displays a dialog box prompting you for the criteria value, but that assumes you, or your users, know what constitutes a valid value. If not, it becomes more problematic. A relative simple solution is to create a combo box on a form that presents a valid set of values for users to select. In the following instructions, I create a simple list of values to run the Parameter query. Read more

Filed Under Microsoft Access |

Print this post

Jan

Access - Removing Those Pesky Duplicate Records

By Bob S

If you find duplicate records through a query, the next logical step is to delete them. However, the number of records to be deleted can run into the hundreds, or even more. Manual deletion of these records would be impractical because of the time involved. And you can use an Access trick to remove duplicates from any table.

If you want the newest of the duplicates to be kept, sort the table in descending order by the key field. If you want the oldest record to be kept, sort the table in ascending order by the key field. Close the table and save your changes.

Next, copy the structure of the table to a new table. Remove all existing keys.

Designate the fields that you have determined as indicators of duplicate records as key fields.

Convert the Find Duplicates query to an append query that will append all records to the new table. Run the query.

Because the fields that contain duplicate information are key fields in the new table, Access will remove the duplicate records because duplicate keys are not allowed.

Remove the existing keys, and designate the original key fields as your keys.

Delete the old table.

Rename the new table with the old table name.

For example, a table called tblCustomer with a Customer ID field as a key field contains duplicate customer information, entered by mistake. Stores in the same city have been entered twice, so the combination of Store name and City indicate duplicate records.

First, open the table tblcustomer. If you want the newest of the duplicates to be kept, sort the table in descending order by the key field. If you want the oldest record to be kept, sort the table in ascending order by the key field. Close the table and save your changes.

Copy the tblCustomer table structure to a new table called tblNewCustomer.

Open the table tblNewCustomer in design view. Remove the key designation from the Customer ID field.

In tblNewCustomer create a multi-field key by selecting the Store Name field and the City field

Create a find duplicates query, using Store name and City as the indicator fields.

Convert the Find Duplicate query to an append query and run it. It will attempt to append all the records to the table NewCustomer. The append record will fail every time an attempt is made to write a duplicate record into the table, so only one of the duplicates will be written to the new table.

After the query finishes, delete the tblCustomer table. Rename the table tblNewCustomer as tblCustomer. All duplicates have been removed.

Filed Under Microsoft Access, Office Applications |

Print this post

Jan

Access - The Easiest Way to Create a Unique List…..

By Bob S

in a drop-down, or combo box. Here’s the problem: You want to create a drop-down list of all current possibilities in a field. For example, you want to create a list of all the states in which you currently have customers. You don’t want a list of all 50 states. You don’t want a list that has duplicates in it. On of the easiest ways to create this list for your drop-down box is to create a one-field summary, or totals query. First, let’s talk about why creating a combo box field based on the table can be problematic.

Beginning with Access XP, the Combo Box wizard included an automatic addition. If the table on which the Combo Box field is based has a key field, the wizard automatically includes the key field as a hidden field as part of the combo box drop-down. This causes those of us that only want a single field to have to go into the properties of the combo box and the underlying SQL statement, removing the field we don’t want and changing the properties of the combo box so that it will display the correct field the way we want.

But now let’s look at an alternative solution, the totals query. This query will group similar entries into a single row, giving us our unique list. And because the drop-down box will be based on a query instead of a table, this eliminates the sometimes-annoying inclusion of an unwanted key field.

Here’s how it works: Going with our example of a list of states in which we currently have customers, we create a query in design view. Include the table with the customer state field in it. Add only the state field to the grid. Now click on the Sigma Button:

This button will add a row called Totals to your design view grid, and will add the default entry “Group By” in the row. Save the query, because you’re done! Your query will now only display one line for each state in your list. Now you can build a combo box based on this query.

Filed Under Microsoft Access |

Print this post

Dec

Access 2007 - Why Can’t I Arrange My Fields On A Form?

By Bob S

You’ve created an access form in Office 2007 by clicking on the Create Form button. All the fields are the same width. Now you want to make one a bit narrower. But when you select it and drag the side, all the fields get narrower. How do you change the width of one field?

When you click on Create Form with an existing table highlighted, Access 2007 creates a form with all fields the same width.

If you try to change the width of the Employee ID field, all the fields will change. Soon you discover the Remove button in the Change Layout group on the Arrange Ribbon.

But when you click on it, the Employee ID field seems to disappear. Actually it goes behind the next field, and no matter how you seem to try to drag the fields on the form, they just don’t go where you would like. That’s because when creating a form in this way, Access controls the layout of the fields, and seems to use something similar to a Word Table Generator. So you think, “I’ll just design the whole thing manually like I used to in 2003. Well, you don’t have to. If you think like a Word user, you need to “turn the table into text” so that you can rearrange the fields. To do this, click on the button that appears at the top left of the first field on the form, similar to selecting an entire table in Microsoft Word. This will highlight all the fields in the form. Now click on the Remove button to remove the automatic (table) formatting from all the fields at once.

Click on an unused part of the form to de-select all the fields. You can now select one field and change the size of that field without affecting the other fields on the form.

Filed Under Microsoft Access, Office Applications |

Print this post

Nov

Eeeek - another acronym ? DDPS and SAM…

By Gerry Vogler

That’s all that technology professionals need, another acronym to learn… more importantly, even when we know the acronym, what is the concept behind it ? DDPS stands for Desktop Deployment Planning Services, yet another service offering from Microsoft that provides software versioning migration planning and SAM (that’s Software Asset Management) as a component of their Software Assurance.

The process is relatively simple - the enterprise identifies a Microsoft Certified Partner that is certified in DDPS to conduct the audit - usually taking 2-5 business days of auditing and consulting. The resulting deliverable identifies a standardization plan and upgrade path that is tied (ideally) to corporate culture and objectives. Yes, this is somewhat of a “fox watching the hen-house” scenario since the Partner conducting the audit is paid by Microsoft for their investment of hours. The consulting hours are not the real attraction to the partner/vendor, in their case, the real upside is the potential sales opportunity of both licenses and services required to actually complete the prescribed upgrade.

Ultimately, DDPS is a valuable free service for any enterprise committed to a desktop upgrade/migration as long as the clients take the time to understand the results and ask the proper questions after the fact.

Filed Under Excel, InfoPath, Microsoft Access, Outlook, PowerPoint, Software Assurance Benefits, Visio, Word |

Print this post