Dsum Id sequence problem in query

[rudolfelizabeth]

How do you correct the following problem
if you dsum your get the sum of values in sorted in order of the id ,
not date sort order ,what if the user forget some information of the previous year
and he input it later with the correct year date this id nummer will be higher
if run the dsum you will get complete wrong values.

you can then to run query with two criteria

clientnumber input by the user.
and date also input by the user you wont get any info.
I have notice that Dsum cannot handle more the one values.
because it has to use three criterias id sorted date sorted
and only clientnumber asked by the user in a query.

After you Dsum how do you refer to a highest of the smallest value in the recordset.

 

[dhookom]

DSum() should not depend at all on record order. For instance, assume your records are marbles in a bag. Each marble has a particular color and a number on it. The marbles have no order. If you want to sum the numbers on the red marbles, your expression might look like:

=DSum("Number","MarblesInBag","[Color]='Red'")

If the marbles also had a date printed on them, I could further filter the marbles in the DSum() like:

=DSum("Number","MarblesInBag","[Color]='Red' AND [TheDate] BETWEEN #1/1/2009# and #1/31/2009#")

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Thanks for your tip but my problem is this
I need a running total
Here follows the code
AliasBalruntotal: Format(DSum("Balance","transactions","number=" & [number] & " and ID <=" & [ID]),"currency")

The above code gives you a running total based on the Id sequence of the current year. but if a user now enters a value of a past year in the database,
he obstruct the sequence of the Id number

The only way to get a correct running total now is
on the date
I have tried a couple of code nothing seems to work.
it seems that Dsum cannot provide a running total on a date field.

 

[PHV]

AliasBalruntotal: Format(DSum("Balance","transactions","number=" & [number] & " and [date field]<=#" & [date field] & "#"),"currency")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

rudolfelizabeth,
If PH's suggestion doesn't work for you, consider providing some sample data to illustrate your requirements.

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Linenumber Number startdate stopdate Withdrawaldate Interestamount Withdrawals Balance AliasBalruntotal2
0 100 31-12-2000 31-12-2001 18.00 0.00 18.00 18.00
0 100 31-12-2001 31-12-2002 18.00 0.00 18.00 36.00
100 31-12-2003 31-12-2004 18.05 0.00 18.05 54.05
100 31-12-2004 31-12-2005 18.00 0.00 18.00 72.05
100 31-12-2005 31-12-2006 18.00 0.00 18.00 90.05
100 30-06-2006 30-06-2006 30-06-2006 0.00 15.00 -15.00 75.05
0 100 31-12-2006 31-12-2007 18.00 0.00 18.00 93.05
0 100 31-12-2007 31-01-2008 1.53 0.00 1.53 94.58
0 100 31-01-2008 29-02-2008 1.43 0.00 1.43 96.01
0 100 29-02-2008 29-02-2008 0.00 15.00 -15.00 81.01
0 100 25-12-2008 25-12-2008 0.00 5.00 -5.00 76.01
0 100 31-12-2008 31-12-2008 0.00 2.00 -2.00 74.01
0 100 31-01-2009 31-01-2009 0.00 18.00 -18.00 56.01
0 100 04-02-2009 04-02-2009 0.00 2.00 -2.00 54.01
0 100 04-04-2009 04-04-2009 0.00 2.00 -2.00 52.01
0 100 25-11-2009 25-11-2009 0.00 11.00 -11.00 41.01
0 100 28-11-2009 28-11-2009 0.00 12.00 -12.00 29.01
0 100 30-11-2009 30-11-2009 0.00 6.00 -6.00 23.01
0 100 01-12-2009 01-12-2009 0.00 2.00 -2.00 74.01
0 100 02-12-2009 04-12-2009 0.10 0.00 0.10 54.01
0 100 04-12-2009 04-12-2009 0.00 2.00 -2.00 52.01
0 100 06-12-2009 06-12-2009 0.00 2.00 -2.00 52.01
0 100 08-12-2009 08-12-2009 0.00 2.00 -2.00 52.01
0 100 09-12-2009 09-12-2009
0.00 2.00 -2.00 52.01

It works but here is the copy of the query at the end the last 6 records sums are not correct

 

[PHV]

