Update Iff?

[sdc1234]

Greetings, I have a spreadsheet that has been imported into access and is missing information in certain columns, ex. col 1 row 56 is missing the amount and col 15 row 68 is missing dates, I have set it up to append new records and now I need to find a way to set it up to where if the value is null or has changed, I need that field to update ...


v/r
novice

[June7]

Update with what from where? From the linked Excel to the Access table? Build a query the joins the objects on common unique ID field, switch to UPDATE query, set the UpdateTo row to field(s) from the Excel.

Or if you don't want to affect existing data (although, does it matter?), try:

Nz([Access table fieldname], [Excel link fieldname])

[ranman256]

Access will read Excel and sometimes guess about the data type. It thinks the column is a number but somewhere there will be text so it gets confused and turns it NULL.
To prevent this on these certain fields, i run a macro in Excel that scans the column and hardcodes it to a text value.

  'it assumes there is a value in every row of the 1st column
Public Sub Cvt2Txt()
Dim iOff As Long
iOff = ActiveCell.Column - 1
While ActiveCell.Offset(0, -iOff).Text <> ""
   If ActiveCell.Value <> "" Then
      If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
   End If
   ActiveCell.Offset(1, 0).Select  'next row
Wend
End Sub