help with a running balance...

[Brook]

good day all,

I am trying to create a running balance for my checking account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it? each of my
records has a disctinct recordid.

can anyone help?

Thanks,

Brook

 

[Allen Browne]

Try typing this into a fresh column in the Field row in query design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " & [RecordID])
Substitute your table name for Table1, and your primary key field for
RecordID.

That should work unless you filter or sort the fields differently.

Note that DSum() will be slow to execute on every line of your query.

 

[Brook]

hello Allen,

I tried typing the following into a new column, but am getting no results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])


Thanks,

Brook

Try typing this into a fresh column in the Field row in query design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " & [RecordID])
Substitute your table name for Table1, and your primary key field for
RecordID.

That should work unless you filter or sort the fields differently.



Note that DSum() will be slow to execute on every line of your query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a running balance for my checking account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it? each of my
records has a disctinct recordid.

can anyone help?

Thanks,

Brook

 

[Allen Browne]

If you open your table in design view, what data type are the 2 fields:
ID
Debit_Credit
?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hello Allen,

I tried typing the following into a new column, but am getting no results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])


Thanks,

Brook

Try typing this into a fresh column in the Field row in query design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " & [RecordID])
Substitute your table name for Table1, and your primary key field for
RecordID.

That should work unless you filter or sort the fields differently.

Note that DSum() will be slow to execute on every line of your query.

I am trying to create a running balance for my checking account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it? each of
my
records has a disctinct recordid.

 

[Brook]

Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

If you open your table in design view, what data type are the 2 fields:
ID
Debit_Credit
?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hello Allen,

I tried typing the following into a new column, but am getting no results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])


Thanks,

Brook

Try typing this into a fresh column in the Field row in query design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " & [RecordID])
Substitute your table name for Table1, and your primary key field for
RecordID.

That should work unless you filter or sort the fields differently.

Note that DSum() will be slow to execute on every line of your query.


I am trying to create a running balance for my checking account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it? each of
my
records has a disctinct recordid.

 

[Allen Browne]

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd argument is
correctly formed for a Number type field that can't be null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

If you open your table in design view, what data type are the 2 fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am getting no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in query design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " & [RecordID])
Substitute your table name for Table1, and your primary key field for
RecordID.

That should work unless you filter or sort the fields differently.

Note that DSum() will be slow to execute on every line of your query.


I am trying to create a running balance for my checking account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it? each
of
my
records has a disctinct recordid.

 

[Brook]

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is calculated as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd argument is
correctly formed for a Number type field that can't be null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

If you open your table in design view, what data type are the 2 fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am getting no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in query design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " & [RecordID])
Substitute your table name for Table1, and your primary key field for
RecordID.

That should work unless you filter or sort the fields differently.

Note that DSum() will be slow to execute on every line of your query.


I am trying to create a running balance for my checking account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it? each
of
my
records has a disctinct recordid.

 

[Allen Browne]

Yes, it does matter, Brook.

The previous request about the Debit_Credit field was so as to determine
whether JET might be misinterpreting the data type of the field. If it is
not a Currency field in a table as you reported, but a calculated query
field, this issue does apply. For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

However, the expression itself could also be the problem. You are comparing
Debit to True. If Debit is a Yes/No field, that makes sense, but the rest of
the expression does not. If Debit is a Currency field, I'm not sure why you
would only want the value of the Debit field when it happened to be -$1.00.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is calculated
as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd argument is
correctly formed for a Number type field that can't be null.

Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

:

If you open your table in design view, what data type are the 2
fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am getting no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in query
design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " & [RecordID])
Substitute your table name for Table1, and your primary key field
for
RecordID.

That should work unless you filter or sort the fields differently.

Note that DSum() will be slow to execute on every line of your
query.


I am trying to create a running balance for my checking account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it?
each
of
my
records has a disctinct recordid.

 

[Brook]

Allen,

The reason for the code that I posted, is that I created a single column
for all debits and credits from the individual fields "Debit" & "Credit",
thinking that it would be easier to create a running balance, but I guess I
was mistaken.

