Replace null value with the previous value?

[Okstate student]

I have a database that was just imported that has approximately 388000
records. The problem is that there is information about a person in multiple
different records but the name did not come across with each record. (So I
have 10 records with information for a certain name, but the name only
appears in field 1 of the first record and not the subsequent 9, etc.) I
need to create a query or expression that will fill field 1 with the
preceding value if it is null. This way I will have all the information for
field 1 in a manner that I can link and combine data.

Simply I need to fill field 1 with the previous records information if it is
null.

Any help would be greatly appreciated.

 

[Klatuu]

Create a Public Function in a standard module with a Static variable that
will return the value is receives if it is a good value or returns the value
saved in the Static variable if not:

PublicFunction GetName(varName As Variant) As String
Static strPreviousName As String

If Not IsNull(varName) Then
strPreviousName = varName
End If

GetName = strPreviousName
End Function

Now, in your query, create a calculated field and pass the Name field to it:

GoodName: GetName([NameField])

 

[Michel Walsh]

That may produces erroneous result if the end user decide to navigates
backward. The remembered value in the static variable, then, COULD be, in
theory, the one from a record coming after the current one actually
displayed after the "move to previous" record.


Vanderghast, Access MVP

Create a Public Function in a standard module with a Static variable that
will return the value is receives if it is a good value or returns the
value
saved in the Static variable if not:

PublicFunction GetName(varName As Variant) As String
Static strPreviousName As String

If Not IsNull(varName) Then
strPreviousName = varName
End If

GetName = strPreviousName
End Function

Now, in your query, create a calculated field and pass the Name field to
it:

GoodName: GetName([NameField])
--
Dave Hargis, Microsoft Access MVP

I have a database that was just imported that has approximately 388000
records. The problem is that there is information about a person in
multiple
different records but the name did not come across with each record. (So
I
have 10 records with information for a certain name, but the name only
appears in field 1 of the first record and not the subsequent 9, etc.) I
need to create a query or expression that will fill field 1 with the
preceding value if it is null. This way I will have all the information
for
field 1 in a manner that I can link and combine data.

Simply I need to fill field 1 with the previous records information if it
is
null.

Any help would be greatly appreciated.

 

[Klatuu]

Good point, Michel. Had not considered that. If the query is forward only
(ie for a report), it would work, but you are correct, navigating backwards
would surely return erroneous results.
--
Dave Hargis, Microsoft Access MVP

That may produces erroneous result if the end user decide to navigates
backward. The remembered value in the static variable, then, COULD be, in
theory, the one from a record coming after the current one actually
displayed after the "move to previous" record.


Vanderghast, Access MVP

Create a Public Function in a standard module with a Static variable that
will return the value is receives if it is a good value or returns the
value
saved in the Static variable if not:

PublicFunction GetName(varName As Variant) As String
Static strPreviousName As String

If Not IsNull(varName) Then
strPreviousName = varName
End If

GetName = strPreviousName
End Function

Now, in your query, create a calculated field and pass the Name field to
it:

GoodName: GetName([NameField])
--
Dave Hargis, Microsoft Access MVP

I have a database that was just imported that has approximately 388000
records. The problem is that there is information about a person in
multiple
different records but the name did not come across with each record. (So
I
have 10 records with information for a certain name, but the name only
appears in field 1 of the first record and not the subsequent 9, etc.) I
need to create a query or expression that will fill field 1 with the
preceding value if it is null. This way I will have all the information
for
field 1 in a manner that I can link and combine data.

Simply I need to fill field 1 with the previous records information if it
is
null.

Any help would be greatly appreciated.

 

[Michel Walsh]

I should move my s from produces and navigates onto decide ( I would
like the possibility to edit those messages after they are posted! )

A possible solution, if navigation must be maintained, would be to do an
update of the table:


UPDATE myTable
SET field1 = Nz( field1, GetName( field1) )


but that CHANGES the data in the table, in a manner that I don't necessary
buy blindly. On the other hand, once the update is done, using the table
would produce a 'repeatable' experience, independent of the navigation...
but that is obvious.


Vanderghast, Access MVP

Good point, Michel. Had not considered that. If the query is forward
only
(ie for a report), it would work, but you are correct, navigating
backwards
would surely return erroneous results.
--
Dave Hargis, Microsoft Access MVP

That may produces erroneous result if the end user decide to navigates
backward. The remembered value in the static variable, then, COULD be, in
theory, the one from a record coming after the current one actually
displayed after the "move to previous" record.


Vanderghast, Access MVP

Create a Public Function in a standard module with a Static variable
that
will return the value is receives if it is a good value or returns the
value
saved in the Static variable if not:

PublicFunction GetName(varName As Variant) As String
Static strPreviousName As String

If Not IsNull(varName) Then
strPreviousName = varName
End If

GetName = strPreviousName
End Function

Now, in your query, create a calculated field and pass the Name field
to
it:

GoodName: GetName([NameField])
--
Dave Hargis, Microsoft Access MVP


:

I have a database that was just imported that has approximately 388000
records. The problem is that there is information about a person in
multiple
different records but the name did not come across with each record.
(So
I
have 10 records with information for a certain name, but the name only
appears in field 1 of the first record and not the subsequent 9, etc.)
I
need to create a query or expression that will fill field 1 with the
preceding value if it is null. This way I will have all the
information
for
field 1 in a manner that I can link and combine data.

Simply I need to fill field 1 with the previous records information if
it
is
null.

Any help would be greatly appreciated.

 

[Klatuu]

I don't know about changing the data in the table. What if you get a power
failure in the middle of that?

If the recordset needs to be navigable, I would consider using a temp table
and make the query into an append query, delete the records before appending.
--
Dave Hargis, Microsoft Access MVP

