more expression syntax

Hi,

This is a follow up to an earlier question. I'm having a heck of a time here. What I'm doing is reading a value into a SSIS variable and trying to evaluate it. I know the value is huge (over 4000 chars) and what I think should happen in my package isn't (I guess because this variable is so big).

What I WANTED to do is a straight character check:

@.[User::xml_output] == "ABC"

but that wasn't working... so I deceided to try the len function.

However xml_output is too big and it's also not working. How would I check to see if the len is greater than 17 characters? Here is what I have so far...and none of it works.

len(trim(DT_WSTR,18,1252)@.[User::xml_output])) > 17

len(trim(@.[User::xml_output])) > 17

(DT_WSTR,18)@.[User::xml_output] > 17

I just want to trim @.[User::xml_output] and see if it's greater than 17 characters. Any help would be appreciated.

Thanks,

Phil

Well, you can do it with script. Phil is laughing, but I'm serious. Script can handle strings over 4,000 chars, but expressions apparently cannot.

I don't know if you're trying to do this in a data flow or a control flow, but here is the code for a control flow script task that gets the length of your variable and puts it into an integer variable named "length". You would of course need to list "xml_output" and "length" in the ReadOnlyVariables and ReadWriteVariables properties respectively.

Code Snippet

Public Sub Main()
Dts.Variables("length").Value = Dts.Variables("xml_output").Value.ToString.Length
Dts.TaskResult = Dts.Results.Success
End Sub

|||Thanks.|||

JayH wrote:

Well, you can do it with script. Phil is laughing, but I'm serious. Script can handle strings over 4,000 chars, but expressions apparently cannot.

If you're referring to me, well, I'm always in favor of using the right tool for the job. That includes being efficient. This solutions works perfectly because his string is over 4,000 characters.|||

I figured you guys worked together or something

It's actually a huge shortcoming of expression syntax to not be able to include over 4000 characters. I was trying to evaluate some XML output and it was over the 4k limit and it basically always assumed it was under 17 characters because that's what my expression syntax was looking for either greater than or less than 17 characters. The less than would always hit because it just ignored the large size of the variable. Work-arounds are good, but in this case I think the language needs to adapt....

Phil

sql

More efficient way of checking for value

