Simple Code question!!!

[Guest]

I have a form in datasheet view. I got three field, [Particular],
[Expenditure], and[Balance].

I need the code something like this.
Current Balance = previous balance - expenditure

What is the code for "previous balance" ?

I actually doing "petty cash" form

example:
Today balance = yesterday balance - expenditure
$100 = $125 - $25

In short, what is the code to select previous record from a field?

Any solutions?

Thanks in advance.

Kennykee

 

[Alex Dybenko]

You can use subquery for example, once you have a field with records order
(date, number)

so your form's recordsource can look like:

Select MyTable.* , (Select top 1 balance from MyTable as t Where t.EntryNo <
MyTable.EntryNo order by EntryNo Desc) as previous balance from MyTable

 

[Guest]

Thanks Alex for the method
ermmm.....I quite not understand
I am new to programming so can i have simpler method?

For example,
VBA code to select last record from a field.
Assume field is [Balance]

Public Form_Current()
[Balance] =...Code For Previous...([Balance]) - [Expenditure]
End Sub

Is there any code for ".....Code For Previous....."

Thanks Much For The Help.

Kennykee

You can use subquery for example, once you have a field with records order
(date, number)

so your form's recordsource can look like:

Select MyTable.* , (Select top 1 balance from MyTable as t Where t.EntryNo <
MyTable.EntryNo order by EntryNo Desc) as previous balance from MyTable


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

I have a form in datasheet view. I got three field, [Particular],
[Expenditure], and[Balance].

I need the code something like this.
Current Balance = previous balance - expenditure

What is the code for "previous balance" ?

I actually doing "petty cash" form

example:
Today balance = yesterday balance - expenditure
$100 = $125 - $25

In short, what is the code to select previous record from a field?

Any solutions?

Thanks in advance.

Kennykee

 

[Alex Dybenko]

Hi,
then try to use DLast function
so if you have a table MyTable, with field Balance and you have a field
DocNo (or some other, which shows what record is previous), showed on a
form - then expression will be:
=dlast("balance", "mytable", "DocNo < " & me.DocNo)

HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex for the method
ermmm.....I quite not understand
I am new to programming so can i have simpler method?

For example,
VBA code to select last record from a field.
Assume field is [Balance]

Public Form_Current()
[Balance] =...Code For Previous...([Balance]) - [Expenditure]
End Sub

Is there any code for ".....Code For Previous....."

Thanks Much For The Help.

Kennykee

You can use subquery for example, once you have a field with records
order
(date, number)

so your form's recordsource can look like:

Select MyTable.* , (Select top 1 balance from MyTable as t Where
t.EntryNo <
MyTable.EntryNo order by EntryNo Desc) as previous balance from MyTable


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

I have a form in datasheet view. I got three field, [Particular],
[Expenditure], and[Balance].

I need the code something like this.
Current Balance = previous balance - expenditure

What is the code for "previous balance" ?

I actually doing "petty cash" form

example:
Today balance = yesterday balance - expenditure
$100 = $125 - $25

In short, what is the code to select previous record from a field?

Any solutions?

Thanks in advance.

Kennykee

 

[Guest]

Thanks Alex,
I tried to use Dlast and Dlookup in query but wont work

How to use expression to seek for previous value in query?
I created a field,
ID1 : ([ID]-1)

So in the same line, I got ID number and previous ID number
I tried lookup("Balance","PettyCash",[ID]=[ID1])

But came out with empty record

Any ideas?

Thanks in advance

Kennykee

Hi,
then try to use DLast function
so if you have a table MyTable, with field Balance and you have a field
DocNo (or some other, which shows what record is previous), showed on a
form - then expression will be:
=dlast("balance", "mytable", "DocNo < " & me.DocNo)

HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex for the method
ermmm.....I quite not understand
I am new to programming so can i have simpler method?

For example,
VBA code to select last record from a field.
Assume field is [Balance]

Public Form_Current()
[Balance] =...Code For Previous...([Balance]) - [Expenditure]
End Sub

Is there any code for ".....Code For Previous....."

Thanks Much For The Help.

Kennykee

You can use subquery for example, once you have a field with records
order
(date, number)

so your form's recordsource can look like:

Select MyTable.* , (Select top 1 balance from MyTable as t Where
t.EntryNo <
MyTable.EntryNo order by EntryNo Desc) as previous balance from MyTable


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



I have a form in datasheet view. I got three field, [Particular],
[Expenditure], and[Balance].

I need the code something like this.
Current Balance = previous balance - expenditure

What is the code for "previous balance" ?

I actually doing "petty cash" form

example:
Today balance = yesterday balance - expenditure
$100 = $125 - $25

In short, what is the code to select previous record from a field?

Any solutions?

Thanks in advance.

Kennykee

 

[Alex Dybenko]

Hi,
i think for query you can write:

DLookup("Balance","PettyCash","[ID]=" & ([ID]-1))

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex,
I tried to use Dlast and Dlookup in query but wont work

How to use expression to seek for previous value in query?
I created a field,
ID1 : ([ID]-1)

So in the same line, I got ID number and previous ID number
I tried lookup("Balance","PettyCash",[ID]=[ID1])

But came out with empty record

Any ideas?

Thanks in advance

Kennykee

Hi,
then try to use DLast function
so if you have a table MyTable, with field Balance and you have a field
DocNo (or some other, which shows what record is previous), showed on a
form - then expression will be:
=dlast("balance", "mytable", "DocNo < " & me.DocNo)

HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex for the method
ermmm.....I quite not understand
I am new to programming so can i have simpler method?

For example,
VBA code to select last record from a field.
Assume field is [Balance]

Public Form_Current()
[Balance] =...Code For Previous...([Balance]) - [Expenditure]
End Sub

Is there any code for ".....Code For Previous....."

Thanks Much For The Help.

Kennykee



:

You can use subquery for example, once you have a field with records
order
(date, number)

so your form's recordsource can look like:

Select MyTable.* , (Select top 1 balance from MyTable as t Where
t.EntryNo <
MyTable.EntryNo order by EntryNo Desc) as previous balance from
MyTable


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



I have a form in datasheet view. I got three field, [Particular],
[Expenditure], and[Balance].

I need the code something like this.
Current Balance = previous balance - expenditure

What is the code for "previous balance" ?

I actually doing "petty cash" form

example:
Today balance = yesterday balance - expenditure
$100 = $125 - $25

In short, what is the code to select previous record from a field?

Any solutions?

Thanks in advance.

Kennykee

 

[Guest]

Alex
Thankssssssssssssssssssssssssssss * 1000000000000000000000

Thanks Alex, you finally solve problems that crack my head
Thanks again

Kennykee

Hi,
i think for query you can write:

DLookup("Balance","PettyCash","[ID]=" & ([ID]-1))

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex,
I tried to use Dlast and Dlookup in query but wont work

How to use expression to seek for previous value in query?
I created a field,
ID1 : ([ID]-1)

So in the same line, I got ID number and previous ID number
I tried lookup("Balance","PettyCash",[ID]=[ID1])

But came out with empty record

Any ideas?

Thanks in advance

Kennykee

Hi,
then try to use DLast function
so if you have a table MyTable, with field Balance and you have a field
DocNo (or some other, which shows what record is previous), showed on a
form - then expression will be:
=dlast("balance", "mytable", "DocNo < " & me.DocNo)

HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Thanks Alex for the method
ermmm.....I quite not understand
I am new to programming so can i have simpler method?

For example,
VBA code to select last record from a field.
Assume field is [Balance]

Public Form_Current()
[Balance] =...Code For Previous...([Balance]) - [Expenditure]
End Sub

Is there any code for ".....Code For Previous....."

Thanks Much For The Help.

Kennykee



:

You can use subquery for example, once you have a field with records
order
(date, number)

so your form's recordsource can look like:

Select MyTable.* , (Select top 1 balance from MyTable as t Where
t.EntryNo <
MyTable.EntryNo order by EntryNo Desc) as previous balance from
MyTable


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



I have a form in datasheet view. I got three field, [Particular],
[Expenditure], and[Balance].

I need the code something like this.
Current Balance = previous balance - expenditure

What is the code for "previous balance" ?

I actually doing "petty cash" form

example:
Today balance = yesterday balance - expenditure
$100 = $125 - $25

In short, what is the code to select previous record from a field?

Any solutions?

Thanks in advance.

Kennykee