Update Query Question Need Help

[TotallyConfused]

I have an update query that I use to update a table with data from another
table. However, my "Finish" table is a checkbox (0 and -1). When I run the
query it does not update. How can I get the Finish field to update? Thank
you in advance for any help will be greatly appreciated.

field: Finish
Table: Tracking Table
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])

field: Finish
Table: Tracking Table1
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too. Also any indexes.

 

[Jason Lepack]

NZ only works with Nulls, not 0's and -1's.
NZ looks at a value and if it's not null uses it, otherwise it uses
the other value. Since your value is never null it always uses that
one.

Try this instead of your nz statement:
T1 is the alias I used for [tbl tracking table1]
T is the alias I used for [tbl tracking table]
IIf([T1].[Finish]<[T].[Finish],[T1].[Finish],[T].[Finish])

Note:
I assumed that you always required the "checked" value over the "non-
checked". If that's not the case then change the '<' to '>'.

Cheers,
Jason

 

[TotallyConfused]

Could I use this same statement to update existing data? Or would I need
something else to update existing data. thank you.

NZ only works with Nulls, not 0's and -1's.
NZ looks at a value and if it's not null uses it, otherwise it uses
the other value. Since your value is never null it always uses that
one.

Try this instead of your nz statement:
T1 is the alias I used for [tbl tracking table1]
T is the alias I used for [tbl tracking table]
IIf([T1].[Finish]<[T].[Finish],[T1].[Finish],[T].[Finish])

Note:
I assumed that you always required the "checked" value over the "non-
checked". If that's not the case then change the '<' to '>'.

Cheers,
Jason

I have an update query that I use to update a table with data from another
table. However, my "Finish" table is a checkbox (0 and -1). When I run the
query it does not update. How can I get the Finish field to update? Thank
you in advance for any help will be greatly appreciated.

field: Finish
Table: Tracking Table
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])

field: Finish
Table: Tracking Table1
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])

 

[Jason Lepack]

Sure it will. Backup your database (which you should always do before
you try something new) and test it out to see if it works.

Place that in your "update to:" and it will do your update.

Cheers,
Jason Lepack

Could I use this same statement to update existing data? Or would I need
something else to update existing data. thank you.

NZ only works with Nulls, not 0's and -1's.
NZ looks at a value and if it's not null uses it, otherwise it uses
the other value. Since your value is never null it always uses that
one.

Try this instead of your nz statement:
T1 is the alias I used for [tbl tracking table1]
T is the alias I used for [tbl tracking table]
IIf([T1].[Finish]<[T].[Finish],[T1].[Finish],[T].[Finish])

Note:
I assumed that you always required the "checked" value over the "non-
checked". If that's not the case then change the '<' to '>'.

Cheers,
Jason

I have an update query that I use to update a table with data from another
table. However, my "Finish" table is a checkbox (0 and -1). When I run the
query it does not update. How can I get the Finish field to update? Thank
you in advance for any help will be greatly appreciated.
field: Finish
Table: Tracking Table
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])
field: Finish
Table: Tracking Table1
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])- Hide quoted text -

- Show quoted text -

 

[TotallyConfused]

Thank you

Sure it will. Backup your database (which you should always do before
you try something new) and test it out to see if it works.

Place that in your "update to:" and it will do your update.

Cheers,
Jason Lepack

Could I use this same statement to update existing data? Or would I need
something else to update existing data. thank you.

NZ only works with Nulls, not 0's and -1's.
NZ looks at a value and if it's not null uses it, otherwise it uses
the other value. Since your value is never null it always uses that
one.

Try this instead of your nz statement:
T1 is the alias I used for [tbl tracking table1]
T is the alias I used for [tbl tracking table]
IIf([T1].[Finish]<[T].[Finish],[T1].[Finish],[T].[Finish])

Note:
I assumed that you always required the "checked" value over the "non-
checked". If that's not the case then change the '<' to '>'.

On Feb 21, 8:12 pm, TotallyConfused
I have an update query that I use to update a table with data from another
table. However, my "Finish" table is a checkbox (0 and -1). When I run the
query it does not update. How can I get the Finish field to update? Thank
you in advance for any help will be greatly appreciated.

field: Finish
Table: Tracking Table
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])

field: Finish
Table: Tracking Table1
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])- Hide quoted text -

- Show quoted text -

 

[Jason Lepack]

You're very welcome.

Thank you

Sure it will. Backup your database (which you should always do before
you try something new) and test it out to see if it works.

Place that in your "update to:" and it will do your update.

Cheers,
Jason Lepack

Could I use this same statement to update existing data? Or would I need
something else to update existing data. thank you.
:
NZ only works with Nulls, not 0's and -1's.
NZ looks at a value and if it's not null uses it, otherwise it uses
the other value. Since your value is never null it always uses that
one.
Try this instead of your nz statement:
T1 is the alias I used for [tbl tracking table1]
T is the alias I used for [tbl tracking table]
IIf([T1].[Finish]<[T].[Finish],[T1].[Finish],[T].[Finish])
Note:
I assumed that you always required the "checked" value over the "non-
checked". If that's not the case then change the '<' to '>'.
Cheers,
Jason
On Feb 21, 8:12 pm, TotallyConfused
I have an update query that I use to update a table with data from another
table. However, my "Finish" table is a checkbox (0 and -1). When I run the
query it does not update. How can I get the Finish field to update? Thank
you in advance for any help will be greatly appreciated.
field: Finish
Table: Tracking Table
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])
field: Finish
Table: Tracking Table1
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -