Book

Suggestions


Enter your email address:

Delivered by FeedBurner



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












Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com



  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

Labels: ,


<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

Labels: , ,


<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

Labels: ,


<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

Labels: ,


<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

Labels: ,


<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

Labels: ,


<Doug Klippert@ 5:15 AM

Comments: Post a Comment