Formatting decimal places and numbers in result of a long query

[sonaljain123]

Hi, I am trying to format the values in a column which is a result of an extremely long query. Now I know that I can set the format in the design view but if I try to view the query in design view, because of its length, I get a message saying that

"the expression you entered exceeds the 1024 character limit for the query design grid"

How can i set the format then in an SQL query to make it a 4 decimal number?

Thanks!

 

[boblarson]

How many fields are in this query? What is the SQL of your current problem query? Is there any other place you can format the data instead of in the query?

 

[sonaljain123]

Hi Bob,

There are 69 rows and 6 columns in the query results, but each of the 6 columns is a large computation of columns from another table. Thats why access is out of the limit in terms of characters.

I dont see any other place where I can set the format, i did set the format in the table from where it is picking up the data..

where else do you think I should change the format?

thank you for helping me out on this one...

 

[boblarson]

Hi Bob,

There are 69 rows and 6 columns in the query results, but each of the 6 columns is a large computation of columns from another table. Thats why access is out of the limit in terms of characters.

I dont see any other place where I can set the format, i did set the format in the table from where it is picking up the data..

where else do you think I should change the format?

thank you for helping me out on this one...

If you have major computations remove them from the query and create functions to do the work. Then use the format on the function like:

Function MyTest(dblInput1 As Double, dblInput2 As Double, dblInput3 As Double, dblInput4 As Double) As Double
 
    Dim dblResult As Double
 
    If dblInput2 > 2.325 Then
 
      dblResult = ((dblInput1^2)/dblInput3) + 3.14159265) * (dblInput4 -dblInput2)
 
   Else
 
      dblResult = ((dblInput1^2)/dblInput3) + 3.14159265) * (dblInput4 + dblInput2)
   End If
   MyTest = dblResult
End Function

Then you would call the function like


Select Format(MyTest([Field1], [Field2], [Field3], [Field4]), "#,#00.0000") As SomeNameHere, Field5, Field6 From TableNameHere

etc.

 

[sonaljain123]

Okay.

So what is happening is that I have a huge table from which one query is pulling out certain accounts. The second query is then calculating a combination of those accounts using + and - operations and then we are using these computer columns to calculate a ratio. I am not sure how I can create a function for the same. I do understand writing a macro for the same and running the query there. I

Are you suggesting this solely because SQL platform might know support the format command you have used above where as VB macro might?

Thanks!

 

[boblarson]

I think I can answer you better if you can post the SQL of each of the queries so I can be a little more specific to your situation. If you didn't understand my example above then it isn't going to help to try to go through it again using generic information.

 

[sonaljain123]

I understand that but I cannot paste the query actually...

 

[sonaljain123]

Can I also possibly ask you something ambitious I was trying to do? So in MS Access, I want to basically create a table such that if i double click on one of the fields in the table, it opens up another table that constitutes that value? So lets say that particular field contains A which is A=B+C+D+E, so what I want to do is when I click on A, it should open up the table with B,C,D,E that add up to that value?

I am open to use query, macro or any other objects available in MS Access.

Doing this would be a real help.

Thanks!