#Error in calculated field

[Marianne]

I have a union query:
SELECT Service_Plan_ID, Format(Start_Time,"h:mm") as Start,
Format(End_Time,"h:mm") as End, ServDate, InvNo, Recnum, "" as Qty, "" as
Rate, Sleepover, "" as Description,Verified, ShadShift
FROM tblService_Date_and_Times

UNION ALL SELECT ServiceNo as Service_Plan_ID, "" as Start, "" as End,
ServDate, InvNo, Recnum, Qty, Rate, False as Sleepover, Description, True as
Verified, False as ShadShift
FROM tblMiscInvoicing;

This union query is in another query where I use the following formula to
calculate the hours

Hrs: IIf([Start]="",[Qty],IIf([Sleepover]=True Or
[Start]=[End],0,IIf([Start]>[End],(([End]-[Start])*24)+24,([End]-[Start])*24)))

I get the error when I run the query. If I try to sort on the field I get
the following message:

Data type mismatch in criteria expression

Anyone with any ideas?

 

[Allen Browne]

There are some issues with data types here.

To start with, the Format() function generates a column of Text. It is not
treated as a Date/Time type, and so the math won't work propertly.

(Hint: You can see that Access is treating a column as text, if the results
display left-aligned. Numbers and dates default to right-aligned.)

Next, you are using a zero-length string (zls) for Qty. Again, Access will
treat this as text rather than a number, and the math won't work properly.
Same for Rate. Use Null instead of "".

Where this is no field and you need the column to be null, you can still
indicate the data type by providing an alternative, e.g.:
IIf(False, 0, Null) AS Qty
Of course, False will never evaluate to True, so the zero will never be
assigned. It will be Null in all rows, but the presence of the alternative
gives JET the clue about the intended data type.

Next, some of the aliases you used for your fields will give you problems:
- End is reserved;
- Start is future reserved;
- Rate is a VBA function.

Once you have the inputs sorted out, you need to be aware of the data types
in the Hrs expression as well. For example, use:
IIf([Start] Is Null, ...
rather than:
IIf([Start] = "", ...

So, something like this:

SELECT Service_Plan_ID,
Start_Time,
End_Time,
ServDate,
InvNo,
Recnum,
IIf(False, 0, Null) AS Qty,
IIf(False, 0, Null) AS TheRate
Sleepover,
Null AS Description,
Verified,
ShadShift
FROM tblService_Date_and_Times
UNION ALL SELECT ServiceNo as Service_Plan_ID,
IIf(False, #0:00:00#, Null) AS Start_Time,
IIf(False, #0:00:00#, Null) AS End_Time,
ServDate,
InvNo,
Recnum,
Qty,
tblMiscInvoicing.[Rate] AS TheRate,
False as Sleepover,
Description,
True as Verified,
False as ShadShift
FROM tblMiscInvoicing;

Finally, your expressions might be easier if the date/time fields contained
both a date and a time value. Access is able to figure out values that cross
over midnight if both the date and time are present in the field.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a union query:
SELECT Service_Plan_ID, Format(Start_Time,"h:mm") as Start,
Format(End_Time,"h:mm") as End, ServDate, InvNo, Recnum, "" as Qty, "" as
Rate, Sleepover, "" as Description,Verified, ShadShift
FROM tblService_Date_and_Times

UNION ALL SELECT ServiceNo as Service_Plan_ID, "" as Start, "" as End,
ServDate, InvNo, Recnum, Qty, Rate, False as Sleepover, Description, True
as
Verified, False as ShadShift
FROM tblMiscInvoicing;

This union query is in another query where I use the following formula to
calculate the hours

Hrs: IIf([Start]="",[Qty],IIf([Sleepover]=True Or
[Start]=[End],0,IIf([Start]>[End],(([End]-[Start])*24)+24,([End]-[Start])*24)))

I get the error when I run the query. If I try to sort on the field I get
the following message:

Data type mismatch in criteria expression

Anyone with any ideas?

 

[Marianne]

Thank you very much. I actually learnt a lot from this post. It all works
beautifully. I have to say, I really admire the work you do.

 

[Allen Browne]

Thank you very much. I actually learnt a lot from this post. It all
works beautifully. I have to say, I really admire the work you do.

Excellent news. And thanks for posting so we know it helped.