I should move my s from produces and navigates onto decide ( I would
like the possibility to edit those messages after they are posted! )

A possible solution, if navigation must be maintained, would be to do an
update of the table:


UPDATE myTable
SET field1 = Nz( field1, GetName( field1) )


but that CHANGES the data in the table, in a manner that I don't necessary
buy blindly. On the other hand, once the update is done, using the table
would produce a 'repeatable' experience, independent of the navigation...
but that is obvious.


Vanderghast, Access MVP

Good point, Michel. Had not considered that. If the query is forward
only
(ie for a report), it would work, but you are correct, navigating
backwards
would surely return erroneous results.
--
Dave Hargis, Microsoft Access MVP

That may produces erroneous result if the end user decide to navigates
backward. The remembered value in the static variable, then, COULD be, in
theory, the one from a record coming after the current one actually
displayed after the "move to previous" record.


Vanderghast, Access MVP


Create a Public Function in a standard module with a Static variable
that
will return the value is receives if it is a good value or returns the
value
saved in the Static variable if not:

PublicFunction GetName(varName As Variant) As String
Static strPreviousName As String

If Not IsNull(varName) Then
strPreviousName = varName
End If

GetName = strPreviousName
End Function

Now, in your query, create a calculated field and pass the Name field
to
it:

GoodName: GetName([NameField])
--
Dave Hargis, Microsoft Access MVP


:

I have a database that was just imported that has approximately 388000
records. The problem is that there is information about a person in
multiple
different records but the name did not come across with each record.
(So
I
have 10 records with information for a certain name, but the name only
appears in field 1 of the first record and not the subsequent 9, etc.)
I
need to create a query or expression that will fill field 1 with the
preceding value if it is null. This way I will have all the
information
for
field 1 in a manner that I can link and combine data.

Simply I need to fill field 1 with the previous records information if
it
is
null.

Any help would be greatly appreciated.

 

[Michel Walsh]

The temp table would be a better alternative, indeed.


Vanderghast, Access MVP

I don't know about changing the data in the table. What if you get a power
failure in the middle of that?

If the recordset needs to be navigable, I would consider using a temp
table
and make the query into an append query, delete the records before
appending.
--
Dave Hargis, Microsoft Access MVP

I should move my s from produces and navigates onto decide ( I
would
like the possibility to edit those messages after they are posted! )

A possible solution, if navigation must be maintained, would be to do an
update of the table:


UPDATE myTable
SET field1 = Nz( field1, GetName( field1) )


but that CHANGES the data in the table, in a manner that I don't
necessary
buy blindly. On the other hand, once the update is done, using the table
would produce a 'repeatable' experience, independent of the navigation...
but that is obvious.


Vanderghast, Access MVP

Good point, Michel. Had not considered that. If the query is forward
only
(ie for a report), it would work, but you are correct, navigating
backwards
would surely return erroneous results.
--
Dave Hargis, Microsoft Access MVP


:

That may produces erroneous result if the end user decide to navigates
backward. The remembered value in the static variable, then, COULD be,
in
theory, the one from a record coming after the current one actually
displayed after the "move to previous" record.


Vanderghast, Access MVP


Create a Public Function in a standard module with a Static variable
that
will return the value is receives if it is a good value or returns
the
value
saved in the Static variable if not:

PublicFunction GetName(varName As Variant) As String
Static strPreviousName As String

If Not IsNull(varName) Then
strPreviousName = varName
End If

GetName = strPreviousName
End Function

Now, in your query, create a calculated field and pass the Name
field
to
it:

GoodName: GetName([NameField])
--
Dave Hargis, Microsoft Access MVP


:

I have a database that was just imported that has approximately
388000
records. The problem is that there is information about a person
in
multiple
different records but the name did not come across with each
record.
(So
I
have 10 records with information for a certain name, but the name
only
appears in field 1 of the first record and not the subsequent 9,
etc.)
I
need to create a query or expression that will fill field 1 with
the
preceding value if it is null. This way I will have all the
information
for
field 1 in a manner that I can link and combine data.

Simply I need to fill field 1 with the previous records information
if
it
is
null.

Any help would be greatly appreciated.

 

[Two Thousand 2000]

I'm working on the same problem, but when I type the below code into the
Access module the first line is in RED. I keep getting an error on the "As"
before Variant.

Create a Public Function in a standard module with a Static variable that
will return the value is receives if it is a good value or returns the value
saved in the Static variable if not:

PublicFunction GetName(varName As Variant) As String
Static strPreviousName As String

If Not IsNull(varName) Then
strPreviousName = varName
End If

GetName = strPreviousName
End Function

Now, in your query, create a calculated field and pass the Name field to it:

GoodName: GetName([NameField])
--
Dave Hargis, Microsoft Access MVP

I have a database that was just imported that has approximately 388000
records. The problem is that there is information about a person in multiple
different records but the name did not come across with each record. (So I
have 10 records with information for a certain name, but the name only
appears in field 1 of the first record and not the subsequent 9, etc.) I
need to create a query or expression that will fill field 1 with the
preceding value if it is null. This way I will have all the information for
field 1 in a manner that I can link and combine data.

Simply I need to fill field 1 with the previous records information if it is
null.

Any help would be greatly appreciated.

 

[John W. Vinson]

On Thu, 7 Feb 2008 15:51:00 -0800, Two Thousand 2000 <Two Thousand

I'm working on the same problem, but when I type the below code into the
Access module the first line is in RED. I keep getting an error on the "As"
before Variant.

If it's actually as posted:

PublicFunction GetName(varName As Variant) As String

you need a blank between Public and Function.

John W. Vinson [MVP]