ACCESS on DRUGS! DSum results change after requery

[GREABR]

I have some text boxes on a form that display the dsum of data in an ODBC Linked Table. The Linked Table is a Visual Foxpro 6 dbf table.

I promise you that the data does not changed and is not accessable by other users. When I Open the form I can see my totals, BUT..... if I requery the text box, the total changes, and changes, and changes.
I can get 3 or 4 different numbers showing in the text box.

"Strangest thing I have ever seen"

It does this also if I close the form and reopen it, then close and open....

It also does it with the sum on a report using the same table, using either sum() or dsum()

at one point I had written a query using the table, then used dsub to get smaller subsets of records from the query to total. (dsum of a query of a table) I though that may have something to do with it.

Currently the dsum uses the table directly and grabs only what it needs for the answer. (dsum of only the table) Still have the error this way.

Any Ideas!

Bradley Greaves

 

[scriverb]

Just a few questions first. How many records are we talking about. What kind of field are we performing the DSum function on? Any chance that there are nulls or other eroneous data in the fields. (i.e. date, time, etc. )

I am looking for some reason for the conversion to access data types where the value would change eratically.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[GREABR]

660 records.

The DSub looks like this, except I left out the expression which limits to a select group of record.

=DSum("round(openqty*price,2)","PKMast01",)

PKMast01 is the linked table, but this is exactly how I used the DSum to get the total of all the records from the query (used the query intead of PKMast01).

In foxpro:
openqty is Numeric 15,6
price is Numeric 15,5

Note:
openqty*price (without rounding) gave me some trouble when I first attempted the DSum text boxes. I remember getting some error message, but I cannot reproduce it now.

I did not mean to round in the DSub, I can do that in the text box, I only did it because I was getting an error. Let me try the DSum with out the round, maybe less complicated that way.

Bradley Greaves
(Guess I need a signature)

 

[GREABR]

Me!Again

I tried to remove the round from the DSum.

seems to stop the erratic behavior but I did stumble accross the error that I got previously.

Run-time error '3759'
Scaling of decimal value resulted in data truncation

(btw, got a signature now)

"Don't be afraid to do things wrong, before you do them right"
Bradley Greaves