Calculating And Returning Values from external Database

[Iamrickdeans]

Apologies if this doesn't quite fit within the queries area exactly...

I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.

I have written a query that pulls to fields of data together but cannot get the two to calculate.

Within SQL server management studio I use the following query to give me a numerical response.


SELECT PRCPRICE, SUM(CAST(VL2BENEFICIAL AS NUMERIC (20,6)) * CAST(PRCPRICE AS MONEY))) AS HOLDINGVALUE

Access doesn't appear to accept the cast functionality does anyone know how I can perform a similar functionality with access?



Help is appreciated, I am completely confused with this one...

 

[pr2-eugin]

What exactly you are trying to do? Convert it into Currency type? If so you can use CCur function. Or if it only for display use Format function..

 

[JHB]

Look in the MS-Access Help file, search for "Type Conversion Functions" and "Functions (Category List)".

 

[Iamrickdeans]

Thanks for the replies guys, I am going to start looking at your sugestions to clarify I am trying to convert the VL2BENEFICIAL into a decimal which can contain up to 20 decimal places and the PRCPRICE is to be converted into Currency so I will try the CCur function.

 

[pr2-eugin]

Let us know how it goes ! Good Luck !

 

[Iamrickdeans]

Well the CCur has worked perfectly, the conversion to decimal not so...

I have written this element as

Select CDec(dbo_T5VaultsL2.VL2Beneficial) AS Beneficial

however I receive the message Wrong number of arguments used with function in query expression 'CDec....'

I tried changing the code based on assumption of similarity to SQL server script but am getting an error on this as well, If you have any thoughts let me know I am scouring the web for an answer...

Thanks though!

 

[Iamrickdeans]

Oh apparently you cannot use the CDec directly within a query!?! I find this quite strange... Well that's me out of ideas.

 

[pr2-eugin]

That looks perfectly fine to me.

Try using Round function instead, see if that helps. NOTE: Round function takes two arguments.

 

[Iamrickdeans]

That looks perfectly fine to me.

Try using Round function instead, see if that helps. NOTE: Round function takes two arguments.

That's cracked it, Again buddy your a star! Access is such a strange beast at times...

 

[pr2-eugin]

Access is such a strange beast at times...

It sometime is ! Glad you have it sorted. Good Luck !