DCount Wierdness

[lana_faith]

Ok, I admit that I know just enough about Access to be dangerous, so maybe I'm going about this the wrong way... I'm trying to set up what seemed like a relatively simple Query, but for whatever reason it's just not behaving in the way I thought it would.

We're attempting to set up a database to track sales of product, as work orders, then take that data and reorganize it through Queries for use in our payroll system.

I have the tables, queries and forms set up to enter in our work orders just fine, and there are no issues there. The problem comes when I attempt to re-query that information for use in the payroll side. Here's where I sit at the moment:

I've built a query which pulls data from the [Work Orders] table, using criteria which filters out data one employee at a time, for certain invoice dates, for only certain status codes - the ones which are payable on this payroll week. Then, I built a form, [Payroll1] and added a few fields in it which *should* pull from [Payroll1]![ProductSold] field, and count the number of instances of, say, "Digital" product, tally that number using the DCount function, and display that number on the form, for later data manipulation. It all looked good, until I actually ran the form and instead, recieved an "#Error" in my newly created field, instead of the tally of "Digital" that I expected. Am I using the function DCount wrong, or is there some other relationship that I'm not understanding here?

Thanks in advance!
~Lana F Call

 

[pbaldy]

Hard to say when you don't post the offending function. Perhaps this will help:

http://www.mvps.org/access/general/gen0018.htm

 

[lana_faith]

Hard to say when you don't post the offending function. Perhaps this will help:

http://www.mvps.org/access/general/gen0018.htm

Ok... I tried a new Tactic while I was waiting for a response. Here's what I have so far...

I've inserted a subform on form [Payroll], called at the moment [Payroll1], which pulls the neccessary work orders, filtered by install date, rep id and status code. then I created a text box, and named it DigitalCount, and inserted the following expression in its control source:

=DCount(Queries!Payroll1!ProductSold,Queries!Payroll1,Queries!Payroll1!ProductSold="Digital")

Now I'm getting the error "#Name". What the heck is wrong here?

 

[pbaldy]

Review the link for proper DCount syntax.

 

[lana_faith]

Review the link for proper DCount syntax.

Sir, your link is for DLookup, and at the risk of sounding completely ignorant, what is wrong with the syntax I'm using?

 

[lana_faith]

Ok... I think I'm getting closer. This is the function now:

=DCount("ProductSold","Queries!Payroll1","ProductSold = Digital")

Now, instead of any errors, i'm getting a blank field. Any suggestions at this point?

 

[pbaldy]

Dare I mention the link again? Specifically the requirement of single quotes around a string value?

 

[lana_faith]

Dare I mention the link again? Specifically the requirement of single quotes around a string value?

Ok please don't get angry... I'm not trying to be rude here.

So the function should look like this:?

=DCount('ProductSold','Payroll1','ProductSold = Digital')

 

[lana_faith]

Or this maybe?

=DCount('ProductSold','Payroll1','ProductSold'='Digital')

 

[lana_faith]

Ohhhhh ok I think I see what you were referring to in terms of syntax. Sorry about this... After reviewing that link again, I came up with this:

=DCount("ProductSold","Payroll1","ProductSold='Digital'")

Which looks like it fits the syntax of the link you sent me, but I still get that #Error? again.

 

[pbaldy]

I'm not trying to be rude either, but nothing you've posted looks like the sample on the link. That example (for a string value) is:

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

so copying in your object names, yours would be:

=DCount("ProductSold" , "Payroll1" , "ProductSold= 'Digital'")

 

[pbaldy]

That looks about right. Can you post a sample db with the form/query/table in it? We'll sort this out.

 

[lana_faith]

I'd be happy to, let me see if I can get this to work... in the meantime, I wonder if you can answer this for me. It may solve the whole issue. Is it simply because I'm asking the function to pull information from a Query, and not a Table? It seems to work fine when I change the values to ones matching a Table, and then returns #Error? when I try a Query. Am I just going about this completely wrong?

 

[lana_faith]

Trying to send you my database, but the upload keeps failing. hm. lovely.

 

[pbaldy]

Table or query shouldn't matter. Make sure the field name exists in the query exactly as in the DCount.

The sample must be zipped and under 393k.

 

[lana_faith]

Try # 2...this should work this time.
View attachment DB1.zip

 

[boblarson]

One IMPORTANT thing to note. You can have the syntax correct, but if you control name is the same as your field name when creating calculations, it will spew and give you #Name or #Error. So, you should have your controls named something different than your field names. For example when using the wizard to create a form it will take a field named "ErrorRate" and name the control that is bound to that field, "ErrorRate." You will need to change the control name to something like txtErrorRate. This is good practice even if you don't do calculations based on the values in a control.

 

[lana_faith]

One IMPORTANT thing to note. You can have the syntax correct, but if you control name is the same as your field name when creating calculations, it will spew and give you #Name or #Error. So, you should have your controls named something different than your field names. For example when using the wizard to create a form it will take a field named "ErrorRate" and name the control that is bound to that field, "ErrorRate." You will need to change the control name to something like txtErrorRate. This is good practice even if you don't do calculations based on the values in a control.

Ok that makes sense. can you point me in the right direction to rename my controls then? How do I get that done?

 

[pbaldy]

BL raises a good point, but there are actually 2 other problems. First, your query is a parameter query ([Enter blah]). The DCount can't supply the parameters, thus an error. If you can have the query get its criteria from a form or otherwise avoid the prompts, this would work.

The second problem you'll run into is due to using lookup fields. Your DCount needs to look for 4, not "Digital", so this works:

=DCount("ProductSold","PayrollQuery","ProductSold = 4")

You'll notice that in the Work Orders table the ProductSold field is a number, not text. You'll find most of us will recommend against lookup fields, because of this kind of confusion.

Finally, you should avoid the use of spaces and symbols in your object names. In the long run, they're more trouble than they're worth.

 

[lana_faith]

Alright. At least I know now why it's not working. The 4 vs. 'Digital' issue I had already thought of, and neither option makes any immediate difference, so I think that the main problem is that it's pulling from a parameter query. I've made an attempt or two at having the query pull from a form, which have met with failure. Can you point me in the direction of a good tutorial helping me create one of these?