Newbie Help Please: Update table via procedure 
Author Message
 Newbie Help Please: Update table via procedure

Greetings:

I am new to Access, so please bear with me. I have a text file that I imported
into a new database. It's a mailing list. The source is in all caps and I need
to change the addresses to resemble proper case as well as add some consistency
('North', 'N', N.' to something consistent) to the data.

I created a routine that opens the table and creates a recordset. I declared
some private vars Address1, Address2, Address3 As Strings, etc. and not all
fields in the database contain data. (This is where I get errors about trying to
assign a NULL value from a variant to a string - How do I check for values????).

Here is the code fragment:
Do Until rst.EOF
// process records
Address1 = ![Address1]
Address2 = ![Address2]
etc...
//Process each field, change case etc.
![Address1] = Address1
etc...
.Update
Loop

The recordset obtained (from what I have read) returns a variant for each field?
If the field is empty, the value returned is NULL. I get an error of trying to
assign a NULL to a string.

How can I overcome this. I have tried everything possible to check for NULL:
// this does not work
if ![Address1] = Null Then
Address1 = ""
Else
Address1 = ![Address1]
Endif

I tried to check for a length as well, but no success.

Can someone help me out here so I can get this going (PLEASE).

Is there a web site out there with example code that I can learn from as well?

Thanks for your valuable time.

Best regards,

George K. Grous
BroadLogic Incorporated
http://www.*-*-*.com/
E-mail address ROT13 encoded



Mon, 26 Nov 2001 03:00:00 GMT  
 Newbie Help Please: Update table via procedure

Quote:

> Greetings:

> I am new to Access, so please bear with me. I have a text file that I imported
> into a new database. It's a mailing list. The source is in all caps and I need
> to change the addresses to resemble proper case as well as add some consistency
> ('North', 'N', N.' to something consistent) to the data.

Use the StrConv function to change things to ProperCase:
'***********
str = StrConv(str,vbProperCase)

The consistency... well, you'll probably have to hard code that for the
conversion... Use the InStr function to locate specific things in the string, and if
it doesn't return a value of zero, then it was found and you can use this to replace
it with some other "consistent" value...

Quote:

> I created a routine that opens the table and creates a recordset. I declared
> some private vars Address1, Address2, Address3 As Strings, etc. and not all
> fields in the database contain data. (This is where I get errors about trying to
> assign a NULL value from a variant to a string - How do I check for values????).
> <snip>
> How can I overcome this. I have tried everything possible to check for NULL:
> // this does not work
> if ![Address1] = Null Then
> Address1 = ""
> Else
> Address1 = ![Address1]
> Endif

Something can't equal Null.  It just *is*...  (ponder on that one for a while...
<g>)

Use the IsNull(<item>) to check for being null.

'************
If IsNull(Address1) Then
    ....
Else
    ....
End if
'**************

Or try something like:

'*************
![Address1] = Address1 & ""
![Address2] = Address2 & ""
'*************

which, if the "variant" string is Null, will cause it to go to a zero-length
string.  Make sure that the field in the table has Allow Zero Length set to True in
the table design....

--
HTH
-------------------------
| gHaD (Aaron Robinson) |
|MJM-PC Solutions, Inc. |
|      Chicago, IL      |
-------------------------



Mon, 26 Nov 2001 03:00:00 GMT  
 Newbie Help Please: Update table via procedure

Thanks for the pointers. I thought I tried the IsNull() function and that did
not work on the recordset field (It was also very late at night...).

I will give this stuff a try when I get home tonight and hopefully I'll be on
track again.

Thanks again for the help.

Quote:

>> Greetings:

>> I am new to Access, so please bear with me. I have a text file that I imported
>> into a new database. It's a mailing list. The source is in all caps and I need
>> to change the addresses to resemble proper case as well as add some consistency
>> ('North', 'N', N.' to something consistent) to the data.

>Use the StrConv function to change things to ProperCase:
>'***********
>str = StrConv(str,vbProperCase)

>The consistency... well, you'll probably have to hard code that for the
>conversion... Use the InStr function to locate specific things in the string, and if
>it doesn't return a value of zero, then it was found and you can use this to replace
>it with some other "consistent" value...

>> I created a routine that opens the table and creates a recordset. I declared
>> some private vars Address1, Address2, Address3 As Strings, etc. and not all
>> fields in the database contain data. (This is where I get errors about trying to
>> assign a NULL value from a variant to a string - How do I check for values????).
>> <snip>
>> How can I overcome this. I have tried everything possible to check for NULL:
>> // this does not work
>> if ![Address1] = Null Then
>> Address1 = ""
>> Else
>> Address1 = ![Address1]
>> Endif

>Something can't equal Null.  It just *is*...  (ponder on that one for a while...
><g>)

>Use the IsNull(<item>) to check for being null.

>'************
>If IsNull(Address1) Then
>    ....
>Else
>    ....
>End if
>'**************

>Or try something like:

>'*************
>![Address1] = Address1 & ""
>![Address2] = Address2 & ""
>'*************

>which, if the "variant" string is Null, will cause it to go to a zero-length
>string.  Make sure that the field in the table has Allow Zero Length set to True in
>the table design....

Best regards,

George K. Grous
BroadLogic Incorporated
http://www.broadlogic.com
E-mail address ROT13 encoded



Mon, 26 Nov 2001 03:00:00 GMT  
 Newbie Help Please: Update table via procedure
Sir:

I am equally new to Access, but with regard to the problem of handling Nulls
one should keep in mind that the string concatenation operator '&' doesn't
propagate Null, so if you want an empty string  where a string field is
null, you can say rec.field("FLDA") & "". At least this seems to work for me
and its is often more convenient than checking IsNull().


Quote:

>Thanks for the pointers. I thought I tried the IsNull() function and that
did
>not work on the recordset field (It was also very late at night...).



Tue, 27 Nov 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Help: 3043 Error updating MS-Access tables via Netware-mounted disk from Win2K

2. PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP,

3. NEWBIE HELP !!! Updating Access Table

4. Exporting Access Table via DDE to Excel - Please Help

5. newbie help please - update a record in a database

6. newbie help please - update a record in a database

7. PLEASE Help with updating two tables

8. problem with a table update, please help

9. Re-linking tables via a procedure...

10. newbie trying tu update Excel cells via external source

11. Updating ODBC linked table via code

12. VB.NET updating three tables at once via a datagrid

 

 
Powered by phpBB® Forum Software