Calculated Field in a Query

[Guest]

Hi,

This is the deal.

I have a field called Client Number (which is the primary key and is text
format). Depending on whether the record is a quote or a policy, the number
inputted is QN5001, going up by one for each record e.g. QN5002, QN5003 etc
for a Quote and 969 - 00001, 969 - 00002, 969 - 00003 etc if the record is a
policy. What I'm trying to do is calculate the number of QN records (i.e
quotes not converted to policies) in the database like this:-

Quotesntu: DCount("[Client Number]" , "Client Details", "[Client Number] =
'QN'")

The expression doesn't bring up an error but the calculated field just shows
all the 969 numbers as well and the result is returned as 0 whereas I would
like it to show 1 and then if someone could tell me how I could sum the
totals of this I'd be grateful as well.

I hope this makes sense?!

Many thanks in advance

Gillian

 

[Tom Lake]

I have a field called Client Number (which is the primary key and is text

format). Depending on whether the record is a quote or a policy, the
number
inputted is QN5001, going up by one for each record e.g. QN5002, QN5003
etc
for a Quote and 969 - 00001, 969 - 00002, 969 - 00003 etc if the record is
a
policy. What I'm trying to do is calculate the number of QN records (i.e
quotes not converted to policies) in the database like this:-

Quotesntu: DCount("[Client Number]" , "Client Details", "[Client Number] =
'QN'")

The expression doesn't bring up an error but the calculated field just
shows
all the 969 numbers as well and the result is returned as 0 whereas I
would
like it to show 1 and then if someone could tell me how I could sum the
totals of this I'd be grateful as well.

You could try this:

Quotesntu: DCount("[Client Number]" , "Client Details", "Left([Client
Number], 2) = 'QN'")

Tom Lake