Change Data Type

[alwsid]

Hello...

could somebody show to me how to change data type from my table:

old data type single to new data type currency

waiting response from you all..
thanks

[JHausmann]

1) build a new table that has the structure the way you want it to be.
2) use SQL to copy the data from the old table to the new table (insert select *)
3) drop the old table
4) use SQL to copy the data from the new table into a table (name it the same as the old table) (select into)
5) drop the table built in step one (you should only have the table built in step 4 with the same name as the original table left)

[alwsid]

Thanks JHausmann...

But I means I've compiled my Access.mdb using vb5 wizard.

So my problem the data type from table in Access.mdb is currency but after I've compiled using vb5 wizard, when i'm open
my vb form that's field just show as number data type. So how could I resolved my problem
to change that data type from my form to currency using DAO..

[Clunietp]

Do you want to change the field in the database from long to currency, or do you want to format the database field as currency before it is displayed to the user, but keep the database field as long??

[Gerald]

It sounds like you have a textbox control bound to a field that is using the MS Access "currency" data type. It also sounds like you want the textbox to show $1.50 instead of 1.5. Is that correct? If this is the case then you will probably need to use the Masked Edit control with the format property set to something like: $#,##0.00;($#,##0.00).

Gerald M.

[alwsid]

Thanks Gerald and Clunietp...

Yes Gerald that's what I need.I mean I want the textbox to show $1.50 like this.
As I know in VB5, the textbox format property we cannot format the data type, unless we using DAO if I've not wrong.
So could you give some example to show how to format that data type using DAO which to show $1.50.

still waiting your anwser...

[Clunietp]

Hi! Add this code to your form, this will format the field automatically after the data is repositioned. It is assuming you are using a data control with bound textboxes.

Private Sub Data1_Reposition()
    On Error Resume Next
    Text1.Text = FormatCurrency(Val(Text1.Text))
End Sub

Put this code in your Form_Activate event as well, so when you load up the form it will be formatted then as well.

Tom

[alwsid]

Thank's Tom
You're the Great Programmer.

Nice to see you and your guide much help me.

I'm trying with your code, wow ! great short.
About your code in Data1_Reposition I've change To: Text1.Text=Format(Val(Text1.Text),"Currency") This code will work.

So I've one more question to you Tom.

Let say I've an array 10 bound Textbox.
7 bound Textbox is a single Data Type and
3 bound Textbox is a vbText.

So how could I Format my bound Textbox which have a single data Type to 2 decimal place.

Needs your help Tom.





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

[Clunietp]

This simple code should work if you don't have any numeric values in your TEXT fields, otherwise you would have to evaluate the type of database field that the textbox is bound to, and format that field correctly.

I started to code that way, but there are quite a few different numeric fields in DAO's DataTypeEnum, and the following code is SOOOO much simpler and still should work. If you need it the other way, let me know.

This assumes you have a text box array that is bound to a data control using DAO (as I thought you had described):

Private Sub Text1_Change(Index As Integer)
    
    if data1.recordset.fields(text1(index).Datafield).type <> dbtext then
        If IsNumeric(Text1(Index).Text) Then
            Text1(Index).Text = Format(Val(Text1(Index).Text), "####.00")
        End If
    end if   
End Sub

Glad I can help!

Tom

[This message has been edited by Clunietp (edited 12-23-1999).]

[alwsid]

Hi Tom..

It's work.
But one thing if my field is dbText which I put CodeID like "001234" change to "001234.00" so how to resolve this problem.

waiting your answer....

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

[Clunietp]

hey alwsid

I have modified my previous code (above)to check for that situation (dbText).

I did not test it, but it should work. Please let me know if it does not.

Tom

[alwsid]

Hi Tom...
Merry christmas.

I'm still trying but it's doesn't work.
Still waiting from you...

[Clunietp]

are you sure your field is dbText? Maybe it is dbMemo or another variant of the text data field....

[alwsid]

Sorry Tom..
Yes You're wright.
I've a mistake with my data type. One of my field is a dbmemo.
Now it's work.

Thank's a lot Tom and see you again.