select query question

[n8kindt]

i have a bunch of blank values in my query. my data looks like this


z z z z s _ _ _ _ _ _
z z z s _ _ _ _ _ _ _
z z z z z z z z z s _
z s _ _ _ _ _ _ _ _ _
z z z z z s _ _ _ _ _

z = data stored in field
s = last field with data (reading left to right) stored in the row
_ = blank (null) fields

i want my query to automatically copy the last field (y) into all the blank (_) fields.
so it will like like this:

z z z z s s s s s s s
z z z s s s s s s s s
z z z z z z z z z s s
z s s s s s s s s s s
z z z z z s s s s s s


how can i accomplish this?

this would also be acceptable:

z z z z s _ _ _ _ _ s
z z z s _ _ _ _ _ _ s
z z z z z z z z z s s
z s _ _ _ _ _ _ _ _ s
z z z z z s _ _ _ _ s

thanks in advance for your time.

[PianoMan64]

have a bunch of blank values in my query. my data looks like this


z z z z s _ _ _ _ _ _
z z z s _ _ _ _ _ _ _
z z z z z z z z z s _
z s _ _ _ _ _ _ _ _ _
z z z z z s _ _ _ _ _

z = data stored in field
s = last field with data (reading left to right) stored in the row
_ = blank (null) fields

i want my query to automatically copy the last field (y) into all the blank (_) fields.
so it will like like this:

z z z z s s s s s s s
z z z s s s s s s s s
z z z z z z z z z s s
z s s s s s s s s s s
z z z z z s s s s s s


how can i accomplish this?

this would also be acceptable:

z z z z s _ _ _ _ _ s
z z z s _ _ _ _ _ _ s
z z z z z z z z z s s
z s _ _ _ _ _ _ _ _ s
z z z z z s _ _ _ _ s

thanks in advance for your time.

Let Assume for the moment since I don't have field names that we're going to call each column by the name of Col and the rows as records.

The way that you'd need to write the query is to do the following:

