Why are numbers changing after query has run???

[Steve]

Hello,

I've got a query that has a column where I add an incrementing value. The
expression in that column looks like this:

secnum: IIf([oemmarkets].[printorder]=1 And
[marketapplications].[printorder]=1,clear([oemmarkets].[printorder]),nextnum
([oemmarkets.printorder]))

Where oemmarkets.printorder and marketapplications.printorder are other
columns in the query.

The code for clear() and nextnum() is:

Dim intNum As Integer

Public Function Clear(varV As Variant) As Integer
intNum = 1
Clear = intNum
End Function

Public Function NextNum(varV As Variant) As Integer
intNum = intNum + 1
NextNum = intNum
End Function

The purpose of all this is to add an incrementing number to records in the
query. When I run the query, it works fine, and I get what I want. If I use
this query to feed another query, I end up with goofy numbers in the secnum
column in the second query. Also, I have a report that is driven by this
query, and when I put the secnum variable in the report as hidden text box,
sub reports referencing that text box then give goofy values.

Can anyone explain this phenomenon? It's driving me crazy! I even tried
making the query maketable, and then using the table to feed reports and
queries with the SAME RESULTS. I thought maybe secnum or nextnum were
reserved words but they aren't.

Any ideas???

Thanks!

 

[Guest]

I think I see what you are doing here - I have used a similar technique to generate unique ordinal numbers against primary keys codes.

I suggest

1) Make sure the query has an explicit order by clause (sort ascending in the query designer) that orders on the appropriate field or expression - this is important for reproducabiity !

2) I would also suggest passing both oemmarkets.printorder and marketapplications.printorder as additional 'dummy' parameters to your functions.

Hope this may help !

columns in the query.

Hello,

I've got a query that has a column where I add an incrementing value. The
expression in that column looks like this:

secnum: IIf([oemmarkets].[printorder]=1 And
[marketapplications].[printorder]=1,clear([oemmarkets].[printorder]),nextnum
([oemmarkets.printorder]))

Where oemmarkets.printorder and marketapplications.printorder are other
columns in the query.

The code for clear() and nextnum() is:

Dim intNum As Integer

Public Function Clear(varV As Variant) As Integer
intNum = 1
Clear = intNum
End Function

Public Function NextNum(varV As Variant) As Integer
intNum = intNum + 1
NextNum = intNum
End Function

The purpose of all this is to add an incrementing number to records in the
query. When I run the query, it works fine, and I get what I want. If I use
this query to feed another query, I end up with goofy numbers in the secnum
column in the second query. Also, I have a report that is driven by this
query, and when I put the secnum variable in the report as hidden text box,
sub reports referencing that text box then give goofy values.

Can anyone explain this phenomenon? It's driving me crazy! I even tried
making the query maketable, and then using the table to feed reports and
queries with the SAME RESULTS. I thought maybe secnum or nextnum were
reserved words but they aren't.

Any ideas???

Thanks!

 

[Van T. Dinh]

I am not sure of the symptoms but there is a problem with
function NextNum: where do you expect the "input"
intNumber comes from? You haven't passed the value to the
function in the argument list of the function.

Suggest you use "Option Explicit" in your modules which
will pick up these problems.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Hello,

I've got a query that has a column where I add an incrementing value. The
expression in that column looks like this:

secnum: IIf([oemmarkets].[printorder]=1 And
[marketapplications].[printorder]=1,clear([oemmarkets]. [printorder]),nextnum
([oemmarkets.printorder]))

Where oemmarkets.printorder and

marketapplications.printorder are other