How to output with a control variable inside cfquery name?

[bonneylake]

Hey Everyone,

Well i am having a problem outputting for my report and hoping someone can explain what i am doing wrong.

What the report is about is comparing my company's part price's to d and h, synnex and techdata. What i was told to do was take are part numbers and combine them with d and h, synnex and techdata to display only one column of parts (did this). Then i was told to create a cfloop using the query i created to combine the parts (did this). then inside the cfloop i created a cfif for each company, doing the cfif is suppose to get the price for each company(did this).Then i was told to add a loop control variable so that if one price is empty it will skip it instead of add the next price.

The problem is that i was able to get one price to output, but now i can not get any of the prices to output at all an not sure what i am doing wrong. Before i added a loop control variable i was able to output techdata's prices by doing #techdata.cost# but now with the loop control variable i am not able to. I was also not able to output synnex and techdata before adding the loop control variable, but i was told that how i was outputting it could be the problem. if anyone could tell me what i am doing wrong, atleast to how i am displaying techdata i would really appreciate it. Here is what i currently have.

This combines all the parts
<cfquery name="matchmanu" datasource="Configurator">
select a.[hc part number]'hcpartnumber',a.mpn,a.Mfg,a.lastbuyquote,a.[description],b.prodid,c.distisku,c.distiid
from [svr-htssqldb].configurator.dbo.[Master Parts List] a
inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_prod b
on a.mpn=b.mfpn
inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_metamap c
on b.prodid=c.prodid
where a.lastbuyquote is not null and (c.distiid='c00030' or c.distiid='c00055' or c.distiid='c00791')
</cfquery>
 
 
<table width="95%" align="center">
<thead>
<tr>
<th>Manu Part number </th>
<th>Techdata_Price</th>
<th>Synnex Price</th>
<th>D&H Price</th>
</tr>
</thead>
<cfset alt=0>
<cfset classStr="">
 
<cfoutput>
<cfloop query="matchmanu">
<cfset t = 1>
 this gets the prices for techdata
<cfif distiid EQ 'C00030'>
<cfquery name="techdata#t#" datasource="Distributor">
Select  COST
From dbo.PRODUCTS
where PART_NUM = '#matchmanu.distisku#'
</cfquery>                          
<cfelse>
 
</cfif>
<cfset t = t + 1>
 
<cfset s = 1>
 this gets the prices fo synnex
<cfif distiid EQ 'C00055'>
<cfquery name="synnex#s#" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU = '#matchmanu.distisku#'
</cfquery>
<cfelse>
</cfif>
<cfset s = s + 1>
 
<cfset d = 1>
 this gets the prices for dandh
<cfif distiid EQ 'C00791'>
<cfquery name="dandh#d#" datasource="Distributor">
Select unit_cost
From dbo.tbl_dandh_price
where dandh_item_num = '#matchmanu.distisku#'
</cfquery>
<cfelse>
</cfif>
<cfset d = d + 1>
 
 
 
<cfif alt eq 0>
    <cfset classStr="">
    <cfset alt=1>
<cfelse>
<cfset alt=0>
</cfif>
<tr>
<td class="#classStr#">
#matchmanu.distisku#
</td>
 this outputs techdata
<td class="#classStr#">
#techdata#t#.COST#
</td>
thisoutputs synnex
<td class="#classStr#">
#synnex.Unit_Cost__wo_PromoRebate#
</td>
 this outputs dandh
<td class="#classStr#">
#dandh.unit_cost#
</td>
</tr>
</cfloop>
</cfoutput>
</table>

Thank you,
Rachel

[bonneylake]

Hey Acoder,

Well i tried your example code but the lowest value is not outputting, but i think its because synnex is not right or else i shouldn't be outputting #lowest# an should be using something else. Here is what i currently have.

<cfif techdata.COST neq 0.00> 
  <cfset lowest = techdata.COST> 
<cfelseif synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
<cfelse> 
   <cfset lowest = dh.unit_cost> 
