UPDDATE qry

[databasesrus]

ACCESS 97


I am trying to create an update query so that my form can show the most current balance of inventory. The query "runs" so to speak but there is no entry to the table.

Am I missing syntax???

UPDATE tblDataEntry INNER JOIN tblAssemblyStQty ON tblDataEntry.AssemblyID = tblAssemblyStQty.AssemblyID SET RunningTOH = StartingQty + AssemblyGood - AssemblyProcessed - AssemblyRejected - AssemblyDTR - AssemblyShipped;

Thank you for any help or suggestions!

 

[jrbarnett]

An update query will update the data in the underlying table. It will not in itself update the data shown on the form unless it is closed and reloaded.

You can force it to reload the data by putting

Me.Requery

in the code after your query is run.

John

 

[databasesrus]

Thank you. But why didnt the query update my table? Is there something wrong with my syntax or something?

 

[jrbarnett]

The main reason for not setting the result in the table is because one of the values used to calculate the result is Null. Thus the expression cannot be evaluated properly, and the result evaluates as Null.
A way around it is to replace fieldname with Nz(fieldname,0) which will substitute zero for any that don't have an explicit value.

There doesn't seem to be a syntax error as such, but I have to assume it is correct for the database structure as otherwise you would get mismatch errors when trying to run it.

John

 

[databasesrus]

I'm not sure what you mean. Which field name would you replace with NZ?

 

[Toga]

Unless you have an error handler on your fields, you'll probably have to utilize the nz function on each one as follows. Now if any of those fields is NULL, the nz function will convert it to a zero.

UPDATE tblDataEntry INNER JOIN tblAssemblyStQty ON tblDataEntry.AssemblyID = tblAssemblyStQty.AssemblyID SET RunningTOH = nz(StartingQty,0) + nz(AssemblyGood,0) - nz(AssemblyProcessed,0) - nz(AssemblyRejected,0) - nz(AssemblyDTR,0) - nz(AssemblyShipped,);

 

[Toga]

Sorry.....Forgot the last zero.

UPDATE tblDataEntry INNER JOIN tblAssemblyStQty ON tblDataEntry.AssemblyID = tblAssemblyStQty.AssemblyID SET RunningTOH = nz(StartingQty,0) + nz(AssemblyGood,0) - nz(AssemblyProcessed,0) - nz(AssemblyRejected,0) - nz(AssemblyDTR,0) - nz(AssemblyShipped,0);

Toga

 

[jrbarnett]

As Toga's post says, the fieldname refers to a field in your table.

Null is distinct in terms of zero or an empty string. It means that there is no data in the field.
It is rather like trying to do arithmetic with infinity as one of the numbers, so you can't produce an exact answer.

John

 

[databasesrus]

I copied and pasted the formula into the query. It "runs" but does not update the Running TOH field. I guess I am confused because the query is doing arithmetic but now with infinity and there is an exact answer. ????

 

[databasesrus]

I'm sorry. It did work!!! Thank you!