If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error in calculated field
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? |
#2
|
|||
|
|||
#Error in calculated field
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Marianne" wrote in message ... 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? |
#3
|
|||
|
|||
#Error in calculated field
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. "Marianne" wrote: 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? |
#4
|
|||
|
|||
#Error in calculated field
"Marianne" wrote in message
... 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
Thread Tools | |
Display Modes | |
|
|