can't display date and currency in cfgridcolumn

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>
LVL 1
sgleeAsked:
Who is Participating?
 
sgleeAuthor Commented:
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
 
sgleeAuthor Commented:
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
 
gheistCommented:
NumberFormat() ?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sgleeAuthor Commented:
gheist
I think I have tried all sorts of things before posting this question.
what normally works in CFINPUT statement did not work.
0
 
_agx_Commented:
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
 
sgleeAuthor Commented:
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
 
_agx_Commented:
What's your db type? SQL Server, MySQL, etc...?
0
 
sgleeAuthor Commented:
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
 
sgleeAuthor Commented:
<CFGRIDCOLUMN NAME="DateListed" DATAALIGN="LEFT"  HEADER="Date Listed" mask="m/d/y" width="100">  ---> displayed 1/15/2015
0
 
_agx_Commented:
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
 
_agx_Commented:
>> ---> 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
 
_agx_Commented:
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
 
sgleeAuthor Commented:
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
 
gheistCommented:
You just copy answer from AGX
http:#a40588574
And claim as yours?
0
 
_agx_Commented:
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
 
sgleeAuthor Commented:
@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
 
gheistCommented:
Yes, you can shovel half of points to your comment as answer... Thats reasonable...
0
 
sgleeAuthor Commented:
Which one should I choose to award half of points?
0
 
sgleeAuthor Commented:
Admin,

  What do I need to do to re-award points?
0
 
gheistCommented:
No more.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.