Solved

can't display date and currency in cfgridcolumn

Posted on 2015-01-18
22
116 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
  • 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 61

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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 61

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 61

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 61

Expert Comment

by:gheist
ID: 40589695
No more.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Read about why website design really matters in today's demanding market.
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now