Access Dcount function with two criteria syntax issue (1 Viewer)

[Damocles25]

Hello Guys,

Need an assist on the syntax for Dcount. I have a table "Tbl_UnitManufacturingRecord" which controls how a part moves and is classified. the two criteria that I am trying to use to count are "IsPmr" (CheckBox yes/no) and "WorkOrderNumber" which is a long integer. The table has multiple records under a given WorkOrder (think batch run with individual Sn as key field) and the PMR is a "Scrap Designation".

I have a form that I want to have populate a field with the current count of PMR (scrap assemblies) for the Forms current Work Order.

The form is "Frm_ActiveWorkOrderStatus" and the call fields are "WorkOrderNumber" and "Chb_IsPmr". Respectively one will pull the WorkOrderNumber value, and the Other will pull the Static "true" value present in the form

Taking baby steps I was able to get a total PMR count from the table when set to true, but this returns all PMR's regardless of Work Order.

Dim VarCount As Integer

VarCount= DCount("[IsPMR]", "Tbl_UnitManufacturingRecord", "[IsPMR] = true")

I researched the syntax but keep getting different errors when I try different suggestions to add the WorkOrderNumber criteria.

for example

'VarCount = DCount("[IsPMR]", "Tbl_UnitManufacturingRecord", "[IsPMR] = true" And [WorkOrderNumber] = " & Forms![Frm_ActiveWorkOrderStatus]![WorkOrderNumber] & ")

Any Help you could give would be greatly appreciated. I just can't seem to hit the right mix.

Sincerely

LP

 

[plog]

"[IsPMR] = true" And [WorkOrderNumber] = " & Forms![Frm_ActiveWorkOrderStatus]![WorkOrderNumber] & ")

You need to build a string representing your criteria. Double quotes start and end literal values (whatever is between them shows up literally as you have typed it). Anything not inside double quotes are seen as variables and whatever value they hold is put into the string you are building.

When you want to add literals to literals, literals to variables, variables to literals and variables to variables you put them together by putting an ampersand between them.

var MyVar1="First Variable"
var MyVar2="Second Variable"

var MyFinalString="This is the first literal, this is the " & myVar1 & " this is the second literal and this is the " & MyVar2 & "."

So, every odd number double quoate starts a literal, every even numbered double quote ends that literal and whatever is after or outside an even numbered double quote is treated as a variable.


Did you mean for "And [WorkOrderNumber] =" to be a variable? Also, you didn't use an ampersand to tie those 2 parts together. Did you mean for " & Forms![Frm_ActiveWorkOrderStatus]![WorkOrderNumber] & " to be a literal?

 

[theDBguy]

Hi. Welcome to AWF!

As you said, "baby steps."

The DCount() syntax is simply this:

DCount("*","TableName","Criteria")

So, in your case, we could do this:

DCount("*","Tbl_UnitManufacturingRecord","IsPMR=True")

which would return all scraps. To add the work order number, we could do this:

DCount("*","Tbl_UnitManufacturingRecord","IsPMR=True AND WorkOrderNumber=33")

Obviously, that would only return the count for work order #33. If we want to replace #33 with whatever record is on the form, we might do it this way:

DCount("*","Tbl_UnitManufacturingRecord","IsPMR=True AND WorkOrderNumber=" & Me.TextboxName)

Hope that helps...

Edit: Oops, too slow...

 

[Damocles25]

Awesome guys! thank you. let me digest it but love the Baby steps explanation. I have been working on projects and figuring stuff as I go, but the fundamentals are clearly an Item that is catching up with me.

I knew from examples that I had the wright structure but was missing the syntax. The program saw what I was doing, but in many cases it was giving me an error because I missed something and as plog wrote... do you mean this? or this?

Thank you both Plog & theDBguy for taking the time to break it down. Ill plug it in and see what magic comes out.