This trigger needs to check the contact1 table for the deleted accountno. If
it is not found there it will then use the record in contact1del.
This trigger works but I know there is a better way to check for the deleted
accountno in contact1 in the IF statement. Accountno values in Contact1 are
always unique
Thank you in advance for reading this. Any help and\or advise on this
trigger in general is greatly appreciated
CREATE TRIGGER Supp_contact_delete ON Contsupp
FOR DELETE
AS
if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from Contact1
)
BEGIN
INSERT INTO Contact1Del
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, contact1.company, contact1.key5
FROM deleted
INNER join
contact1
ON contact1.accountno = deleted.accountno
WHERE deleted.rectype='C' AND deleted.accountno > ''
END
ELSE
BEGIN
INSERT INTO Contact1Del
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
FROM deleted
INNER join
contact1del
ON contact1del.accountno = deleted.accountno
WHERE contact1del.rectype IS NULL and deleted.rectype='C'
ENDJenks,
Try using EXISTS instead.
See:
http://msdn.microsoft.com/library/d...br />
0a2b.asp
HTH
Jerry
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:1D3AE7DF-B678-461B-B0FF-11F4010DFDD6@.microsoft.com...
> This trigger needs to check the contact1 table for the deleted accountno.
> If
> it is not found there it will then use the record in contact1del.
> This trigger works but I know there is a better way to check for the
> deleted
> accountno in contact1 in the IF statement. Accountno values in Contact1
> are
> always unique
> Thank you in advance for reading this. Any help and\or advise on this
> trigger in general is greatly appreciated
>
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from
> Contact1)
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join
> contact1
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> END
> ELSE
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
> FROM deleted
> INNER join
> contact1del
> ON contact1del.accountno = deleted.accountno
> WHERE contact1del.rectype IS NULL and deleted.rectype='C'
> END
>|||jenks wrote:
> This trigger needs to check the contact1 table for the deleted
> accountno. If it is not found there it will then use the record in
> contact1del.
> This trigger works but I know there is a better way to check for the
> deleted accountno in contact1 in the IF statement. Accountno values
> in Contact1 are always unique
> Thank you in advance for reading this. Any help and\or advise on this
> trigger in general is greatly appreciated
>
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from
> Contact1)
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1,
> fax, address1, address2, address3, city, state, zip, country,
> mergecodes, lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact,
> deleted.title, deleted.contsupref, deleted.dear, deleted.phone ,
> deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join
> contact1
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> END
> ELSE
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1,
> fax, address1, address2, address3, city, state, zip, country,
> mergecodes, lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact,
> deleted.title, deleted.contsupref, deleted.dear, deleted.phone ,
> deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
> FROM deleted
> INNER join
> contact1del
> ON contact1del.accountno = deleted.accountno
> WHERE contact1del.rectype IS NULL and deleted.rectype='C'
> END
IF EXISTS (
SELECT *
FROM dbo.Contact1
INNER JOIN deleted
on deleted.accountno = Contact1.accountno )
But your original code does not handle multi-row deletes where one row
deleted exists in the Contact1 table and one row that is deleted does
not. Tthe EXISTS code above won't work correctly in that case either.
You could just always run both inserts for rows that exist and for rows
that do not, assuming those rows are mutually exclusive and forget
checking for existence.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In this case, the deleted rows with rectype of 'C' will always have an
accountno value in one of the two tables(contact1 or contact1del). EXISTS
will always work, unless I am missing something.
Thanks alot guys. Really appreciate it.
"David Gugick" wrote:
IF EXISTS (
SELECT *
FROM dbo.Contact1
INNER JOIN deleted
on deleted.accountno = Contact1.accountno )

> But your original code does not handle multi-row deletes where one row
> deleted exists in the Contact1 table and one row that is deleted does
> not. Tthe EXISTS code above won't work correctly in that case either.
> You could just always run both inserts for rows that exist and for rows
> that do not, assuming those rows are mutually exclusive and forget
> checking for existence.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||David, I just read your response more carefully and I see I missed your poin
t.
In this case, the records are touched through a front end app. All Contsupp
records with a rectype of 'C' will always have an associated Accountno in
Contact1. It is possible to delete individual Contsupp records, in which cas
e
the first part of the query with the IF EXISTS statement will be true. The
second part of the trigger is to handle deletion of Contact1 records. When a
Contact1 record is deleted, the associated Contsupp records are automaticall
y
deleted imediately after the Contact1 record. (there is a deletion trigger o
n
Contact1 as well).
Again, thank you for taking a look
"jenks" wrote:
> In this case, the deleted rows with rectype of 'C' will always have an
> accountno value in one of the two tables(contact1 or contact1del). EXISTS
> will always work, unless I am missing something.
> Thanks alot guys. Really appreciate it.
> "David Gugick" wrote:
> IF EXISTS (
> SELECT *
> FROM dbo.Contact1
> INNER JOIN deleted
> on deleted.accountno = Contact1.accountno )
>|||jenks wrote:
> In this case, the deleted rows with rectype of 'C' will always have an
> accountno value in one of the two tables(contact1 or contact1del).
> EXISTS will always work, unless I am missing something.
> Thanks alot guys. Really appreciate it.
> "David Gugick" wrote:
> IF EXISTS (
> SELECT *
> FROM dbo.Contact1
> INNER JOIN deleted
> on deleted.accountno = Contact1.accountno )
Let's say that two rows are deleted in a single statement:
Delete from dbo.Contsupp
Where accountno in (1, 2)
One of the rows deleted exists in Contact1. The other one doesn't. The
EXISTS statement will return true because one row exists in the
relationship between deleted and Contact1 and the corresponding insert
will take place. However, there is another row in the deleted table
which does not exist in Contact1 and its insert will not be executed.
So I think you still have a problem in your code.
David Gugick
Quest Software
www.imceda.com
www.quest.com

