IIF field is Null then field=previous
- Thread starter sargon
- Start date
- Local time
- Today, 03:16
- Joined
- Jan 23, 2006
- Messages
- 15,573
Hello,
I have a problem, pls help!
B = IIF (A = NULL); then B = previous B; else B = A
A B
1 1
6 6
_ 6
4 4
_ 4
_ 4
2 2
3 3
7 7
_ 7
I don't know how to make previous B...
A can NEVER = NULL
B= iif(IsNull(A),Previous,Next)
Take a look at the syntax for iif
Good luck
If you pull from a single table/query, here's my suggestion:
Add an autonumber field to your table, forcing it to be unique.
Add the ability to re-update that autonumber field in case records get deleted, resulting in some numbers no longer existing in your table.
After that, refer to your table/query twice within the query you are currently making, having no joins between the the original or duplicate source query (this will create a quite the slow query if you're working with even a small number of records, a snail's pace if you're using thousands of records).
Add a field to the query refering to the autonumber field from your original table/query, and have the criteria of that field be the value of the autonumber field in your duplicate of the source table/query (usually tablename_1) minus one. That will give you access to the previous record any time you refer to the duplicated source table/query.
If you manage all that, you'd then use something like this if your table was just called tblTable:
B: iif(IsNull(tblTable.A),tblTable_1.B,tblTable.A)
The only other option I would know of would be to make a table from the data you are referring to. Then write a sub/function to do what you're asking modifying the newly made table, which would then be viewed in a later used query/form/report. Something like this would be put in a module:
Dim rRecSet As New ADODB.Recordset
Public Function HandleBWhenANull()
dim sLastB as String '(or whatever variable type B or A is)
If rRecSet.State = adStateOpen Then rRecSet.Close
rRecSet.CursorLocation = adUseClient
rRecSet.Open "SELECT * from [SourceTable];", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
While not rRecSet.EOF
if isnull(rRecSet!A) then
rRecSet!B = sLastB
else
rRecSet!B = A
End If
sLastB = iif(isnull(rRecSet!B), "", rRecSet!B)
rRecSet.MoveNext
Wend
End Function
Then you'd write a macro using the command RunCode, and put in HandleBWhenANull() as the function name.
Add an autonumber field to your table, forcing it to be unique.
Add the ability to re-update that autonumber field in case records get deleted, resulting in some numbers no longer existing in your table.
After that, refer to your table/query twice within the query you are currently making, having no joins between the the original or duplicate source query (this will create a quite the slow query if you're working with even a small number of records, a snail's pace if you're using thousands of records).
Add a field to the query refering to the autonumber field from your original table/query, and have the criteria of that field be the value of the autonumber field in your duplicate of the source table/query (usually tablename_1) minus one. That will give you access to the previous record any time you refer to the duplicated source table/query.
If you manage all that, you'd then use something like this if your table was just called tblTable:
B: iif(IsNull(tblTable.A),tblTable_1.B,tblTable.A)
The only other option I would know of would be to make a table from the data you are referring to. Then write a sub/function to do what you're asking modifying the newly made table, which would then be viewed in a later used query/form/report. Something like this would be put in a module:
Dim rRecSet As New ADODB.Recordset
Public Function HandleBWhenANull()
dim sLastB as String '(or whatever variable type B or A is)
If rRecSet.State = adStateOpen Then rRecSet.Close
rRecSet.CursorLocation = adUseClient
rRecSet.Open "SELECT * from [SourceTable];", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdText
While not rRecSet.EOF
if isnull(rRecSet!A) then
rRecSet!B = sLastB
else
rRecSet!B = A
End If
sLastB = iif(isnull(rRecSet!B), "", rRecSet!B)
rRecSet.MoveNext
Wend
End Function
Then you'd write a macro using the command RunCode, and put in HandleBWhenANull() as the function name.