</cfif> 
<cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0 and lowest gt synnx.Unit_Cost__wo_PromoRebate> 
  <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
</cfif> 
<cfif dh.unit_cost neq 0.0 and lowest gt dh.unit_cost> 
   <cfset lowest = dh.unit_cost> 
</cfif> 
 
<td class="#classStr#">
#lowest#
</td>

an well unit_cost__wo_promorebate is a (float,null) ,synnex_sku is a numeric(18,0),null and the #synnxsku# is a varchar.

i did the following an this seems to get me a bit closer to my goal

<cfquery name="synnx" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU = '#val(synnxsku)#'
</cfquery>

the thing is though doing it that way a lot of my values i should have is missing.
An example i am suppose to have

price
price
no price
no price
price
price

and its doing it like this

price
no price
no price
no price
price
price

Any suggestions on how to make it put the value that is not numeric?

Thank you,
Rach

[acoder]

When a company doesn't have a price, is the price field NULL or does the table not have a record? Perhaps you could leave out records with value null by adding IS NOT NULL checks in the query. Then to check if it's not got a price, you could just check the recordcount of the query.

[bonneylake]

Hey Acoder,

The way i am checking it is by running the following in sql server

exec usp_matching_vendor_distisku_from_MPL

an according to it, it says the value is NULL. But its really hard to go through each table an check if there is an actual value or not since its millions of records.But i know that it is possible that it does not have a record for it because some companys wont have a price for a part or even the part.

I did the following for the where an now for all the values with nothing i get 0.00. Do i need to do the record count, if so how would i do the record count?

<cfquery name="synnx" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where SYNNEX_SKU is not null and (SYNNEX_SKU = '#val(synnxsku)#' )
</cfquery>

But i realize i got another problem. Some of the prices are not matching up right. Some match up right, some are not. Is there anyway to make sure all the prices match up correctly? Because right now some spots are having a value when its suppose to be null or some do not have a value where a value is suppose to be.An i am having this trouble with all prices not just synnex.

Besides that the lowest value is almost working correctly. If i have 3 values all on one row it is getting the lowest value, but if i have 2 values an a blank it doesn't get the lowest value.


Thank you,
Rach

[acoder]

The recordcount is a property of the query result, e.g. synnex.recordcount.

The "is not null" should be for the cost price, so you only get valid prices, though if it's 0.0, you could add a check for that too.

[bonneylake]

Hey Acoder,

Are you saying something like this for the where

<cfquery name="synnx" datasource="Distributor">
Select Unit_Cost__wo_PromoRebate
From dbo.tbl_synnex_price
where Unit_Cost__wo_PromoRebate is not null and (SYNNEX_SKU = '#val(synnxsku)#') 
</cfquery>

an then for the record count i did something like this

#dollarformat(synnx.recordcount.Unit_Cost__wo_PromoRebate)#

but got the error Error resolving parameter

I also have another question. If in my stored procedure i get the values for the following prices

techdata    synnex       d and h
price         price            no
price         price             no
price          no               no
price          no               no
price          price           no

if i do in my report a where for part num = techdata (techdata coming from stored procedure) will i get a different output in my report then in my stored procedure? reason i ask is because a good amount of the values i got in my stored procedure are not matching up to the values in my report. Some match some don't so just want to know if my report is wrong or if that is how it should be coming out based on i am comparing it to the part num instead of just outputting techdata. because right now when i output to report i get the following instead of the above.

techdata      synnex     d and h
 $34.47       $33.82      $0.00  
$34.47        $0.00        $0.00  
$0.00         $0.00         $0.00  
 $0.00        $0.00           $0.00  
$0.00        $82.38         $82.53  

Thank you,
Rach

[acoder]

Recordcount gives you the number of records returned by the query, so you'd use it like this:

<cfif synnex.recordcount neq 0>
<!--- now compare --->

Let's deal with one problem at a time otherwise it can get more difficult to follow what's going on.

[bonneylake]

Hey Acoder,

