#name error with DCount

[dkmoreland]

I am constructing a form with multiple calculated fields from multiple queries. The form itself is not bound to a record source.

I got one field working with DCount. However, the other field is giving me a #Name error and I cannot see why.

The control is a text box named TxtNCRjobs. The query I want a record count from is called [jobs with ncrs distinct test]. My control source statement for the text box is this. There are no criteria - I just want the number of records.

DCount("*", "[jobs with ncrs distinct test]")

This form also contains a subform, listing the data from the query I'm trying to count. I've attached a screen shot for reference.

Can anybody see why I'm getting the #Name error? Is my DCount statement incomplete?

Thanks

 

[Ranman256]

does [jobs with ncrs distinct test] run without errors or params?

in the field source did you say: =Dcount(....)

 

[isladogs]

I just did a test:
I created a query with your rather awkward query name: jobs with ncrs distinct test without the square brackets!

I ran the following in the immediate window:
?DCount("*", "[jobs with ncrs distinct test]")
?DCount("*", "jobs with ncrs distinct test")

Both worked & gave me the correct answer
So I assume Ranman's guess about the = sign is the answer

If the query doesn't run, I think you would get DCount=0

I don't believe your query is called [jobs with ncrs distinct test] with the [] brackets as that flouts access naming rules.
I tried adding the [] and Access wouldn't play along

Out of interest are you starting to rename items in this database?
If not, you really should!

 

[dkmoreland]

does [jobs with ncrs distinct test] run without errors or params?

in the field source did you say: =Dcount(....)

Yes - the query runs just fine. The initial query does run with parameters, then this one queries the first one. The query results are displayed in a subform, as shown in the screen shot in my first post.

This makes no sense at all to me - I did check my field source and it was indeed missing the =.

My statement was

DCount("*", "[jobs with ncrs distinct test]")

BUT - when I add the missing = and corrected my statement to this:

=DCount("*", "[jobs with ncrs distinct test]")

I get an invalid syntax error. (Operand without operator)

What's confusing me is that I have another field with a DCount on a different query that works just fine.

 

[dkmoreland]

I just did a test:
I created a query with your rather awkward query name: jobs with ncrs distinct test without the square brackets!

I ran the following in the immediate window:
?DCount("*", "[jobs with ncrs distinct test]")
?DCount("*", "jobs with ncrs distinct test")

Both worked & gave me the correct answer
So I assume Ranman's guess about the = sign is the answer

If the query doesn't run, I think you would get DCount=0

I don't believe your query is called [jobs with ncrs distinct test] with the [] brackets as that flouts access naming rules.
I tried adding the [] and Access wouldn't play along

Out of interest are you starting to rename items in this database?
If not, you really should!

The brackets are not part of the name - I put those in there because the name is so awkward. And yes, I am renaming things. I just installed V-Tools so I can use the deep search and get everything cleaned up.

Like I said in my last post, now I'm getting an invalid syntax error when I add the =.

 

[arnelgp]

Are there any missing reference on your vba.

 

[dkmoreland]

Are there any missing reference on your vba.

There's not any vba for this form yet. It's just a form with some calculated fields.

I have something happening now that I have never seen before - I changed my statement in the control source to this:

=DCount("*","jobs with ncrs distinct test")

And now the control says #error and is flickering when I move the mouse focus away from that control.

 

[arnelgp]

Reference is global whether you have code or not.

 

[dkmoreland]

Reference is global whether you have code or not.

I don't have any indication of any missing references.

 

[isladogs]

Try creating your form again from new in case your existing form is corrupted.
Or at least try putting a control with that DCount on a new form

 

[arnelgp]

Can you rename the txtbox.

 

[dkmoreland]

Sorry, but neither suggestion was successful. I'm beginning to think I'm going about this the wrong way - I may be stepping on my own feet. If you guys don't mind, let me explain what I'm trying to accomplish and pick your brains regarding the best way to go about it.

One table holds the job information from production - the query is to display the number of jobs that have run in a given date range.

Another table contains all of the NCR info (Non-Conformance records - meaning that the job output is noncompliant with standards). It is possible for a job to have multiple NCR records. One query is to determine the number of jobs that have NCRs. The 2nd query is to count the number of jobs that have at least one NCR.

Yet another query is to capture the total number of NCRs that were reported within the date range.

Here's what I need to do - I have attached the screen shot of what I have so far.

• 
  Allow the user to select the date range (either by separate form or by using an after update event on the main form).

• Display the number of jobs for the date range.

• Display the number of jobs with NCRs.

• Display a list of all NCRs reported during the date range.

• The user will decide from that list which NCRs to throw away. They will then input the number of NCRs to be used to calculate and display the first pass yield (number of jobs with NCRs divided by the total number of jobs).

• Display a list of the NCR jobs and how many NCRs each job had.

• Print all of this on a report.

Would it be better to have all of this in a vba routine triggered by the after update event of the date selection or continue to use form controls and the queries I have created?

Is it more efficient to put a date selection form in front of this attached form and trigger it off of a button click or stick with the date range fields I currently have?

I look forward to the input and appreciate everyone's time. Believe me, I'm not trying to get anyone to do it for me. I just need some perspective from people with more experience.

Please let me know if I can provide any additional information.

Thanks

 

[isladogs]

Not answering your broader question for now....

As there is nothing wrong with your DCount formula and testing it in a new form didn't help, I would still investigate possible corruption but this time in the table(s) used in your query.

I recommend you check for what is often referred to as chinese characters in one or more records. If you have a lot of records, try filtering it down.
Also try making another identical query. If you get the same result, there is definitely corruption there. Similarly if DFirst or DMax etc based on same dataset fails that would further confirm it.

You could also try sorting the dataset using different fields

 

[dkmoreland]

I got it to work with a subform. Never mind about the broader question - I've about got it.