Это выражение набрано неправильно, или оно слишком сложно для оценки.

Question

Этот запрос запускает регистр элементов. Когда я пытаюсь суммировать поле Returns в отчете о доступе, используя =SUM([Returns]), т.е. Return - текстовое поле в отчете о доступе. это показывает следующую ошибку при запуске отчета. **

Это выражение набрано неправильно, или оно слишком сложно для оценки. Например, числовое выражение может содержать слишком много сложных элементов. Попробуйте упростить выражение, назначив его части переменным.

**

SELECT ITEM, [Transaction Date], ID, Reciept, Issued, Returns, Remarks, Manual
FROM 
 (SELECT t.item AS ITEM, irh.reciept_date AS [Transaction Date], ird.inv_id AS ID, ird.inv_qyt AS Reciept, Null AS Issued, NULL AS Returns, ird.remarks AS Remarks, ird.inv_manual_number AS Manual
FROM inventory_reciepts_header AS irh, inventory_reciepts_detail AS ird, inventory AS t
WHERE 
ird.inv_id=t.id 
AND ird.reciept_header_id=irh.id
UNION ALL

 SELECT t.item AS ITEM, iih.issue_date As  [Transaction Date], iid.inventory_id AS ID,NULL AS Reciept, iid.inventory_qyt AS Issued, NULL AS Returns, iid.remarks AS Remarks,Null AS Manual 
FROM inventory_issue_header AS iih, inventory_issue_detail AS iid,inventory 
AS t  WHERE  iid.inventory_id=t.id
    AND 
    iid.issue_header_id=iih.id

UNION ALL    
 SELECT t.item AS ITEM, iirh.return_date As  [Transaction Date], iir.item_id AS ID, Null AS Reciept, NULL AS Issued,iir.qyt AS Returns, iir.reasons AS Remarks,Null AS Manual 
FROM inventory_return_header AS iirh, inventory_return_detail AS iir, inventory AS t
WHERE      
iir.item_id=t.id
AND 
iir.return_header_id=iirh.id
 ) AS [ITEM Ledger]
WHERE ID=[Forms]![Reports Window]![cmb_ar]
and
[Transaction Date] between [Forms]![Reports Window]![ar_frm_dt] And [Forms]![Reports Window]![ar_to_dt];

Answer 1

Укажите тип данных параметров в заголовке:

PARAMETERS 
    [Forms]![Reports Window]![cmb_ar] Long,
    [Forms]![Reports Window]![ar_frm_dt] DateTime,
    [Forms]![Reports Window]![ar_to_dt] DateTime;
SELECT 
    ITEM, [Transaction Date], ID, Reciept, Issued, Returns, Remarks, Manual
FROM
<snip>

добавление

Возможно, Sum не удается, поскольку поле возвращается в виде текста из-за значений Null в первом запросе.

Решение состоит в том, чтобы запрос ожидал, что значение поля будет числом. Это может быть достигнуто с помощью выражения:

IIf(False, 0, Null)

Условие проверки False никогда не будет иметь значение True, поэтому выражение всегда будет возвращать Null, но, тем не менее, запрос оценивает обе опции вывода и решает, что это число.

Вставьте выражение, и запрос будет читать:

SELECT 
    t.item AS ITEM, 
    irh.reciept_date AS [Transaction Date], 
    ird.inv_id AS ID, 
    ird.inv_qyt AS Reciept, 
    IIf(False, 0, Null) AS Issued, 
    IIf(False, 0, Null) AS Returns, 
    ird.remarks AS Remarks, 
    ird.inv_manual_number AS Manual
FROM 
    inventory_reciepts_header AS irh, 
    inventory_reciepts_detail AS ird, 
    inventory AS t
<snip>

Answer 2