Would it be easier to create a "running balance" from my individual Debit
& Credit Fields?

Thanks,

Brook

Yes, it does matter, Brook.

The previous request about the Debit_Credit field was so as to determine
whether JET might be misinterpreting the data type of the field. If it is
not a Currency field in a table as you reported, but a calculated query
field, this issue does apply. For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

However, the expression itself could also be the problem. You are comparing
Debit to True. If Debit is a Yes/No field, that makes sense, but the rest of
the expression does not. If Debit is a Currency field, I'm not sure why you
would only want the value of the Debit field when it happened to be -$1.00.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is calculated
as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd argument is
correctly formed for a Number type field that can't be null.


Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

:

If you open your table in design view, what data type are the 2
fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am getting no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in query
design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " & [RecordID])
Substitute your table name for Table1, and your primary key field
for
RecordID.

That should work unless you filter or sort the fields differently.

Note that DSum() will be slow to execute on every line of your
query.


I am trying to create a running balance for my checking account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it?
each
of
my
records has a disctinct recordid.

 

[Allen Browne]

You will need to sort out the details, because I'm not clear if the Debit
values are negative, or whether the records can have both a Credit or Debit,
or whether Nulls are allowed in either field, but you could try something
along these lines:

Debit_Credit: CCur(Nz([Credit],0) + Nz([Debit],0))

Nz() deals with the cases where one field is null.
CCur() converts the result to Currency so JET can't get it wrong.
The plus needs to be a minus if the Debit column values are not negative.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The reason for the code that I posted, is that I created a single column
for all debits and credits from the individual fields "Debit" & "Credit",
thinking that it would be easier to create a running balance, but I guess
I
was mistaken.

Would it be easier to create a "running balance" from my individual
Debit
& Credit Fields?

Thanks,

Brook

Yes, it does matter, Brook.

The previous request about the Debit_Credit field was so as to determine
whether JET might be misinterpreting the data type of the field. If it is
not a Currency field in a table as you reported, but a calculated query
field, this issue does apply. For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

However, the expression itself could also be the problem. You are
comparing
Debit to True. If Debit is a Yes/No field, that makes sense, but the rest
of
the expression does not. If Debit is a Currency field, I'm not sure why
you
would only want the value of the Debit field when it happened to
be -$1.00.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is
calculated
as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

:

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd argument is
correctly formed for a Number type field that can't be null.


Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

:

If you open your table in design view, what data type are the 2
fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am getting no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " &
[ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in query
design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " &
[RecordID])
Substitute your table name for Table1, and your primary key
field
for
RecordID.

That should work unless you filter or sort the fields
differently.

Note that DSum() will be slow to execute on every line of your
query.


I am trying to create a running balance for my checking
account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it?
each
of
my
records has a disctinct recordid.

 

[Brook]

Hello Allen,

Debit Column: Values are Negative, and the column can contain Null
Values and contains only Debits
Credit Column: Values are Positive, and the column can contain Null
Values and contains only Credits

I did create a new column and added the code that you provided, and what
it does is create a new column with all my debits and credits only no running
balance? Is that what is should do?

Or do I need to added the column Expr1:
DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])

Thanks very much for all your help & your patience!

Brook

You will need to sort out the details, because I'm not clear if the Debit
values are negative, or whether the records can have both a Credit or Debit,
or whether Nulls are allowed in either field, but you could try something
along these lines:

Debit_Credit: CCur(Nz([Credit],0) + Nz([Debit],0))

Nz() deals with the cases where one field is null.
CCur() converts the result to Currency so JET can't get it wrong.
The plus needs to be a minus if the Debit column values are not negative.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The reason for the code that I posted, is that I created a single column
for all debits and credits from the individual fields "Debit" & "Credit",
thinking that it would be easier to create a running balance, but I guess
I
was mistaken.

Would it be easier to create a "running balance" from my individual
Debit
& Credit Fields?

Thanks,

Brook

Yes, it does matter, Brook.

