DateDiff Function

In Access, the DateDiff function returns the difference between two date values, based on the interval specified.
The syntax for the DateDiff function is:

DateDiff ( interval, date1, date2, [firstdayofweek], [firstweekofyear])

interval is the interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.

View only records with uppercase letters for a certain field in Access

Question: In Access 2003/XP/2000/97, if I wanted to view only the records with Uppercase letters for a certain field, how would I do it?
For example, the query would ignore "Bob", but show "JOHN".
Answer: Access was built to be case-INSENSITIVE, but we can always use the ASC function to determine upper vs lower case.

Calculate and format an elapsed time value in Access

Question: In Access 2003/XP/2000/97, how do I format a time on a report so it appears as elapsed hours and minutes?
For example, 27 hours and 31 minutes appears as 27:31. I can do this for values up to 24 hours. But for anything over 24 hours, the full day(s) are dropped off.
Answer: A "elapsed time" is usually calculated by taking the difference between two dates. So, you will need to store the "from" and "to" dates separately and then calculate the elapsed time.

Convert currency into words

Question: In Access, how can I convert currency to words?
For example,

$100 should read as "one hundred exactly"

Answer: To convert currency into words, you'll need to open your Access database and create a new module.

Generate a random number between 2 user-specified values

In Access, the Rnd function allows you to generate a random number (integer value). You can specify the random number to be a value between 2 user-specified numbers.
The syntax for the Rnd function is:

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

upperbound is the highest value that the random number can be.
lowerbound is the lowest value that the random number can be.

Report Example (Employee hours worked per day)

Question: In Access 2003/XP/2000/97, I have a database where I report employee hours daily. I need a report that displays the hours worked daily and the total of hours worked per week per employee.
Answer: We've created a sample Access database that contains the tables, queries, and reports required to demonstrate this example.

Display parameter values in a report based on a parameter query

Question: In Access 2003/XP/2000/97, how do I display parameter values in a report based on a parameter query?
Answer: To display a parameter value in a report, you will need to redesign your parameter query to return the parameter as a field in the result set.
We've created an Access sample that you can download.

Capitalize each word in a string (similar to InitCap in Oracle)

In Access, the StrConv function returns a string converted as specified.
The syntax for the StrConv function is:

StrConv ( text, conversion, LCID )

text is the string that you wish to convert.