syntax for count text field
in [Reports]
|
From: Revned on 21 Mar 2010 06:42 Hi, I have a report bound in qryVendor in this query I have a fieldname: Stat with expression type Stat:IIF([VDB]=[TlVDB])="Close","") I want to count number of close in my report I name it Total No of CLOSED PO: can anyone is kind to help how to figure out thanks in advance
From: Marshall Barton on 21 Mar 2010 10:10 Revned wrote: >I have a report bound in qryVendor >in this query I have a fieldname: Stat >with expression type > >Stat:IIF([VDB]=[TlVDB])="Close","") > >I want to count number of close in my report >I name it Total No of CLOSED PO: What are VDB and TIVDB? Yout syntax is never going to work. The parenthesis are unbalanced and the ([VDB]=[TlVDB]) part will either be True, False or Null, none of which can ever be equal to "Close". If you are trying to check if both VDB and TlVDB are equal to "Close", then you should use: IIf(VDB = "Close" And TlVDB = "Close", "", Null) But even that seems odd to me. I think it best if you explained what you are trying to do, instead of us guessing what the the goal is. -- Marsh MVP [MS Access]
From: Revned on 21 Mar 2010 23:59 Sorry Marshal barton if i make you confuse. but here it is in my qryVendor my TlVBD field I set Total to Count same with my VDB fied in my query in my Stat fieldname i set to Expression Stat:IIF([VDB]=[TlVDB],"Close","") I want to have exact syntax on how to SUM or COunt number of records that has "Close" status in my STAT Field.... Please need your help again thanks "Marshall Barton" wrote: > Revned wrote: > >I have a report bound in qryVendor > >in this query I have a fieldname: Stat > >with expression type > > > >Stat:IIF([VDB]=[TlVDB])="Close","") > > > >I want to count number of close in my report > >I name it Total No of CLOSED PO: > > > What are VDB and TIVDB? > > Yout syntax is never going to work. The parenthesis are > unbalanced and the ([VDB]=[TlVDB]) part will either be True, > False or Null, none of which can ever be equal to "Close". > > If you are trying to check if both VDB and TlVDB are equal > to "Close", then you should use: > IIf(VDB = "Close" And TlVDB = "Close", "", Null) > > But even that seems odd to me. I think it best if you > explained what you are trying to do, instead of us guessing > what the the goal is. > > -- > Marsh > MVP [MS Access] > . >
From: Duane Hookom on 22 Mar 2010 00:08 To count the number of records where the two fields/columns have the same value, use: StatCount: Sum(Abs([VDB]=[TlVDB])) Note: this only works in a totals query. I'm not sure if VDB and TIVDB are allready aggregates or what you actual SQL is for the query or your actual fields are. -- Duane Hookom MS Access MVP "Revned" <Revned(a)discussions.microsoft.com> wrote in message news:B8837F6F-769F-4F47-9902-B2BA9795A175(a)microsoft.com... > Sorry Marshal barton if i make you confuse. > but here it is > in my qryVendor > my TlVBD field I set Total to Count > same with my VDB fied in my query > > in my Stat fieldname > i set to Expression > > Stat:IIF([VDB]=[TlVDB],"Close","") > > I want to have exact syntax on how to SUM or COunt > number of records that has "Close" status > in my STAT Field.... > > Please need your help again > > thanks > > > "Marshall Barton" wrote: > >> Revned wrote: >> >I have a report bound in qryVendor >> >in this query I have a fieldname: Stat >> >with expression type >> > >> >Stat:IIF([VDB]=[TlVDB])="Close","") >> > >> >I want to count number of close in my report >> >I name it Total No of CLOSED PO: >> >> >> What are VDB and TIVDB? >> >> Yout syntax is never going to work. The parenthesis are >> unbalanced and the ([VDB]=[TlVDB]) part will either be True, >> False or Null, none of which can ever be equal to "Close". >> >> If you are trying to check if both VDB and TlVDB are equal >> to "Close", then you should use: >> IIf(VDB = "Close" And TlVDB = "Close", "", Null) >> >> But even that seems odd to me. I think it best if you >> explained what you are trying to do, instead of us guessing >> what the the goal is. >> >> -- >> Marsh >> MVP [MS Access] >> . >>
From: Revned on 23 Mar 2010 06:35 Thank you I do appreciate your help thank you very much "Duane Hookom" wrote: > To count the number of records where the two fields/columns have the same > value, use: > StatCount: Sum(Abs([VDB]=[TlVDB])) > > Note: this only works in a totals query. I'm not sure if VDB and TIVDB are > allready aggregates or what you actual SQL is for the query or your actual > fields are. > > -- > Duane Hookom > MS Access MVP > > > "Revned" <Revned(a)discussions.microsoft.com> wrote in message > news:B8837F6F-769F-4F47-9902-B2BA9795A175(a)microsoft.com... > > Sorry Marshal barton if i make you confuse. > > but here it is > > in my qryVendor > > my TlVBD field I set Total to Count > > same with my VDB fied in my query > > > > in my Stat fieldname > > i set to Expression > > > > Stat:IIF([VDB]=[TlVDB],"Close","") > > > > I want to have exact syntax on how to SUM or COunt > > number of records that has "Close" status > > in my STAT Field.... > > > > Please need your help again > > > > thanks > > > > > > "Marshall Barton" wrote: > > > >> Revned wrote: > >> >I have a report bound in qryVendor > >> >in this query I have a fieldname: Stat > >> >with expression type > >> > > >> >Stat:IIF([VDB]=[TlVDB])="Close","") > >> > > >> >I want to count number of close in my report > >> >I name it Total No of CLOSED PO: > >> > >> > >> What are VDB and TIVDB? > >> > >> Yout syntax is never going to work. The parenthesis are > >> unbalanced and the ([VDB]=[TlVDB]) part will either be True, > >> False or Null, none of which can ever be equal to "Close". > >> > >> If you are trying to check if both VDB and TlVDB are equal > >> to "Close", then you should use: > >> IIf(VDB = "Close" And TlVDB = "Close", "", Null) > >> > >> But even that seems odd to me. I think it best if you > >> explained what you are trying to do, instead of us guessing > >> what the the goal is. > >> > >> -- > >> Marsh > >> MVP [MS Access] > >> . > >>
|
Pages: 1 Prev: #Name? - Access 2007-Windows 7 Next: Using Code instead of Criteria |