Home Forums General Discussions Open Topic sql assistance

Viewing 13 posts - 1 through 13 (of 13 total)
  • Author
    Posts
  • #46602

    Anthony
    Participant

    <Recordset1>

    That will plop the records from column "Drawing Number" from Recordset1.

    the data in that column are numbers
    1
    2
    10
    35
    70
    100
    200
    300
    400

    I want to have the results displayed like this:
    001
    002
    010
    035
    070
    100
    200
    300
    400

    If drawing numbers <10 then add 00
    if drawing numbers <100 then add 0

    what is the correct syntax for this?

    asp.

    #98231

    Bucky Ramone
    Participant

    Know that there’s a nice trick for it, will look it up at work tomorrow…. :lol:

    #98232

    jeremiah
    Keymaster

    are you just displaying the results or storing them in a table afterwords?
    If you are just diplaying it you could cast it into a 3 char field for that purpose or do a select as and set the format?

    #98233

    Anthony
    Participant

    im just displaying

    3 char field? how to set up ?

    im hoping to just apply some sort of format with an ‘if’ statement to achieve

    #98234

    jeremiah
    Keymaster

    You could try something like this:

    intNumber = 1
    strResult = Right("00" & intNumber, 3) ‘ gives 001

    intNumber = 35
    strResult = Right("00" & intNumber, 3) ‘ gives 035

    intNumber = 200
    strResult = Right("00" & intNumber, 3) ‘ gives 200

    #98235

    Bucky Ramone
    Participant

    Nice one, Jeremiah…. :D

    This should also do the trick:

    to_char(intNumber,"009")

    the "009" is the format which returns the leading zeroes. If you use the format "999", it will supress the leading zeroes.

    intNumber = 1 –> 001
    intNumber = 35 –> 035
    intNumber = 200 –> 200

    #98236

    jeremiah
    Keymaster

    Yours was even better DB :)

    does to_char work on sqlserver? I’ve never tried it there, just with oracle. Very cool. :twisted:

    Just thought about this – what type of db is this anthony?

    #98237

    Anthony
    Participant

    so is this one ;)

    <%IF (Recordset1.Fields.Item("Drawing Number").Value) <10 THEN Response.Write "00" ELSE IF (Recordset1.Fields.Item("Drawing Number").Value) <100><Recordset1>

    :)

    #98238

    Anthony
    Participant

    it is access 97 data

    #98239

    Bucky Ramone
    Participant
    "jeremiah" wrote:
    Yours was even better DB :)

    does to_char work on sqlserver? I’ve never tried it there, just with oracle. Very cool. :twisted:

    Thanks :D

    I am not too familiar with sqlserver, but according to me ‘to_char’ is a standard sql function :roll:

    #98240

    jeremiah
    Keymaster
    "dB stands for den Buck" wrote:
    "jeremiah" wrote:
    I am not too familiar with sqlserver, but according to me ‘to_char’ is a standard sql function :roll:

    LOL – :)

    if only more packages would adhere to standards my life would easier for sure!

    http://www.zdnet.com.au/builder/architect/database/story/0,2000034918,20264748,00.htm”>http://www.zdnet.com.au/builder/archite … 748,00.htm

    #98241

    Bucky Ramone
    Participant

    :lol:

    One of our sqlserver guys came with this solution:

    Right$("000" & cstr(intNumber),3)

    …which looks remarkably alike to Jeremiah’s suggestion…. :wink: :D

    #98242

    Anthony
    Participant

    thanks for the input guys.
    i went with the last example i posted.
    ;)

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic.