Date formatting problem and Multiple selection filter field.

Y

yeokphong

Hi, I have two questions for two problems. And this is my first problem.
Below is my code for Date problem.

Set cst = pt.Constants
With pt.ActiveView
For intCnt = 0 To .FieldSets.Count - 1
If .FieldSets(intCnt).BoundField.DataType = 135 Then '135 = date
data type
.FieldSets(intCnt).Fields(0).NumberFormat = "MMM DD YYYY
HH:MM AM/PM"
For intCnt2 = 0 To .FieldSets(intCnt + 1).Fields.Count - 1
.FieldSets(intCnt + 1).Fields(intCnt2).Caption =
Trim(Replace(.FieldSets(intCnt).Fields(0).Caption, "Date", "")) & " " &
..FieldSets(intCnt + 1).Fields(intCnt2).Caption
Next
For intCnt2 = 0 To .FieldSets(intCnt + 2).Fields.Count - 1
If .FieldSets(intCnt + 2).Fields(intCnt2).Caption =
"Days" And .FieldSets(intCnt + 2).Fields(intCnt2).NumberFormat = "dd-mmm"
Then _
.FieldSets(intCnt +
2).Fields(intCnt2).NumberFormat = "dd"
.FieldSets(intCnt + 2).Fields(intCnt2).Caption =
Trim(Replace(.FieldSets(intCnt).Fields(0).Caption, "Date", "")) & " " &
..FieldSets(intCnt + 2).Fields(intCnt2).Caption
Next
intCnt = intCnt + 2
End If
Next
End With


1) For Date format, when view at the pivot report, i don't understand why
it's automatically give me 2 extra diff Date format, that is By Week and By
Month. Then, it's ok for me. And when i use By Month and expand it until Day,
it has show dd-mmm format and i already re-format by writing
..FieldSets(intCnt + 2).Fields(intCnt2).NumberFormat = "dd". It's success.
When i export to Excel file, I'm believe that it's will take whatever format
and data that it's show at pivot to pull into Excel file. But it's not. The
By Month for Day, it's still give me dd-mmm format. May i know got way to
change the format to "dd"? If cann't, may i know why? Does have any article
to support it? Thanks.

2) In Microsoft Office Web Components 9.0, is it cann't tick multiple
selection for filter field at pivot table? Does have any article to support
it behind this reason?

Thank you.
 
A

Alvin Bruney [MVP - ASP.NET]

your format specifiers are incorrect. you will need to use the right
combination of lowercase and uppercase specifiers

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc
 
Y

yeokphong

sorry, i cann't get your meaning. Can give me an example on that? Thank you.

Alvin Bruney said:
your format specifiers are incorrect. you will need to use the right
combination of lowercase and uppercase specifiers

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc
yeokphong said:
Hi, I have two questions for two problems. And this is my first problem.
Below is my code for Date problem.

Set cst = pt.Constants
With pt.ActiveView
For intCnt = 0 To .FieldSets.Count - 1
If .FieldSets(intCnt).BoundField.DataType = 135 Then '135 = date
data type
.FieldSets(intCnt).Fields(0).NumberFormat = "MMM DD YYYY
HH:MM AM/PM"
For intCnt2 = 0 To .FieldSets(intCnt + 1).Fields.Count - 1
.FieldSets(intCnt + 1).Fields(intCnt2).Caption =
Trim(Replace(.FieldSets(intCnt).Fields(0).Caption, "Date", "")) & " " &
.FieldSets(intCnt + 1).Fields(intCnt2).Caption
Next
For intCnt2 = 0 To .FieldSets(intCnt + 2).Fields.Count - 1
If .FieldSets(intCnt + 2).Fields(intCnt2).Caption =
"Days" And .FieldSets(intCnt + 2).Fields(intCnt2).NumberFormat = "dd-mmm"
Then _
.FieldSets(intCnt +
2).Fields(intCnt2).NumberFormat = "dd"
.FieldSets(intCnt + 2).Fields(intCnt2).Caption =
Trim(Replace(.FieldSets(intCnt).Fields(0).Caption, "Date", "")) & " " &
.FieldSets(intCnt + 2).Fields(intCnt2).Caption
Next
intCnt = intCnt + 2
End If
Next
End With


