Populate entire field in query with the value of a cell

[Ben8765]

Hi,

I would like to populate an entire field in a query with the value of a cell
(from another field).

Table1:

FieldA
1
2
3
4

I would like to populate Expr1 in a query with the cell that equals 4.
It should look like this:

FieldA Expr1
1 4
2 4
3 4
4 4

I've tried to do this with this line of sql:
Expr1: IIf([FieldA]=4,[FieldA],[FieldA])

But instead, it returns each value of FieldA (1,2,3,4) and not all 4's:

FieldA Expr1
1 1 [should be 4]
2 2 [should be 4]
3 3 [should be 4]
4 4

How can i do this?

Thanks,
-Ben

 

[S.Clark]

If you simply want to shove the static value of 4, then just put this in a
new column of the query grid.

Expr1: 4

 

[KARL DEWEY]

Your problem is the FieldA does not = 4 except in the last record --
FieldA Expr1
1 1 [False - it returns the contents of FieldA which is 1]
2 2 [False - it returns the contents of FieldA which is 2]
3 3 [False - it returns the contents of FieldA which is 3]
4 4 [True - it returns the contents of FieldA which is 4]

It is doing exactly what you asked it to do.

 

[Ben8765]

Hi,

Thanks for your response. I should have put it differently.

I do not want just a static value in Expr1.

I would like a query (based on a table) to do this:

Where the value of FieldA = D, get the value of FieldB of that record, and
populate Expr1 (the entire field) with that value.

FieldA FieldB Expr1
A 1 4
B 2 4
C 3 4
D 4 4

Thanks,

-Ben

If you simply want to shove the static value of 4, then just put this in a
new column of the query grid.

Expr1: 4

Hi,

I would like to populate an entire field in a query with the value of a cell
(from another field).

Table1:

FieldA
1
2
3
4

I would like to populate Expr1 in a query with the cell that equals 4.
It should look like this:

FieldA Expr1
1 4
2 4
3 4
4 4

I've tried to do this with this line of sql:
Expr1: IIf([FieldA]=4,[FieldA],[FieldA])

But instead, it returns each value of FieldA (1,2,3,4) and not all 4's:

FieldA Expr1
1 1 [should be 4]
2 2 [should be 4]
3 3 [should be 4]
4 4

How can i do this?

Thanks,
-Ben

 

[Ben8765]

Yes, thanks.

I know that my line of sql is doing what it is supposed to do. I just don't
know how to get it to do what i want to do.

I would like a query (based on a table) to do this:

Where the value of FieldA = D, get the value of FieldB of that record, and
populate Expr1 (the entire field) with that value.

FieldA __ FieldB ___ Expr1
__A _______1 ______4
__B_______ 2______ 4
__C_______3______ 4
__D_______ 4______ 4

Can i store the value of FieldB, where FieldA = D, in a variable, and then
make Expr1 = that variable?

Thanks,

-Ben

Your problem is the FieldA does not = 4 except in the last record --
FieldA Expr1
1 1 [False - it returns the contents of FieldA which is 1]
2 2 [False - it returns the contents of FieldA which is 2]
3 3 [False - it returns the contents of FieldA which is 3]
4 4 [True - it returns the contents of FieldA which is 4]

It is doing exactly what you asked it to do.

--
Build a little, test a little.

Hi,

I would like to populate an entire field in a query with the value of a cell
(from another field).

Table1:

FieldA
1
2
3
4

I would like to populate Expr1 in a query with the cell that equals 4.
It should look like this:

FieldA Expr1
1 4
2 4
3 4
4 4

I've tried to do this with this line of sql:
Expr1: IIf([FieldA]=4,[FieldA],[FieldA])

But instead, it returns each value of FieldA (1,2,3,4) and not all 4's:

FieldA Expr1
1 1 [should be 4]
2 2 [should be 4]
3 3 [should be 4]
4 4

How can i do this?

Thanks,
-Ben

 

[S.Clark]

Expr1: Dlookup("Tablename", "FieldB", "FieldA = 'D'")

It will be incredible slow, but it will work.

Hi,

Thanks for your response. I should have put it differently.

I do not want just a static value in Expr1.

I would like a query (based on a table) to do this:

Where the value of FieldA = D, get the value of FieldB of that record, and
populate Expr1 (the entire field) with that value.

FieldA FieldB Expr1
A 1 4
B 2 4
C 3 4
D 4 4

Thanks,

-Ben

If you simply want to shove the static value of 4, then just put this in a
new column of the query grid.

Expr1: 4

Hi,

I would like to populate an entire field in a query with the value of a cell
(from another field).

Table1:

FieldA
1
2
3
4

I would like to populate Expr1 in a query with the cell that equals 4.
It should look like this:

FieldA Expr1
1 4
2 4
3 4
4 4

I've tried to do this with this line of sql:
Expr1: IIf([FieldA]=4,[FieldA],[FieldA])

But instead, it returns each value of FieldA (1,2,3,4) and not all 4's:

FieldA Expr1
1 1 [should be 4]
2 2 [should be 4]
3 3 [should be 4]
4 4

How can i do this?

Thanks,
-Ben

 

[KARL DEWEY]

Use a subquery --
UPDATE YourTable SET YourTable.Expr1 = (SELECT [XX].FieldB FROM YourTable AS
[XX] WHERE [XX].FieldA="D") AS Temp;

--
Build a little, test a little.

Yes, thanks.

I know that my line of sql is doing what it is supposed to do. I just don't
know how to get it to do what i want to do.

I would like a query (based on a table) to do this:

Where the value of FieldA = D, get the value of FieldB of that record, and
populate Expr1 (the entire field) with that value.

FieldA __ FieldB ___ Expr1
__A _______1 ______4
__B_______ 2______ 4
__C_______3______ 4
__D_______ 4______ 4

Can i store the value of FieldB, where FieldA = D, in a variable, and then
make Expr1 = that variable?

Thanks,

-Ben

Your problem is the FieldA does not = 4 except in the last record --
FieldA Expr1
1 1 [False - it returns the contents of FieldA which is 1]
2 2 [False - it returns the contents of FieldA which is 2]
3 3 [False - it returns the contents of FieldA which is 3]
4 4 [True - it returns the contents of FieldA which is 4]

It is doing exactly what you asked it to do.

--
Build a little, test a little.

Hi,

I would like to populate an entire field in a query with the value of a cell
(from another field).

Table1:

FieldA
1
2
3
4

I would like to populate Expr1 in a query with the cell that equals 4.
It should look like this:

FieldA Expr1
1 4
2 4
3 4
4 4

I've tried to do this with this line of sql:
Expr1: IIf([FieldA]=4,[FieldA],[FieldA])

But instead, it returns each value of FieldA (1,2,3,4) and not all 4's:

FieldA Expr1
1 1 [should be 4]
2 2 [should be 4]
3 3 [should be 4]
4 4

How can i do this?

Thanks,
-Ben