Unofficial Access Stuff

Book

Suggestions

Use your pdf converter to make your pdf files easy! You can now buy software that makes converting pdf to doc possible! Did you know you can even convert pdf to word?

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Host your Web site with PureHost!

Thursday, December 17, 2009 – Permalink –

Combo Box Queries

How to

Parameter queries add flexibility to filtering records in a database. To make it easy, take a look at this approach from Martin Green's Office Tips site:

Drop down box in a Parameter Query

Build a dialog box with as many combo boxes as you need.

Design a query to read its criteria from the information on the dialog box.

Create a macro or visual basic procedure to tell them both what to do.

Also:
Base Combo Box on Parameter Query to Filter Values

See all Topics

access
Labels: Combo, Queries, Tutorials

<Doug Klippert@ 3:25 AM

Comments: Post a Comment

Saturday, October 31, 2009 – Permalink –

Hiding Duplicates in Query Results

Once is enough

It's easy to hide duplicate entries when you run a query, even though Access doesn't go out of its way to call attention to this ability.

Set up a query as usual using the design grid.

Choose View>Properties from the menu bar to display the Query Properties dialog box.

Change the Unique Values property to Yes.

Access displays unique records based on each field returned by the query.

See all Topics

access
Labels: Customize, Queries, Tips

<Doug Klippert@ 3:12 AM

Comments: Post a Comment

Friday, August 28, 2009 – Permalink –

Parameter v. Form

You have a choice

There are a couple of ways to limit the data that is displayed in a Query or in a report.
One is a Parameter Query

Create a query to use as the RecordSource of your report.

In query design view, in the Criteria row under your date field, enter:

Between [StartDate] And [EndDate]

The other is to create a specific form. The unbound form has the following advantages:

Flexible: user does not have to limit report to from and to dates.

Better interface: allows defaults and other mechanisms for choosing dates.

Validation: can verify the date entries.

Allen Browne has supplied a clear explanation along with some typical code.

Also see FontStuff.com:
Using Parameter Queries

See all Topics

access
Labels: Forms, Queries, Tips, Tutorials

<Doug Klippert@ 3:02 AM

Comments: Post a Comment

Sunday, May 03, 2009 – Permalink –

Crosstab Query Column Headings

Using Month Numbers

If you display a crosstab query as a datasheet, consider using a month's or day's number as a column heading instead of a text abbreviation (e.g., 1 instead of Jan or January, or 2 instead of Mon).

Text abbreviations are sorted alphabetically. Apr appears before Feb, Mon appears before Sun, etc. Number representations will sort in their proper order.

See all Topics

access
Labels: Queries

<Doug Klippert@ 3:03 AM

Comments: Post a Comment

Saturday, February 28, 2009 – Permalink –

Parameter Queries Deux

Another look at parameters
The ability to use dynamic criteria in a Query makes Access even more valuable.

David Badurina operates NlueMooseTech.com.

He has produced an Access Parameter Query Tutorial video that walks the viewer gently through the process.

The site also has information on Excel and Word.

(Parameter queries are also referenced here:
Parameter v. Form)

How to create a parameter query

Using Parameter Queries

See all Topics

access
Labels: Queries, Tutorials

<Doug Klippert@ 3:24 AM

Comments: Post a Comment

Friday, October 10, 2008 – Permalink –

Hidden Passwords

Format to mask entry

You can easily set up a text box to mask entries with asterisks, allowing you to hide entries like passwords from prying eyes.

To do so, in Design view, display the properties for the text box and change the Input Mask property to Password.

Passwords can then be displayed as ******

Also see:

Microsoft-AccessSolutions.co.uk:

Add Simple Security

How to create a User Login Form in Microsoft Access

See all Topics

access
Labels: Forms, Properties, Queries, Tutorials

<Doug Klippert@ 3:23 AM

Comments: Post a Comment

Monday, July 07, 2008 – Permalink –

Hidden Tables

Don't peek

If you do not want the ordinary user to see a table in your database, you can hide it in the database windows so that other users cannot select it.

Preface the table's name with the four letters 'Usys'. For example, if your table's name is ProprietaryCosts , change it to Usys ProprietaryCosts. This makes your table into a system object which Access does not display.

To see this table later, in Tools>Options, on the View tab in the Show section, put a check mark by System objects.
If we agree among ourselves not to tell the uninitiated, no one need be the wiser.

Microsoft Office Online:
How to Hide Objects in the Database Window

Here's how it works in 2007:
Hide tables as system tables in Access 2007

See all Topics

access
Labels: Queries, Tables, Tips

<Doug Klippert@ 3:30 AM

Comments: Post a Comment

Friday, June 20, 2008 – Permalink –

Import Queries

As Tables

If you want to use the results of a query, and you don't need to update the underlying tables, you don't have to import unnecessary data.

You can import the query as a new table.

Select File>Get External Data Import from the menu bar.
(External Data tab, Import in 2007)
Select the appropriate database and click Import.
Select the queries you want to import on the Import Objects dialog box's Queries sheet.
Next, click the Options >> button and select the As Tables option button on the Import Queries panel.
Finally, click OK

Access processes the queries and saves the results as a table with the same name as the original query.

See all Topics

access
Labels: Queries, Tables

<Doug Klippert@ 6:49 AM

Comments: Post a Comment

Saturday, May 03, 2008 – Permalink –

Sample Queries, Forms, Reports

Examples to part out

This sample queries database contains examples of useful database queries, including the crosstab query, the union query , and the join query

Sample: query topics database

Here are some other sample databases. They are all for Access 2000, but the installed base is predominantly in that format. Access 2000 is also the default format for Access 2002 and 2003.
Sample Access databases that you can download and adapt

