Multiple dcount definitions

[ciskid]

Hi Folks

I have a text box which shows the following

=DCount("[Ref number]","main","[open or clsoed] = 'open'")

This looks at a table with a primary Key called Ref Number in a table called main with a field called open or clsoed and looks for the value open only.

I need to specify another fileld, called engineer wheer I match the username =jimmy

Im struggling to add this extra field

So it wiill look , for Open and an engineer called Jimmy in a table called main that has a primary key set !

Can anyone give me some pointers on this simple question ?

Br

Jimmy

 

[RV]

Try

=DCount("[Ref number]","main","[open or clsoed] = 'open' AND username = jimmy'")

RV

 

[ciskid]

Expression not functioning

Hi Rv

I have tried the code below and it doesnt work for me, I get and error in my text box ?

=DCount("[Ref number]","test","[team] = 'rangers' AND name = jimmy'")

I have attached an example, check out the form. (AC97 as its all I have at work)

Appreciate any advice

Cheers

Jimmy

 

[RV]

Sorry, made a typo.
Code should read

=DCount("[Ref number]","test","[team] = 'rangers' AND name = 'jimmy'")

Rename your column called name.
Name is a reserved word in Access abd should not be used for object names.
Also, avoid using spaces in object names.

RV

 

[ciskid]

Thanks M8

Hi RV

Many thanks for the reply.

All sorted now

cheers

Jimmy

 

[ciskid]

More help needed

Hi RV

Another question ?

=DCount("[Ref number]","main","[casestatus] = 'open' AND monthopen like '**/07/**'")

I can make a query with has the criteria set too **/07/** and also where the status is open

I can then do a DCOUNT on this query whcih would give me a count of all the open records for july.

But this would mean 12 querys per year, and so i believe the answer is an expression, which means I need no queries

But Im having trouble again with the syntax

The above doesnt work, have you any ideas how to express a like so I can pick out specific months

Many thanks

Jimmy

 

[ciskid]

ok all sorted

I took ur advice and changed some of my fileds to no spaces and my expressions worked ok

But thanks again

 

[ciskid]

Anotehr question on querie expressions ?

Hi RV

I need some more advice

=DCount("[Ref number]","main","[casestatus] = 'open'")

The above does a dcount on the table main to find all open cases

Imagine I have a query called test which is based on all records of the table main.

I would like to specify this query in the expression above Like so

=DCount("[Ref number]","test","[casestatus] = 'open'")


But it doesnt work ?

Can you help/advise me on how to write this dcount on a query instead of a table ?

Cheers

Jimmy