Help with Function for a Query Field 1

[pdldavis]

Hi, I posted this in the query & sql thread and got an answer but still need a little help.

I need to create an expression in a query field where
where the first value in the field = '0' and then all other rows for that field are '1':

Field1
0
1
1
1
1
1

I was provided with the following and it doesn't seem to do what I need it to do or I don't know how to use it:

Set up a global variable and use it in a public function:

Public FirstRecord As Boolean

Set it to True before you start and then use this function:

Public Function ReturnValue() As Integer

If FirstRecord = True Then
ReturnValue = 0
FirstRecord = False
Else
ReturnValue = 0
End If

End Function

In your query add the field:

Field1: ReturnValue()

Usually a 'True' means a'value of -1 so when I run this I get all 0's or -1's. I can't show the first row in the field as '0' and the rest of the rows as '1'

Any help would be appreciated.

Thanks, Dan

 

[mp9]

You need to add two, not one, expression fields to your query, as follows:

RowNum: (Select Count (*) FROM [YourTableNameHere] as Temp 
WHERE [Temp].[NumericIDFieldNameHere] < [YourTableNameHere].[NumericIDFieldNameHere])+1


Field1: -1 * (RowNum=1)

Please note I haven't tested this!

http://www22.brinkster.com/accessory/

 

[pdldavis]

That got me what I needed. Thanks!