1) For Date format, when view at the pivot report, i don't understand why
it's automatically give me 2 extra diff Date format, that is By Week and By
Month. Then, it's ok for me. And when i use By Month and expand it until Day,
it has show dd-mmm format and i already re-format by writing
.FieldSets(intCnt + 2).Fields(intCnt2).NumberFormat = "dd". It's success.
When i export to Excel file, I'm believe that it's will take whatever format
and data that it's show at pivot to pull into Excel file. But it's not. The
By Month for Day, it's still give me dd-mmm format. May i know got way to
change the format to "dd"? If cann't, may i know why? Does have any article
to support it? Thanks.

2) In Microsoft Office Web Components 9.0, is it cann't tick multiple
selection for filter field at pivot table? Does have any article to support
it behind this reason?

Thank you.
Click to expand...
Click to expand...
 
A

Alvin Bruney [MVP - ASP.NET]

This is from the top of my head so it likely won't give you what you want,
but it will point you in the direction. Your format specifier should be
"dd-MM-yyy" format specifiers are case sensitive

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc
yeokphong said:
sorry, i cann't get your meaning. Can give me an example on that? Thank you.

Alvin Bruney said:
your format specifiers are incorrect. you will need to use the right
combination of lowercase and uppercase specifiers

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc
yeokphong said:
Hi, I have two questions for two problems. And this is my first problem.
Below is my code for Date problem.

Set cst = pt.Constants
With pt.ActiveView
For intCnt = 0 To .FieldSets.Count - 1
If .FieldSets(intCnt).BoundField.DataType = 135 Then '135
Click to expand...
=
date
data type
.FieldSets(intCnt).Fields(0).NumberFormat = "MMM DD YYYY
HH:MM AM/PM"
For intCnt2 = 0 To .FieldSets(intCnt + 1).Fields.Count - 1
.FieldSets(intCnt + 1).Fields(intCnt2).Caption =
Trim(Replace(.FieldSets(intCnt).Fields(0).Caption, "Date", "")) & " " &
.FieldSets(intCnt + 1).Fields(intCnt2).Caption
Next
For intCnt2 = 0 To .FieldSets(intCnt + 2).Fields.Count - 1
If .FieldSets(intCnt + 2).Fields(intCnt2).Caption =
"Days" And .FieldSets(intCnt + 2).Fields(intCnt2).NumberFormat = "dd-mmm"
Then _
.FieldSets(intCnt +
2).Fields(intCnt2).NumberFormat = "dd"
.FieldSets(intCnt + 2).Fields(intCnt2).Caption =
Trim(Replace(.FieldSets(intCnt).Fields(0).Caption, "Date", "")) & " " &
.FieldSets(intCnt + 2).Fields(intCnt2).Caption
Next
intCnt = intCnt + 2
End If
Next
End With


1) For Date format, when view at the pivot report, i don't understand why
it's automatically give me 2 extra diff Date format, that is By Week
Click to expand...
and
By
Month. Then, it's ok for me. And when i use By Month and expand it
Click to expand...
until
Day,
it has show dd-mmm format and i already re-format by writing
.FieldSets(intCnt + 2).Fields(intCnt2).NumberFormat = "dd". It's success.
When i export to Excel file, I'm believe that it's will take whatever format
and data that it's show at pivot to pull into Excel file. But it's
Click to expand...
not.
The
By Month for Day, it's still give me dd-mmm format. May i know got way to
change the format to "dd"? If cann't, may i know why? Does have any article
to support it? Thanks.

2) In Microsoft Office Web Components 9.0, is it cann't tick multiple
selection for filter field at pivot table? Does have any article to support
it behind this reason?

Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sum function in the pivot table does not compute sum but instead gives count 1
VBA in Excel 2007 date formatting 2
Combobox date value matching 0
Date formatting issue VB 0
Date format. 2
ComboBox value matching 0
Workbook reminder 0
Display billing month based on start date and end date 5

Top