Update Query

If your supplier value will always be blank in this circumstance (as well as the other fields), then you could update only if the supplier value is blank.

If you need to update your entire table with only non-blank records

UPDATE YourTable
SELECT *
FROM YourTable1
WHERE LTRIM(t.Supplier) = ''

Select allOpen in new window

Or if you need to just update the value of 1 field

UPDATE YourTable
SET x = x -- fill in with appropriate updated value
FROM YourTable t
WHERE LTRIM(t.Supplier) = ''

Select allOpen in new window

Or if you need to make sure all fields are blank, then you can add additional criteria to the WHERE clause.

UPDATE YourTable
SET <ColumnName> = x -- fill in with appropriate updated value
FROM YourTable t
WHERE LTRIM(t.Supplier) = ''
  AND LTRIM(t.Type) = ''
  AND LTRIM(t.Material) = ''
  AND LTRIM(t.Finish) = ''
  AND LTRIM(t.Width) = ''
  AND LTRIM(t.Thick) = ''
-- and so on until you have included all fields that would be blank in this circumstance

Select allOpen in new window

Also, it would be helpful to see your SELECT query and existing UPDATE query (if you have one). I hope that gives you an idea of how to do this.

Hi

Thanks for that. Does the last item in your comment allow the user to enter information in any field and have that updated also?

What is needed is a query that will change one, two or all field if they have data but do nothing to any field where the corresponding entry is blank.

Here is the select query code

SELECT FrameType.TimberCostM³, FrameType.[Waste%], ((((1+([Waste%]/100))*[TimberThick]*[TimberWidth]*[TimberCostM³])/10000)/100)+(((100+[Waste%])*0.01)*[FrameAssembly]) AS NetCost, FrameType.TimberMargin, Round((1+([TimberMargin]/100))*[NetCost]*100)/100 AS [Price/Meter], FrameType.TimberThick, FrameType.FrameType, FrameType.TotalFT, FrameType.Supplier, FrameType.Depth, FrameType.FF, FrameType.FrameAssembly, FrameType.TimberWidth, FrameType.FrameMaterial
FROM FrameType
WHERE (((FrameType.FrameType) Like nz([Forms]![FrameTypeForm]!,"*")) AND ((FrameType.TotalFT) Like nz([Forms]![FrameTypeForm]![G],"*")) AND ((FrameType.Supplier) Like nz([Forms]![FrameTypeForm]![A],"*")) AND ((FrameType.Depth) Like nz(DLookUp("[Depth]","[FrameTypeDLookupForm]"),"*")) AND ((FrameType.FF) Like nz([Forms]![FrameTypeForm]![H],"*") Or (FrameType.FF) Is Null) AND ((FrameType.FrameMaterial) Like nz([Forms]![FrameTypeForm]![C],"*")));

  tag.

I think this would work.

UPDATE YourTable
SET Supplier = a.Supplier, Type = a.Type, Material = a.Material, Finish = a.Finish,
  Width = a.Width -- ** include all fields to update if that's what you need
FROM (
SELECT FrameType.TimberCostM³, FrameType.[Waste%], ((((1+([Waste%]/100))*[TimberThick]*[TimberWidth]*[TimberCostM³])/10000)/100)+(((100+[Waste%])*0.01)*[FrameAssembly]) AS NetCost, FrameType.TimberMargin, Round((1+([TimberMargin]/100))*[NetCost]*100)/100 AS [Price/Meter], FrameType.TimberThick, FrameType.FrameType, FrameType.TotalFT, FrameType.Supplier, FrameType.Depth, FrameType.FF, FrameType.FrameAssembly, FrameType.TimberWidth, FrameType.FrameMaterial
FROM FrameType

WHERE 
-- here are my suggested filters
LTRIM(FrameType.Supplier) <> ''
  AND LTRIM(FrameType.FrameType) <> ''
-- and so on in order to make sure no records are updated in YourTable unless the -specified fields are populated (or not = to a blank value)

-- these are your filters
(((FrameType.FrameType) Like nz([Forms]![FrameTypeForm]!,"*")) AND ((FrameType.TotalFT) Like nz([Forms]![FrameTypeForm]![G],"*")) AND ((FrameType.Supplier) Like nz([Forms]![FrameTypeForm]![A],"*")) AND ((FrameType.Depth) Like nz(DLookUp("[Depth]","[FrameTypeDLookupForm]"),"*")) AND ((FrameType.FF) Like nz([Forms]![FrameTypeForm]![H],"*") Or (FrameType.FF) Is Null) AND ((FrameType.FrameMaterial) Like nz([Forms]![FrameTypeForm]![C],"*")))
)a

Select allOpen in new window

"and so on in order to make sure no records are updated in YourTable unless the -specified fields are populated (or not = to a blank value)"

What I need is for an update query to only select or change the columns that need to change. Otherwise, presumably, the Update query will update any column with either a blank or a value. If it does not update a blank where does it get a value from? or does it not select the column?

Does your solution solve this?

What I imagine is something like:
Select FrameType.Material Only If the corresponding field criteria is not null, FrameType.Supplier Only If the corresponding field criteria is not null. etc.

Unfortunately I don't seem able to make it work

Help!

Derek

Help

Derek

Hi

Spent a few hours working with this. It seams that you are under the impression that the "Update too" values are coming from a table. They do in fact come from the form that I attached a snip of and from the fields that are highlighted which are Unbound controls.

So the user filters the table data [FrameType] data using the blue combos to get the few records he wants to change and then, say, he just wants to change the supplier name for that selection. I want him to put the new suppliers name in the white highlighted box below the blue combo and update only supplier. If other white highlighted boxes have entries, say, FrameType I would like that field updated too. I can see you understand that bit. So looking at your code if you could just show me how to choose the update value from the form- which we can call the [FrameForm] and the unbound control to update Supplier Is [HU] I can do the rest (I think). Incidentally I do not need to know if the update value does not equal any other value. If the idiot user wants to update a supplier, with the same supplier name then he can accept the consequences.

Is this going to effectively be running 11 individual queries?

Thank you very much for your help so far.
Capture.JPG