Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
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


 
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
 
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?

 
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
 
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

 
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

 
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.





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





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