Looking for a solution to my problem, amybe an IIf statement will

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of data with lots of rows. One of my columns has a group of
rows associated with one account number, then another group of rows will be
below that, but, they'll be associated with a different account number. The
account number is always in the first row following the group of rows, with
the rest of the rows underneath the first row being empty, until a new
account number begins. Is there a formula that will look at the account
number and copy it down to the rest of the rows, but, only when the cell is
blank.

In excel this is done rather easy, I input the first account number in a new
column (let's say "C", where column "D" is my original data, so C1=D1), then
in C2, I use this formula =IF(D2="",C1,D2), which I can then use all the way
down to the end of my data. This returns me the value in column "D" if there
is data, if there isn't data in column "D" it returns the value from above in
column "C".

How could I input this into my query in Access to acheive the same effect?

-Tony
 
In the query, create a new field and use iif

NewFieldName: IIF([FieldNameD2]="" Or [FieldNameD2] Is Null
,[FieldNameC1],[FieldNameD2])

In SQL it will look like

Select IIF([FieldNameD2]="" Or [FieldNameD2] Is Null
,[FieldNameC1],[FieldNameD2]) As NewFieldName From TableName
 
Sorry, the example I gave you apply only if it is the same record, it
wouldn't move to the prev record

--
\\// Live Long and Prosper \\//
BS"D


Ofer said:
In the query, create a new field and use iif

NewFieldName: IIF([FieldNameD2]="" Or [FieldNameD2] Is Null
,[FieldNameC1],[FieldNameD2])

In SQL it will look like

Select IIF([FieldNameD2]="" Or [FieldNameD2] Is Null
,[FieldNameC1],[FieldNameD2]) As NewFieldName From TableName

--
\\// Live Long and Prosper \\//
BS"D


tonytheolo said:
I have a table of data with lots of rows. One of my columns has a group of
rows associated with one account number, then another group of rows will be
below that, but, they'll be associated with a different account number. The
account number is always in the first row following the group of rows, with
the rest of the rows underneath the first row being empty, until a new
account number begins. Is there a formula that will look at the account
number and copy it down to the rest of the rows, but, only when the cell is
blank.

In excel this is done rather easy, I input the first account number in a new
column (let's say "C", where column "D" is my original data, so C1=D1), then
in C2, I use this formula =IF(D2="",C1,D2), which I can then use all the way
down to the end of my data. This returns me the value in column "D" if there
is data, if there isn't data in column "D" it returns the value from above in
column "C".

How could I input this into my query in Access to acheive the same effect?

-Tony
Click to expand...
Click to expand...
 
I have a table of data with lots of rows. One of my columns has a group of
rows associated with one account number, then another group of rows will be
below that, but, they'll be associated with a different account number. The
account number is always in the first row following the group of rows, with
the rest of the rows underneath the first row being empty, until a new
account number begins. Is there a formula that will look at the account
number and copy it down to the rest of the rows, but, only when the cell is
blank.

In excel this is done rather easy, I input the first account number in a new
column (let's say "C", where column "D" is my original data, so C1=D1), then
in C2, I use this formula =IF(D2="",C1,D2), which I can then use all the way
down to the end of my data. This returns me the value in column "D" if there
is data, if there isn't data in column "D" it returns the value from above in
column "C".

How could I input this into my query in Access to acheive the same effect?

-Tony
Click to expand...

You're applying spreadsheet logic to a relational database... and it
won't work. Ofer's suggestion (as he immediately realized) won't work.

Your problem is that - unlike a spreadsheet - a Table HAS NO ORDER.
There is no "above" or "below". Records in a table are like potatoes
in a wheelbarrow - just a heap, in no usable order. There is therefore
nothing to identify any particular record with a blank account number
as being associated with any other potato in the wheelbarrow!

If you're VERY lucky, you may be able to open a Recordset based on the
table in VBA and loop through it; or, if you have a sequential
Autonumber field defining a sequence of records, you can update the
AccountNumber with an Update Query:

UPDATE YourTable
SET AccountNumber = DLookUp("[AccountNumber]", "[YourTable]", "[ID] =
" & DMax("[ID]", "[YourTable]", "[AccountNumber] IS NOT NULL AND [ID]
< " & [ID]))
WHERE AccountNumber IS NULL;

If you don't have such a sequential ID field... I don't know ANY good
way.

John W. Vinson[MVP]
 
Back
Top