johnnyg123
asked on
conditional update query
I have 2 ms access 2003 tables with the following sample data
table a
id Floater
1
2
3
4
table b
id Floater
1 N
3 Y
Note: there is only one entry for a given id on both tables
there may be id entries on table a that are not on table b
I am trying to write an update query that will update the value of floater on table a
if there is an matching id entry on table b I would like the value of floater on table a to be set to that on table b
If there is no matching id entry on table b I would like the floater value on table a to be set to N
using the example data I would like table a to have the following values after the update query is run
table a
id Floater
1 N
2 N
3 Y
4 N
table a
id Floater
1
2
3
4
table b
id Floater
1 N
3 Y
Note: there is only one entry for a given id on both tables
there may be id entries on table a that are not on table b
I am trying to write an update query that will update the value of floater on table a
if there is an matching id entry on table b I would like the value of floater on table a to be set to that on table b
If there is no matching id entry on table b I would like the floater value on table a to be set to N
using the example data I would like table a to have the following values after the update query is run
table a
id Floater
1 N
2 N
3 Y
4 N
update tableA set floater = nz(Dlookup("Floater", "TableB","Id = " & tableA.Id) , "N")
ASKER
I have the access query prompts on
when I run the query I get a message saying it is going to update 4 records but then when I click ok
I get an error message that says
Microsoft Office Access can't update all the records in the update query
Microsoft office access didn't update 4 field(s) due to a type conversion failure
when I run the query I get a message saying it is going to update 4 records but then when I click ok
I get an error message that says
Microsoft Office Access can't update all the records in the update query
Microsoft office access didn't update 4 field(s) due to a type conversion failure
ASKER
I have attached a sample database
test.mdb
test.mdb
You'll need the following, because the id fields are text:
UPDATE tablea
SET floater = Nz(DLookup("floater", "tableb", "id = """ & id & """"), "N")
UPDATE tablea
SET floater = Nz(DLookup("floater", "tableb", "id = """ & id & """"), "N")
@JezWalters is right
String variables need to be surrounded by quotes
But, gazillions of quotes drive me mad and are VERY hard to debug
Chr(34) is a quote mark
Works in VBA and the query editor
This works and is easier to digest
UPDATE tableA
SET tableA.floater = nz(DLookUp("Floater","Tabl eB","Id = " & Chr(34) & tableA.Id & Chr(34)),"N");
Chr(34) & tableA.Id & Chr(34) is your text field id surrounded by quotes
String variables need to be surrounded by quotes
But, gazillions of quotes drive me mad and are VERY hard to debug
Chr(34) is a quote mark
Works in VBA and the query editor
This works and is easier to digest
UPDATE tableA
SET tableA.floater = nz(DLookUp("Floater","Tabl
Chr(34) & tableA.Id & Chr(34) is your text field id surrounded by quotes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.