MonthName function in IIF

Hi,
We are getting a runtime #error when using MonthName in an IIF function more
than once as follows:
IIF(Parameters!ReportPeriod.Value <
7,MonthName(Parameters!ReportPeriod.Value +
6),MonthName(Parameters!ReportPeriod.Value))
Parameters!ReportPeriod is an integer, and the expression works Ok until the
second MonthName is added.
Thanks,
MattDoes this work (by adding an explicit cast)?
= IIF(Parameters!ReportPeriod.Value > 7,
MonthName(Parameters!ReportPeriod.Value + 6),
MonthName(CInt(Parameters!ReportPeriod.Value)))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E9371099-FDAB-4A79-95D5-8344C8595347@.microsoft.com...
> Hi,
> We are getting a runtime #error when using MonthName in an IIF function
> more
> than once as follows:
> IIF(Parameters!ReportPeriod.Value <
> 7,MonthName(Parameters!ReportPeriod.Value +
> 6),MonthName(Parameters!ReportPeriod.Value))
> Parameters!ReportPeriod is an integer, and the expression works Ok until
> the
> second MonthName is added.
> Thanks,
> Matt|||Thanks - that works Ok.
"Robert Bruckner [MSFT]" wrote:
> Does this work (by adding an explicit cast)?
> = IIF(Parameters!ReportPeriod.Value > 7,
> MonthName(Parameters!ReportPeriod.Value + 6),
> MonthName(CInt(Parameters!ReportPeriod.Value)))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E9371099-FDAB-4A79-95D5-8344C8595347@.microsoft.com...
> > Hi,
> > We are getting a runtime #error when using MonthName in an IIF function
> > more
> > than once as follows:
> >
> > IIF(Parameters!ReportPeriod.Value <
> > 7,MonthName(Parameters!ReportPeriod.Value +
> > 6),MonthName(Parameters!ReportPeriod.Value))
> >
> > Parameters!ReportPeriod is an integer, and the expression works Ok until
> > the
> > second MonthName is added.
> >
> > Thanks,
> > Matt
>
>sql

Month value in SQL

Hi all

Does anyone know how to get the month field from a date in a SQL statement? I am using Oracle 8.0

There is a field called "birth_date" in my table and I have to print the number of records group by month as follows:

Month Count
--- ---
1 45
2 187
3 18
. ..
. ..
. ..
etc

I need the month value in 1-12 format. Please help..

-Chinnaselect month(birth_date),count(*)
from yourtable
group by month(birth_date)

rudy|||There is not "month" function in Oracle!
that shoulde be:
select to_char(brith_day,'mm'),count(*) from yourtable group by to_char(brith_day,'mm')|||select to_char(sysdate,'MON') from dual;

will return today's month|||MONTH() is ANSI/ISO standard sql

my apologies, Chinna, i overlooked the fact that you said oracle 8

oracle corp, in its infinite wisdom, apparently did not begin supporting standard sql until release 9

