OpenForm gives varying results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following code gives varying results. If the PurchaseOrderNumber (which
is a Double) is 1234 or 1234.01 the form opens and the data is displayed. If
the PurchaseOrderNumber is 1234.02 or greater, the value of the
PurchaseOrderNumber in the PurchaseOrderDetail form is 0. There are no
warnings or errors.

Ideas?


stDocName = "PurchaseOrderDetail"
stLinkCriteria = "[PurchaseOrderNumber] = " & Me![PurchaseOrderNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
You could be the victim of round-off error. Just as we can't represent
certain numbers (like one-third, for instance) exactly in base 10, so too
are there many numbers that can't be represented exactly by computers.

Do you actually do arithmetic on the PurchaseOrderNumber? If not, then make
it a text field rather than a double.
 
I do math to determine the next PO in sequence and determine back order PO
numbers. I guess I'll have to change them to strings and have fun with Val()
and Str().

Thanks

Douglas J. Steele said:
You could be the victim of round-off error. Just as we can't represent
certain numbers (like one-third, for instance) exactly in base 10, so too
are there many numbers that can't be represented exactly by computers.

Do you actually do arithmetic on the PurchaseOrderNumber? If not, then make
it a text field rather than a double.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Paul K. said:
The following code gives varying results. If the PurchaseOrderNumber (which
is a Double) is 1234 or 1234.01 the form opens and the data is displayed. If
the PurchaseOrderNumber is 1234.02 or greater, the value of the
PurchaseOrderNumber in the PurchaseOrderDetail form is 0. There are no
warnings or errors.

Ideas?


stDocName = "PurchaseOrderDetail"
stLinkCriteria = "[PurchaseOrderNumber] = " & Me![PurchaseOrderNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Click to expand...
Click to expand...
 
No offense, but so-called "intelligent keys" like that are seldom the right
way to go.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Paul K. said:
I do math to determine the next PO in sequence and determine back order PO
numbers. I guess I'll have to change them to strings and have fun with Val()
and Str().

Thanks

Douglas J. Steele said:
You could be the victim of round-off error. Just as we can't represent
certain numbers (like one-third, for instance) exactly in base 10, so too
are there many numbers that can't be represented exactly by computers.

Do you actually do arithmetic on the PurchaseOrderNumber? If not, then make
it a text field rather than a double.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Paul K. said:
The following code gives varying results. If the PurchaseOrderNumber (which
is a Double) is 1234 or 1234.01 the form opens and the data is
Click to expand...
displayed.
If
the PurchaseOrderNumber is 1234.02 or greater, the value of the
PurchaseOrderNumber in the PurchaseOrderDetail form is 0. There are no
warnings or errors.

Ideas?


stDocName = "PurchaseOrderDetail"
stLinkCriteria = "[PurchaseOrderNumber] = " & Me![PurchaseOrderNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Click to expand...
Click to expand...
Click to expand...
 
A couple other options.
Change the field type to Currency (which is accurate to 4 decimal places and
doesn't have the same problems as doubles)

or a second not quite as good
Break the field into two fields BOTH as Type long. The integer portion in
field one and the decimal portion in a second but stored as a whole number.

All in all, I agree with Mr. Steele. Unless you are planning to do arithmetic
No offense, but so-called "intelligent keys" like that are seldom the right
way to go.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Paul K. said:
I do math to determine the next PO in sequence and determine back order PO
numbers. I guess I'll have to change them to strings and have fun with Val()
and Str().

Thanks

Douglas J. Steele said:
You could be the victim of round-off error. Just as we can't represent
certain numbers (like one-third, for instance) exactly in base 10, so too
are there many numbers that can't be represented exactly by computers.

Do you actually do arithmetic on the PurchaseOrderNumber? If not, then make
it a text field rather than a double.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


The following code gives varying results. If the PurchaseOrderNumber
(which
is a Double) is 1234 or 1234.01 the form opens and the data is displayed.
If
the PurchaseOrderNumber is 1234.02 or greater, the value of the
PurchaseOrderNumber in the PurchaseOrderDetail form is 0. There are no
warnings or errors.

Ideas?


stDocName = "PurchaseOrderDetail"
stLinkCriteria = "[PurchaseOrderNumber] = " & Me![PurchaseOrderNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Click to expand...
Click to expand...
Click to expand...
 
or a second not quite as good
Break the field into two fields BOTH as Type long. The integer portion in
field one and the decimal portion in a second but stored as a whole number.
Click to expand...

On the contrary that sounds exactly like what is required, with the possible
adjustment to integers (or text) rather than longs. It would be very easy
to write a small function for "doing math" on the text/strings if these were
used.

Numerical inaccuracy is probably the cause of MS Access not opening the form
of the correct field, and unless you want to fall back on using currency
(and all the formatting issues that may accompany that) then I think you are
going to have to "firm up" the data types. Otherwise this problem may not
go away (it may disapper for your current data set but reappear later). Is
is possible that there are other places this inaccuracy is causing problems,
and you have only noticed it on the form? eg are some queries using it and
missing records but noone has noticed.

Paul did suggest just using 1 string, which gets around the data inaccuracy
issue, but it sounds to me like there are 2 separate pieces of info here and
so using 2 fields (or even better another table) sounds like the way to go.

Pragmatism disclaimer: how much you have to change could determine what you
are prepared to do, but do check for as yet undiscovered problems first.

Regards
AC
 
The reason I wrote that the second option might now be as good was that the OP
had said something about having to do a lot of manipulation in code to handle
the text option that was proposed. It sounded as if they wanted to avoid the
use of vba code to manipulate the data between numeric and text data. Without
knowing the structure of the data and the problems being solved, I proposed two
alternative solutions.

Although I do agree, that breaking the original field into two fields would
_probably_ be better since I _suspect_ that the two portions of the "number"
represent two different (but related) bits of data.
 
Hi Mr Spencer.

It was understood, its more that I would be very nervous about keeping a
data type that is currently producing know problems and possible unknown
problems (which is far far worse) and hence think that there may well be no
option other than to fix it properly. In that light I would be thinking
your 2nd option was way better rather than not as good. I was just wanting
to re-highlight the 2nd option in case it was being brushed over by someone
who took your "not quite as good" comments as an indication of its ability
to fix the problem rather than as it was intended - namely that it will
require more work.

Perhaps I didnt explain the point I was trying to make very well, apologies.

Regards
AC
 
Back
Top