Can I use group by clause in DCount

[mls via AccessMonster.com]

I want to dispaly the total orders for orderid on the form next to Orderid. I
tried creating a unbound text box and used Dcount which gives me the total
number of records in that table. Where as I want the total number of records
for that particular orderid. =DCount("*","[tblTemp]","OrderID")
Is this possible?

Thanks

 

[ruralguy via AccessMonster.com]

Yes, but you need to fix your criteria argument.
DCount("*","[tblTemp]","[OrderID] =" & Me.OrderID)
...if OrderID is a number OR
DCount("*","[tblTemp]","[OrderID] ='" & Me.OrderID & "'")
...if OrderID is a string.

I want to dispaly the total orders for orderid on the form next to Orderid. I
tried creating a unbound text box and used Dcount which gives me the total
number of records in that table. Where as I want the total number of records
for that particular orderid. =DCount("*","[tblTemp]","OrderID")
Is this possible?

Thanks

 

[Daryl S]

mls -

You can add criteria as the third parameter to the DCount function. You
need to provide the criteria, not just a field name. If OrderID is a text
field, and the value you want to restrict it to is on your form, then use
something like this:

=DCount("*","[tblTemp]","[OrderID] = '" & Me.OrderID & "'")

 

[mls via AccessMonster.com]

Orderid is numeric field. And I tried the following..
=DCount("*","[tblTemp]","[OrderID] = " & Me.OrderID &)
but it shows as #Name? in my form view

I know that ACCESS doesn't like extra space etc. so can you correct the above
line?

Yes, but you need to fix your criteria argument.
DCount("*","[tblTemp]","[OrderID] =" & Me.OrderID)
...if OrderID is a number OR
DCount("*","[tblTemp]","[OrderID] ='" & Me.OrderID & "'")
...if OrderID is a string.

I want to dispaly the total orders for orderid on the form next to Orderid. I
tried creating a unbound text box and used Dcount which gives me the total

[quoted text clipped - 3 lines]

Thanks

 

[ruralguy via AccessMonster.com]

Loose the last &
=DCount("*","[tblTemp]","[OrderID] = " & Me.OrderID)

Orderid is numeric field. And I tried the following..
=DCount("*","[tblTemp]","[OrderID] = " & Me.OrderID &)
but it shows as #Name? in my form view

I know that ACCESS doesn't like extra space etc. so can you correct the above
line?

Yes, but you need to fix your criteria argument.
DCount("*","[tblTemp]","[OrderID] =" & Me.OrderID)

[quoted text clipped - 7 lines]

 

[mls via AccessMonster.com]

I tried and it is still showing the same #name? error. In fact when I saved,
it added square brackets around Me.orderId..

=DCount("*","[tblTemp]","[OrderID] = " & [Me].[OrderID])

Loose the last &
=DCount("*","[tblTemp]","[OrderID] = " & Me.OrderID)

Orderid is numeric field. And I tried the following..
=DCount("*","[tblTemp]","[OrderID] = " & Me.OrderID &)

[quoted text clipped - 7 lines]

 

[ruralguy via AccessMonster.com]

What is the name of the control with the OrderID you want to confine your
DCount to? Obviously is is not OrderID

I tried and it is still showing the same #name? error. In fact when I saved,
it added square brackets around Me.orderId..

=DCount("*","[tblTemp]","[OrderID] = " & [Me].[OrderID])

Loose the last &
=DCount("*","[tblTemp]","[OrderID] = " & Me.OrderID)

[quoted text clipped - 4 lines]

 

[mls via AccessMonster.com]

Name of my orderid field is OrderID and COUNTER is the name of the text field
I want to display.

What is the name of the control with the OrderID you want to confine your
DCount to? Obviously is is not OrderID

I tried and it is still showing the same #name? error. In fact when I saved,
it added square brackets around Me.orderId..

[quoted text clipped - 6 lines]

 

[ruralguy via AccessMonster.com]

Try:
=DCount("*","[tblTemp]","[OrderID] = " & [OrderID])
...which should point to the field in your RecordSource named OrderID

Name of my orderid field is OrderID and COUNTER is the name of the text field
I want to display.

What is the name of the control with the OrderID you want to confine your
DCount to? Obviously is is not OrderID

[quoted text clipped - 4 lines]

 

[mls via AccessMonster.com]

It worked. Thank you

Try:
=DCount("*","[tblTemp]","[OrderID] = " & [OrderID])
...which should point to the field in your RecordSource named OrderID

Name of my orderid field is OrderID and COUNTER is the name of the text field
I want to display.

[quoted text clipped - 4 lines]

 

[ruralguy via AccessMonster.com]

You're welcome. I rename my controls with a prefix like txtControlName to
make sure there is no confusion with the Field with a similar name.

It worked. Thank you

Try:
=DCount("*","[tblTemp]","[OrderID] = " & [OrderID])

[quoted text clipped - 5 lines]