Unofficial Access Stuff

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!

Sunday, March 25, 2018 – Permalink –

Srt Fields Defaults

Speed up table creation with default field settings

When you add fields to a table, Access assumes you want to use a 50/255-character Text field by default. However, you may typically use a smaller field size or you may personally use Number fields more often than Text ones.

You can avoid having to change the size and data types for new fields by setting defaults that are appropriate to your own design habits. To do so:

Choose Tools> Options from the menu bar and switch to the Tables/Queries sheet.

Select the data type you use most from the Default Field Type dropdown list.

Set the Text and Number sizes you usually want to use in the Default Field Sizes panel and

Click OK.

In Access 2007+ go to Access Options>Object Designers:

See all Topics access
Labels: Functions, General, Reference, Tips, Tutorials

<Doug Klippert@ 3:10 AM

Comments: Post a Comment

Thursday, December 07, 2017 – Permalink –

Access Developer Extensions

No cost tool

"The Access Developer Extensions include the following components:

Save As Template
Enables you to create database templates (ACCDTs) that can be featured in the Access 2007 Getting Started screen.

Package Solution Wizard
A wizard that creates a Windows Installer Package (MSI) to install your database and any supporting files and optionally includes the Access 2007 Runtime, or prompts the user to download the Access 2007 Runtime.

Source Code Control
Integration with Microsoft Visual SourceSafe or other source code control systems to allow check-in/check-out of queries, forms, reports, macros, modules, and data. You can also see the differences that have been made to your checked out objects."

Developer Extensions

See all Topics access
Labels: Functions, General, Tips, Tutorials

<Doug Klippert@ 3:31 AM

Comments: Post a Comment

Sunday, February 05, 2017 – Permalink –

Add a Switchboard

Simplify Access
A How to YouTube.

AccessLearningZone.com

See all Topics access
Labels: Functions, General, Reference, Tips, Tutorials

<Doug Klippert@ 3:10 AM

Comments: Post a Comment

Sunday, May 15, 2016 – Permalink –

Parameters v Forms

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, Functions, General, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:12 AM

Comments: Post a Comment

Wednesday, April 20, 2016 – Permalink –

Fiscal Year in Access

Make up your own year

You can show a custom Fiscal Year starting June, 1 and ending May 31.

BeginFiscalYr = DateSerial(Year(Date), 6, 1)

EndFiscalYr = DateSerial(Year(Date) + 1, 6, 1) - 1

Also:
Calculating a future or past date in Access

See all Topics access
Labels: Functions, General, Reference, Tips, Tutorials

<Doug Klippert@ 3:02 AM

Comments: Post a Comment

Saturday, October 03, 2015 – Permalink –

Alphabetize by One Field or the Other

If one is missing, use the other

Let's say you have a database that has the company name and a contacts name.

In some cases the CompanyName field is empty. If that happens, you want to continue the alpha sort using the contact's LastName.

To do this, you need to create an extra query field to provide the sort, using the NZ() function to replace the contents of one field for null values in another.

(Nz(variant, [valueifnull])

Select the Queries, and then click "Create query in Design view".

Choose the table you want to sort, click Add.

Click Close.

Drag down all the fields you want to display in your form, including the two separate fields you want to alphabetize.

Insert a new column on the left side of the QBE grid.

In the Field cell, enter the expression
NZ([CompanyName],[LastName])

Select Ascending for the Sort option.

When you run the query, if CompanyName is null (empty — no entry), the NZ() function uses the contents in LastName instead.

Here's another way to do it:

See all Topics access
Labels: Functions, General, Properties, Queries, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:46 AM

Comments: Post a Comment

Saturday, July 11, 2015 – Permalink –

Make Null Zero

It's nothing

When it is desirable to return a zero (or another value) rather than an empty field, Access (Visual Basic) has a function Nz():

Nz(variant, [valueifnull])

The Nz function has the following arguments.

variant

A variable of data type Variant. Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.

valueifnull

This example demonstrates how you can simplify an IIF function

Instead of:

varTemp = IIf(IsNull(varFreight), 0, varFreight) varResult = IIf(varTemp > 50, "High", "Low")

You could use:

varResult = IIf(Nz(varFreight) > 50, "High", "Low")

Helen Feddema offers a suggestion about forcing a zero when Nz() doesn't work

When you want to display zeroes in text boxes (or datasheet columns) when there is no value in a field, the standard method is to surround the value with the Nz() function, to convert a Null value to a zero. However, this doesn't always work, especially in Access 2003, which is much more data type-sensitive than previous versions. In these cases, you can force a zero to appear instead of a blank by using two functions: first Nz() and then the appropriate numeric data type conversion function, such as CLng or CDbl. Here is a sample expression that will yield a zero when appropriate:

NoAvailable: CLng(Nz([QuantityAvailable]))

Download a sample from:
ACCESS Watch

See all Topics access
Labels: Functions, General, Macros, Reference, Shortcuts, Tips, Tutorials, VBA

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

Sunday, June 28, 2015 – Permalink –

Calculate Age

A few solutions

Here are some methods that have been posted to the newsgroups:

Assuming that the birth date field is called [BDate] and is of type date, you can use the following calculation:
Age:DateDiff("yyyy", [Bdate], Now())+ _ Int( Format(now(), "mmdd")
Alternately you can use this function to calculate age:

Function Age(Bdate, DateToday) As Integer ' Returns the Age in years between 2 dates ' Doesn't handle negative date ranges i.e. Bdate > DateToday If Month(DateToday) < age =" Year(DateToday)" age =" Year(DateToday)">
From:
The Access Web (MVPs.org)

Also see:

Support.Microsoft.com:
Two Functions to Calculate Age in Months and Years

Office Tips:
Martin Green
Working out Someone's Age

See all Topics access
Labels: Functions, General, Reference, Tutorials, VBA

<Doug Klippert@ 3:57 AM

Comments: Post a Comment