8/24/2012

DateDiff Function

no image 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.

8/16/2012

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

no image 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

no image 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.

8/06/2012

Convert currency into words

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.

8/05/2012

Generate a random number between 2 user-specified values

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.

8/04/2012

Report Example (Employee hours worked per day)

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.

8/03/2012

Display parameter values in a report based on a parameter query

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.

8/01/2012

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

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.