The REPLACE Function

Ever need to change data or a string to meet your own unique criteria? The REPLACE function is perfect for this task.

Here's an example that I've used to take the data from a boolean field (entitled "ModReqd") to display some meaningful text in a new field (entitled "Note") to the reader when the boolean data is True;

Note: Replace(Replace([ModReqd],True,"Custom (long-lead item)"),False,"")

Here's the criteria and syntax for REPLACE ;

Returns a String in which a specified substring has been replaced with another substring a specified number of times.

Syntax

Replace(expression, find, replace [, start ] [, count ] [, compare ] )

The Replace function syntax has these arguments:

Argument Description
expression   Required. String expression containing substring to replace.
find   Required. Substring being searched for.
replace   Required. Replacement substring.
start   Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.
count   Optional. Number of substring substitutions to perform. If omitted, the default value is –1, which means make all possible substitutions.
compare   Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

Settings

The compare argument can have the following values:

Return Values

Replace returns the following values:

If Replace returns
expression   is zero-length Zero-length string ("")
expression   is Null An error.
find   is zero-length Copy of expression.
replace   is zero-length Copy of expression with all occurences of find removed.
start   Len(expression) Zero-length string.
count   is 0 Copy of expression.

Remarks

The return value of the Replace function is a string, with substitutions made, that begins at the position specified by start and concludes at the end of the expression string. It is not a copy of the original string from start to finish.

Performing Math Functions in a New Query Field

Ever need to perform a math function in a query and display its result in a newly-created column (field)? Its actually very easy.

Here's an example that I've used to calculate the effective nominal capacity of a vessel based upon the dimensions of the pills which will ultimately be dispensed from the vessle;

The three existing fields are WIDTH, LENGTH and THICKNESS.
Because I also want to ensure whole integers in the output, I've also incorporated the ROUND function in my expression.

Here's the final expression that returns the volume of the vessel (162 cc), divided by the individual volume of the pill (Width*Length*Thickness), and rounds it to the nearest integer;

Nominal Pill Capacity: Format(Round(162/([Width]*[Length]*[Thickness])*1000),"0")

As you can see, this is a pretty simple expression.

Embed the Current Date in a New Query Field

Okay, so I'd have a difficult time explaining why I needed this column to be displayed to the user in the first place (but I'l try :)).

I had a project in which the user would add a date in a field to represent an action. As I wanted a simple method of determining the age of the exported report -And didn't want the user to tinker with this date. To make this happen I simply embedded the following code in a new column in the affected query;

Expt (int use only): Format(Date(),"yyyymmdd")

As you can see the new field column [entitled "Expt (int use only)"] would now display the current date. I chose to use a non-traditional date format of yyyymmdd (ex. 20110607 would denote June 7, 2011).