Manipulation tables in VBA 
Author Message
 Manipulation tables in VBA

Hi

I am a expirenced programmer in languages like Object Pascal (Delphi) and C,
but I am new to Access and VBA.

I have a Access DB with a table X in it. X has a column A that I want split
into to other columns B and C. The contents of A (numbers) has to be written
into B if it is below 20 and into C if it is above. Ex

A
5
22
19
18
26
...

becomes

B   C
5
     22
19
18
     26
...

I have made the columns B and C but I have alot of rows in the table so I
want the process of writing the numbers from A to B and C to be automatic. I
guess that I can write some VBA code to do it, but have no idea of how to
get access to the table X in the VBA code. Generally I dont know how to do
it. Please help!!!

Thanx

Per Steffensen



Sat, 09 Nov 2002 03:00:00 GMT  
 Manipulation tables in VBA
Two update queries

Query1 SQL (Update field B)
-------------------------------------------
UPDATE Table1 SET [B] = [A]
WHERE (([A]<20));

Query2 SQL (Update field C)
-------------------------------------------
UPDATE Table1 SET [C] = [A]
WHERE ((([A])>20));

Of course you don't say what you want to happen if Field A is equal to 20.


Quote:
> Hi

> I am a expirenced programmer in languages like Object Pascal (Delphi) and
C,
> but I am new to Access and VBA.

> I have a Access DB with a table X in it. X has a column A that I want
split
> into to other columns B and C. The contents of A (numbers) has to be
written
> into B if it is below 20 and into C if it is above. Ex

> A
> 5
> 22
> 19
> 18
> 26
> ...

> becomes

> B   C
> 5
>      22
> 19
> 18
>      26
> ...

> I have made the columns B and C but I have alot of rows in the table so I
> want the process of writing the numbers from A to B and C to be automatic.
I
> guess that I can write some VBA code to do it, but have no idea of how to
> get access to the table X in the VBA code. Generally I dont know how to do
> it. Please help!!!

> Thanx

> Per Steffensen



Sat, 09 Nov 2002 03:00:00 GMT  
 Manipulation tables in VBA
You could do this in code, but it's easier and more efficient with an update
query:

UPDATE tblTest SET tblTest.B = IIf([A]<20, [A], Null), tblTest.C =
IIf([A]>20, [A], Null);

BTW: What happens if A = 20?

--
Brendan Reynolds


Quote:
> Hi

> I am a expirenced programmer in languages like Object Pascal (Delphi) and
C,
> but I am new to Access and VBA.

> I have a Access DB with a table X in it. X has a column A that I want
split
> into to other columns B and C. The contents of A (numbers) has to be
written
> into B if it is below 20 and into C if it is above. Ex

> A
> 5
> 22
> 19
> 18
> 26
> ...

> becomes

> B   C
> 5
>      22
> 19
> 18
>      26
> ...

> I have made the columns B and C but I have alot of rows in the table so I
> want the process of writing the numbers from A to B and C to be automatic.
I
> guess that I can write some VBA code to do it, but have no idea of how to
> get access to the table X in the VBA code. Generally I dont know how to do
> it. Please help!!!

> Thanx

> Per Steffensen



Sat, 09 Nov 2002 03:00:00 GMT  
 Manipulation tables in VBA
I wouldn't use code.  I would use either an update query (to update B and C
in the same table as A), or an append query (to add to a new table).  The
query would be designed something like (update query SQL):

UPDATE Table1 SET Table1.B = IIf([A]<20,[A],Null), Table1.C =
IIf([A]>=20,[A],Null);

I modified your specification a little.  If it is less than 20, it will
appear in column B.  If it is greater than #or equal to# 20, it will appear
in column C.

This will work mutch faster than VBA code.

Michael Kucan

Quote:

>Hi

>I am a expirenced programmer in languages like Object Pascal (Delphi) and
C,
>but I am new to Access and VBA.

>I have a Access DB with a table X in it. X has a column A that I want split
>into to other columns B and C. The contents of A (numbers) has to be
written
>into B if it is below 20 and into C if it is above. Ex

>A
>5
>22
>19
>18
>26
>...

>becomes

>B   C
>5
>     22
>19
>18
>     26
>...

>I have made the columns B and C but I have alot of rows in the table so I
>want the process of writing the numbers from A to B and C to be automatic.
I
>guess that I can write some VBA code to do it, but have no idea of how to
>get access to the table X in the VBA code. Generally I dont know how to do
>it. Please help!!!

>Thanx

>Per Steffensen



Sat, 09 Nov 2002 03:00:00 GMT  
 Manipulation tables in VBA
The answers are fine, but just for the sake for curiousity. How would one do
if it should be done in VBA-code.

Kind Regards Per Steffensen



Mon, 11 Nov 2002 03:00:00 GMT  
 Manipulation tables in VBA
In VBA you would need to open a recordset for the table, then loop
through the records.
Look at "openrecordset" in the help.

HTH,

Vincent



Mon, 11 Nov 2002 03:00:00 GMT  
 Manipulation tables in VBA
Sub CreateBC

dim db as database
dim rs as recordset

set db = dbengine()() 'Sets the db as the default current db

set rs = db.openrecordset("TableA")

with rs
      .movefirst
      while not .eof
            if .fields("FieldA") < 20 then
                    .edit
                    .fields("FieldB") = .Fields("FieldA")
                    .update
            else
                    .edit
                    .fields("FieldC") = .Fields("FieldA")
                    .update
            end if
     .movenext
     wend
end with
rs.close

end sub



Quote:
> Hi

> I am a expirenced programmer in languages like Object Pascal (Delphi)
and C,
> but I am new to Access and VBA.

> I have a Access DB with a table X in it. X has a column A that I want
split
> into to other columns B and C. The contents of A (numbers) has to be
written
> into B if it is below 20 and into C if it is above. Ex

> A
> 5
> 22
> 19
> 18
> 26
> ...

> becomes

> B   C
> 5
>      22
> 19
> 18
>      26
> ...

> I have made the columns B and C but I have alot of rows in the table
so I
> want the process of writing the numbers from A to B and C to be
automatic. I
> guess that I can write some VBA code to do it, but have no idea of
how to
> get access to the table X in the VBA code. Generally I dont know how
to do
> it. Please help!!!

> Thanx

> Per Steffensen

--
----------------------------------------------
Looking for something? Try me.
http://peopleprofit.com/biz/jimsdepot.html

Sent via Deja.com http://www.deja.com/
Before you buy.



Tue, 12 Nov 2002 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. VBA String Manipulation DLL Missing

2. String manipulation in VBA

3. vba script in excel to perform manipulations in excel

4. Mail manipulation from VBA in Access

5. Word File Manipulation with VBA or VB6

6. VBA (MS Access 2000) - Menubar manipulation on forms

7. Text file manipulation from MS Access VBA

8. VBA manipulation of word-embedded chart

9. Opening Table & Manipulation

10. Data Manipulation between tables

11. Copy fields from record in table A to record in table B using VBA

12. Copy Record from Table 1 to Table 2 - Acc97 - VBA

 

 
Powered by phpBB® Forum Software