The search key was not found in any record

[Guest]

I'm trying to run a make-table query which takes data from one table and
creates a new table with an additional field to calculate dollar amounts.
the formula which calculates the dollar amount, contains a nested if
statement to decide what percentage to use.

When I try to run the query, I get the "The search key was not found in any
record" error message.

I did not create this query myself, but I have used it many times an never
had a problem before now.

Can anybody tell me how to fix it?

 

[Douglas J. Steele]

I think you'll find most of us will need to see the SQL of the query in
order to be able to help you.

 

[Guest]

Here's the SQL (I simplified it a little) --

SELECT MASTER.CCOMPANY AS [COMP], MASTER.CBUBLOC AS BR, MASTER.CSHIP_NUM AS
SHIPTO, dbo_ACMLIST.CUST_NAME AS CUST_NAME, MASTER.CPART_NUM AS [FPN#],
MASTER.NQTY AS [QTY SHIP], MASTER.NRESALE AS RESALE,
[NQTY]*[NRESALE]*[NBTEXTOUS]*IIf([cuom]="m",0.001,IIf([cuom]="C",0.01,IIf([cuom]="E",1,0))) AS [dollar Amounts] INTO [Extract]
FROM MASTER LEFT JOIN dbo_ACMLIST ON (MASTER.CSHIP_NUM =
dbo_ACMLIST.CUST_NUM) AND (MASTER.CCOMPANY = dbo_ACMLIST.COMPANY);

 

[Gary Walter]

PMFBI

Just to be clear, you get the same error message
with this "simplified" query?

It looks to me like you have a heterogeneous join
between an Access table (MASTER)
and a linked table (dbo_ACMLIST)

the first thing I might do is delete the link to
dbo_ACMLIST and recreate it.

then verify dbo fields were not changed somehow....
(by sight, or try following simple query)

SELECT
MASTER.CSHIP_NUM,
MASTER.CCOMPANY,
dbo_ACMLIST.CUST_NUM,
dbo_ACMLIST.COMPANY,
dbo_ACMLIST.CUST_NAME,
[NBTEXTOUS],
[cuom]
FROM
MASTER
LEFT JOIN
dbo_ACMLIST
ON
(MASTER.CSHIP_NUM = dbo_ACMLIST.CUST_NUM)
AND
(MASTER.CCOMPANY = dbo_ACMLIST.COMPANY);

if above errors out with same message,
then we know it wasn't the IIF
but points to only "key" I can imagine are the fields
in the ON clause...

if it produces other errors, then go from there...

if it doesn't error out, try original query...

Here's the SQL (I simplified it a little) --

SELECT MASTER.CCOMPANY AS [COMP], MASTER.CBUBLOC AS BR, MASTER.CSHIP_NUM
AS
SHIPTO, dbo_ACMLIST.CUST_NAME AS CUST_NAME, MASTER.CPART_NUM AS [FPN#],
MASTER.NQTY AS [QTY SHIP], MASTER.NRESALE AS RESALE,
[NQTY]*[NRESALE]*[NBTEXTOUS]*IIf([cuom]="m",0.001,IIf([cuom]="C",0.01,IIf([cuom]="E",1,0)))
AS [dollar Amounts] INTO [Extract]
FROM MASTER LEFT JOIN dbo_ACMLIST ON (MASTER.CSHIP_NUM =
dbo_ACMLIST.CUST_NUM) AND (MASTER.CCOMPANY = dbo_ACMLIST.COMPANY);

I think you'll find most of us will need to see the SQL of the query in
order to be able to help you.

 

[Guest]

In regards to the "simplified" query, I have not run that exact query. I
just took some of the fields out for the purpose of this post because there
were about sixty of them and they all look alike. The only different one was
the one doing the calculation.

As for the join, the MASTER table has most of the data including the
customer number, but the ACMLIST has both the customer number and the
customer name. The final output needs to include both.

Further investigation has turned up the fact that this database was
converted from 97 to 2002. I ran the query on the box where it was created
in 97 format and it worked. So I'm thinking that something happened in the
conversion that was dormant until the moment I urgently needed something!

Looks like this girl has some rebuilding to do... sigh...

Thanks for the input.

PMFBI

Just to be clear, you get the same error message
with this "simplified" query?

It looks to me like you have a heterogeneous join
between an Access table (MASTER)
and a linked table (dbo_ACMLIST)

the first thing I might do is delete the link to
dbo_ACMLIST and recreate it.

then verify dbo fields were not changed somehow....
(by sight, or try following simple query)

SELECT
MASTER.CSHIP_NUM,
MASTER.CCOMPANY,
dbo_ACMLIST.CUST_NUM,
dbo_ACMLIST.COMPANY,
dbo_ACMLIST.CUST_NAME,
[NBTEXTOUS],
[cuom]
FROM
MASTER
LEFT JOIN
dbo_ACMLIST
ON
(MASTER.CSHIP_NUM = dbo_ACMLIST.CUST_NUM)
AND
(MASTER.CCOMPANY = dbo_ACMLIST.COMPANY);

if above errors out with same message,
then we know it wasn't the IIF
but points to only "key" I can imagine are the fields
in the ON clause...

if it produces other errors, then go from there...

if it doesn't error out, try original query...

Here's the SQL (I simplified it a little) --

SELECT MASTER.CCOMPANY AS [COMP], MASTER.CBUBLOC AS BR, MASTER.CSHIP_NUM
AS
SHIPTO, dbo_ACMLIST.CUST_NAME AS CUST_NAME, MASTER.CPART_NUM AS [FPN#],
MASTER.NQTY AS [QTY SHIP], MASTER.NRESALE AS RESALE,
[NQTY]*[NRESALE]*[NBTEXTOUS]*IIf([cuom]="m",0.001,IIf([cuom]="C",0.01,IIf([cuom]="E",1,0)))
AS [dollar Amounts] INTO [Extract]
FROM MASTER LEFT JOIN dbo_ACMLIST ON (MASTER.CSHIP_NUM =
dbo_ACMLIST.CUST_NUM) AND (MASTER.CCOMPANY = dbo_ACMLIST.COMPANY);

I think you'll find most of us will need to see the SQL of the query in
order to be able to help you.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm trying to run a make-table query which takes data from one table
and
creates a new table with an additional field to calculate dollar
amounts.
the formula which calculates the dollar amount, contains a nested if
statement to decide what percentage to use.

When I try to run the query, I get the "The search key was not found in
any
record" error message.

I did not create this query myself, but I have used it many times an
never
had a problem before now.

Can anybody tell me how to fix it?