Blog

Almost everyone at some point likes to find a date. I do. You? In fact, when I am in Blue Mountain RAM, I like to find lots of them. What? What did you think I was talking about? Anyway… the following are examples of pairs of criteria statements you can use in Blue Mountain RAM’s Query Designer to find different date ranges of information. The Asset Schedule’s Due Date field is used, but any date field could be used. Full instructions for working with Query Designer may be found in the Blue Mountain RAM User Manual.

MACRO: @LCLADDDATE+0@

The @LCLADDDATE+0@ macro evaluates dates that equal the current date plus/minus a specified offset. For example: if you want to find records displaying a date 90 days from today, use @LCLADDDATE+90@. To find records displaying a date 30 days ago, use @LCLADDDATE-30@

  • Everything in the NEXT Rolling 30 Days (use the AND join operator):

Record Type:

Asset Schedules 

Record Type:

Asset Schedules

Field:

Due Date

Field:

Due Date

Value Type:

Macro

Value Type:

Macro

Function:

Greater Than Or Equal To

Function:

Less Than Or Equal To

Value:

@LCLADDDATE+0@

Value:

@LCLADDDATE+30@

(macro offset)

+0

(macro offset)

+30

  • Everything in the PREVIOUS Rolling 30 Days (use the AND join operator):

Record Type:

Asset Schedules

Record Type:

Asset Schedules

Field:

Due Date

Field:

Due Date

Value Type:

Macro

Value Type:

Macro

Function:

Greater Than Or Equal To

Function:

Less Than Or Equal To

Value:

@LCLADDDATE-30@

Value:

@LCLADDDATE+0@

(macro offset)

-30

(macro offset)

+0

MACROS: @MONTHSTART+0@ and @MONTHEND+0@

The @MONTHSTART+0@ macro returns the first day of a month, where the month is identified by an offset that is in relation to the current month. For example, if the current month is JUNE, the offsets behave like so:

@MONTHSTART+2@ = the first of the month, 2 months ahead of current month of June (August 1)
@MONTHSTART-3@ = the first of the month, 3 months behind current month of June (March 1)

The @MONTHEND+0@ macro is provided with one sole purpose: to act as a way to end a date range started with the @MONTHSTART+0@ macro. @MONTHEND+0@ paired with @MONTHSTART+0@ creates a date range that begins on the first day of a month and ends on the last day of a month. You can span multiple calendar months by putting different offset numbers in @MONTHSTART+0@ and @MONTHEND+0@.

  • Everything in the PREVIOUS Calendar Month (use the AND join operator):

Record Type:

Asset Schedules

Record Type:

Asset Schedules

Field:

Due Date

Field:

Due Date

Value Type:

Normal

Value Type:

Normal

Function:

Greater Than Or Equal To

Function:

Less Than Or Equal To

Value:

@MONTHSTART-1@

Value:

@MONTHEND-1@

  • Everything in the CURRENT Calendar Month (use the AND join operator):

Record Type:

Asset Schedules

Record Type:

Asset Schedules

Field:

Due Date

Field:

Due Date

Value Type:

Normal

Value Type:

Normal

Function:

Greater Than Or Equal To

Function:

Less Than Or Equal To

Value:

@MONTHSTART+0@

Value:

@MONTHEND+0@

  • Everything in the NEXT Calendar Month (use the AND join operator):

Record Type:

Asset Schedules

Record Type:

Asset Schedules

Field:

Due Date

Field:

Due Date

Value Type:

Normal

Value Type:

Normal

Function:

Greater Than Or Equal To

Function:

Less Than Or Equal To

Value:

@MONTHSTART+1@

Value:

@MONTHEND+1@

Well, Part One of Building Queries In Blue Mountain RAM was dining at the Database Bistro, and Part Two was finding a date. What could be next? Stay tuned; stay tuned…

THE QUERY DESIGN FEATURE OF BLUE MOUNTAIN RAM IS ACCESSIBLE BY: logging into Blue Mountain RAM, opening the “Go To” menu, and selecting “Query Design”. Query designs are organized by the record type of the records they return. For example, to find query designs that return lists of maintenance work records, look under Work, Maintenance Work Queries. The “Add Similar” command is available in the right-click menu of existing designs. This saves time when a new title expands or contracts upon an existing title. Users must be granted query building and sharing rights by their Blue Mountain RAM Administrator. Rights are granted on a record type by record type basis so that user access to data can be finely controlled.

ADDITIONAL RESOURCES FOR BLUE MOUNTAIN RAM QUERY DESIGNERS: