Avatar of pdvsa
pdvsa
Flag for United States of America asked on

Update a table field with value above it

Hello,

I need to update a table: tblImportToAccess.[TradeNo] and tblImportToAccess.[CoName]
with the value that is in the row above it.
Pic
In the pic above, I need the nulls on [TradeNo] and [CoName] to populate with 123 and XYZ respectively for the first null but the second null I would need it to populate with 333 and ABC respectively (much like dragging down the cell above if it were in excel).

My dataset is much larger and there are different TradeNo and CoName.
I am not sure if this would best be done by way of an update query but if so then I have a part of it below and looking for the remainder.
I am a novice user of Access. I can easily do this in excel with a macro but I want to to perform this in access after I import the excel data.

UPDATE tblImportToAccess SET
WHERE (((tblImportToAccess.[TradeNo]) Is Null) AND ((tblImportToAccess.[CoName]) Is Null));

Thank you.
* Update QueryMicrosoft Access

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
Anders Ebro (Microsoft MVP)

It is important to understand that a database has no concept of row order or row number, unless defined by a query, that tells the database how rows are ordered, e.g. by stating "ORder by OrderDate"
John Tsioumpris

Just use this code

Function UpdatePrevious() 
Dim rst As DAO.Recordset 
Dim tmpTradeNo As Long 
Dim tmpCoName As String 
Set rst = CurrentDb.OpenRecordset("tblImport") 
With rst 
tmpTradeNo = .Fields("TradeNo") 
 tmpCoName = .Fields("CoName") 
.MoveNext 
While Not .EOF 
If IsNull(.Fields("TradeNo")) Then 
.Edit 
.Fields("TradeNo") = tmpTradeNo 
.Update 
End If 
If Len(Nz(.Fields("CoName"), "")) = 0 Then 
.Edit 
.Fields("CoName") = tmpCoName 
.Update 
End If 
If Not IsNull(.Fields("TradeNo")) Then tmpTradeNo = .Fields("TradeNo") 
If Len(Nz(.Fields("CoName"), "")) > 0 Then tmpCoName = Nz(.Fields("CoName"), 0) 
.MoveNext 
Wend 
End With 
rst.Close 
End Function

Open in new window

As long your very first record has values this code should the job

pdvsa

ASKER

Hi John and thanks for the response.  


I do have an error though.  

I had a update code to the "real' field names.   I posted the question and changed the names for better reading.  

The field names I changed were as follows:

[TradeNo] to the real field name of [Trade #] and [CoName] to [Buy CP].


I am not sure if that is why when I run I get a type mismatch error on tmpTradeNo = .Fields("Trade #") (7th line)

Not sure if I can have any spaces or if the "#" is what is causing the error. 


here is the code as I have it now after updating the field names to the "real" field names

Function UpdatePrevious()
    Dim rst As DAO.Recordset
    Dim tmpTradeNo As Long
    Dim tmpCoName As String
    Set rst = CurrentDb.OpenRecordset("tblImportToAccess")
    With rst
    tmpTradeNo = .Fields("Trade #")
     tmpCoName = .Fields("Buy CP")
    .MoveNext
    While Not .EOF
    If IsNull(.Fields("Trade #")) Then
    .Edit
    .Fields("Trade #") = tmpTradeNo
    .Update
    End If
    If Len(Nz(.Fields("Buy CP"), "")) = 0 Then
    .Edit
    .Fields("Buy CP") = tmpCoName
    .Update
    End If
    If Not IsNull(.Fields("Trade #")) Then tmpTradeNo = .Fields("Trade #")
    If Len(Nz(.Fields("Buy CP"), "")) > 0 Then tmpCoName = Nz(.Fields("Buy CP"), 0)
    .MoveNext
    Wend
    End With
    rst.Close

End Function

Open in new window


This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Mark Edwards

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
John Tsioumpris

Spaces and Special Characters are not the issue here...check if you have wrong datatype.
PatHartman

Best practice would be to fix the offending column names to remove the embedded spaces and special characters.  Otherwise, you always have to enclose these bad names in square brackets.

pdvsa

ASKER

Experts, sorry for my tardiness.  I will be testing this soon and revert back with any questions.  

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pdvsa

ASKER

Hello, 


Mark, thank you. It is a little bit above me though.  


John, please see pic of the error I get.  Maybe it is simple.  

[Trade #] is short text in the table.  Just to recap what I said up above, I  changed your code from [TradeNo] to what it really is in the db which is [Trade #].  I know the name is not appropriate but I  am importing from Excel and need to stick to the field names.  Thank you. 


PatHartman

Please refer to my earlier comment.


PS - you don't have to use the Excel column names as your table's column names.

pdvsa

ASKER

Hi Pat, ok i understand however I need to keep the names as the ones found in excel.  I do not have control over the excel file names.  

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PatHartman

That's not what I said.  You don't need to change the names in the excel file.  Link to the spreadsheet of importing.  Then you can use a query to append the data from the spreadsheet into properly named columns for you to use for the rest of the app.

pdvsa

ASKER

Ahh I see what you are saying.  Sorry for my misunderstanding.  I have now updated my append query to change the bad names into good names.  I didnt think that I could change the names of the fields to whatever I wanted within the append.  I feel much better now that those bad names are gone.  Thank you so much....

PatHartman

You're welcome

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mark Edwards

Glad to help....