Update query with if else condition (need to refer to cell indexes

[Mushtaq Mohammed]

Hi All,

I am trying to write an update query on a table with if-else condition. I
have the following scenario:

Column A is completely blank. Column B has some entries. What I want is
whenever there is an entry in column B set column A value as "1" and
increment each cell of column A by 1 until you hit a value in column B again.
At this point reset the counter to 0, and start putting the values in column
A from 1. For explanation please see the sample data:

Column A Column B
blank OOX
blank blank
blank OOX
blank blank
blank blank
blank OOX

The logic of my query is : Update Table X Set Column A = iif((Column
B="OOX"),1, "Column A[current index-1]+1")

I know the way of refering index in the above query is incorrect. I just
wanted to show you my logic:

After the query is run output should be somewhat like this:

Column A Column B
1 OOX
2 blank
1 OOX
2 blank
3 blank
1 OOX

Can this be done via update query? or does it have to be a macro? If so,
can you please provide a sample code for the macro? I am very new to macro
programming

 

[Dale Fye]

Before you do this, I recommend you copy your table, in case the results
don't come back in the order you expect.

You can do this with a function and a query. Add the following function to
a code module:

Public Function IncValue(SomeValue As Variant) As Integer

Static myValue As Integer

If Len(SomeValue & "") = 0 Then
myValue = myValue + 1
Else
myValue = 1
End If

IncValue = myValue

End Function

Now, you can use that function, along with the value in Column B to get what
you want.

UPDATE yourTable
SET [Column A] = IncValue([Column B])

The way this function works is that it uses a static variable to keep track
of the previous value of myValue. It then checks to see whether the value
you have passed it (SomeValue) is NULL or blank "". If so, it adds one to
the value, otherwise, it resets the value to 1.

The problem with this is that what it gives you depends on the order in
which the records are processed, so if you have a ID field, or some other
field you can use to sort by, I recommend that you add that field and a sort
argument (ascending or descending) to the query.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Tom van Stiphout]

On Mon, 5 Jan 2009 05:32:03 -0800, Mushtaq Mohammed <Mushtaq
(e-mail address removed)> wrote:

Silver platters are in the other isle marked with Paid Professional
Services. This is your opportunity to learn about recordsets and
loops. Keywords: DAO, Recordset, While, EOF, Edit, Update. This will
enable you to write some VBA code (macros can't do this) to loop over
your data and update the empty column. Or re-think the whole idea to
see if there is a better overall solution.

-Tom.
Microsoft Access MVP