SELECT LastValue(Col1,nz(Col2,0),nz(Col3,0),nz(Col4,0),nz (Col5,0),nz(Col6,0),nz(Col7,0),nz(Col8,0),nz(Col9, 0),nz(Col10,0) as Col11 FROM {TableName}

Create a query with the following SELECT Statement.

Notice that I've created a function called LastValue(). You would need to put in the parathasise () the column names for each one of them.

Copy and Paste the following into a module

Open the Visual Basic Code Editor ALT-F11

Select Insert -> Module

 
Function LastValue(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10)
 
    If Col10 <> 0 then
           LastValue = Col10
           Exit Function
   End if
   If Col9 <> 0 Then
           LastValue = Col9
           Exit Function
   End if
   ' Continue with the conditional statements until you have all columns defined
   If Col# <> 0 then
          LastValue = Col#
          Exit Function
   End if
 
End function
 

[n8kindt]

Let Assume for the moment since I don't have field names that we're going to call each column by the name of Col and the rows as records.

The way that you'd need to write the query is to do the following:

SELECT LastValue(Col1,nz(Col2,0),nz(Col3,0),nz(Col4,0),nz (Col5,0),nz(Col6,0),nz(Col7,0),nz(Col8,0),nz(Col9, 0),nz(Col10,0) as Col11 FROM {TableName}

Create a query with the following SELECT Statement.

Notice that I've created a function called LastValue(). You would need to put in the parathasise () the column names for each one of them.

Copy and Paste the following into a module

Open the Visual Basic Code Editor ALT-F11

Select Insert -> Module

 
Function LastValue(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10)
 
    If Col10 <> 0 then
           LastValue = Col10
           Exit Function
   End if
   If Col9 <> 0 Then
           LastValue = Col9
           Exit Function
   End if
   ' Continue with the conditional statements until you have all columns defined
   If Col# <> 0 then
          LastValue = Col#
          Exit Function
   End if
 
End function
 

this is exactly what i need. thanks so much. wow, i can't be thankful enough for the help i get from this site. it is truly outstanding!!

cheers,
nate

[n8kindt]

this is exactly what i need. thanks so much. wow, i can't be thankful enough for the help i get from this site. it is truly outstanding!!

cheers,
nate

everything works great. however, after viewing the query in datasheet mode and then pulling it back into design, there is some serious lagging. it flickers in and out of being responsive anywhere from 5-100 (not even kidding) times.

it only happens when my function is used in the query. here's my code:

SQL

SELECT [0].ID, [1].ID, [2].ID, [3].ID, [4].ID, [5].ID, [6].ID, [7].ID, [8].ID, [9].ID, [10].ID, [11].ID, ColumnEnd([0.ID],nz([1.ID],0),nz([2.ID],0),nz([3.ID],0),nz([4.ID],0),nz([5.ID],0),nz([6.ID],0),nz([7.ID],0),nz([8.ID],0),nz([9.ID],0),nz([10.ID],0),nz([11.ID],0)) AS StoredFC
FROM (((Table1Info AS 2 RIGHT JOIN Table1Info AS 1 ON [2].[Recruited By] = [1].ID) RIGHT JOIN Table1Info AS 0 ON [1].[Recruited By] = [0].ID) LEFT JOIN (((((((Table1Info AS 10 RIGHT JOIN Table1Info AS 9 ON [10].[Recruited By] = [9].ID) RIGHT JOIN Table1Info AS 8 ON [9].[Recruited By] = [8].ID) RIGHT JOIN Table1Info AS 7 ON [8].[Recruited By] = [7].ID) RIGHT JOIN Table1Info AS 6 ON [7].[Recruited By] = [6].ID) RIGHT JOIN Table1Info AS 5 ON [6].[Recruited By] = [5].ID) RIGHT JOIN Table1Info AS 4 ON [5].[Recruited By] = [4].ID) RIGHT JOIN Table1Info AS 3 ON [4].[Recruited By] = [3].ID) ON [2].ID = [3].[Recruited By]) LEFT JOIN Table1Info AS 11 ON [10].ID = [11].[Recruited By]
WHERE ((([11].ID) Is Null));

VBA

Option Compare Database
 
Function ColumnEnd(Col0, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11) As Integer
 
 
 
 
 
 
 
          If Col11 <> 0 Then
 
                 ColumnEnd = Col11
 
                 Exit Function
 
         End If
 
         If Col10 <> 0 Then
 
                 ColumnEnd = Col10
 
                 Exit Function
 
         End If
 
                  If Col9 <> 0 Then
 
                 ColumnEnd = Col9
 
                 Exit Function
 
         End If
 
                  If Col8 <> 0 Then
 
                 ColumnEnd = Col8
 
                 Exit Function
 
         End If
 
                  If Col7 <> 0 Then
 
                 ColumnEnd = Col7
 
                 Exit Function
 
         End If
 
                  If Col6 <> 0 Then
 
                 ColumnEnd = Col6
 
                 Exit Function
 
         End If
 
                  If Col5 <> 0 Then
 
                 ColumnEnd = Col5
 
                 Exit Function
 
         End If
 
                  If Col4 <> 0 Then
 
                 ColumnEnd = Col4
 
                 Exit Function
 
         End If
 
                  If Col3 <> 0 Then
 
                 ColumnEnd = Col3
 
                 Exit Function
 
         End If
 
                  If Col2 <> 0 Then
 
                 ColumnEnd = Col2
 
                 Exit Function
 
         End If
 
                  If Col1 <> 0 Then
 
                 ColumnEnd = Col1
 
                 Exit Function
 
         End If
 
                  If Col0 <> 0 Then
 
                 ColumnEnd = ColBase
 
                 Exit Function
 
         End If
 
 
End Function
 

its very strange considering, i can open it in design view and it works just fine. it only happens after viewing the query and pulling it back into design.

anybody see where the problem is? also, i am using access 2007. hopefully this isn't a 2007 bug.