Variance Query

[Steven R via AccessMonster.com]

I have a query which compares the "NOTES" field in the ContractsNew table vs.
the same field in the ContractsOld table - my syntax is simply:

<>ContractsOld.Notes

in the criteria field under ContractsNew.Notes - so simple, yet it doesn't
work - I have used this before - any troubleshooting ideas ?

Thanks !


my e-mail is
(e-mail address removed)

 

[John Spencer (MVP)]

It doesn't work? As in you geÝ an error, you get no results, you get the wrong
results, the computer crashes.

Do you have the ContractsOld table in the query?

Post your SQL statement and perhaps someone can pick out the problem.

Open query in Design view
Select View: SQL
Copy the text and paste it into the body of the message.

 

[Steven R via AccessMonster.com]

SELECT ContractsNew.VendorName, ContractsNew.VendorNum, ContractsNew.RFQnum,
ContractsNew.RFQrecd, ContractsNew.RFQdate, ContractsNew.StartDate,
ContractsNew.EndDate, ContractsNew.ContractNum, ContractsNew.Value,
ContractsNew.Type, ContractsNew.OldAgNum, ContractsNew.Description,
ContractsNew.Notes
FROM ContractsNew INNER JOIN ContractsOld ON ContractsNew.VendorNum =
ContractsOld.VendorNum
WHERE (((ContractsNew.Notes)<>[ContractsOld].[notes]));


I go into contractsNew, type text in the Notes field in one of the records,
it should give me something - the tables are exact duplicates of one another -
all the properties are exactly the same for both "Old" and "New"

Thanks for your help

It doesn't work? As in you geÝ an error, you get no results, you get the wrong
results, the computer crashes.

Do you have the ContractsOld table in the query?

Post your SQL statement and perhaps someone can pick out the problem.

Open query in Design view
Select View: SQL
Copy the text and paste it into the body of the message.

I have a query which compares the "NOTES" field in the ContractsNew table vs.
the same field in the ContractsOld table - my syntax is simply:

[quoted text clipped - 8 lines]

my e-mail is
(e-mail address removed)

 

[Steven R via AccessMonster.com]

John,
I forgot to answer one of your questions - I get no results in my recordset -
sorry about that.... Steve

It doesn't work? As in you geÝ an error, you get no results, you get the wrong
results, the computer crashes.

Do you have the ContractsOld table in the query?

Post your SQL statement and perhaps someone can pick out the problem.

Open query in Design view
Select View: SQL
Copy the text and paste it into the body of the message.

I have a query which compares the "NOTES" field in the ContractsNew table vs.
the same field in the ContractsOld table - my syntax is simply:

[quoted text clipped - 8 lines]

my e-mail is
(e-mail address removed)

 

[John Spencer (MVP)]

It looks as if your query should work EXCEPT in those cases where one or both of
the notes fields are null (or blank).

First test, drop the where clause. Do you get records back? If so, we know the
problem is in the where clause; if not the problem is in the join. Assuming it
is the where clause,

Next try changing the where to
WHERE ContractsNew.Notes<>[ContractsOld].[notes]
OR (ContractsNew.Notes Is Null and ContractsOld.Notes is Not Null)
OR (ContractsNew.Notes Is Not Null and ContractsOld.Notes is Null)

You could use
WHERE ContractsNew.Notes & "" <>[ContractsOld].[notes] & ""

That will work although it may be slower than the first option.

SELECT ContractsNew.VendorName, ContractsNew.VendorNum, ContractsNew.RFQnum,
ContractsNew.RFQrecd, ContractsNew.RFQdate, ContractsNew.StartDate,
ContractsNew.EndDate, ContractsNew.ContractNum, ContractsNew.Value,
ContractsNew.Type, ContractsNew.OldAgNum, ContractsNew.Description,
ContractsNew.Notes
FROM ContractsNew INNER JOIN ContractsOld ON ContractsNew.VendorNum =
ContractsOld.VendorNum
WHERE (((ContractsNew.Notes)<>[ContractsOld].[notes]));

I go into contractsNew, type text in the Notes field in one of the records,
it should give me something - the tables are exact duplicates of one another -
all the properties are exactly the same for both "Old" and "New"

Thanks for your help

It doesn't work? As in you geÝ an error, you get no results, you get the wrong
results, the computer crashes.

Do you have the ContractsOld table in the query?

Post your SQL statement and perhaps someone can pick out the problem.

Open query in Design view
Select View: SQL
Copy the text and paste it into the body of the message.

I have a query which compares the "NOTES" field in the ContractsNew table vs.
the same field in the ContractsOld table - my syntax is simply:

[quoted text clipped - 8 lines]

my e-mail is
(e-mail address removed)