Query Error (See Table)

[kennethnaga]

Index&Facility	Reporting Year	SCUs Balance	Previous Balance
101A	2019	5000	4000
101B	2019	4500	3500
101A	2018	4000	-
101B	2018	3500	-

Expression:

Previous Balance: DLookUp("Balance SCUs","[tbl_Sch2_Summary]","[tbl_Sch2_Summary].[Index&Facility] = '" & [tbl_Sch2_Summary].[Index&Facility] & "' And [tbl_Sch2_Summary].[Reporting Year] = " & [tbl_Sch2_Summary].[Reporting Year]-1)

Error: Syntax error (missing operator) in query expression 'Balance SCUs'.

Hi, I'm trying to make a query which cross-checks values, in this case it's the balance field. In the previous balance field, I want to display the balance from the previous year for a particular Facility ID. In Excel, the SUMIFS function was used, but as far as I'm aware this does not exist in Access. Any help regarding this issue is greatly appreciated and if anything is unclear please let me know, thank you!

The pattern of FacilityID and Reporting Year is not consistent (does not alternate back and forth between 101A and 101B as shown).

Edit: I know I shouldn't have symbols/spaces in object names, I'm working on an existing dB that I did not create. Thank you!

[Gicu]

Maybe its just a mistake here in the forum but you have the field name reversed in the dlookup (should be [SCUs Balance] not [Balance SKUs]).

Cheers

[kennethnaga]

Yea it's just a mistake in the post

[Bob Fitz]

Cross-Posted: https://www.accessforums.net/showthread.php?t=79596

kennethnaga Please read this: https://www.excelguru.ca/content.php?184

[kennethnaga]

Thanks for the link, promise not to do it again

[Gicu]

Looks like the Reporting year is left-aligned, is the data type text (if yes you need to wrap it in single quotes as well).

Cheers,

[kennethnaga]

Reporting year is number type (not like the table)