Simple wildcard parameter on report control

[PeteyP]

I have searched the database but cannot find a solution to this problem. I
have written a text box control on an access report as follows:

=IIf([CaseNo] Like "A-*",
DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR Prelim'"),"None")

Some of our cases begin with *A-* and others with *C-*. This expression
results in all the 'AR Prelim' records are counted regardless of what they
begin with.

 

[PeteyP]

I've also tried:

=IIf("[CaseNo]
='A-*'",DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR
Prelim'"),"None")

 

[Dirk Goldgar]

I have searched the database but cannot find a solution to this problem. I
have written a text box control on an access report as follows:

=IIf([CaseNo] Like "A-*",
DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR
Prelim'"),"None")

Some of our cases begin with *A-* and others with *C-*. This expression
results in all the 'AR Prelim' records are counted regardless of what they
begin with.

Are you trying to find all the 'AR Prelim' records for the current [CaseNo],
but only if that [CaseNo] begins with "A-"? If so, you need to include the
[CaseNo] as a criterion in your DCount expression; for example,

=IIf([CaseNo] Like "A-*",
DCount("*","QuarterlyCompilationStart",
"[CaseNo]='" & [CaseNo] & "' AND [Determ]='AR Prelim'"),
"None")

I broke that onto multiple lines for clearer posting, but it really would
all be on one line in the text box's ControlSource proeprty.

On the other hand, if you just want a count of all 'AR Prelim' records that
begin with "A-", you could use a controlsource expression like this:

=DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")

or maybe this:

=IIf(DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")

0,

DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'"),
"None")

 

[PeteyP]

Thanks, Dirk, but I'm only trying to count those where [Determ] are "AR
Prelim" AND where [CaseNo] begins with "A". So I modified your effort to
read:

=IIf([CaseNo] Like "A*",
DCount("*","QuarterlyCompilationStart","[Determ]='AR
Prelim'"),"None")

This counts all the AR Prelim records regardless whether they start with A
or C.

When I change the expression to select records starting with C, it returns
None!

Thanks again.

I have searched the database but cannot find a solution to this problem. I
have written a text box control on an access report as follows:

=IIf([CaseNo] Like "A-*",
DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR
Prelim'"),"None")

Some of our cases begin with *A-* and others with *C-*. This expression
results in all the 'AR Prelim' records are counted regardless of what they
begin with.

Are you trying to find all the 'AR Prelim' records for the current [CaseNo],
but only if that [CaseNo] begins with "A-"? If so, you need to include the
[CaseNo] as a criterion in your DCount expression; for example,

=IIf([CaseNo] Like "A-*",
DCount("*","QuarterlyCompilationStart",
"[CaseNo]='" & [CaseNo] & "' AND [Determ]='AR Prelim'"),
"None")

I broke that onto multiple lines for clearer posting, but it really would
all be on one line in the text box's ControlSource proeprty.

On the other hand, if you just want a count of all 'AR Prelim' records that
begin with "A-", you could use a controlsource expression like this:

=DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")

or maybe this:

=IIf(DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")

0,

DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'"),
"None")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[PeteyP]

Some sample informaiton may help

Field [CaseNo] always starts with 'A' or 'C'
Sample data: A-533-833
Sample data: C-570-326

Field [Determ]
Sample data: AR Prelim

I want to do a DCount on a table where [Determ]='AR Prelim' AND [CaseNo]='A-*'

Thanks, Dirk, but I'm only trying to count those where [Determ] are "AR
Prelim" AND where [CaseNo] begins with "A". So I modified your effort to
read:

=IIf([CaseNo] Like "A*",
DCount("*","QuarterlyCompilationStart","[Determ]='AR
Prelim'"),"None")

This counts all the AR Prelim records regardless whether they start with A
or C.

When I change the expression to select records starting with C, it returns
None!

Thanks again.

I have searched the database but cannot find a solution to this problem. I
have written a text box control on an access report as follows:

=IIf([CaseNo] Like "A-*",
DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR
Prelim'"),"None")

Some of our cases begin with *A-* and others with *C-*. This expression
results in all the 'AR Prelim' records are counted regardless of what they
begin with.

Are you trying to find all the 'AR Prelim' records for the current [CaseNo],
but only if that [CaseNo] begins with "A-"? If so, you need to include the
[CaseNo] as a criterion in your DCount expression; for example,

=IIf([CaseNo] Like "A-*",
DCount("*","QuarterlyCompilationStart",
"[CaseNo]='" & [CaseNo] & "' AND [Determ]='AR Prelim'"),
"None")

I broke that onto multiple lines for clearer posting, but it really would
all be on one line in the text box's ControlSource proeprty.

On the other hand, if you just want a count of all 'AR Prelim' records that
begin with "A-", you could use a controlsource expression like this:

=DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")

or maybe this:

=IIf(DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")

0,

DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'"),
"None")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[PeteyP]

I got it from studying your reply, Dirk. Thanks again. Sorry for all the
posts!

Some sample informaiton may help

Field [CaseNo] always starts with 'A' or 'C'
Sample data: A-533-833
Sample data: C-570-326

Field [Determ]
Sample data: AR Prelim

I want to do a DCount on a table where [Determ]='AR Prelim' AND [CaseNo]='A-*'

Thanks, Dirk, but I'm only trying to count those where [Determ] are "AR
Prelim" AND where [CaseNo] begins with "A". So I modified your effort to
read:

=IIf([CaseNo] Like "A*",
DCount("*","QuarterlyCompilationStart","[Determ]='AR
Prelim'"),"None")

This counts all the AR Prelim records regardless whether they start with A
or C.

When I change the expression to select records starting with C, it returns
None!

Thanks again.

I have searched the database but cannot find a solution to this problem. I
have written a text box control on an access report as follows:

=IIf([CaseNo] Like "A-*",
DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR
Prelim'"),"None")

Some of our cases begin with *A-* and others with *C-*. This expression
results in all the 'AR Prelim' records are counted regardless of what they
begin with.


Are you trying to find all the 'AR Prelim' records for the current [CaseNo],
but only if that [CaseNo] begins with "A-"? If so, you need to include the
[CaseNo] as a criterion in your DCount expression; for example,

=IIf([CaseNo] Like "A-*",
DCount("*","QuarterlyCompilationStart",
"[CaseNo]='" & [CaseNo] & "' AND [Determ]='AR Prelim'"),
"None")

I broke that onto multiple lines for clearer posting, but it really would
all be on one line in the text box's ControlSource proeprty.

On the other hand, if you just want a count of all 'AR Prelim' records that
begin with "A-", you could use a controlsource expression like this:

=DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")

or maybe this:

=IIf(DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")
0,
DCount("*","QuarterlyCompilationStart",
"[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'"),
"None")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)