Using Date and Time Analyses
Each LIMS sample includes fields to record the following dates and times:
Collected date Collected time
Received date Received time
Started date Due date
Completed date and time Reported date and time
Only the collected date is required. Both the Completed and Reported date and time are automatically updated when a sample is completed and optionally reported so these fields are not available for data entry. The remaining fields are often sufficient to document your sample’s dates and times. However, you may encounter circumstances where you need to record additional dates and times, such as when specific testing was initiated and completed. While you could record this information in the sample’s Notes field, consider using date and time analyses for more flexibility.
Results for analyses are either a floating point result value, a short predefined result type (e.g. Pass, Fail, Positive, Negative, etc.), or a combination of result type and value (e.g. < 0.01). Since dates and times in an Access database are represented and stored internally as a single floating point value (more on these values below), LIMS supports analyses with date, time, and date and time results.
To create an analysis for date and time results, simply add a new analyte in the Analyses setup screen, set the result and warning maximum to a large number such as 1E+99, and set the result and warning minimum to zero. Next, enter an appropriate format in the Report Format field. Since the date and time format options are numerous, the Report Format pick list does not include such formats. However, LIMS supports both named and user-defined format options for dates and times. The table below lists example formats you can use in the Report Format field. Use the Test button adjacent to the field to experiment.
General Date 1/31/2013 2:45:00 PM
Long Date Thursday, January 31, 2013
Medium Date 31-Jan-13
Short Date 1/31/2013
m/d/yy 1/31/13
ddd mm/dd/yyyy Thu 01/31/2013
m/d/yyyy hh:mm 1/31/2013 14:45
Long Time 2:45:00 PM
Medium Time 02:45 PM
Short Time 14:45
h:m 14:45
hh:mm am/pm 02:45 pm
For more information, see Visual Basic for Applications Format Function for a complete list of the named date and time formats as well as the characters you can use to create user-defined date/time formats.
To enter date, time, or date and time values in the results entry screens, either right-click within the result value field and select “Date/Time Value…” from the shortcut menu or enter a forward slash (/), colon (:), or semi-colon (;) to open the popup form for date and time entry. Right-click within the popup form’s fields to access the shortcut menu options for dates and times including the popup calendar.
When you enter a date or time and close the popup you will see that Microsoft Access stores dates and times as a floating point value where the integer portion represents the date and the decimal portion represents the time.
A date value of zero represents December 30, 1899. Access stores dates before December 30, 1899 as negative numbers and those after as positive numbers. Valid time values are .0 (00:00:00) to .99999 (23:59:59), which represents a fraction of a day. Since Microsoft Excel uses the same numeric representation for dates and times, you can use Excel to “view” the numeric value. Enter a date or a time then change the cell’s format to General or Number. Similarly, the example below shows numeric values formatted as dates, times, and dates and times. Columns B, D, and F repeat the values in the preceding column but with alternate cell formatting.
You can use the numeric representation of dates and times to enter appropriate result and warning minimum and maximum values for your analyte. For example, if a time analyte should only allow values between 8:00 AM and 5:00 PM, enter these times in Excel cells then change the cell formats to General and you will see that you can set the analyte’s result minimum to 0.33333 and result maximum to 0.70833.
You can also use the numeric representation of dates and times to query samples using your date and time analyses. Again, use Excel to find the date or time values then use the values in an analyte-specific criterion on the Analytes tab of the query control. The example below shows the criterion to find samples with the My Date analyte with a value between 1-Jan-13 and 31-Jan-13.
Add date and time analyses to your LIMS projects and samples whenever you need to document additional dates and times. Add data entry specifications and query capabilities and put date and time analyses to work for you.
NOTE:
The 1899 date in SQL Server appears if you assign the number 0 to a date field. One way this can happen is if you are building an SQL statement in code and forget to wrap a date string in pound signs (“#”). Bill Mosca has a succinct explanation here:
Dates need octothorpes (#) surrounding them. Otherwise they are looked at as a mathematical expression such as 4 divided by 23 divided by 2012.
BAD : updaterec = updaterec & " set invoicemaileddate = " & InvoiceDate GOOD: updaterec = updaterec & " set invoicemaileddate = #" & InvoiceDate & "#"