:(

Month Format

Hello Experts!

I am trying to format the datetime field in my grouping. Right now I am using the expression =Month(Fields!TestDateTime.Value) in the edit group properties and it returns (for example) May 01 for the group. I would like it to show up as May 2007 instead. Would someone be kind enough to post what the correct expression should be?

Thanks,

Clint

Not sure if there is a better way, but this works:

Code Snippet

=MonthName(Month(Fields!DateOrderCreated.Value)) + " " + format(Fields!DateOrderCreated.Value,"yyyy")

|||Thanks, but that also does not work. It returns the same "May 01".|||What datatype is the field you are formatting?|||It is a datetime data typ|||

If it is a datetime data type, then you can use Format() on it using any of the standard formatting codes, like this:

Code Snippet

=Format(myDataField, "Mon yyyy")

|||Hmmmm still not working. What area should the code be put in to? I have tried the group properties, the field property value, and the custom format are of the field to no avail. It will either error out or return the same "May 01". I am working from the report layout area of the reporting services.|||LOL. Found the answer. Simply in the Format area, I just put a "Y" in the custom format field. thanks so much for your help though.

Money type confusion

I need a little advice for the Money datatype. When entering a value like $18.20 into a column of datatype Money, it stores and returns a value of 18.2 . It is set to the default paramaters. How can I change this so that it is correctly displayed as $18.20. My results are printed onscreen through VB, is that where I need to reformat?You sure it's money?

DECLARE @.x money

SELECT @.x = 18.20

SELECT @.x

EDIT: It's a presentation layer issue...

Then There Also

SELECT CONVERT(varchar(15),@.x,1)|||Positive it's Money|||Where's the sql being executed from? a stored procedure or called from a front end?

Did you try the code I posted in QA?|||It's a .asp page with VB. It's a dynamic SQL statment. And after running your code I got 18.2000. So it looks like it's good to 4 decimal places. This may be dumb to ask now, but should I kill that column and recreate it?|||You should be able to use the Convert function to format your output as a string, but formatting is best handled by your application interface.

blindman|||Do you know how to script the table so we can see the DDL?

Did you use the convert from the page?|||I'm a graphic designer doing the basics on MSSQL, I didn't even know you could script a table let alone tell you what a DDL is. But I'm a smart guy, I'll catch on quick.

And by converting do you mean something like CSTR to cast the returned value into a string or is it done within the SQL statment?

Originally posted by Brett Kaiser
Do you know how to script the table so we can see the DDL?

Did you use the convert from the page?|||SELECT CONVERT(varchar(15),@.x,1)

Just replace the @.x with the column name

Do you have SQL Server client tools installed on your desktop?

Do you know what Enterprise Manager is?|||Actually you should make the varchar(25) to handle all possible money values...

money and smallmoney
money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.|||[i]Actually you should make the varchar(25) to handle all possible money values...

quote:
------------------------

money and smallmoney
money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.

------------------------

Brett ...
That sounds like an awful lot of Money. Hope the person having so much can lend some to me :D|||HAHA, after 5 minutes of staring at the wrong webpage I was working on, I got myself straightened out and found the right one. The Convert expression works like magic.

And yeah, i'm working in Enterprise Manager. I guess I'm a little above basic.|||I thought 15 was enough...but you still have to take in the right side of the decimal, the decimal itself, commas, and a sign...25

922 Trillion Dollars...|||Thanks for you help, I should be able to manage from here|||If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.|||Actually, it's more a matter of a development methodolgy...always code for what can happen...always...

That way there will never be any holes...|||Originally posted by stmaher
If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.

There go my hopes down the drain :D.

BTW Brett is right. The code should take care of all possible conditions.

Brett ... though I think varchar(15) will take care of Bill Gates wealth for sure|||Free forum !? What is your mailing address ? Or you can call the toll free number and submit your credit card information.|||What? Have NONE of you guys been receiving your checks?

Must be a problem in the payroll database...

blindman|||Just send me your SSN's and mailing addresses and I will get you on the payroll. If you receive any unexpected mail, like credit cards ..., ignore it and just forward them to me. :-)|||> That sounds like an awful lot of Money

hang on a sec...you must be assuming USD. I wonder whether the amount would still look so large if it was to refer to Japanese Yen. Not to mention some inflated currency from a developing country.|||You'd be surprized.
We have a thing in the IT dept. we call the "2 comma rule".

In other words, if the number (currency) doesn't have at least 2 commas, it isn't worth worrying about.

Swear to God.

money format ..need some help..please

Hi everybody.
Here's my Product table

ID int,
Title nvarchar(50),
Price money

How can I get value from price field like this
IF PRICE is 12,000.00 it will display 12,000
IF PRICE is 12,234.34 it will display 12,234.34

Thanks very much...I am a beginner. Sorry for foolish question

Formatting is generally applied at the page where the data is going to be displayed as opposed to altering the datasource itself.

If you were going to databind your sql data to something such as a GridView, then you can alter the data's format by specifying a DataFormatString.

for currency you can use: {0:c}
for numeric data with 2 digits after the decimal you can use: {0:n2}

For example:

 
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="Price" DataFormatString="{0:c}" HtmlEncode=False HeaderText="With Currency Symbol" /> <asp:BoundField DataField="Price" DataFormatString="{0:n2}" HtmlEncode=False HeaderText="Without Currency Symbol" /> </Columns></asp:GridView>
  

If you are actually looking to alter the data in sql server, please let us know.

|||

Thanksmbanavige very much for your answer. I use DataList to display my items and I want to display1 pricein my page. I try to use this code

<%If((Eval("Price") *100) mod 100 >0) { %>

<%#Eval("Price","{0:N2}")%>

<% } else {

%> <%#Eval("Price","{0:c}") %>

<% } %> . But it seem wrong. How can I do something like that ?

|||

I'm not sure i follow what you're trying to do with that code. If you want one price, then choose only one of the sample formats i provided

use this: <%#Eval("Price","{0:c}") %>

or this: <%#Eval("Price","{0:N2}")%>

but not both at the same time.

Unless you're dealing with unpredictalbe currencies, i'd probably just go with: <%#Eval("Price","{0:c}") %>

|||

mbanavige:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Price" DataFormatString="{0:c}"
HtmlEncode=False HeaderText="With Currency Symbol" />
</Columns>
</asp:GridView>

Hi Mike,

What would cause a page with say ten colums, all formated in the same manner, to only have five colums display currency correctly and the remainder to just be numbers?

I have this weird scenario going on and it is not a syntax error, as I have checked in dozens of times.

|||

Did you set HtmlEncode to False for all the columns?

|||

Yes I did. I just did a character by character study of two similar pages. The first page is for one State the next page for a different State. Every single character is identical. Therefore I have no option to conclude that there must be some fundamental change in the SQL database tables themselves. each State has it's own table. I will go in and make sure that each field is set to exactly the same data type.

If there is any other possibility that you can think of, please let me know. I am certain that this is not a code issue, the code is working, the reason I am not getting the formatting to work is probably because what is expected in the DB table, is something else. I'll drop you a line after I have checked the tables.

|||

Oaky, I was correct, the problem was in the database. The fields in question on the working table were 'money' and the same fields in the non-working table were 'float'. The instant I changed over to 'money', the web pages formatted perfectly.

There is an interesting sideline to this. In the beginning all state tables were identical, and the database itself was hosted on remote servers in Dallas, Texas in the USA. The database is now hosted on remote servers in Sydney, Australia and when the databse was transferred it somehow mysteriously changed. The technique of restoring a backup on the new server, then attaching it, was how we did the transfer, but it was not 100% flawless. We could not then nor now figure out why some very minor changes occured, perhaps there may have been some slight version differences on server software.

At any rate it's all fixed now, so the moral to the tale is this, don't always assume your code is incorrect, sometimes, it might be other things, as in this case, an issue with the database.

Best wishes.

money format

Hi.
I have a money field and its value is greater than a thousand.For example its value = 32.885,60
I want to show the field's format like this (this number's format).
I mean the thousand separator should be .(dot) and the decimal separator should be ,(comma)
And I want two digit after the decimal separator.All these conditions matches with this number(32.885,60)
Could you help me?

