Formating a column in access using visual basic

[superfly]

Hi,

I am new to visual basic and Ms Access but this is what I am trying to do. I have a Field called Act/Inact and in this field I have some records that say active and some records that say inactive. I would like a code that looks for active and assigns a 0 in that field and when it sees inactive, it assigns a 1.

So far this is what I have;

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimisti c = 3

Set objConnection = CreatObject("AD ODB.Connection" )
Set objRecordSet = CreatObject("AD ODB.Recordset")

objConnection.O pen _
"Provider=Micro soft.Jet.OLEDB. 4.0; " & _
"Data Source = C:\script\db1.m db"

objRecordSet.Op en "SELECT * FROM General" , _
objConnection, adOpenStatic, adLockOptimisti c

objRecordSet.Cl ose



Please help
objConnection.C lose

[Killer42]

Do you want to actually change the value stored in the field, or just display it differently?

[superfly]

Hi,
I want to change the value in the field. I have written a script but not sure if its correct since I am new to visual basic. Also I am getting an error when I compile it in command prompt. It say's Microsoft VBScript compilation error: Expected 'End'

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimisti c = 3

Set objConnection = CreateObject("A DODB.Connection ")
Set objRecordSet = CreateObject("A DODB.Recordset" )

objConnection.O pen _
"Provider = Microsoft.Jet.O LEDB.4.0; " & _
"Data Source = C:\script\db1.m db"
objRecordset.Cu rsorLocation = adUseClient

objRecordSet.Op en "SELECT * FROM general" , _
objConnection, adOpenStatic, adLockOptimisti c

objRecordSet.Mo veFirst

if strCriteria = "Act/Inact = 'ACTIVE'" then
Do Until objRecordSet.EO F
objRecordSet.Fi nd strCriteria
objRecordset.Fi elds.Item("Act/Inact") = "0"
objRecordset.Up date
ObjRecordset.Mo veNext
Loop

objRecordSet.Mo veFirst

ELSE if strCriteria = "Act/Inact = 'INACTIVE'" then
Do Until objRecordSet.EO F
objRecordSet.Fi nd strCriteria
objRecordset.Fi elds.Item("Act/Inact") = "1"
objRecordset.Up date
ObjRecordset.Mo veNext
Loop

ELSE strCriteria = "Act/Inact = '' "
Do Until objRecordSet.EO F
objRecordset.Up date
ObjRecordset.Mo veNext
Loop

objRecordSet.Mo veFirst

Do Until objRecordSet.EO F
Wscript.Echo objRecordSet.Fi elds.Item("Act/Inact")
objRecordSet.Mo veNext
Loop


objRecordSet.Cl ose
objConnection.C lose

Thanks

[Dököll]

Hi,
I want to change the value in the field. I have written a script but not sure if its correct since I am new to visual basic. Also I am getting an error when I compile it in command prompt. It say's Microsoft VBScript compilation error: Expected 'End'

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\script\db1.mdb" 
objRecordset.CursorLocation = adUseClient

objRecordSet.Open "SELECT * FROM general" , _
    objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

if strCriteria = "Act/Inact = 'ACTIVE'" then
Do Until objRecordSet.EOF
objRecordSet.Find strCriteria
objRecordset.Fields.Item("Act/Inact") = "0"
objRecordset.Update
ObjRecordset.MoveNext
Loop

objRecordSet.MoveFirst

ELSE if strCriteria = "Act/Inact = 'INACTIVE'" then
Do Until objRecordSet.EOF
objRecordSet.Find strCriteria
objRecordset.Fields.Item("Act/Inact") = "1"
objRecordset.Update
  ObjRecordset.MoveNext
Loop

ELSE strCriteria = "Act/Inact = '' " 
Do Until objRecordSet.EOF
objRecordset.Update
  ObjRecordset.MoveNext
Loop

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("Act/Inact")
    objRecordSet.MoveNext
Loop


objRecordSet.Close
objConnection.Close

Thanks

I got nothing, just codes tags, and this link :-)

http://classicasp.aspfaq.com/general/why-do-i-get-errors-in-the-800a03f1-800a03ff-range.html

And this:

http://www.microsoft.com/technet/scriptcenter/funzone/puzzle/answers/jul0105.mspx

Hope it helps you tackle this:-)