DCount with Multiple Criteria 1

[CharlieT302]

Hi All,

I am having a syntax problem. I am trying to prevent a duplicate record from being entered into a subform.

Each of the two expressions below work fine independently.

If Dcount("[Home_Qty]","tbl_Orders",Item=Dlookup("[Item_Id]","Menu"))>0 then
Msgbox

If Dcount("[Home_Qty]","tbl_Orders","Entry_date=#" & Parent!Entry_date &"#" & "and Site_ID=' " & Parent!site_ID & " ' ")>0 then
Msgbox

However, I when I combine the three criteria (below)into a single expression, it evaluates as false, even though it shouldn't.

If Dcount("[Home_Qty]","tbl_Orders",Item=Dlookup("[Item_Id]","Menu")," and Entry_date=#" & Parent!Entry_date &"#" & "and Site_ID= ' " & Parent!site_ID & " ' "))>0
Msgbox

What would be the correct syntax to combine these?

Background Info:
Item & Item_ID fields are numeric

Thanks much

 

[PHV]

If DCount("Home_Qty","tbl_Orders","Item=(SELECT Item_Id FROM Menu) AND Entry_date=#" & Parent!Entry_date & "# AND Site_ID='" & Parent!site_ID & "'") > 0 Then


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

Thanks PHV,

I tried your suggestion. However, it returned the error:
Run Time error 3354

"At most one record can be returned by this subquery"

 

[PHV]

Which Item_Id from the Menu table do you want ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

The id of the current record.

Let me explain further:
This code is attached to a combo box. When the user makes a choice, it is saved along with the date and site id.

If the user enters another record, the expression checks to see if the same choice already exists for the same site on the same date. If so, it is disallowed and prompts the user with a message.

This is why I need all three fields to be evaluated
Item
Entry Date
Site Id

 

[PHV]

Something like this ?
If DCount("Home_Qty","tbl_Orders","Item=" & Me!Item_Id & " AND Entry_date=#" & Parent!Entry_date & "# AND Site_ID='" & Parent!site_ID & "'") > 0 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

Thanks PHV,

You helped me catch a ridiculous error on my part. I was evaluating whether an Id was valid by checking its existence (with the Dlookup)and not whether it was entered on the current record.

This meant that records with the same site id and entry date were considered duplicates even though they had different items.

Thanks for the catch!