hi muhsin,

write an embeddede function which returns your amount like this: amount.ToString("N")

|||

Dear,

Write the embedded fucntion (=format(field,'fomat')

HT

from

sufian

|||Can you write for me please?
I tried but it didn't work.
What should I write in the format style area?|||

Could you format the string in code and then do a string replace to replace the , with a . and the . with a ,?

|||

Hi Muhsin,

sorry this isn't an answer for you, but very curious as to the currency you are working in?

I am used to formatting it the other way around (32,885.60)

99

|||

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

|||

SpaceCadet wrote:

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

But this is not I want.
This is already a format in the properties of the textbox.
You don't need to write a function for this.
I want the thousand seperator to be dot and the decimal seperator to be comma.
Your solution's result is for example 15,250.30.
But I want 15.250,30.
Thanks anyway.
If you find another solution , please share it with me!|||You can accomplish this by adjusting the Language property for the textbox, or the entire report to a language that uses this format. The language settings can be specific to just number formats when just setting the NumeralLanguage property on the textbox. After setting these properties, just use "N" as the format code.

See the following link for more information about International Considerations for Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

money format

Hi.
I have a money field and its value is greater than a thousand.For example its value = 32.885,60
I want to show the field's format like this (this number's format).
I mean the thousand separator should be .(dot) and the decimal separator should be ,(comma)
And I want two digit after the decimal separator.All these conditions matches with this number(32.885,60)
Could you help me?