AliasBalruntotal: DSum("Balance","transactions","number=" & [number] & " and [date field]<=#" & Format([date field],"yyyy-mm-dd") & "#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rudolfelizabeth]

Transactionsqueryvalues

AliasBalendtotal: DSum("Balance","transactions","number=" & [number])

AliasBalruntotal2: Format(DSum("Balance","transactions","number=" & [number] & " and [startdate]<=#" & [startdate] & "#"),"currency")


AliasBalBeginbaltotal3: Sum(DSum("Balance","transactions","Number=" & [yournumber] & " and [startdate]<=#" & Format([yourstartdate],"yyyy-mm-dd") & "#"))

Query criteria field: Number [yournumber]

Field startdate :<[yourstartdate]

Input by user number 100
Startdate 1-1-2004

Transactions
ID Balance AliasBalendtotal AliasBalruntotal2 Number startdate AliasBalruntotal AliasBalBeginbaltotal3
12 18.00 13.11 18.00 100 31-12-2000 18 54.05
13 18.00 13.11 36.00 100 31-12-2001 36 54.05
1 18.05 13.11 54.05 100 31-12-2003 54.05 54.05

So far correct


Dsumforwardquery values
Criteria field : number:[yournumber]
Startdate:>[startdate]

AliasBalcarryforwardruntotal: DSum("Balance","transactions","number=" & [number] & " and [startdate]<=#" & Format([startdate],"dd-mm-yyyy") & "#")


DsumBalanceforwardquery
Number startdate Balance AliasBalcarryforwardruntotal
100 31-12-2004 18.00 72.05
100 31-12-2005 18.00 90.05
100 30-06-2006 -15.00 75.05
100 31-12-2006 18.00 93.05
100 31-12-2007 1.53 94.58
100 31-01-2008 1.43 96.01
100 29-02-2008 -15.00 81.01
100 25-12-2008 -5.00 76.01
100 31-12-2008 -2.00 74.01
100 31-01-2009 -18.00 56.01
100 04-02-2009 -2.00 54.01
100 04-04-2009 -2.00 52.01
100 25-11-2009 -11.00 41.01
100 28-11-2009 -12.00 29.01
100 30-11-2009 -6.00 23.01
100 01-12-2009 -2.00 74.01
100 02-12-2009 0.10 54.01
100 04-12-2009 -2.00 52.01
100 06-12-2009 -2.00 52.01
100 08-12-2009 -2.00 52.01
100 09-12-2009 -2.00 52.01


The last 6 records are not correct.

 

[dhookom]

I think you just got lucky on a couple other records in the query. You need to change the format of your date to match the MS standard. Try:

AliasBalcarryforwardruntotal: DSum("Balance","transactions","number=" & [number] & " and [startdate]<=#" & Format([startdate],"mm-dd-yyyy") & "#")

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Thank you
the last last solution is correct
AliasBalBeginbaltotal3: Sum(DSum("Balance","transactions","Number=" & [yournumber] & " and [startdate]<=#" & Format([yourstartdate],"mm-dd-yyyy") & "#"))

AliasBalcarryforwardruntotal: DSum("Balance","transactions","number=" & [number] & " and [startdate]<=#" & Format([startdate],"mm-dd-yyyy") & "#")

is it possible to
use these two in one query
with criteria <[startdate]
and with criteria >[startdate]

I have tried the same code the first one works
the second doesn't.

 

[rudolfelizabeth]

ID Number startdate AliasBalBeginbaltotal3 AliasBalcarryforwardruntotal
12 100 31-12-2000 54.05 18
13 100 31-12-2001 54.05 36
1 100 31-12-2003 54.05 54.05
2 100 31-12-2004 54.05 72.05
3 100 31-12-2005 54.05 90.05
4 100 30-06-2006 54.05 75.05
11 100 31-12-2006 54.05 93.05
14 100 31-12-2007 54.05 94.58
15 100 31-01-2008 54.05 96.01
16 100 29-02-2008 54.05 81.01
24 100 25-12-2008 54.05 76.01
22 100 31-12-2008 54.05 74.01
18 100 31-01-2009 54.05 56.01
25 100 04-02-2009 54.05 54.01
27 100 04-04-2009 54.05 52.01
20 100 25-11-2009 54.05 41.01
19 100 28-11-2009 54.05 29.01
21 100 30-11-2009 54.05 23.01
28 100 01-12-2009 54.05 21.01
29 100 02-12-2009 54.05 21.11
30 100 04-12-2009 54.05 19.11
31 100 06-12-2009 54.05 17.11
32 100 08-12-2009 54.05 15.11
33 100 09-12-2009 54.05 13.11


I tried

<[yourstartdate] or >[yourstartdate]

but the three first records must be left out.
because yourstartdate =1/1/2004

 

[rudolfelizabeth]

Dear

Duane I solved the last problem allready.

I have an old table with the first and last name together

Now I build a new database and imported the information in my new database,

I have parse the last name to a seperate field,it was delimeted by a ,

what code is use in a query to wipe the last name from the field with both names

 

[dhookom]

You might want to look at faq705-6468.

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Dear Duane,

Is it possible to update the currentusers field using code
in the event procedure?

On getfocus

What is the code?

 

[dhookom]

Please describe what you have and what you need. It sounds like you want to parse the field at the time of data entry. If this is true, just train your users to enter the data correctly.

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

I want to know which user change the data.
in the database

so I have a field userx I want to update it with the currentuser name on getfocus
Thank you