calculation returns -1! 1

[englishnewbie]

dear all,

more calculations that are baffling me.

i'm still playing with a query that will produce a number based upon text AND numerical fields.

the text fields are confusing me.

i have three variables, F, N and E. the expression i'm trying to build should return numerical value of 200 for F, 100 for E, and 0 for N.

i've tried "Expr1: 100 And [company]![NEW/EXIST/FU]="E"

but it returns -1, instead of 100.

why??

thanks, neil

 

[Golom]

SQL is interpreting your expression as a Boolean expression because of the "AND" operator. The sequence of processing is that it construes "100" to be a Boolean value because

[company]![NEW/EXIST/FU]="E"

is a Boolean (i.e. returns TRUE or FALSE)

In the Boolean world, "0" is False and NOT "0" is true, so this boils down to
* 100 is TRUE
* "[company]![NEW/EXIST/FU]="E"" is also TRUE,
* "TRUE AND TRUE" is TRUE and that has a numeric value of -1.

You probably want the "[company]![NEW/EXIST/FU]="E"" as part of a WHERE clause rather than as the value of a field in the SELECT.

 

[englishnewbie]

aha, thanks.

so what's a simple way of going about this?

i just want basically to say if "e" then the value is 100, if "f" then the value is 200.

thanks, neil

 

[Golom]

Probably something like

Select IIF([company]![NEW/EXIST/FU]='E',100,200) As [fldValue]), ...

 

[englishnewbie]

thanks!

as you can imagine, i've moved onto the next problem: don't know if it relates so much to queries, (well it does in that i need to analyse two tables) so here goes:


i have two tables: company and people.

The link is the company field, which is text, a company name.

I've put these two tables together separately, importing the data.

I want to create a relationship between company field in one and company field in the other.
But, having done so, there seems to be no link.

I REALLY don't want to have to do these 500 entries manually, so what's wrong? 300 or so should match.....

thanks, neil

 

[Golom]

Answered this on the other thread