DCount making me go insane! - please help 1

[Jayz]

Ok,
I'm obviously doing something wrong here, I can't get this to work.

I have a query named [qryBacklog] and a field name

 which displays letters A-F depending on days elapsed for the total days an order is overdue. eg >100 days =A, >80days=C and so on...

I created my continuous form based on the query. and created an unbound text box. In the control source I entered:

=DCount("[code]","qryBacklog","[Code] = 'A'")

This is suppose to count the Number of times the letter "A" appears, but instead I get the #Error.

What am I doing wrong.

Please help.

Regards,
Jay

 

[jimmythegeek]

The first argument in the DCount function is NOT optional, you have to have a value here, in your example, you do not. This represents the field that you want to count.

If the name of your field is Field1, then the DCount function should look like this:

=DCount("Field1", "qryBackLog", "Field1='A'&quot Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[Jayz]

Hi Jim,

This is the code I applied:

=DCount("code","qryBackLog","code='A'&quot

My field name is

, I'm still getting that #Error.

Any suggestions why?

Thanks,
Jay

 

[jayz12]

my field name is code. I don't know why Tek-Tips converts it to that funny character.

 

[jimmythegeek]

Your syntax is correct. Try opening the debug window (ctrl+g) and typing in

?DCount("code","qryBackLog","code='A'&quot

and hit enter. See if the number comes back correctly. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jayz12]

Would this have anthing to do with the field "code" being created in the query grid and not actually existing in a table.

eg. The field Code in the query grid has been created:

Code:IIf([Elapsed]>=100,"A",IIf([Elapsed]>=81,"B",IIf([Elapsed]>=61,"C",IIf([Elapsed]>=41,"D",IIf([Elapsed]>=21,"E","F&quot))))

Any help please??

 

[jayz12]

Hi Jim,
Thanks for taking out the time to look at this.

I ran the code in the debug window and it came up with this error message:

Run-time error '62506':
Data type mismatch in criteria expression

What does this mean?

 

[jimmythegeek]

Actually, why don't you just create a function, and use it instead:

Put this in a module, replace the red part with the name of the table the Elapsed field is in.

=====
Public Function GetACount() As Long
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Count(Elapsed) AS TotCount FROM TableNameHere WHERE Elapsed>=100", dbOpenSnapshot)
GetACount = rs!TotCount
rs.Close
Set rs = Nothing
End Function
=====

Now in your text box, instead of using the DCount, put this:

=GetACount()
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[Jayz]

Perfect!!!!,

Thanks Jim for all your patience and help. Keep up the good work.


Regards,
Jay