Solved

can't display date and currency in cfgridcolumn

Posted on 2015-01-18
22
136 Views
Last Modified: 2015-02-04
InventoryListing.pngHi,
 I have CF V7 and the following code. There is no problem except date and price fields are not displayed correctly like above: I like to display Date like "12/5/2014" and Price like $20,000.95".

<cfform action = "#CGI.SCRIPT_NAME#">
    <cfgrid name = "FirstGrid" width = "1000"
            query = "GetInventory" colheaderbold="Yes"
            font = "Tahoma" rowHeaders = "No"
            selectColor = "Red" selectMode = "Edit" format="HTML" >

        <cfgridcolumn name = "ID" header="ID" display = "Yes" width=30 >
        <cfgridcolumn name = "ItemNo" header = "Item No" Select="No"  width=60 textcolor="blue" bold="Yes" href="AdminItemDetails.cfm" hrefkey="ID">
        <cfgridcolumn name = "Title" header = "Title" Select="No" width=200 textcolor="blue" bold="Yes">
        <cfgridcolumn name="Category" header="Category" width=100>
               <cfgridcolumn NAME="DateListed" DATAALIGN="LEFT"  HEADER="Date Listed" mask="M/D/Y" width="80">
        <cfgridcolumn name="Price" header="Price" width=80>
        <cfgridcolumn name="Pic1" header="PictureFileName" width=250>          
    </cfgrid>
    <br>
    <cfinput type="submit" name="gridEntered">
</cfform>
0
Comment
Question by:sglee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 4
22 Comments
 

Author Comment

by:sglee
ID: 40559037
I added type="date" in
 <cfgridcolumn NAME="DateListed" DATAALIGN="LEFT" type="date"  HEADER="Date Listed" width="70">
Now the date listed shows the date correctly.
But I still like to display $ with decimal points for Price: ex) $9,500.95
0
 
LVL 62

Expert Comment

by:gheist
ID: 40576858
NumberFormat() ?
0
 

Author Comment

by:sglee
ID: 40577206
gheist
I think I have tried all sorts of things before posting this question.
what normally works in CFINPUT statement did not work.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 52

Expert Comment

by:_agx_
ID: 40588574
I don't have MX7 anymore, so I can't test it. From what I remember, some of the extra formatting options for cfgrid features were for applet/flash format only. For grins, try using using something like one of these:

        type="currency" mask="$99,999.00"
        type="numeric" mask="$99,999.00"
        type="numeric"

for the price column.  If that does not work, you may need to resort to formatting in your sql query. Upside, it would work in any version. Downside is it may alter the sorting of the column.
0
 

Author Comment

by:sglee
ID: 40589327
type="currency" mask="$99,999.00"  ---> Created an error.
  type="numeric" mask="$99,999.00"  ---> no error, it displays numbers with 2 decimal m points (ex. 100.95) but no $ either.
I don't know how to format in SQL query. Can you show me an example?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40589341
What's your db type? SQL Server, MySQL, etc...?
0
 

Author Comment

by:sglee
ID: 40589345
MS ACCESS

While we are at it, how can I display the date?
<CFGRIDCOLUMN NAME="DateListed" DATAALIGN="LEFT"  HEADER="Date Listed" mask="mm-dd-yy" width="100"> does not work.
0
 

Author Comment

by:sglee
ID: 40589364
<CFGRIDCOLUMN NAME="DateListed" DATAALIGN="LEFT"  HEADER="Date Listed" mask="m/d/y" width="100">  ---> displayed 1/15/2015
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40589388
My Access is rusty.  Format() should work for both dates and numbers, but I don't remember if it works from SQL or is just a VBA function. Can you try something like this?

SELECT Format( [ColumnName], "mm-dd-yy")
FROM    Table

For more details see the docs or:
http://webcheatsheet.com/SQL/access_functions/format.php
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40589395
>> ---> displayed 1/15/2015

Hm.. I'm pleased, but surprised.  I seemed to remember limited formatting options with the CF7 grids - except for the applet/flash versions.
0
 

Accepted Solution

by:
sglee earned 0 total points
ID: 40589404
I think I got my problems taken care of.
For date, the following worked.
<CFGRIDCOLUMN NAME="DateListed" HEADER="Date Listed" mask="m/d/y">

For the number field, the following worked.
<cfgridcolumn name="ListPrice" header="ListPrice" type="numeric" mask="$99,999.00">
 I wish I could display $ sign, but it did not and displaying $ is NOT mandatory.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40589451
Glad to hear it.  Sorry I couldn't be of more help.  MX7 specific stuff is hard to test.  

Don't forget to mark your comment as the answer to close out the thread!
0
 

Author Comment

by:sglee
ID: 40589510
I've requested that this question be closed as follows:

Accepted answer: 0 points for sglee's comment #a40589404

for the following reason:

I found the answers to my question.
0
 
LVL 62

Expert Comment

by:gheist
ID: 40589511
You just copy answer from AGX
http:#a40588574
And claim as yours?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40589545
Thanks, but I'm actually the one that suggested it :)  I don't have the version they're using and was just throwing out "shot in the dark" ideas.  Since I don't have any sense of whether any of it even works (and they did their own testing) I'd rather they accept their own answer.
0
 

Author Comment

by:sglee
ID: 40589561
@gheist
Let me answer two ways, but Just to make it clear ... I have no problem awarding points to experts.
That said:
(1) I had two problems: date formatting issue and $/current formatting issue.
      I figured out date formatting issue. AGX suggested currency format as well as number format, but currency format did not work and number format did not display $ sign.
(2) AGX said specifically "Don't forget to mark your comment as the answer to close out the thread!" I was just following his lead.

I am all for awarding points for experts. What (ID#) do you suggest that I pick to award points?
0
 
LVL 62

Expert Comment

by:gheist
ID: 40589615
Yes, you can shovel half of points to your comment as answer... Thats reasonable...
0
 

Author Comment

by:sglee
ID: 40589664
Which one should I choose to award half of points?
0
 

Author Comment

by:sglee
ID: 40589671
Admin,

  What do I need to do to re-award points?
0
 
LVL 62

Expert Comment

by:gheist
ID: 40589695
No more.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Google Analytics - viewing a page 10 83
Anyone familiar with PhotoSwipe? 3 42
isNaN issue on the Table input text 2 41
center div inside another div 2 32
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question