Double data type in query rounds when criteria is present

  • Thread starter Thread starter Gray Sadler via AccessMonster.com
  • Start date Start date
G

Gray Sadler via AccessMonster.com

I have a select query that looks up values from a table based on a control I
have in a form. In the query, one value's datatype is Double, and the other
is Text. The criteria I have in the query is under Text where it looks up the
value from a control on a form. When this criteria is present, it rounds my
Double datatype, but without the criteria it doesn't. Has anyone heard of
this? I'm stumped. If anyone could help I would greatly appreciate it!

Gray
 
You may have to post your code for us to see the problem but you can try:
Where SomeField = CDbl(Forms!frmYourForm!SomeField)
 
I tried CDbl() and it still rounds the number. Here's my SQL:

SELECT PAYROLL.policyNum, PAYROLL.rate
FROM PAYROLL
WHERE (((PAYROLL.policyNum)=[Forms]![frmMain]![policyNum]));

policyNum's datatype is actually a Long Int. and rate is Double. When I do
the select query without the WHERE, it shows the correct 'rate', which is not
rounded. An example of what it should look like is: .0064. It's either
rounding the double datatype or changing the data type all together. I can't
figure it out.
 
How are you using this query? In other words, when does it run? What runs
it? How are you displaying the results of the query?

I must say that I've never run across this situation previously, so I'm
wondering if it has to do with the context for the query's run.
 
As of right now, the query is only opened manually. The results are displayed
in spreadsheet view. This is how I noticed it...today I noticed that the
'rate' field was rounding. I realized that the data type for 'rate' was a
Long Integer, so I changed it to Double. When I opened the table, I then saw
the correct 'rates' with decimals. But when I created a query that had
'policyNum' from a form as the criteria for the query, it still rounded.

I've been playing around with this almost all day, and it seems that there is
something special about this table, because other tables with decimals query
just fine.
 
I fixed it. I deleted all records, compacted, and put the records back in. I
still don't know how that happened.
 
Back
Top