I think i got this right. Its putting the lowest value out of all 3 an not using 0.00, unless all 3 have 0.00 then its putting 0.00. An that sounds like how it should be working. This is what i currently have, i bolded where i added to it. If this is right let me know whats next.

 
 <cfif techdata.recordcount neq 0>
<cfif techdata.COST neq 0.00> 
 </cfif>
  <cfset lowest = techdata.COST> 
<cfelseif synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
<cfelse> 
   <cfset lowest = dh.unit_cost> 
</cfif> 
 <cfif synnx.recordcount neq 0>
<cfif synnx.Unit_Cost__wo_PromoRebate neq 0.0 and lowest gt synnx.Unit_Cost__wo_PromoRebate> 
  <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
</cfif> 
 </cfif>
 <cfif dh.recordcount neq 0>
<cfif dh.unit_cost neq 0.0 and lowest gt dh.unit_cost> 
   <cfset lowest = dh.unit_cost> 
</cfif>
 </cfif>
 
 

Thank you,
Rach

[acoder]

Almost. You could combine everything together - something like this:

<cfset lowest = -1>
<cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
  <cfset lowest = techdata.COST> 
</cfif>
<cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
  <cfif lowest eq -1>
    <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  <cfelseif lowest gt synnx.Unit_Cost__wo_PromoRebate>
    <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
  </cfif>
</cfif>

and similar code for dandh. That would avoid repetition.

If lowest is still -1 then you know that no companies have prices for that part.

[bonneylake]

Hey Acoder,

Would it be alright instead of doing -1 to just do 0 instead? just afraid that doing -1 would mess up the > -+ 3% when i get the lowest and compare it to my company cost.But also afraid if anyone saw it as -1 they might get confused. But would this be correct way to do the lowest cost? Trying it this way looks correct though.

 <cfset lowest = 0>
 <cfif techdata.recordcount neq 0 and techdata.COST neq 0.00> 
     <cfset lowest = techdata.COST>
      </cfif>
    <cfif synnx.recordcount neq 0 and synnx.Unit_Cost__wo_PromoRebate neq 0.00> 
      <cfif lowest eq 0>
        <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
      <cfelseif lowest gt synnx.Unit_Cost__wo_PromoRebate>
        <cfset lowest = synnx.Unit_Cost__wo_PromoRebate> 
     </cfif>
   </cfif>
 <cfif dh.recordcount neq 0 and dh.unit_cost neq 0.00> 
      <cfif lowest eq 0>
        <cfset lowest = dh.unit_cost> 
      <cfelseif lowest gt dh.unit_cost>
        <cfset lowest = dh.unit_cost> 
     </cfif>
   </cfif>

Thank you,
Rach

[acoder]

It wouldn't matter because you can check for -1, so you'd know there were no prices, so when it comes to displaying, you could output "N/A" for the price difference. If it's not -1 (or 0 if you choose), you have a lowest price which you can compare with the company price.

[bonneylake]

Hey Acoder,

Alrighty just wanted to make sure it be ok to do it that way. But i do have another problem which is going to affect doing the < - + 3% part.

Well i am trying to just output the cost for my company. The problem i am having is the way its outputting. If i do it this way it matching up the cost with the lastbuyquote (or where the cost should be) but instead of displaying the cost its displaying the lasbuyquote.

<cfquery name="acompare" datasource="Configurator">
select Cost
from dbo.[Master Parts List]
where Cost = #matchmanu.lastbuyquote#
</cfquery>

an then if i do it this way, it outputs the cost but it displays the same cost down the whole column an doesn't match up property so all i see is 45 all the way down.

<cfquery name="acompare" datasource="Configurator">
select Cost
from dbo.[Master Parts List]
where lastbuyquote = Cost
</cfquery>-

Any suggestions on what i am doing wrong?

Thank you,
Rach

[acoder]

What's the last buy quote? What you need to use in the where clause is a foreign key that matches with the part number. Perhaps a look at the fields in that table may help.

