Solved

#Error in query field

Posted on 2014-12-22
3
128 Views
Last Modified: 2014-12-22
I have a field in a query that is returning #Error.

The syntax I have for the field in the query designer is:  ConvertedSchedHrs: CDbl([SchedHrs])*24

The field SchedHrs syntax is SchedHrs: [LoadSchComplete]-[LoadSchStart]  and is a shorttime field.

LoadSchComplete and LoadSchStart have no format in the query but in the table they both are both Date/Time fields.

How can I get rid of the #Error result which is ultimately showing up on a report field also?
0
Comment
Question by:SteveL13
3 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40512910
try using the Nz() function here

SchedHrs:Nz([LoadSchComplete],0)-Nz([LoadSchStart],0)
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40512914
Is the error in the "ConvertedSchedHrs" column or the "SchedHrs" column?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40513053
What happened to using:

Format([LoadSchComplete]-[LoadSchStart], "hh:nn")

Personally, if you don't want to use that option, I would have used:

ConvertedSchedHours: IIF(IsNull([LoadSchComplete]), NULL, IIF(IsNull([LoadSchStart]), NULL, [LoadSchComplete] - [LoadSchStart])) * 24

This would return a NULL if either the Complete or Start field is NULL
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now