Dcount

[amnist]

Hey guys have several combo boxes that am trying to use Dcount for in order
to give me the data count. The problem is that, the tables have archive data
that goes back years. How do i restrict Dcount to only give me the most
recent data count.

this is my Dcount currently. Please help.


=DCount("*",[Forms]![frm_HC_Prior]![cbo_Prior_Period].Value,"
[Dept_Description] = '" & [Forms]![frm_HC_Prior]![cbo_dept_Description].Value
& "'")

 

[Duane Hookom]

You should be able to use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

=Concatenate("SELECT Count(*) FROM " &
[Forms]![frm_HC_Prior]![cbo_Prior_Period] & " WHERE [Dept_Description] ='" &
[Forms]![frm_HC_Prior]![cbo_dept_Description] & "'")

If you haven't got a value in Forms!frm_HC_Prior!cbo_Prior_Period, you will
get an error message from the code.

 

[amnist]

It didn't work. now its given me this #Nam
I have data in the fields but yet still am having problems.

You should be able to use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

=Concatenate("SELECT Count(*) FROM " &
[Forms]![frm_HC_Prior]![cbo_Prior_Period] & " WHERE [Dept_Description] ='" &
[Forms]![frm_HC_Prior]![cbo_dept_Description] & "'")

If you haven't got a value in Forms!frm_HC_Prior!cbo_Prior_Period, you will
get an error message from the code.

Hey guys have several combo boxes that am trying to use Dcount for in
order

[quoted text clipped - 9 lines]

[Forms]![frm_HC_Prior]![cbo_dept_Description].Value
& "'")

 

[amnist]

I forget to add, am trying run it as an expression not from vb.

You should be able to use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

=Concatenate("SELECT Count(*) FROM " &
[Forms]![frm_HC_Prior]![cbo_Prior_Period] & " WHERE [Dept_Description] ='" &
[Forms]![frm_HC_Prior]![cbo_dept_Description] & "'")

If you haven't got a value in Forms!frm_HC_Prior!cbo_Prior_Period, you will
get an error message from the code.

Hey guys have several combo boxes that am trying to use Dcount for in
order

[quoted text clipped - 9 lines]

[Forms]![frm_HC_Prior]![cbo_dept_Description].Value
& "'")

 

[Duane Hookom]

I tested this before posting it and it did work. Normally if you see #Name,
it is because the name of your text box is the same as the name of a field
in your report's record source. I also used the function name as the control
source of a text box. I didn't add any code other than using the concatenate
function.

I would add code to the function to ignore errors.

--
Duane Hookom
MS Access MVP

It didn't work. now its given me this #Nam
I have data in the fields but yet still am having problems.

You should be able to use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

=Concatenate("SELECT Count(*) FROM " &
[Forms]![frm_HC_Prior]![cbo_Prior_Period] & " WHERE [Dept_Description] ='"
&
[Forms]![frm_HC_Prior]![cbo_dept_Description] & "'")

If you haven't got a value in Forms!frm_HC_Prior!cbo_Prior_Period, you
will
get an error message from the code.

Hey guys have several combo boxes that am trying to use Dcount for in
order

[quoted text clipped - 9 lines]

[Forms]![frm_HC_Prior]![cbo_dept_Description].Value
& "'")