SELECT COUNT Questions

[brsawvel]

Hello,

I want to have a textbox provide a count of records matching certain parameters. I have a general idea of how to write the code, but can't make it work...

Basically, I want the textbox to display the count of all records that match fldA (reference number) and fldB (project type).

Here's what I wrote (which I know is wrong)

SELECT Count(*) FROM tblA.fldB WHERE tblA.fldB = "x" AND tblA.fldA = Me.fldA

 

[MSAccessRookie]

The FROM statement required the name of a Table as its argument, and you have provided a column. You also want to return the Count f the Items.

Try Changing:

SELECT Count(*) FROM [COLOR=red][B]tblA.fldB[/B] [/COLOR]WHERE tblA.fldB = "x" AND tblA.fldA = Me.fldA

[/quote]

To:

SELECT Count(*) [COLOR=red][B]As[/B][/COLOR] [COLOR=red][B]CountOfItems[/B] [/COLOR]FROM [B][COLOR=red]tblA[/COLOR][/B] WHERE tblA.fldB = "x" AND tblA.fldA = Me.fldA

 

[boblarson]

Use a DCount:

=DCount("*", "tblA", "[fldB] = 'x' AND [fldA] = " & Me.fldA)

And if fldA is text:

=DCount("*", "tblA", "[fldB] = 'x' AND [fldA] = " & Chr(34) & Me.fldA & Chr(34))

or if fldA is a date

=DCount("*", "tblA", "[fldB] = 'x' AND [fldA] = #" & Me.fldA & "#")