ВЫБЕРИТЕ tEnter.C_Unique, Right ([c_date], 2) & Mid ([c_date], 5,2) & Left ([c_Date], 4) AS [дата], tEnter.C_TIME AS [время], IIf ([L_TID ] = 1, '01 ', IIf ([L_TID] =3, '01', IIf ([L_TID] =7, '01 ', IIf ([L_TID] =9, '01', '02'))) ) AS InOut, tEnter.C_Name Имя AS, cOffice.C_Name Тип AS, cPost.C_Name AS Deptt, tEmploye.c_work, tEnter.L_TID, tEmploye.C_Office, tEmploye.C_Staff, wWork_NAMEResult.C_ShiftCame, wWork_NAMEResult.C_ShiftC C_Post, IIf ([C_ShiftCode] = "02", IIf (IIf ([L_TID] = 1, '01 ', IIf ([L_TID] =3, '01', IIf ([L_TID] =7, '01 ', IIf ([L_TID] =9, '01 ',' 02 ')))) ="01", [раз] +4,5,0), IIf (IIf ([L_TID] = 1, '01', IIf ([ L_TID] =3, '01 ', IIf ([L_TID] =7, '01', IIf ([L_TID] =9, '01 ',' 02 ')))) ="01", [раз] +4 , 0)) AS MaxT, IIf ([InOut] ="01",Int (Left ([C_Time], 2)), 0) AS InTime, IIf(Abs (IIf ([C_ShiftCode] = "02", IIf (IIf ([L_TID] = 1, '01 ', IIf ([L_TID] =3, '01 ', IIf ([L_TID] =7, '01', IIf ([L_TID] =9, '01 ',' 02 ')))) ="01", Int ([L_SF1Time1] / 60 -24) + ([L_SF1Time1] / 60-24) -Int ([L_SF1Time1] / 60-24), 0), IIf (IIf ([L_TID] = 1, '01 ', IIf ([L_TID] =3, '01', IIf ([L_TID] =7, '01 ', IIf ([L_TID] =9, '01', '02')))) ="01", Int ([L_SF1Time1] / 60-24) + ([L_SF1Time1] / 60-24) -Int ([L_SF1Time1] / 60-24), 0)) - IIf (IIf ([L_TID] = 1, '01 ', IIf ([L_TID] =3, '01 ', IIf ([L_TID] =7, '01', IIf ([L_TID] =9, '01 ',' 02 ')))) ="01", Int (Left ([C_Time], 2)), 0))>5, «Истекло», «Хорошо») Статус AS, Int ([L_SF1Time1] / 60-24) + ([L_SF1Time1] / 60-24) -Int ([L_SF1Time1] / 60-24) AS TimeS ,Int ([L_SF1Time2]/60-24) + ([L_SF1Time2]/60-24) -Int ([L_SF1Time2]/60-24) AS TimeE, Right ([c_date], 2) AS [день], tEnter.c_date AS Da1, IIf([inout]="02", ([da1] -1),[da1]) AS da2, Format ([da1], "Short Date") & "" & Format ([time], " Кратковременное ") AS da3, IIf ((Int ([L_SF1Time2]/60-24) + ([L_SF1Time2]/60-24) -Int ([L_SF1Time2]/60-24))> 24, (IIf ((IIf ([L_TID] = 1, '01 ', IIf ([L_TID] =3, '01', IIf ([L_TID] =7, '01 ', IIf ([L_TID] =9, '01', '02') ))))) = "02", ((DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))) - 1), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))), (DateSerial (Left ([c_Date], 4),Mid ([c_date] ], 5,2), Вправо ([c_date], 2)))) AS Da4, cStaff.C_Name AS Desig, tEmploye.c_remark Скорость AS, Формат ([da4], 'yyyymmdd') AS DA, tMoney.D_WT1Money ОТ ВНУТРЕННЕГО СОЕДИНЕНИЯ cOffice (((((tEnter INNER JOIN wWorkResult ON (tEnter.C_Date = wWorkResult.C_WorkDate) AND (tEnter.C_Unique = wWorkRloyesult.C_Unique = WWorkRloyesult.C_Unique_INNERLE_INE_INE_INE_INE)) tNENERLE_INE_ID_INENERLE_INENERLINE)) tNENERLE_INE_ID_INE_Unique) wWorkShift ON wWorkResult.C_ShiftCode = wWorkShift.C_Code) ВНУТРЕННЕЕ СОЕДИНЕНИЕ cPost НА tEmploye.C_Post = cPost.C_Code) ВНУТРЕННЕЕ СОЕДИНЕНИЕ cStaff НА tEmploye.C_Staff = cStaff.C_ConeyCode = ONConeyConey_Coney_ConeyCode = ONConeyCode) INNERConeyCode) INNER_ConeyCode) INNERConeyCode. = tEmploye.C_Office WHERE (((cOffice.C_Name)="SPMW") AND ((tEnter.L_TID) Между 7 и 10) AND ((IIf ((Int ([L_SF1Time2]/60-24) + ([L_SF1Time2]/60-24) -Int ([L_SF1Time2]/60-24))> 24, (IIf ((IIf ([L_TID] = 1, '01 ', IIf ([L_TID] =3, '01', IIf ( [L_TID] =7, '01 ', IIf ([L_TID] =9, '01', '02'))))) = "02", ((DateSerial (Left ([c_Date], 4),Mid ( [c_date], 5,2),Справа ([c_date], 2))) - 1), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))) Между [Forms]![ReportForm]! [Fromdate] и [Forms]![ReportForm]! [Дата]) И ((tEnter.c_date)> «20210131»)); UNION ALL SELECT tEnter.C_Unique, Right ([c_date], 2) & Mid ([c_date], 5,2) & Left ([c_Date], 4) AS [дата], tEnter.C_TIME AS [время], IIf ( [L_TID] =13, '01 ',' 02 ') AS InOut, tEnter.C_Name Имя AS, cOffice.C_Name Тип AS, cPost.C_Name AS Deptt, tEmploye.c_work, tEnter.L_TID, tEmploye.C_Office, tEmploye.C_ , wWorkResult.C_ShiftCode, wWorkShift.C_NAME AS C_ShiftName, tEmploye.C_Post, IIf ([C_ShiftCode] = "02", IIf (IIf ([L_TID] =13, '01 ',' 02 ') ="01", [раз ] +4.5,0), IIf (IIf ([L_TID] =13, '01 ',' 02 ') ="01",[раз] +4,0)) AS MaxT, IIf ([InOut] ="01", Int (Left ([C_Time], 2)), 0) AS InTime, IIf(Abs (IIf ([C_ShiftCode] = " 02 ", IIf (IIf ([L_TID] =13, '01 ',' 02 ') =" 01 ", Int ([L_SF1Time1] / 60-24) + ([L_SF1Time1] / 60-24) -Int ([ L_SF1Time1] / 60-24), 0), IIf (IIf ([L_TID] =13, '01 ',' 02 ') ="01", Int ([L_SF1Time1] / 60-24) + ([L_SF1Time1] / 60-24) -Int ([L_SF1Time1] / 60-24), 0)) - IIf (IIf ([L_TID] =13, '01 ',' 02 ') ="01", Int (Left ([C_Time] , 2)), 0))>5, «Истекло», «Ок») Статус AS, Int ([L_SF1Time1]/60-24)+([L_SF1Time1]/60-24)-Int([L_SF1Time1]/60-24) AS TimeS, Int([L_SF1Time2]/60-24)+([L_SF1Time2]/60-24)-Int([L_SF1Time2]/60-24) AS TimeE, Right ([c_date], 2) AS [ день], tEnter.c_date AS Da1, IIf([inout]="02", ([da1] -1),[da1]) AS da2, Format ([da1], "Short Date") & "" &Формат ([время], «Кратковременное время») AS da3, IIf ((Int ([L_SF1Time2]/60-24) + ([L_SF1Time2]/60-24) -Int ([L_SF1Time2]/60-24))> 24, (IIf ((IIf ([L_TID] =13, '01 ',' 02 ')) = «02», ((DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2 ), Right ([c_date], 2))) - 1), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2)))) ), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2)))) AS Da4, cStaff.C_Name AS Desig, tEmploye.c_remark AS Rate, Format([da4],'yyyymmdd') AS DA, tMoney.D_WT1MoneyFROM cOffice INNER JOIN ((((((tEnter INNER JOIN wWorkResult ON (tEnter.C_Unique = wWorkResult.C_Unique) AND (tEnter INNER JOIN) AND (tEnter INNER JOIN wWorkResult ON) (tEnter.C_Unique = wWorkResult.C_Unique) AND (torkDesult) )) ВНУТРЕННЕЕ СОЕДИНЕНИЕ tEmploye НА tEnter.L_UID = tEmploye.L_UID) ВНУТРЕННЕЕ СОЕДИНЕНИЕ wWorkShift НА wWorkResult.C_ShiftCode = wWorkShift.C_Code) ВНУТРЕННЕЕ СОЕДИНЕНИЕ cPost НА tEmploye.C_Post.C_Code) ВНУТРЕННЕЕ СОЕДИНЕНИЕ cStaff НА tEmploye.C_Staff = cStaff.C_Code) ВНУТРЕННЕЕ СОЕДИНЕНИЕ tMoney НА tEmploye.C_Money = tMoney.C_Code) НА cOffice.C_Code = tEmploye.C_OfficeWHERE (((cOffice) "И ((cOffice)") (tEnter.L_TID) Между 12 и 13) И ((IIf ((Int ([L_SF1Time2]/60-24) + ([L_SF1Time2]/60-24) -Int ([L_SF1Time2]/60-24))> 24 , (IIf ((IIf ([L_TID] =13, '01 ',' 02 ')) = «02», ((DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2) , Справа ([c_date], 2))) - 1), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))) , (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))) Между [Forms]![ReportForm]! [Fromdate] и [ Формы]! [ReportForm]! [Дата]) И ((tEnter.c_date)> «20210131»));C_Office WHERE (((cOffice.C_Name)="BP SPMW") AND ((tEnter.L_TID) Между 12 и 13) AND ((IIf ((Int ([L_SF1Time2]/60-24) + ([L_SF1Time2] / 60 -24) -Int ([L_SF1Time2]/60-24))> 24, (IIf ((IIf ([L_TID] =13, '01 ',' 02 ')) = «02», ((DateSerial (Left ( [c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))) - 1), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2), Справа ([c_date], 2))))), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2)) ))) Между [Forms]![ReportForm]! [Fromdate] и [Forms]![ReportForm]! [Todate]) AND ((tEnter.c_date)>"20210131"));C_Office WHERE (((cOffice.C_Name)="BP SPMW") AND ((tEnter.L_TID) Между 12 и 13) AND ((IIf ((Int ([L_SF1Time2]/60-24) + ([L_SF1Time2] / 60 -24) -Int ([L_SF1Time2]/60-24))> 24, (IIf ((IIf ([L_TID] =13, '01 ',' 02 ')) = «02», ((DateSerial (Left ( [c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))) - 1), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2), Справа ([c_date], 2))))), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2)) ))) Между [Forms]![ReportForm]! [Fromdate] и [Forms]![ReportForm]! [Todate]) AND ((tEnter.c_date)>"20210131"));Справа ([c_date], 2))) - 1), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))) Между [Forms]![ReportForm]! [Fromdate] и [Forms]![ReportForm]! [Дата]) И ((tEnter.c_date)> «20210131»));Справа ([c_date], 2))) - 1), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))), (DateSerial (Left ([c_Date], 4),Mid ([c_date], 5,2),Right ([c_date], 2))))) Между [Forms]![ReportForm]! [Fromdate] и [Forms]![ReportForm]! [Дата]) И ((tEnter.c_date)> «20210131»));