[bonneylake]

Hey Acoder,

Lastbuyquote is one of the fields that appear in the master parts list table,but it is used in the stored procedure.So i figured if i used the lastbuyquote from the stored procedure and matched it up to the cost that it would match up cost to the correct lastbuyquotes. but there are a lot of fields in the table an the only field in there that holds the part number (which i am outputting using the stored procedure) is mpn. An i don't think i can just use mpn without doing matchmanu.mpn because it will output all the mpn instead of just the ones used in the report. so is the problem the fact that i am using a column from a different cfquery or stored procedure that is causing the trouble?

Thank you,
Rach

[acoder]

If you can get the price from the main stored procedure, then there's no need for a separate query to get the cost/price. If you can't, then look at the fields in the Master Parts Table and see which one is for parts number. Then, in the query, match that field against whichever field is the parts number in the stored procedure.

[bonneylake]

Hey Acoder,

Your second idea worked perfectly! I feel blond for not seeing that because it makes perfect sense! So how would i begin doing the > - + 3% part?

Thank you :),
Rach

[acoder]

If you want to work out the price difference as a percentage:

(lowest - company price)/company price

I've not tested, but that would be the idea.

[bonneylake]

Hey Acoder,

would something like this work for it?

(#acompare.Cost# - (#lowest# * 3 / 100)) and (#acompare.Cost# + (#lowest# * 3 / 100))

an how would i output just the results (final number). When i do it above i get the whole line appearing with the values.i tried <cfset test = the above but i ended up getting errors.

Thank you,
Rach

[acoder]

What exactly do you want to display? The price difference, the % price difference, 3% of the lowest price?

[bonneylake]

Hey Acoder,

Basically i am trying to see my company's price difference of > + - 3% from the lowest cost distributor (company with the lowest price)

Thank you,
Rach

[acoder]

So, if it's less than +- 3%, you don't want to display it?

[bonneylake]

Hey Acoder,

Yes basically they don't want to see it unless its 3% and up.

Thank you,
Rach

[acoder]

So you could try

<cfset cost = aCompare.cost>
<cfset pricediff = (lowest - cost)/cost)>

Then check that it's greater than 3 or less than -3. If it is, display, otherwise just display nothing or a space.

[bonneylake]

Hey Acoder,

Don't think i got this right. Am i suppose to be using lowest for the gt or lt or am i suppose to be using pricediff?An i am getting and error with the ) it says Invalid parser construct

