DCount with 2 parameters

[FBrnstrmr]

Hi, does anyone know how to use dcount based on a date in the subform as well
as an address in a the associated form? Thanks very much IA, -Fred

 

[Tom van Stiphout]

On Sun, 22 Feb 2009 10:21:01 -0800, FBrnstrmr

=DCount("myField", "myTable", "myDate=#" & forms!myForm!myDateControl
& "# and myAddress='" & forms!myForm!myAddressControl & "'")
(replace myObjectNames with yours)
Note how I am wrapping date values with #-signs, and string values
with single-quotes.

-Tom.
Microsoft Access MVP

 

[FBrnstrmr]

Sorry I am slightly (I hope) confused by what each of thes does.

& forms!myForm!myDateControl and

& "# and myAddress='" and

& forms!myForm!myAddressControl & "'")

which means I don't know what is supposed to go where, as I am not sure of
the interconnection.
Thanks very much,
-Fred

 

[FBrnstrmr]

I have tried to do this in the default space for the count# of the subform,
is that the best place, as I am getting ####### then ?Name then blanks
depending upon what other data I have placed in the subform. Sorry for my
confusion.
-Fred

 

[Tom van Stiphout]

On Sun, 22 Feb 2009 11:26:01 -0800, FBrnstrmr

It's all Access 101. & is used for string concatenation:
dim s as string
dim b as string
b = "bbb"
s = "aaa" & b
debug.print s
aaabbb

To get the value of some control on some form:
Forms!myForm!myControl
(replace myObjectNames with yours)

The rest I have already said: wrap date values with #-signs, and
string values with single-quotes.

-Tom.
Microsoft Access MVP

 

[FBrnstrmr]

