Query to fill in blanks within a table? Maybe some other way? (1 Viewer)

Hi,

I'm not sure if I'm going about this right but I'll give it a go

I have a Database with a lot of tables and queries. Queries run on Table A and make a new Table "Table X". Another query runs on Table B and creates "Table Y". A third Query then compares Table X and Table Y's "Minute" Field. From here, All the records in Table X are copied to a new table "Table Z" and the matches from Table Y are added onto the columns in "Table Z"

What this creates is a table with, lets say for example 1000 records and approx 60 or so have blank sections (i.e. where there is no match from Table Y).

What I now want is to run something (I'm not sure if a Query is the right way to go about this) on the resulting table which fills in the blanks with whatever is in the previous record.

E.g.

Table X Table Y
HasData Hasdata
HasData NoData
HasData NoData
HasData HasData

So what I want to happen is when it finds a null vlaue, it copies in the previous records value. So in the Above the Data would have "HasData" in all records.

I'm thinking something like:

If Is Null then ?????
Else
???????

but I don't know SQL well enough to understand what I need, only that I know it should be possible.

I hope!!!

Thanks in advance for any help possible!

Rob.

I am not sure I understood correctly, but...
There is a function in SQL that does replace null value and here it is:
And in VBA, it is like this:
If Val1 is null, it gives Val2, else it gives Val1.

I hope that this is what you've been searching for.

No, not really

What I need is for it to look at the value, and if it's null copy the previous value in that field into the null value.

For example:

1
2
2

3

4

5

Would look like this:

1
2
2
2
3
3
4
4
5

I had an idea that maybe if I get it to make a new record at the end where it finds a null value, and copy over the values (using a clone script i found) from the previous record and then deleting any null values after it runs.

So, I'm trying to use:

Private Sub nullvalue_test()
On Error GoTo nullvalue_test_error
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset, rstcln As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open "SELECT * FROM NASP_LL2 WHERE [F1] = NULL"
Set rstcln = rst.Clone

With rstcln

.AddNew
If [Day] Is Null Then
![Day] = rst![Day]
Else
If [F1] Is Null Then
![F1] = rst![F1]
Else
If [F2] Is Null Then
![F2] = rst![F2]
Else
If [Datapkt] Is Null Then
![Datapkt] = rst![Datapkt]
Else
If [MaxOfA_Date] Is Null Then
![MaxOfA_Date] = rst![MaxOfA_Date]
Else
If [Timestring] Is Null Then
![Timestring] = rst![Timestring]
Else
If [AvgOfBizNasp] Is Null Then
![AvgOfBizNasp] = rst![AvgOfBizNasp]
Else
If [NASP] Is Null Then
![NASP] = rst![NASP]
Else
If [Daystring] Is Null Then
![Daystring] = rst![Daystring]
Else
If [tran_id] Is Null Then
![tran_id] = rst![tran_id]
Else
If [Field3] Is Null Then
![Field3] = rst![Field3]
Else
If [Field4] Is Null Then
![Field4] = rst![Field4]
Else
If [Field5] Is Null Then
![Field5] = rst![Field5]
Else
If [Field6] Is Null Then
![Field6] = rst![Field6]
Else
If [Field7] Is Null Then
![Field7] = rst![Field7]
Else
If [Transaction_name] Is Null Then
![Transaction_name] = rst![Transaction_name]
End If
.Update

End With
rstcln.Close
rst.Close

nullvalue_test_Exit:
ExitSub
nullvalue_test_error:
MsgBox Err.Description
Resume nullvalue_test_Exit



End Sub

However I have no idea if this is correct and I get an error message on the first line

lol I'm rubbish.

The error message was "error user defined type not defined"

Hi Rob, I think the best way to address this problem is using vba code.

The general idea is to navigate the recordset, and assign the value in the field to a variable if it isn't null, if it is null then use the variable as the value of the field. I've marked a couple of things that you will need to change.

Sub nullparts()
Dim i As String

Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("Part") 'Replace Part with the name of your table
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "Part_Num" Then ' Replace "Part_Num" with the name of the field
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop

End Sub

Let me know if this works,
ZDog

ZDog that worked great! thankyou very much it's really appreciated!!! Is there a way to get the Table and Field ID's linked to a Form text box??

E.g.

Sub nullparts()
Dim i As String

Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("[Forms]![exampleform]![textbox2]" ) 'Replace Part with the name of your table
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "[Forms]![exampleform]![textbox2]" Then ' Replace "Part_Num" with the name of the field
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop

End Sub

Glad I could help out Rob.
As for you question regarding the form, my guess would be that it should work.

I'm assuming that you would like to specify the table and field name based on textboxes on a form. Only change that you need to make is that you don't need the double quotes around [Forms]![exampleform]![textbox] and you will need a command button on the form to run the sub.

Hope that helps
ZDog

How Would I run this code from a macro? I can use the openmodule macro but it only opens it and doesnt run the code. The run code macro doesn't give me the option to run this...

Thanks again!!!

Rob.

Nevermind you cant call a sub from a macro so i just changed them to functions.

ta

1. A form's recordset clone is a DAO object so you cannot assign it to an ADO object. That was the cause of the data type error.
2. Records in a table have no particular relationship to each other. You would need to order a recordset to reliably predict the actual order of rows in a recordset.
3. The only way to preserve data entry order when you import data from a "flat file" file type is to append the rows to a table with an autonumber defined. You can then sort by the autonumber to revert to data entry order.

Not sure if pat meant to post that on this message??

Sometimes, but not every time I run this macro, I get an error "Data type conversion error"

Any idea why this is?

I'm using the code as below, running on several different tables (Each table has it's own nullpartsX function and fields etc..)

Function nullparts5()
Dim i As String 'Declare i

Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Set MyRs as the records within the specified table
'Replace "NASP_SYN7" with the name of the table if needed for other tables
MyRs.MoveFirst 'moves to the first record
Do While Not MyRs.EOF 'Do The following while not at the end of the file
MyRs.Edit 'Edit "MyRs"
For Each myfld In MyRs.Fields 'tells the script to run in fields
If myfld.Name = "F1" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then 'If MyRs is not Null ("")
i = MyRs(myfld.Name).Value 'then make i that value
Else 'Otherwise
MyRs(myfld.Name).Value = i 'make the content of the current record field i
MyRs.Update
End If
End If

Next myfld 'Goto the next record

MyRs.MoveNext
Loop



Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "F2" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop





Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "Day" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop




Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "DataPkt" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop





Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "TimeString" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop




Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "BIZNASP" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop





Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "NASP" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop





Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "DayString" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop




Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "tran_id" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop




Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "Field3" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop



Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "Field4" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop


Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "Field5" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop



Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "Field6" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop



Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "Field7" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop



Set myDb = CurrentDb()
Set MyRs = myDb.OpenRecordset("NASP_SYN7") 'Replace "F1" with the name of the table if needed for other tables
MyRs.MoveFirst
Do While Not MyRs.EOF
MyRs.Edit
For Each myfld In MyRs.Fields
If myfld.Name = "Transaction_name" Then ' Replace "F1" with the name of the field if needed for other fields
If MyRs(myfld.Name).Value <> "" Then
i = MyRs(myfld.Name).Value
Else
MyRs(myfld.Name).Value = i
MyRs.Update
End If
End If

Next myfld

MyRs.MoveNext
Loop

End Function