dlookup trouble in Access 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 25 Nov 2007 23:09 GMT

I am impoprting a text file into Access 2007 and trying to copy down data
from one field to the one below, unless there is data already present. If
data is present in the field below, then that data should be copied until a
new field with data is present.
I am using DLookup as follows with ID as an autonumber field.

DLookUp("[Account]","Corp0203","[ID] =" & [ID]-1) ; Criteria "Is Null"

The problem - DLookup only copies one time for each field of data each time
the query is run instead of copying until it finds data. I can repeatedly run
the query until it is all filled in but this is not desireable.

Thanks for any suggestions

Reply to this Message

mscertified - 26 Nov 2007 15:07 GMT

Thats exactly what Dlookup should do, it does not loop. Look in Access Help
for what it does.
There is no 'one below' in a relational database as physical sequence of
rows is unknown.
You will need to create your own query to do this. It should sort the
records into whatever sequence you need.

-Dorian

> I am impoprting a text file into Access 2007 and trying to copy down data
> from one field to the one below, unless there is data already present. If
[quoted text clipped - 9 lines]
>
> Thanks for any suggestions

Reply to this Message

John - 26 Nov 2007 15:45 GMT

In the past, it did not loop either, it copied one line down if null, then
read the next line and copied just written line to the succeeding line, and
so on through the table. See the link below, it describes the goal and it
works for them. It has worked for me in the past but not now.

http://p2p.wrox.com/topic.asp?TOPIC_ID=40244

Thanks.

> Thats exactly what Dlookup should do, it does not loop. Look in Access Help
> for what it does.
[quoted text clipped - 18 lines]
> >
> > Thanks for any suggestions

Reply to this Message

mscertified - 26 Nov 2007 20:05 GMT

OK, you did not mention using DLookup in a query I thought you were running
it from VBA. Why don't you go into SQL mode for your query and post the SQL.
Then I could probably fuigure out why it's not working.

DLookup is not going to do anything other than return a single value from a
single row. As I said look in Access Help, you should be familiar with what
it does.

It is the query that needs to run DLookup multiple times, once for each row.

-Dorian

> In the past, it did not loop either, it copied one line down if null, then
> read the next line and copied just written line to the succeeding line, and
[quoted text clipped - 27 lines]
> > >
> > > Thanks for any suggestions

Reply to this Message