Update Query Question Need Help

G

Guest

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])
 
G

Guest

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.
 
J

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
 
G

Guest

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

Jason Lepack said:
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])
Click to expand...
Click to expand...
 
J

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.



Jason Lepack said:
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.
Click to expand...
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])
Click to expand...
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
Click to expand...
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
G

Guest

Thank you

Jason Lepack said:
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.



Jason Lepack said:
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.
Click to expand...
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])
Click to expand...
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.
Click to expand...
field: Finish
Table: Tracking Table
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])
Click to expand...
field: Finish
Table: Tracking Table1
Update to: NZ([tbl tracking table].[Finish],[tbl tracking table1].[Finish])- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
J

Jason Lepack

You're very welcome.

Thank you



Jason Lepack said:
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.
Click to expand...
Place that in your "update to:" and it will do your update.
Click to expand...
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type Mismatch in Expression 4
DSum update query with multiple fields 0
Update Query Help *urgent* 0
Crosstab query as data source for update query 7
Update query changes when saved 6
Help with Update Query 2
Use DATE in query expression 1
Add a field in a query. Easy. . .so I though. 5

Top