Thanks Tom,
I went through it again and I see that I was viewing it as three lines
whereas it is just 2
=DCount("myField", "myTable",
"myDate=#" & forms!myForm!myDateControl & "#
and
myAddress='" & forms!myForm!myAddressControl & "'")

so I tried that too but it doesn't work either.
So here is a 101 question for you,
Which field is the "myField", the place where i am trying to put it or one
of the criteria I am using to define the count? Thanks very much,
-fred

 

[FBrnstrmr]

It feels to me as though I need something like
DCount(myfield,mytable) & (myotherfield,myothertable) does that work like
that?
Thanks,
-Fred

 

[John W. Vinson]

It feels to me as though I need something like
DCount(myfield,mytable) & (myotherfield,myothertable) does that work like

No.

DCount takes three operands: the field you want to count (use "*" if you want
to count all records); the name of the table or query within which those
records exist; and a text string which evaluates to the WHERE clause of a
Query identifying which items you want.

I can't see your database, and I don't know the structure of your table, and I
can't see your form (hint: nobody else here can either unless you post them),
but I'm guessing that you want

=DCount("*", "myTable", "[myDate] = #" & [Forms]![MyMainForm]![myDateControl]
& "# AND MyAddress = '" &
[Forms]![MyMainForm]![Subformcontrolname].Form![Textboxname] & "'")

This would all be ONE LINE - it's a single function call, not three separate
statements. You can break up the line for readability by putting a blank
followed by an underscore at the end of a (partial) line - don't do so in the
middle of a string constant though.

I'm assuming (again, in the absence of any information) that you have a form
named MyMainForm with a textbox myDateControl that you want to use as a date
criterion; a Subform control on that mainform named Subformcontrolname; and
that the form within that subform has a control named Textboxname containing
the address. I'm also assuming that the table myTable contains a date field
named mydate and also a text field name MyAddress and that you want to find
how many records match on both criteria.

Why you should have both the date and address in the same table, but ALSO
apparently have them in two separate tables (the form and subform's
recordsource) I do not know, but that's the situation as I've been able to
piece it together from your somewhat confusing posts. If you can clarify my
misunderstandings I'll be glad to try to help.

 

[FBrnstrmr]

Thanks very much John,

This is what I have;

A main Table with the address and other data of course
and a child table which may have numerous violations per inspection per
address, plus the date for each inspection. Generally, all inspections for an
address will be on the same day, until the next inspection (or reinspection).
Each inspection will be defined by the date and address, but each violation
per inspecton needs to be numbered individually. So I have a form which shows
the address via a combo-box and a subform with a cascading combo-box to show
violation listings and the violation listings by classification. I have a
text box for which I am trying to use DCount to number the Violations
according to the date (on the subform) and the address (on the Form). I am
hoping to reset the counter everytime I change to a new address on the form
and add a new violation for a given date and to change the count by one for
each violation on that date at that location.
If I could send the layout I would but it is very busy and I don't know
how,yet.

On a separate note, My cascading combo-box is sending the data in the
correct format to the table for the violation but it is sending the index
rather than the classification info for the primary combo-box to the table.
ie: I am getting a single number showing in the table for the classification
rather than the required text, but the next level down gives me the violation
text as required.

You may be wondering why I am torturing myself and to a lesser degree you
folks as well, it is because the folks I work with need this in a big way,
and more so, the people who live in the buildings we need to inspect. I am
hoping to convince the folks that make the decisions, to purchase what we
need to do our jobs better, but I need a working product in order to do that.
and so, Thank-you very very much for any help you can provide.

It feels to me as though I need something like
DCount(myfield,mytable) & (myotherfield,myothertable) does that work like

No.

DCount takes three operands: the field you want to count (use "*" if you want
to count all records); the name of the table or query within which those
records exist; and a text string which evaluates to the WHERE clause of a
Query identifying which items you want.

I can't see your database, and I don't know the structure of your table, and I
can't see your form (hint: nobody else here can either unless you post them),
but I'm guessing that you want

=DCount("*", "myTable", "[myDate] = #" & [Forms]![MyMainForm]![myDateControl]
& "# AND MyAddress = '" &
[Forms]![MyMainForm]![Subformcontrolname].Form![Textboxname] & "'")

This would all be ONE LINE - it's a single function call, not three separate
statements. You can break up the line for readability by putting a blank
followed by an underscore at the end of a (partial) line - don't do so in the
middle of a string constant though.

I'm assuming (again, in the absence of any information) that you have a form
named MyMainForm with a textbox myDateControl that you want to use as a date
criterion; a Subform control on that mainform named Subformcontrolname; and
that the form within that subform has a control named Textboxname containing
the address. I'm also assuming that the table myTable contains a date field
named mydate and also a text field name MyAddress and that you want to find
how many records match on both criteria.

Why you should have both the date and address in the same table, but ALSO
apparently have them in two separate tables (the form and subform's
recordsource) I do not know, but that's the situation as I've been able to
piece it together from your somewhat confusing posts. If you can clarify my
misunderstandings I'll be glad to try to help.

 

[FBrnstrmr]

I may have confused the issue more with my diatribe
so I will try to shorten it

I am trying to place a counter on my subform to count each violation This I
have done already except it counts them all. I need to limit the counter to
just those on the date which is also on the subform and the address which is

It feels to me as though I need something like
DCount(myfield,mytable) & (myotherfield,myothertable) does that work like

No.

DCount takes three operands: the field you want to count (use "*" if you want
to count all records); the name of the table or query within which those
records exist; and a text string which evaluates to the WHERE clause of a
Query identifying which items you want.

I can't see your database, and I don't know the structure of your table, and I
can't see your form (hint: nobody else here can either unless you post them),
but I'm guessing that you want

=DCount("*", "myTable", "[myDate] = #" & [Forms]![MyMainForm]![myDateControl]
& "# AND MyAddress = '" &
[Forms]![MyMainForm]![Subformcontrolname].Form![Textboxname] & "'")

This would all be ONE LINE - it's a single function call, not three separate
statements. You can break up the line for readability by putting a blank
followed by an underscore at the end of a (partial) line - don't do so in the
middle of a string constant though.

I'm assuming (again, in the absence of any information) that you have a form
named MyMainForm with a textbox myDateControl that you want to use as a date
criterion; a Subform control on that mainform named Subformcontrolname; and
that the form within that subform has a control named Textboxname containing
the address. I'm also assuming that the table myTable contains a date field
named mydate and also a text field name MyAddress and that you want to find
how many records match on both criteria.

Why you should have both the date and address in the same table, but ALSO
apparently have them in two separate tables (the form and subform's
recordsource) I do not know, but that's the situation as I've been able to
piece it together from your somewhat confusing posts. If you can clarify my
misunderstandings I'll be glad to try to help.

 

[John W. Vinson]

I may have confused the issue more with my diatribe
so I will try to shorten it

I am trying to place a counter on my subform to count each violation This I
have done already except it counts them all. I need to limit the counter to
just those on the date which is also on the subform and the address which is
on the mainform.
hope that helps. and Thanks again.

I would like to help, but you're making it very difficult to do so.

I do not know the name of your tables. You haven't posted them.
I do not know the names of your forms or the controls on them.
I do not know the names of the fields that contain the information you want.

That said...

=DCount("*", "SomeTableName", "[Datefield] = #" &
[Forms]![Mainform]![Subformname].Form![Datecontrol] & "# AND Address = '" &
Forms![Mainform]![Addresscontrol] & "'")

as the control source for a textbox should get you started.