dlookup trouble in Access 2007

[John]

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

 

[mscertified]

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

 

[John]

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

 

[mscertified]

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