Database of Access 2000 sample forms
The sample forms in this database demonstrate a variety of form types and techniques, including how to manipulate data, use controls, and create undo and redo operations.

Some forms include:

Bring a subtotal from a subform to a main form
Create a running sum
Create a stopwatch form
Display line numbers on subform records
Fill current record with data from previous record automatically
Hide the combo box drop-down arrow
Simulate drag-and-drop capabilities

Database of Access 2000 sample reports
The sample reports in this database demonstrate a number of techniques, including how to shade every other row or every nth row in a report, how to create a table of contents or an index for a report, and how to create a top 10 report.

See all Topics

access
Labels: Forms, Queries, Reports

<Doug Klippert@ 6:15 AM

Comments: Post a Comment

Sunday, April 06, 2008 – Permalink –

Add a Table

Drag drop trick

While working with a query in Design view, you may find that you need to add a table or query. The "book" way to do it is to click the Show Table toolbar button, drag the appropriate objects from the list, and then close the dialog box.
There is another way to do this.

Drag the table or query object's icon from the Database window/Navigation pane directly to the top half of the query design grid.

You can also use this technique in Access's Relationships window

See all Topics

access
Labels: Queries, Tables, Tips

<Doug Klippert@ 6:50 AM

Comments: Post a Comment

Wednesday, December 19, 2007 – Permalink –

Quickly Query Table Names

Change by code

If you've ever developed a dozen or more complex queries, then had to change one of the table names, you know how frustrating it can be to all but rebuild the queries in the Query Design view grid by changing the table name in each cell.

One quick alternative is to choose View >SQL View while the query is open and then cut and paste all the SQL code into Word.

Next, do a Find and Replace, changing all the instances of the old table name to the new table name.

Finally, copy and paste the SQL statements back into the SQL view of your Access queries.

When you go back to the QBE, the new table will replace the old one.

See all Topics

access
Labels: Queries, SQL

<Doug Klippert@ 4:00 AM

Comments: Post a Comment

Tuesday, December 11, 2007 – Permalink –

Add Objects to the Query Grid

Easy additions

If you need to add a table or query to a query you're building in Design view, you most likely click the Show Table button, drag the appropriate objects from the resulting dialog box, and then close the dialog box.

However, there's a much easier way to do this.

Simply drag the table or query object's icon directly to the gray background of the query design grid. This same technique also works with Access's Relationships window.

See all Topics

access
Labels: Queries, Shortcuts

<Doug Klippert@ 8:04 AM

Comments: Post a Comment

Friday, November 09, 2007 – Permalink –

Null Parameter

Show something

If a user doesn't specify a parameter value, you can use a wildcard with the parameter in the format
Like [Enter Name] & "*"

The problem with this is that the query will return records that partially match the criteria.

For instance, if users searching for records based on last name enter a parameter value of "Smith" they'll also get the records for Smithers, Smithfield and Smithson.

Another problem is that the parameter query will ignore any records where the field being searched contains a Null value when you try to return the entire recordset with a blank parameter.

To fix this, set up a query to limit responses to explicit parameter entries, but still allow users to return all records by leaving the parameter blank.

If you're searching for LastName, open the query design grid and add LastName to it.

In the Criteria row for the field, enter the parameter prompt
[Enter Name]

Then, in the next blank column of the design grid, enter the same parameter (everything between and including the square brackets) in the Field text box.

Finally, in the Or row, enter the criteria Is Null .

If you're using any additional criteria for other fields, make sure to copy that criteria to the Or line as well.

See all Topics

access
Labels: Queries

<Doug Klippert@ 6:21 AM

Comments: Post a Comment

Thursday, July 26, 2007 – Permalink –

Use a Table Alias

to change names in a query

When you need to change the table name referenced in an existing Access query, it can be a pain. This is especially true if the query contains a large number of fields.

You typically might use an alias when you need to relate a table to itself or tables have long or unwieldy names.

If you make a practice of always using aliases in your queries, you can easily change which table is used by changing the one occurrence of the original table name in the query's FROM clause.
To set a table alias in the query's Design view,
Right-click on the table field list and choose Properties.
Then, enter the alias name you want to use in the Alias text box.

See all Topics

access
Labels: Queries, Tables

<Doug Klippert@ 7:36 AM

Comments: Post a Comment

Thursday, March 15, 2007 – Permalink –

SQL statements Automatically

Hidden code

If you're having trouble figuring out a complex SQL statement for use in code, you may be able to simplify the process by first setting it up the query you want in the query design grid.

Once you've got it configured correctly, choose View >SQL view to reveal the underlying SQL statement, which you can copy and paste into your code.

You may have to make minor modifications, but this technique often eliminates much of the hassle of manually constructing SQL statements.

You can also tweak the underlying SQL code to adjust your Query. This code can also be copied and reused in other Queries after a little customizing.

See all Topics

access
Labels: Queries, SQL

<Doug Klippert@ 6:23 AM

Comments: Post a Comment

Wednesday, January 10, 2007 – Permalink –

View Related Fields

The whole thing

When designing a query, you'll often find the need to temporarily view fields beyond the ones you want shown in the final result.

To do so, you probably drag the relevant fields to the design grid and then delete them when you're done. However, there's an easier way to view the data that you typically want excluded in the query.

To do so:
Open the query in Design view.
Display the query's Properties sheet
Set the Output All Fields property setting to Yes
Run the query

Data from all of the underlying tables' fields will be displayed in addition to the fields you specified in the QBE grid.

To change the query so that it only displays the fields explicitly selected in the QBE grid, simply reset the Output All Fields property to No.

See all Topics
Labels: Queries

<Doug Klippert@ 4:51 AM

Comments: Post a Comment