Using dates as criteria in Voyager Prepackaged Access Reports
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
Tips on using dates as criteria in Voyager Prepackaged Access Reports.
Answer
The following are some basic tips on using dates as criteria in Prepackaged Access Reports.
Date Constants
Uses hashes (number signs).
Example:
#2/1/2006#
Note that this can be affected by Windows Regional Settings.
Defaults to current year: #2/1#
Be wary of using year alone and always carefully check your results.
Criteria with Operators
Examples:
> #1/1/2006# Greater than
>= #1/1/2006# And < #1/2/2006#
HAVING (((ITEM.CREATE_DATE)<#4/1/2015#) AND ((Max([Circulation Transactions (Charges)].CHARGE_DATE))<#4/1/2015# Or (Max([Circulation Transactions (Charges)].CHARGE_DATE)) Is Null) AND ((ITEM.PERM_LOCATION)="8"));
Ranges
Examples:
Between #7/27/2006# And #7/28/2006#
Between #7/27/2006# And #7/27/2006 23:59:59#1
Using Prompts
If an Access query doesn't recognize what is in the square brackets as a field name or table name, it will prompt for a value. This is how prompting for criteria works. If the query prompts you for the value of a field, that means it is not in your query.
Suggested Best Practice: Make your prompts the best they can be. Imagine that the users do not know what kind of information is expected. Provide enough information in the prompt that they can figure it out.
Examples:
[start]
[start date]
[Start Date:]
Between [Start Date:] And [End Date:]
Note: If you use the same exact prompt more than once, it only prompts you once and substitutes the same value in each spot.
Examples of Queries Using Dates as Criteria
Counts items added during the prompted date range:
SELECT [Start Date:] AS StartDate, [End Date:] AS EndDate, ITEM_TYPE.ITEM_TYPE_CODE,
ITEM_TYPE.ITEM_TYPE_NAME, Count(ITEM.ITEM_ID) AS Items
FROM ITEM INNER JOIN ITEM_TYPE ON ITEM.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID
WHERE (((ITEM.CREATE_DATE) Between [Start Date:] And [End Date:]))
GROUP BY [Start Date:], [End Date:], ITEM_TYPE.ITEM_TYPE_CODE, ITEM_TYPE.ITEM_TYPE_NAME
ORDER BY ITEM_TYPE.ITEM_TYPE_CODE;
Make Table query of archived circulation Transactions (hard coded):
SELECT CIRC_TRANS_ARCHIVE.CIRC_TRANSACTION_ID, CIRC_TRANS_ARCHIVE.CHARGE_DATE, ITEM_TYPE.ITEM_TYPE_CODE INTO [Circ FY 1]
FROM (CIRC_TRANS_ARCHIVE INNER JOIN ITEM ON CIRC_TRANS_ARCHIVE.ITEM_ID = ITEM.ITEM_ID) INNER JOIN ITEM_TYPE ON ITEM.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID
WHERE (((CIRC_TRANS_ARCHIVE.CHARGE_DATE) Between #1/1/2014# And #1/30/2014#));
Bibs created after certain date grouped by LC Class:
SELECT LCCLASS_VW.CLASS, Count(BIB_MASTER.BIB_ID) AS BibsAdded
FROM (LCCLASS_VW INNER JOIN BIB_MFHD ON LCCLASS_VW.MFHD_ID = BIB_MFHD.MFHD_ID) INNER JOIN BIB_MASTER ON BIB_MFHD.BIB_ID = BIB_MASTER.BIB_ID
WHERE (((BIB_MASTER.CREATE_DATE)>#7/1/2020#))
GROUP BY LCCLASS_VW.CLASS
ORDER BY LCCLASS_VW.CLASS;
Example of weeding report filtering on create date, charge date and other criteria:
SELECT ITEM.ITEM_ID, ITEM.CREATE_DATE, Max([Circulation Transactions (Charges)].CHARGE_DATE) AS MostRecentCharge, MFHD_MASTER.NORMALIZED_CALL_NO, BIB_TEXT.TITLE INTO [Weeding Report from Salika 4]
FROM (((([Circulation Transactions (Charges)] RIGHT JOIN ITEM ON [Circulation Transactions (Charges)].ITEM_ID = ITEM.ITEM_ID) INNER JOIN MFHD_ITEM ON ITEM.ITEM_ID = MFHD_ITEM.ITEM_ID) INNER JOIN MFHD_MASTER ON MFHD_ITEM.MFHD_ID = MFHD_MASTER.MFHD_ID) INNER JOIN BIB_MFHD ON MFHD_MASTER.MFHD_ID = BIB_MFHD.MFHD_ID) INNER JOIN BIB_TEXT ON BIB_MFHD.BIB_ID = BIB_TEXT.BIB_ID
GROUP BY ITEM.ITEM_ID, ITEM.CREATE_DATE, MFHD_MASTER.NORMALIZED_CALL_NO, BIB_TEXT.TITLE, ITEM.PERM_LOCATION
HAVING (((ITEM.CREATE_DATE)<#4/1/2015#) AND ((Max([Circulation Transactions (Charges)].CHARGE_DATE))<#4/1/2015# Or (Max([Circulation Transactions (Charges)].CHARGE_DATE)) Is Null) AND ((ITEM.PERM_LOCATION)="8"));
Additional Information
1The Date/Time "Problem"
Most Date/Time values include a time.
Example:
#2/1/2006# equals midnight at the beginning of the day, 2/1/2006
This is important to keep in mind for some queries.
As a rule of thumb: When a query asks you for a date range, the ending date in a date range should almost always be the day after the last day that you want:
January = 1/1/14 to 2/1/14
2013 = 1/1/2013 to 1/1/2014
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 27-Jan-2021