Regular Query Problem

[MaliciousMike]

Here's my query.

[COLOR="Red"]SELECT[/COLOR]
CheckFieldSheet.[Company Name] 

IIf([CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 1 *]) And IsNull([tbl_clients].[Phone Number 1 *]),1,0))) AS [COLOR="Blue"]Number1[/COLOR],

CheckFieldSheet.[Phone Number 1 *], tbl_clients.[phone number 1 *], 

IIf([CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 2 *]) And IsNull([tbl_clients].[Phone Number 2 *]),1,0))) AS [COLOR="Blue"]Number2[/COLOR], 

CheckFieldSheet.[Phone Number 2 *], tbl_clients.[phone number 2 *], 

IIf([CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 3 *]) And IsNull([tbl_clients].[Phone Number 3 *]),1,0))) AS [COLOR="Blue"]Number3[/COLOR], 

CheckFieldSheet.[Phone Number 3 *], tbl_clients.[phone number 3 *], 

IIf([Number1]=1 And [Number2]=1 And [Number3]=1,1,0) AS [COLOR="Blue"]Reject[/COLOR]

[COLOR="Red"]FROM[/COLOR]
tbl_clients 

[COLOR="Red"]INNER JOIN[/COLOR] 
CheckFieldSheet ON (tbl_clients.[company name] = CheckFieldSheet.[Company Name]) AND 
(tbl_clients.[Region Name] = CheckFieldSheet.RegionName) AND 
(tbl_clients.[Site Name] = CheckFieldSheet.[Site Name]) AND
(tbl_clients.[Plot Number/name] = CheckFieldSheet.[Plot Number/Name]) AND
(tbl_clients.[Site Code] = CheckFieldSheet.[Site Code])

[COLOR="Red"]WHERE[/COLOR]
(((CheckFieldSheet.Returns)="Wrong Number"));

Three expressions (Number1, Number2 and Number3), make up 'Reject'
If all three expressions are 1, then reject = 1.
Any other combinations will make reject = 0.

When i view the queery as it is up there, it works fine and shows everything absolutely perfectly.
When I want to view all records where 'Reject' = 0, it asks me what the expressions are. (see attachment)

I have no idea why this is happening.
Any light on why, and how to resolve it would be sweet!

Thanks.

 

[James Deckert]

It may have to do with a null value. You can't compare a null value to some other value. Maybe this is not allowing the calculation to work. I notice you are doing some comparisons prior to checking to see if the value is null.

I'd try to see if converting a null to -1 or some other value would work in your test (e.g. nz([CheckFieldSheet].[Phone Number 1 *],-1). This will convert the number to -1 in the calculation and there will be no need to check for null.

If this is feasible your line would look something like
IIf(nz([CheckFieldSheet].[Phone Number 1 *],-1)=nz([tbl_clients].[Phone Number 1 *],-1) . . .
You can change the default value which you convert a null to on each number so that you will get a "false" if both values are null.

James

 

[MaliciousMike]

I never understool nz, so i never used it.
Am i right in saying that it'll convert a null value, to a figure of the user's choice, which can be represented as null?

I did this:
Number1: IIf([CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(nz([CheckFieldSheet].[Phone Number 1 *],-1)=nz([tbl_clients].[Phone Number 1 *],-1),1,0)))

Not sure if i've done this right.

 

[pbaldy]

Actually I think you'll find that the problem stems from how queries are processed. As you've noted, it works without the criteria. Jet processes the WHERE clause before the SELECT clause, so at the point it's looking for Number1, it hasn't been "created", so it can't find it. The simplest solution would be to leave the original query alone and create second query based on the original, with the criteria in the second.

 

[MaliciousMike]

Jet sucks!

My objective is to replace the telephone numbers in tbl_clients with the numbers in CheckFieldSheet if there's a different number for the same record in CheckFieldSheet.

If i'm going about this the long way round, please let me know!!