The previous request about the Debit_Credit field was so as to determine
whether JET might be misinterpreting the data type of the field. If it is
not a Currency field in a table as you reported, but a calculated query
field, this issue does apply. For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

However, the expression itself could also be the problem. You are
comparing
Debit to True. If Debit is a Yes/No field, that makes sense, but the rest
of
the expression does not. If Debit is a Currency field, I'm not sure why
you
would only want the value of the Debit field when it happened to
be -$1.00.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is
calculated
as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

:

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd argument is
correctly formed for a Number type field that can't be null.


Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

:

If you open your table in design view, what data type are the 2
fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am getting no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " &
[ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in query
design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " &
[RecordID])
Substitute your table name for Table1, and your primary key
field
for
RecordID.

That should work unless you filter or sort the fields
differently.

Note that DSum() will be slow to execute on every line of your
query.


I am trying to create a running balance for my checking
account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use it?
each
of
my
records has a disctinct recordid.

 

[Allen Browne]

Yes, Brook, that's what it should do.

Now that you have that working, you can go back to the beginning of this
thread, and use the working expression in the first argument of DSum(), such
as:

DSum("CCur(Nz([Credit],0) + Nz([Debit],0))", "tblcheckingaccount", "[ID] <=
" & [ID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Debit Column: Values are Negative, and the column can contain Null
Values and contains only Debits
Credit Column: Values are Positive, and the column can contain Null
Values and contains only Credits

I did create a new column and added the code that you provided, and
what
it does is create a new column with all my debits and credits only no
running
balance? Is that what is should do?

Or do I need to added the column Expr1:
DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])

Thanks very much for all your help & your patience!

Brook

You will need to sort out the details, because I'm not clear if the Debit
values are negative, or whether the records can have both a Credit or
Debit,
or whether Nulls are allowed in either field, but you could try something
along these lines:

Debit_Credit: CCur(Nz([Credit],0) + Nz([Debit],0))

Nz() deals with the cases where one field is null.
CCur() converts the result to Currency so JET can't get it wrong.
The plus needs to be a minus if the Debit column values are not negative.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The reason for the code that I posted, is that I created a single
column
for all debits and credits from the individual fields "Debit" &
"Credit",
thinking that it would be easier to create a running balance, but I
guess
I
was mistaken.

Would it be easier to create a "running balance" from my individual
Debit
& Credit Fields?

Thanks,

Brook

:

Yes, it does matter, Brook.

The previous request about the Debit_Credit field was so as to
determine
whether JET might be misinterpreting the data type of the field. If it
is
not a Currency field in a table as you reported, but a calculated
query
field, this issue does apply. For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

However, the expression itself could also be the problem. You are
comparing
Debit to True. If Debit is a Yes/No field, that makes sense, but the
rest
of
the expression does not. If Debit is a Currency field, I'm not sure
why
you
would only want the value of the Debit field when it happened to
be -$1.00.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is
calculated
as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

:

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd argument
is
correctly formed for a Number type field that can't be null.


Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

:

If you open your table in design view, what data type are the 2
fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am getting
no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " &
[ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in query
design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " &
[RecordID])
Substitute your table name for Table1, and your primary key
field
for
RecordID.

That should work unless you filter or sort the fields
differently.

Note that DSum() will be slow to execute on every line of
your
query.


I am trying to create a running balance for my checking
account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use
it?
each
of
my
records has a disctinct recordid.

 

[Brook]

Allen,

So I added the DSUM code, as the start of the thread, but I am getting
very "strange" calculations.

here is my expression:

Bal: DSum("CCur(Nz([Credit],0) +
Nz([Debit],0))","tblcheckingaccount","[ID] <= " & [ID])

Here are some of the results:

ID Debit Credit Debit_Credit Bal
397 $0.00 $0.00 $0.00 -3944.42
356 ($20.99) $0.00 ($20.99) -22416
302 ($157.49) $0.00 ($157.49) -18264.11
326 ($95.52) $0.00 ($95.52) -20992.6
348 ($30.36) ($30.36) -22212.71
357 ($19.72) ($19.72) -22435.72
342 ($41.99) ($41.99) -21999.31
387 $100.00 $100.00 -22508.88
396 $10,100.00 $10,100.00 -3944.42
341 ($41.99) ($41.99) -21957.32
283 ($1,000.00) ($1,000.00) -9802.44
296 ($291.23) ($291.23) -17014.81
392 $1,000.00 $1,000.00 -20495.42
333 ($62.50) ($62.50) -21539.97
328 ($92.83) ($92.83) -21179.92
279 ($2,200.00) ($2,200.00) -3324.57


Any Ideas?

Does the field / column alignment make a difference?

Thanks,

Brook

Yes, Brook, that's what it should do.

Now that you have that working, you can go back to the beginning of this
thread, and use the working expression in the first argument of DSum(), such
as:

DSum("CCur(Nz([Credit],0) + Nz([Debit],0))", "tblcheckingaccount", "[ID] <=
" & [ID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Debit Column: Values are Negative, and the column can contain Null
Values and contains only Debits
Credit Column: Values are Positive, and the column can contain Null
Values and contains only Credits

I did create a new column and added the code that you provided, and
what
it does is create a new column with all my debits and credits only no
running
balance? Is that what is should do?

Or do I need to added the column Expr1:
DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])

Thanks very much for all your help & your patience!

Brook

You will need to sort out the details, because I'm not clear if the Debit
values are negative, or whether the records can have both a Credit or
Debit,
or whether Nulls are allowed in either field, but you could try something
along these lines:

Debit_Credit: CCur(Nz([Credit],0) + Nz([Debit],0))

Nz() deals with the cases where one field is null.
CCur() converts the result to Currency so JET can't get it wrong.
The plus needs to be a minus if the Debit column values are not negative.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


The reason for the code that I posted, is that I created a single
column
for all debits and credits from the individual fields "Debit" &
"Credit",
thinking that it would be easier to create a running balance, but I
guess
I
was mistaken.

Would it be easier to create a "running balance" from my individual
Debit
& Credit Fields?

Thanks,

Brook

:

Yes, it does matter, Brook.

The previous request about the Debit_Credit field was so as to
determine
whether JET might be misinterpreting the data type of the field. If it
is
not a Currency field in a table as you reported, but a calculated
query
field, this issue does apply. For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

However, the expression itself could also be the problem. You are
comparing
Debit to True. If Debit is a Yes/No field, that makes sense, but the
rest
of
the expression does not. If Debit is a Currency field, I'm not sure
why
you
would only want the value of the Debit field when it happened to
be -$1.00.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is
calculated
as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

:

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd argument
is
correctly formed for a Number type field that can't be null.


Allen,

ID is an autonumber field and Debit_Credit is a currency field.

Thanks for the response...

Brook

:

If you open your table in design view, what data type are the 2
fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am getting
no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= " &
[ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in query
design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " &
[RecordID])
Substitute your table name for Table1, and your primary key
field
for
RecordID.

That should work unless you filter or sort the fields
differently.

Note that DSum() will be slow to execute on every line of
your
query.


I am trying to create a running balance for my checking
account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to use
it?
each
of
my
records has a disctinct recordid.

 

[Allen Browne]

Brook, back at the beginning of this thread, we pointed out that the DSum()
expression would only work if you sorted the records on the same field the
DSum() criteria uses.

I need to let you take this from here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

So I added the DSUM code, as the start of the thread, but I am getting
very "strange" calculations.

here is my expression:

Bal: DSum("CCur(Nz([Credit],0) +
Nz([Debit],0))","tblcheckingaccount","[ID] <= " & [ID])

Here are some of the results:

