Dcount as control for text box

[DFowler]

I basically have 2 questions, First question is can Dcount work based on a query as opposed to a table and then if it can work on a query could someone let me know why my following dcount code is not working or if i am just being a little simple, Basically i am trying to do a count based on a selection from a combo box on a form with the results populating a text box

=DCount("[Premise]","[D - QrySelect100]","[Name 1/Last Name]=[Combo0]")

 

[Oldsoftboss]

Sure can....

=DCount("[Premise]","[D - QrySelect100]","[Name 1/Last Name]=[Combo0]")

Premise = field name
D - QrySelect100 = Query name (strange query name - should have no spaces at least)
Name 1/Last Name = field that is used in the criteria. Once again strange field name.

Presuming the field and query names are correct...

=DCount("[Premise]","[D - QrySelect100]","[Name 1/Last Name]= " & Me.Combo0)


Dave

 

[DFowler]

all names are correct but the fix u have given me gives me an error in the text box which it is using as a control

 

[Oldsoftboss]

"[Name 1/Last Name]= " & Me.Combo0)

This piece presumes a numeric value (sorry)

"[Name 1/Last Name]= ' " & Me.Combo0 & " ' " )

See how that goes

 

[DFowler]

Right still nothing i am getting An Automation object error with the statement Me. Unsure as to what this is but i do know it still gives me an error wheras my original code just gave me 0 and nothing more.

 

[DFowler]

I have tweaked the code you gave me and i have had success, Thankyou for your assistance and i have pasted the code that worked below, Cheers again

=DCount("[Premise]","[D - QrySelect100]","[Name 1/Last Name]='" & [Combo0] & "'")

 

[cb@chrisb.eu]

Hi.
=DCount("*","Table2","[Field1]=" & Forms![Form1]![Field1])
looks OK according to most forums, but I get Name errors. What am I doing wrong?

 

[missinglinq]

Is the [Field1] field Numeric (as your syntax would indicate) or Text?

 

[cb@chrisb.eu]

Hi. they're Reference numbers, but with gaps between (as in 75 045 564). Probably safer to treat them as text?

 

[vbaInet]

If it contains spaces then it's a Text field.

Use this:

=DCount("*", "Table2", "[Field1] = [COLOR=Red]'[/COLOR]" & [Forms]![Form1]![Field1] & "[COLOR=Red]'[/COLOR]")

Notice the red bit? That's how you handle Text fields.

Also, I would advise that you work on your naming conventions. Field1 isn't a meaningful name.

 

[Galaxiom]

If you are going to refer to the field (or control) via the Forms Collection you might as well use this expression rather than concatenation.

=DCount("*","Table2","[Field1]=Forms![Form1]![Field1]")

This syntax requires no delimiters for text and handles dates without any fuss at all.

Concatenation is used when the reference is via the current class module (Me). It has the advantage of being able to handle different contexts but you then need to deal with the delimiters.

 

[cb@chrisb.eu]

VBAInet, your translation produces a #Name (error): =DCount("*","Haemoglobinopathy Investigations","[Hospital No]="” & [Forms]![Patient Details]![Hospital No]& "'")
Galaxiom, your translation produces a flashing #Error (error): =DCount("*","Haemoglobinopathy Investigations","[Hospital No]=Forms![Patient Details]![Hospital No]") Am I (gulp) still doing something wrong? I can see in a form the record it's denying...

 

[vbaInet]

You wrote:

  =DCount("*","Haemoglobinopathy Investigations","[Hospital No]=[COLOR=Red]"” [/COLOR]& [Forms]![Patient Details]![Hospital No]& "[COLOR=Red]'[/COLOR]")

I wrote:

=DCount("*", "Table2", "[Field1] = [COLOR=Red]'[/COLOR]" & [Forms]![Form1]![Field1] & "'")

Can you spot the difference? Plus pay close attention to the spaces between & and the field name.

 

[cb@chrisb.eu]

Sorry, but still #Name?
=DCount("*", "Haemoglobinopathy Investigations", "[Hospital No] = ' " & [Forms]![Patient Details]![Hospital No] & " ' ") (Single quotes gapped for clarity: no effect on result. .Extra gaps are reduced by the text box.)

 

[cb@chrisb.eu]

Sorry, but still #Name?
=DCount("*", "Haemoglobinopathy Investigations", "[Hospital No] = ' " & [Forms]![Patient Details]![Hospital No] & " ' ") (Single quotes gapped for clarity: no effect on result. .Extra gaps are reduced by the text box.)

 

[vbaInet]

Either one of:

=DCount("*", "Haemoglobinopathy Investigations", "[Hospital No] = '" & [Hospital No] & "'")

=DCount("*", "Haemoglobinopathy Investigations", "[Hospital No] = " & [Hospital No])

 

[cb@chrisb.eu]

The first one works fine. Thanks very much.
(The second one gives #Error)

 

[vbaInet]

That's because, like I mentioned, your field's data type is Text. So fyi, if it's a Number data type you will use the second one.

 

[Galaxiom]

One of these doesn't exist.

[Haemoglobinopathy Investigations] must be a query or a table
[Hospital No] must be a field in that query or table.

[Hospital No] must be a field or control in the form [Patient Details]. Is it in a subform?

 

[cb@chrisb.eu]

HI (for Short) is a table. It , as well as the form, shares a (linked) field of Hospital No. The text Box (bottom left) now works fine. On this blank form, a string of 0s, but on real pages, lots of 1 and 2s.