Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 19th, 2006, 09:27 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copy previous field record if next field is null

I have imported records into a table and some of the records have empty fields. All of the blank fields (number and name) belong to the previous record. For example:

Number Name ReportTo Time
11111 John Medical 0500
22222 Jane Dental 0700
                       Medical 1100
44444 Steve HR 0900
55555 Julie Training 0800
                       Records 1400
                       Business 1700
66666 David Medical 0800

I want to find a way to populate this table and eliminate all blank fields. I have tried the Dlookup to find the null values, but I cannot find a way to copy it into the appropriate record.

The tble should look like this:

Number Name ReportTo Time
11111 John Medical 0500
22222 Jane Dental 0700
22222 Jane Medical 1100
44444 Steve HR 0900
55555 Julie Training 0800
55555 Julie Records 1400
55555 Julie Business 1700
66666 David Medical 0800


Any help and guidance willbe greatly appraciated.

Thank you


Reply With Quote
  #2 (permalink)  
Old February 19th, 2006, 12:35 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

My approach to problems like this is the export it to Excel, manipulate it, and then import it back into Access.

In Excel you would have:
  A B C D
Number Name ReportTo Time
11111 John Medical 0500
22222 Jane Dental 0700
                  Medical 1100
44444 Steve HR 0900
55555 Julie Training 0800
                  Records 1400
                  Business 1700
66666 David Medical 0800

I would do Column E as New Number and F as New Name.

In Column E do something like =IF(A2="", E1, A2)
& in F something like =IF(B2="", F1, B2)

Then just drag those formulas down, and it should give you a solid list of Numbers and Names. Select Column E & F, Copy them, then Right Click on E1 and choose Paste Special, and choose the Values Option. Then you can copy E & F over A & B, Erase E&F and then import the sheet into Access.

Hope that helps,

Mike

Mike
EchoVue.com
Reply With Quote
  #3 (permalink)  
Old February 19th, 2006, 01:07 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for your suggestion, the thing about transfer it to excel is that the data importing is a task to be performed everyday by a person who is not very computer savy. I want to make the process with the least amount of user interaction to prevent and minimized errors. The initial data is coming already to Access from a .txt tab delimited file. Can your formula be used, let's say in a VBA module?

Reply With Quote
  #4 (permalink)  
Old February 19th, 2006, 01:15 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Aah, the user - the weakest link of any system!!

I have done something like this in VBA before. What you want to do, is set up some variables for the current line, and some for the name & Number of the previous line. Read each line in, then if the Name and Number fields are blank, set the current name and number = to the previous one, otherwise, copy the new name and number onto the previous variables. The set up an INSERT SQL satement, and put it into the table.

Mike


Mike
EchoVue.com
Reply With Quote
  #5 (permalink)  
Old February 21st, 2006, 10:29 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I figured out a way with an update query, a dlookup statement in the "update to" field and an "is null" statement in the criteria field.....

Thanks for the help

Reply With Quote
  #6 (permalink)  
Old June 20th, 2006, 02:47 PM
Registered User
 
Join Date: Jun 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Would you please show me how you did this? I have to do the same for a table. Thanks.


Quote:
quote:Originally posted by ecampos
 I figured out a way with an update query, a dlookup statement in the "update to" field and an "is null" statement in the criteria field.....

Thanks for the help

Reply With Quote
  #7 (permalink)  
Old June 23rd, 2006, 12:55 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here's how I've solved it:

in the Update query in design view:
select your field and your table.
In the "Update to:" field:
DLookUp("[YOURFILED]";"YOURtableNAME";"[ID] = " & [ID]-1)
"Criteria" field:
Is Null

This is working for me...hopefully you'll get it to work also.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I handle NULL resultset field? cm.cruz Classic ASP Basics 1 January 28th, 2008 06:29 PM
Replace Gridview field if null with new field Indo77 ASP.NET 2.0 Basics 1 June 18th, 2007 06:22 AM
Previous field /column value in cross-tab mozilago SQL Server ASP 0 March 7th, 2007 02:58 AM
Null Value for Date/Time Field ksegars4 Crystal Reports 2 November 17th, 2004 11:58 AM
copy previous field value VBAHole22 Access VBA 1 August 8th, 2003 07:21 AM



All times are GMT -4. The time now is 11:41 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.