Need to modify a module in order to stop variance in results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

twisty1980 - 24 May 2007 09:27 GMT

Hi,

I have the following table:
code Amount
a 10
15
20
b 5
10

which I need to show as follows:
code Amount
a 10
a 15
a 20
b 5
b 10

Basically filling in the null cells below with the value from above.

Currently I am using a module called from a query both codes are below:

Module:

Option Compare Database

Global OldValue As String
Function GetOldValue(NewValue As Variant)
If Not IsNull(NewValue) Then
OldValue = NewValue
End If
GetOldValue = OldValue
End Function

SQL Query:

SELECT GetOldValue([code]) AS NewCode, Table1.* INTO Table2
FROM Table1;

The problem I am having is that the module is dynamic so the reult is not
always the same. Is there any way of eliminating the chance that the wrong
code will be assigned to the wrong amount?

Reply to this Message

Ofer Cohen - 24 May 2007 10:40 GMT

I assume you ment that the query is dynamic, as I noted in the first post,
you need the query to come up with the same order every time.

Sort by DateField , Counter etc

How can we tell where to put "a" or "b" in the code, if not by the order of
the data in the query?

Signature

Good Luck
BS"D

> Hi,
>
[quoted text clipped - 38 lines]
> always the same. Is there any way of eliminating the chance that the wrong
> code will be assigned to the wrong amount?

Reply to this Message