hi muhsin,

write an embeddede function which returns your amount like this: amount.ToString("N")

|||

Dear,

Write the embedded fucntion (=format(field,'fomat')

HT

from

sufian

|||Can you write for me please?
I tried but it didn't work.
What should I write in the format style area?|||

Could you format the string in code and then do a string replace to replace the , with a . and the . with a ,?

|||

Hi Muhsin,

sorry this isn't an answer for you, but very curious as to the currency you are working in?

I am used to formatting it the other way around (32,885.60)

99

|||

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

|||

SpaceCadet wrote:

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

But this is not I want.
This is already a format in the properties of the textbox.
You don't need to write a function for this.
I want the thousand seperator to be dot and the decimal seperator to be comma.
Your solution's result is for example 15,250.30.
But I want 15.250,30.
Thanks anyway.
If you find another solution , please share it with me!|||You can accomplish this by adjusting the Language property for the textbox, or the entire report to a language that uses this format. The language settings can be specific to just number formats when just setting the NumeralLanguage property on the textbox. After setting these properties, just use "N" as the format code.

See the following link for more information about International Considerations for Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

money datatype

i have money a datatype in my table and it allows null
value. If i try to enter a null value from my asp page i
am getting error... what i need to do on my sql server
table so it does not error me out. i think, it has
something to do with precision and scale... but I don't
know what.
ERROR:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near ','.
ThanksCould you display the string that you are constructing in your application?
Look at this string when the money column is null. I am sure you will know
what's causing the problem. It just looks like you are concatenating
something with NULL and the whole thing becomes NULL.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"eren" <oeren@.calcoastcu.org> wrote in message
news:004c01c37d37$d3bd55e0$a401280a@.phx.gbl...
i have money a datatype in my table and it allows null
value. If i try to enter a null value from my asp page i
am getting error... what i need to do on my sql server
table so it does not error me out. i think, it has
something to do with precision and scale... but I don't
know what.
ERROR:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near ','.
Thanks|||This looks like a syntax error in the statement generated by the asp page.
Verify that the statement is correct and that nulls are handled correctly by
the asp code generating the statement.
Shaun
"eren" <oeren@.calcoastcu.org> wrote in message
news:004c01c37d37$d3bd55e0$a401280a@.phx.gbl...
> i have money a datatype in my table and it allows null
> value. If i try to enter a null value from my asp page i
> am getting error... what i need to do on my sql server
> table so it does not error me out. i think, it has
> something to do with precision and scale... but I don't
> know what.
> ERROR:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near ','.
> Thanks