Stuart,

This would work (I've included working tables etc)

But the insert at the end works, you would just need to modify it for your tables.

create table dbo.sumtest

(

ID int identity,

Type varchar(10),

Amount int

)

insert...

Hi Peter,

I had looked at using decimal and money - didn't think to use float - tend to avoid it if I can! Nice solution.

Pity I missed what happens with...

Dear Dobrzak,

I am sure you have a really good reason for this, as if the numbers are stored as decimal or money then the trailing zeros wouldn't matter anyway, to...

Hi John,

Many thanks for that.

The funny thing is he'll end up with full admin rights eventually but this is his first moving from Access to SQL Server, so I was...

Hi John,

Thanks for that, but doesn't that also allow him to drop objects?

Cheers,

Rodney.

Yelena,

I also agree with you.

I know that the questions and free and I have still learn something from this, and that some questions are abigious on purpose (pick the...

I am with you Terry.

I doubled checked with BOL just to make sure my memory wasn't deceiving me - I noticed the caveat so re-read the question. It didn't seem...

Ritesh,

It's looks as though you have missed out a &

strSQL2 = "SELECT qryEatingDisorder.* FROM qryEatingDisorder WHERE PageID = '" & lngRecordNo & "'" & "and EatingID = '2'"

Should do the...

Yueming,

You need to write your case statement like this:

Case when...

Also don't forget that dates in SQL Server use single quotes not #. So '12/02/2005'

Also it's a general rule to put dates in the following format yyyymmdd, as to avoid...

Hi Bob,

If I understand you correctly, you want to insert data from a query into a table but not populate all the fields in the table, in which case something...

Hi Jeff,

I did think of using SUBSTRING, but I can't remember know why I used RIGHT... I think it may have been because the account numbers are variable length, so...

Syed,

Just realised I missed a closing bracket, one after the +1, should read:

select RIGHT(mytext, Len(mytext) - patindex('%[^0]%', mytext)+1) from mytable

Obviously I should have said replace "mytext" with you column name,...

Hi Syed,

I had a similar problem when bringing together account information from three different sources and combining them into one report, without leading zeros - don't you love it when...

noggin,

I found this MSDN article:

http://msdn2.microsoft.com/en-us/library/ms189566(SQL.90).aspx

UNSAFE