ID Debit Credit Debit_Credit Bal
397 $0.00 $0.00 $0.00 -3944.42
356 ($20.99) $0.00 ($20.99) -22416
302 ($157.49) $0.00 ($157.49) -18264.11
326 ($95.52) $0.00 ($95.52) -20992.6
348 ($30.36) ($30.36) -22212.71
357 ($19.72) ($19.72) -22435.72
342 ($41.99) ($41.99) -21999.31
387 $100.00 $100.00 -22508.88
396 $10,100.00 $10,100.00 -3944.42
341 ($41.99) ($41.99) -21957.32
283 ($1,000.00) ($1,000.00) -9802.44
296 ($291.23) ($291.23) -17014.81
392 $1,000.00 $1,000.00 -20495.42
333 ($62.50) ($62.50) -21539.97
328 ($92.83) ($92.83) -21179.92
279 ($2,200.00) ($2,200.00) -3324.57


Any Ideas?

Does the field / column alignment make a difference?

Thanks,

Brook

Yes, Brook, that's what it should do.

Now that you have that working, you can go back to the beginning of this
thread, and use the working expression in the first argument of DSum(),
such
as:

DSum("CCur(Nz([Credit],0) + Nz([Debit],0))", "tblcheckingaccount", "[ID]
<=
" & [ID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Debit Column: Values are Negative, and the column can contain Null
Values and contains only Debits
Credit Column: Values are Positive, and the column can contain Null
Values and contains only Credits

I did create a new column and added the code that you provided, and
what
it does is create a new column with all my debits and credits only no
running
balance? Is that what is should do?

Or do I need to added the column Expr1:
DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])

Thanks very much for all your help & your patience!

Brook

:

You will need to sort out the details, because I'm not clear if the
Debit
values are negative, or whether the records can have both a Credit or
Debit,
or whether Nulls are allowed in either field, but you could try
something
along these lines:

Debit_Credit: CCur(Nz([Credit],0) + Nz([Debit],0))

Nz() deals with the cases where one field is null.
CCur() converts the result to Currency so JET can't get it wrong.
The plus needs to be a minus if the Debit column values are not
negative.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


The reason for the code that I posted, is that I created a single
column
for all debits and credits from the individual fields "Debit" &
"Credit",
thinking that it would be easier to create a running balance, but I
guess
I
was mistaken.

Would it be easier to create a "running balance" from my
individual
Debit
& Credit Fields?

Thanks,

Brook

:

Yes, it does matter, Brook.

The previous request about the Debit_Credit field was so as to
determine
whether JET might be misinterpreting the data type of the field. If
it
is
not a Currency field in a table as you reported, but a calculated
query
field, this issue does apply. For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

However, the expression itself could also be the problem. You are
comparing
Debit to True. If Debit is a Yes/No field, that makes sense, but
the
rest
of
the expression does not. If Debit is a Currency field, I'm not sure
why
you
would only want the value of the Debit field when it happened to
be -$1.00.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is
calculated
as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

:

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd
argument
is
correctly formed for a Number type field that can't be null.


Allen,

ID is an autonumber field and Debit_Credit is a currency
field.

Thanks for the response...

Brook

:

If you open your table in design view, what data type are the
2
fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am
getting
no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= "
&
[ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in
query
design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " &
[RecordID])
Substitute your table name for Table1, and your primary
key
field
for
RecordID.

That should work unless you filter or sort the fields
differently.

Note that DSum() will be slow to execute on every line of
your
query.


I am trying to create a running balance for my checking
account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to
use
it?
each
of
my
records has a disctinct recordid.

 

[Brook]

Sorry....

Thanks for your help!

Brook

Brook, back at the beginning of this thread, we pointed out that the DSum()
expression would only work if you sorted the records on the same field the
DSum() criteria uses.

I need to let you take this from here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

So I added the DSUM code, as the start of the thread, but I am getting
very "strange" calculations.

here is my expression:

Bal: DSum("CCur(Nz([Credit],0) +
Nz([Debit],0))","tblcheckingaccount","[ID] <= " & [ID])

Here are some of the results:

ID Debit Credit Debit_Credit Bal
397 $0.00 $0.00 $0.00 -3944.42
356 ($20.99) $0.00 ($20.99) -22416
302 ($157.49) $0.00 ($157.49) -18264.11
326 ($95.52) $0.00 ($95.52) -20992.6
348 ($30.36) ($30.36) -22212.71
357 ($19.72) ($19.72) -22435.72
342 ($41.99) ($41.99) -21999.31
387 $100.00 $100.00 -22508.88
396 $10,100.00 $10,100.00 -3944.42
341 ($41.99) ($41.99) -21957.32
283 ($1,000.00) ($1,000.00) -9802.44
296 ($291.23) ($291.23) -17014.81
392 $1,000.00 $1,000.00 -20495.42
333 ($62.50) ($62.50) -21539.97
328 ($92.83) ($92.83) -21179.92
279 ($2,200.00) ($2,200.00) -3324.57


Any Ideas?

Does the field / column alignment make a difference?

Thanks,

Brook

Yes, Brook, that's what it should do.

Now that you have that working, you can go back to the beginning of this
thread, and use the working expression in the first argument of DSum(),
such
as:

DSum("CCur(Nz([Credit],0) + Nz([Debit],0))", "tblcheckingaccount", "[ID]
<=
" & [ID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Debit Column: Values are Negative, and the column can contain Null
Values and contains only Debits
Credit Column: Values are Positive, and the column can contain Null
Values and contains only Credits

I did create a new column and added the code that you provided, and
what
it does is create a new column with all my debits and credits only no
running
balance? Is that what is should do?

Or do I need to added the column Expr1:
DSum("Debit_Credit","tblcheckingaccount","[ID] <= " & [ID])

Thanks very much for all your help & your patience!

Brook

:

You will need to sort out the details, because I'm not clear if the
Debit
values are negative, or whether the records can have both a Credit or
Debit,
or whether Nulls are allowed in either field, but you could try
something
along these lines:

Debit_Credit: CCur(Nz([Credit],0) + Nz([Debit],0))

Nz() deals with the cases where one field is null.
CCur() converts the result to Currency so JET can't get it wrong.
The plus needs to be a minus if the Debit column values are not
negative.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


The reason for the code that I posted, is that I created a single
column
for all debits and credits from the individual fields "Debit" &
"Credit",
thinking that it would be easier to create a running balance, but I
guess
I
was mistaken.

Would it be easier to create a "running balance" from my
individual
Debit
& Credit Fields?

Thanks,

Brook

:

Yes, it does matter, Brook.

The previous request about the Debit_Credit field was so as to
determine
whether JET might be misinterpreting the data type of the field. If
it
is
not a Currency field in a table as you reported, but a calculated
query
field, this issue does apply. For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

However, the expression itself could also be the problem. You are
comparing
Debit to True. If Debit is a Yes/No field, that makes sense, but
the
rest
of
the expression does not. If Debit is a Currency field, I'm not sure
why
you
would only want the value of the Debit field when it happened to
be -$1.00.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all your help,

I cannot get it to work, but will keep trying.

I'm not sure if this matters, but the Debit_Credit Field is
calculated
as:

Debit_Credit: IIf([Debit]=True,[Debit],[Credit])

Thanks again,

Brook

:

Then, keep working on it, Brook.

You can sum a Currency field without problem, and the 3rd
argument
is
correctly formed for a Number type field that can't be null.


Allen,

ID is an autonumber field and Debit_Credit is a currency
field.

Thanks for the response...

Brook

:

If you open your table in design view, what data type are the
2
fields:
ID
Debit_Credit
?

hello Allen,

I tried typing the following into a new column, but am
getting
no
results
in the column, once I run the query? Any ideas?


Expr1: DSum("Debit_Credit","tblcheckingaccount","[ID] <= "
&
[ID])


Thanks,

Brook


:

Try typing this into a fresh column in the Field row in
query
design:
DSum("Debit_Credit", "Table1", "[RecordID] <= " &
[RecordID])
Substitute your table name for Table1, and your primary
key
field
for
RecordID.

That should work unless you filter or sort the fields
differently.

Note that DSum() will be slow to execute on every line of
your
query.


I am trying to create a running balance for my checking
account
Debit_Credit Field.

I have been reading about DSUM, but am unsure how to
use
it?
each
of
my
records has a disctinct recordid.