<cfset cost = #acompare.cost#>
<cfset pricediff = (#lowest# - cost)/cost)>
<cfif lowest gt 0.03 or lowest lt -0.03>
#cost#
<cfelse>
 
</cfif>

Thank you,
Rach

[acoder]

pricediff, not lowest.

[bonneylake]

Hey Acoder,

So would something like this be correct? or does the pricediff gt and lt need to be on the same line?

<cfset cost = #acompare.cost#>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03>
#cost#
<cfelseif pricediff lt -0.03>
 
</cfif>

also i am getting an error saying

An error occurred while evaluating the expression:

pricediff = ((#lowest# - cost)/cost)

Cannot convert to number.

An i think i got one part confused. They want to see > + - 3%. I been thinking the whole time its <.



Thank you,
Rach

[acoder]

It should be on one line. Check the values of cost and lowest to see what they are (display them for testing purposes).

[bonneylake]

Hey Acoder,

Confused on what you mean Check the values of cost and lowest? just output them like #acompare.cost# output it an then change it from that to #lowest# and output it? bit confused on how to check.

so everyone on one line should be something like this?

<cfset cost = #acompare.cost#>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#cost#
<cfelse>
 
</cfif>

Thank you,
Rach

[acoder]

Yes, just output them to test what their values are, so you can see what values you're working with and if they're correct or not.

[bonneylake]

Hey Acoder,

According to what i am looking at, there both outputting numbers. Its getting the correct value for the lowest company by doing #lowest# and when i do the cost, the cost is matching up correctly and its a number as well by doing #acompare.Cost#. But i know cost is a float and the company price is numeric, i don't know if that makes a difference or not as to why the pricediff wont work.

i know the problem has to be with cost because when i did the following, i got the error Division by zero is not allowed. I know that cost is a float value and lowest is numeric. but not sure how i would convert float to numeric

<cfset cost = #acompare.cost#>
<cfset pricediff = ((#lowest# - '#val(cost)#')/'#val(cost)#')>
<cfif pricediff gt 0.03>
#cost#
<cfelseif pricediff lt -0.03>
 
</cfif>

Thank you,
Rach

[acoder]

Don't put the val() in quotes. Is cost ever 0? If it is, it would cause zero division errors?

If it's 0, a comparison doesn't make sense, so a blank or "N/A" would have to be output.

[bonneylake]

Hey Acoder,


Actually doing this seemed to help. I am not getting errors anymore an now some of the values are 0 and some are not.But i am not sure if this is the correct solution or not.

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost)>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03>
#cost#
<cfelseif pricediff lt -0.03>
 
</cfif>
</cfif>
 

i changed the 0 that appears underneath cfelseif statement to n/a an that didn't seem to make a difference at all to the results.

but here is what i had before when i was just outputting #acompare.cost#

for line 194 i had 16 49
for line 292 i had 432.8000

an then when i did it with the isnumeric (above) this is what i get

for line 194 i get 0
for line 292 i get 432.8000

i also did a test with the calculator to make sure its right i did for the first one i did
(($9.98 - $16.49)/$16.49) and got the result -0.394 (an the numbers continue)

an then for the second one i did

(($453.97 - $432.80)/$432.80) and i got the result 0.048 (an the numbers continue)

Thank you,
Rach

[acoder]

You need to combine lines 4 and 6:

<cfif pricediff gt 0.03 and pricediff lt -0.03>

Do you want to output the cost or the price difference?

What are lines 194 and 292 that you mentioned in the previous post? Are these in the table? Also, does 16.49 have a space, i.e. is it "16 49"? If it is, the IsNumeric() will be false.

[bonneylake]

Hey Acoder,

Definately want to output the price different so instead of using #cost# would i use #pricediff#?. Line 194 and 292 appear in the report.used them because there the first 2 that have something in the price diff column an my bad its 16.49 (typed it wrong).

So would this be correct?

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost)>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Thank you,
Rach

[acoder]

Yes, but as well as a check to make sure cost is numeric, you may want to add a check that it's not 0.

[bonneylake]

Hey Acoder,

So would this be correct? not sure if i got the cost neq in the right spot or not. not sure if it should be before or after isnumeric

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost)>
<cfif cost neq 0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>
</cfif>

Thank you,
Rach

[acoder]

Yes, or you could combine the two:

<cfif IsNumeric(cost) and cost neq 0.0>

[bonneylake]

Hey Acoder,

I tried it the way i had it and tried it your way an now all the values for the price diff say n/a. I think its cause when you do the price diff at the beginning of all of them they had 0.0 in front? Any suggestions?

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and cost neq 0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Thank you,
Rach

[acoder]

That can only mean it's failing the IsNumeric or neq 0 test. Is cost a float or is it stored as a string?

[bonneylake]

Hey Acoder,

I know the neq 0 part is failing,because the isnumeric was working before. But cost is a float field in the table, not sure if its a string or not. Just know its float.

But i took out the cost neq 0 to make sure it was causing it and turns out its me using #pricediff# to output it is causing the problem. Its saying n/a for all of them based on i changed #cost# to pricediff#.Any suggestions?

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and cost neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>
 

Thank you,
Rach

[acoder]

Add val() around cost. If it's displaying N/A, that means the price difference is less than +-3%.

[bonneylake]

Hey Acoder,

Around which cost? the one in isnumeric, the one where cost neq 0.0, the cost's in the pricediff? an then should i still be outputting using #pricediff#?

Thank you,
Rach

[bonneylake]

Hey Acoder,

I tried the following, but it still says all of the costs are n/a an that don't make sinse based on the math, especially for record 292 because when i do that one i get a value of 0.04.

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and #val(cost)# neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03 and pricediff lt -0.03>
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

I also tried the following (took out "and pricediff lt 0.03") an that shows all the prices above 0.03, an puts N/A for anything under 0.031.so would this work or not work?

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and #val(cost)# neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfif pricediff gt 0.03  >
#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Also, i noticed something. I thought i was suppose to get less than +- 3% but i relooked at it an i am suppose to get >(greater than) + - 3% so will what we have still work? or is what we have only working to get less than?

Thank you,
Rach

[acoder]

It's a bit of a duh! moment. The line:

<cfif pricediff gt 0.03 and pricediff lt -0.03>

will always be false because they are mutually exclusive. It needs to be or:

<cfif pricediff gt 0.03 or pricediff lt -0.03>

[bonneylake]

Hey Acoder,

Ok so i changed it to an or and now if a number if its greater than 3% it will show 0.04. An anything below 0.03 it shows n/a is this correct?

there is a few parts i am confused on. One value has - 0.39 and the other is 0.03 (one negative one positive). If you could explain what that means for one to be negative and one to be positive it would be helpful because it has me a bit off

another part i am confused on is where does the + 3% part show up?would that just be 0.03?

An the last question i have is there anyway to make the number smaller? right now i get 0.0489140480591, is there anyway to shorten that to just 0.04 (don't think they need to see the other numbers).I would do dollarfomart around it but not sure if it should be a price of not, i am guessing it should since its a price difference but thought i would ask your judgment on that one.

Thank you :),
Rach

[acoder]

To round the number, use Round() and multiply the number by 100 to get the percentage, so 0.03 would become 3.

And yes, the +3 would be 0.03, but if you want to show a plus sign, you can add a cfif > 0 check to show +.

[bonneylake]

Hey Acoder,

Do you think they would want to see just the rounded number or see 0.03? They say % price different so i am thinking the 0.03, but i don't know how i just display the 0.03.

An also wanted to make sure, the if one value is -0.04 then it means its lower then 3 percent right?

An the last question i am a bit confused on how to show the + would i just do <cfif
pricediff gt 0.03 > + >#pricediff#</cfif>. Little bit confused on it.

Thank you,
Rach

[acoder]

I'd think as a percentage, so you'd use round to round it to two decimal places, e.g. 0.03245678 would become 3.25% by multiplying by a 100 and then rounding. -0.04 is less than -3%.

Since you want to show the +, you could separate the two price differences, e.g.

<cfif pricediff gt 0.03>+#pricediff#
<cfelseif pricediff lt -0.03>#pricediff#
<cfelse>N/A
</cfif>

[bonneylake]

Hey Acoder,

So is this the correct way to do the round? because mine didn't display like 3.25% it instead just kept putting first number 0. This is how i did it.

#Round(pricediff * 100)#

Thank you,
Rach

[acoder]

To round to 2 decimal places, you'd multiply by 100 and then divide by 100, but since you want the decimal (0.03) as an integer too, you'd multiply by another 100, so something like:

<cfset pricediff = round(pricediff * 10000)/100>

[bonneylake]

Hey Acoder,

My last question (i hope is my last) is instead of displaying the round as your example how would i display it as .039 instead of how it originally was which was 0.394784718011 ? would something like #decimalFormat(Pricediff)# work?

here is what i got in total code wise now.

<cfset cost = #acompare.cost#>
<cfif IsNumeric(cost) and #val(cost)# neq 0.0>
<cfset pricediff = ((#lowest# - cost)/cost)>
<cfset pricediff = round(pricediff * 10000)/100>
<cfif pricediff gt 0.03>
+#Pricediff#
<cfelseif pricediff lt -0.03>#pricediff#
<cfelse>
N/A
</cfif>
</cfif>

Thank you,
Rach