appending 00 at the end of string data from sql server db

[rebeccah2004]

hello. i hope you can help me. this seems pretty straight forward, but i am missing something. i have a column called internala_c and it is 6 chars in the db. we need to append 2 zeros at the end in out vb app. i have this:
spaces_required = (internala_c - Len(tempString))
tempString = tempString & Space(spaces_required) & Mid(rs.Fields(5), 1, internala_c_Len) & "00"

spaces_required = (cusip_mtf - Len(tempString))
tempString = tempString & Space(spaces_required) & Mid(rs.Fields(6), 1, cusip_mtf_Len)

but what is does is this:
306211 00040228AG3
when i need this:
30621100040228AG3

my column positions look like this(i included the 2 surrounding also):
settle_date_mtf = 1
internala_c = 7
cusip_mtf = 17
trantypetextmtf = 26

any input or help would be greatly appreciated!

thank you,
rebecca

 

[johnwm]

This bit < & Space(spaces_required)> is adding your spaces. If you don't want spaces, leave that bit out

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[rebeccah2004]

IT WORKED! thank you very much.

regards,
rebecca

 

[bboffin]

I realise that you have solved your problem but an alternative approach is to get the SQL database to do it for you as follows:

Instead of using a SQL statement that contains:

SELECT internala_c, .... FROM ....

try

SELECT internala_c + '00' AS internala_c00, .... FROM ....

This will return a column called internala_c00 that already has the '00' appended to it.

SQL also has quite a range of String functions that can be used in this way and which generally correspond to the VB string functions.

 

[rebeccah2004]

wow, thanks for that tip too. i will give that a shot.

everyone is really nice and helpful in these forums. that's really cool.

regards,
rebecca