Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

can't display date and currency in cfgridcolumn

Posted on 2015-01-18
22
Medium Priority
?
167 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 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
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.

 
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses

916 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