Dsum multiple criteria (1 Viewer)

[theinviter]

Dear guys,

Need help please with attached file ,

i have tried Dsum but did not get any value. jus getting " #Name?"

I need the form "Data_list" where
Field " Price1" Dlookup the ([Buy Rate], from Table " Rate_report" where "item code" match with " New Code"
Text153 = DSum([Quantity]," [MRN_Report] ","[Item Code
] =" & [New Code] & "'") + need add triple criteria between {date from and to} and [ Location (Clinic) Code ] match with "Location code" .

i have attached the file. so if you can please modify and correct me.

tanks

 

[June7]

=DSum("[Quantity]","[MRN_Report]","[Item Code] ='" & [New Code] & "'")

Advise not to use spaces nor punctuation/special characters in naming convention.

 

[theinviter]

=DSum("[Quantity]","[MRN_Report]","[Item Code] ='" & [New Code] & "'")

Advise not to use spaces nor punctuation/special characters in naming convention.

tanks alot, but i need to add 2 more criteria, how to ammend this code for [ Location (Clinic) Code ] match with "Location code" and between {date from and to} .

 

[theinviter]

=DSum("[Quantity]","[MRN_Report]","[Item Code] ='" & [New Code] & "'")

Advise not to use spaces nor punctuation/special characters in naming convention.

i tried this for second criteria : not correct
=DSum("[Quantity]","[MRN_Report]","[Item Code] ='" & [New Code] & "' & and "[Location (Clinic) Code] ='" & [Location code] &"'" )

 

[June7]

Literal text must be within quote marks.

=DSum("[Quantity]", "[MRN_Report]", "[Item Code] ='" & [New Code] & "'AND [Location (Clinic) Code] ='" & [Location code] & "' AND [some date field] BETWEEN #" & [date start] & "# AND #" & [date end] & "#")

 

[theinviter]

Literal text must be within quote marks.

=DSum("[Quantity]", "[MRN_Report]", "[Item Code] ='" & [New Code] & "'AND [Location (Clinic) Code] ='" & [Location code] & "' AND [some date field] BETWEEN #" & [date start] & "# AND #" & [date end] & "#")

tried this got " Error"

 

[June7]

You need to replace [some date field] and [date start] and [date end] with actual field/control names from your db.

 

[theinviter]

You need to replace [some date field] and [date start] and [date end] with actual field/control names from your db.

=DSum("[Quantity]","[MRN_Report]","[Item Code] ='" & [New Code] & "'AND [Location (Clinic) Code] ='" & [Location code] & "'")
i removed the Date and tried this but still got "Error"

 

[June7]

Okay, [Location Code] is a number field so remove the apostrophes from that parameter.

Apostrophes are used for text fields, # for date/time, nothing for number.

Sorry, my typo earlier. Need a space in front of AND so text does not run together in the compiled string.:
=DSum("[Quantity]","[MRN_Report]","[Item Code] ='" & [New Code] & "' AND [Location (Clinic) Code] =" & [Location code])

No longer error but does not return a value.

You have fields:

Location (Clinic) Code

and

Location - Clinics Code

The first does not have data, the second does yet you reference the first in expression, therefore no data is returned by the DLookup().