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!

Sunday, July 26, 2009 – 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, Tips

<Doug Klippert@ 3:29 AM

Comments: Post a Comment

Saturday, September 13, 2008 – 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]))

ACCESS Watch Vol 7 No. 5

See all Topics

access
Labels: Functions, Macros, Tips

<Doug Klippert@ 3:32 AM

Comments: Post a Comment

Thursday, August 28, 2008 – 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, Tips<Doug Klippert@ 3:03 AM Comments: Post a Comment Saturday, March 29, 2008 – Permalink – Week Numbers Who's counting? For most purposes, weeks are numbered with Sunday considered the first day of the week. This works most of the time, but it can be a little confusing certain years. 2004 has 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005. Chip Pearson is the Date and Time guy: Week Numbers In Excel "Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4. While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999. An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year. Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec." The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004. Wikipedia: Week Dates If your week starts on a different day, you can use the Analysis ToolPac function: =WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday. Also this from ExcelTip.com: Weeknumbers using VBA in Microsoft Excel "The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer." In Access: DatePart Function If your work week is always Saturday through Friday then datepart("ww",[DateField],7,1) will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc. Otherwise use 1 for Sunday through 7 for Saturday. The last number sets these parameters: 1, Start with week in which January 1 occurs (default). 2, Start with the first week that has at least four days in the new year. 3, Start with first full week of the year. See all Topics access Labels: Functions, Tips <Doug Klippert@ 7:23 AM Comments: Post a Comment Saturday, September 01, 2007 – Permalink – Total Footer Sum() it up If you would like to show a total in the footer or each page of a report, you may have a problem. Access does not allow the SUM() function in the footer. The way around this is to put a SUM() function in an unbound text box in another part of the report. Choose Properties and set the visible property of the control to No. In the footer, create another control using the "calculation" text box as the ControlSource. The Sum() function, as well as the other aggregate (totals) functions can reference only a field and not a control. From the Microsoft Knowledge base: How to Sum a Calculation in a Report How to Display and Total Subtotals from Subreports How to Print a Group Footer at a Specific Location See all Topics access Labels: Functions, Reports <Doug Klippert@ 7:50 AM Comments: Post a Comment Monday, April 23, 2007 – Permalink – Numbers to Words Cardinal numbers You can create a User Defined Function in Access to covert numbers to words. The function can be used in a calculated field or control in a form or report. From the Microsoft Knowledgebase collection: How to Convert a Numeric Value into English Words - 210586 Also: The Access Web (MVPS) Convert Currency ($500) into words (Five Hundred Dollars) TECH on the Net.com Convert currency into words (The Access code also works in Excel) To create Cardinal numbers in Excel see: Excel - Numbers to Words (The Excel code also works in Access) Word appears to be the only Office app with a built in cardinal number function. For Word see: Word - Numbers to Words See all Topics access Labels: Functions, VBA <Doug Klippert@ 5:15 AM Comments: Post a Comment