Access 2016 query rounding field up automatically

[Jacqueline]

I'm using Access 2016. When my query runs, it rounds the number up automatically. I have tried to use Formate, General Number, and setting the decimals out to 2 places, but that does not work. :banghead:



The query pulls data from a linked table. Example:

The data in the table is 0.6, and the query rounds it up to 1. Is there a way to stop this behavior?

Thank you,

Jacqueline




SELECT dbo_Class.AdministrativeUnitID, dbo_Enrollment.YearQuarterID, dbo_Class.Department, dbo_Class.ItemNumber, dbo_Class.Credits, dbo_Class.CourseTitle, dbo_Student.SID, dbo_Student.LastName, dbo_Student.FirstName, dbo_Student.MiddleInitial, dbo_Student.EveningPhone, dbo_Student.DaytimePhone, dbo_Class.InstructorName, dbo_Class.StartTime, dbo_Class.EndTime, dbo_Day.Title, dbo_Enrollment.GradeID, dbo_Enrollment.ClassID



FROM (dbo_Enrollment INNER JOIN (dbo_Class INNER JOIN dbo_Day ON dbo_Class.DayID = dbo_Day.DayID) ON (dbo_Enrollment.YearQuarterID = dbo_Class.YearQuarterID) AND (dbo_Enrollment.ClassID = dbo_Class.ClassID)) INNER JOIN dbo_Student ON dbo_Enrollment.SID = dbo_Student.SID



GROUP BY dbo_Class.AdministrativeUnitID, dbo_Enrollment.YearQuarterID, dbo_Class.Department, dbo_Class.ItemNumber, dbo_Class.Credits, dbo_Class.CourseTitle, dbo_Student.SID, dbo_Student.LastName, dbo_Student.FirstName, dbo_Student.MiddleInitial, dbo_Student.EveningPhone, dbo_Student.DaytimePhone, dbo_Class.InstructorName, dbo_Class.StartTime, dbo_Class.EndTime, dbo_Day.Title, dbo_Enrollment.GradeID, dbo_Enrollment.ClassID



HAVING (((dbo_Class.AdministrativeUnitID) Like [Enter Admin Unit or leave blank for all] & "*") AND ((dbo_Enrollment.YearQuarterID)=[Enter Quarter Code]) AND ((dbo_Class.Department) Like [Enter Department or leave blank for all] & "*") AND ((dbo_Class.ItemNumber) Like [Enter Item Number or leave blank for all] & "*"))



ORDER BY dbo_Class.ItemNumber, dbo_Student.LastName, dbo_Student.FirstName;


The dbo_Class.Credits field is rounding up to 1 from 0.6 in the original database table. The tables I'm pulling from are the college's read-only scheduling database. I have no control of the data in the tables.

 

[theDBguy]

Hi. Can you please post the SQL statement for the query you're using? Thanks.

 

[June7]

Post the query statement. What is the backend db - Access?

 

[isladogs]

Check the number datatype for that field. Is it integer or long integer? If so it will round 'off' your numbers

 

[Jacqueline]

dbo_Class.Credits is the field I am pulling from the college's read-only scheduling tables. The data in the tables is 0.6, and my query rounds it up to 1.

Please see my edits in the original post; I did not see the post reply button until I had copied the SQL into the first post.

Thanks,

Jacqueline

 

[isladogs]

So what is the datatype for that field?

 

[June7]

Data is in a SQLServer backend? Need to find out datatype for that field.

 

[Jacqueline]

Well now I feel stupid, I just checked the table, and the data in the table is one credit. So the query is pulling what it found from the table. I know better than to not check out my user's information before running around with my hair on fire!

Thanks for making me look at the table data.



Jacqueline