Problem with returning NULL

[Arjuna80]

Problem with returning NULL (solved)

Hi

Situation:
Trying to read out of a Table(Old) to set each Fields value in another Table(New) i had to write some routines to change the values.
In this situation, after reading out 15 other fields, for this one it (below variable: tmp) returns NULL, allthough the field contains a string: "x".
The fieldName "PerDu" is a boolean field in access 2000.

    tmp = mGetValue("perDu", i)
    If BooleanHandler(tmp) = True Then
        rs.Fields("PerDu") = True
    Else
        rs.Fields("PerDu") = False
    End If

Public Function BooleanHandler(ByVal vInput$) As Boolean
' reads a string and if input contains a string longer than zero, it returns true
On Error GoTo errExit
    BooleanHandler = False
 
    vInput = Trim(vInput)
 
    If Len(vInput) > 0 Then
        BooleanHandler = True
    End If
errExit:
End Function

--> Allthouth checking if it returns NULL, it still recieves NULL, even as it should not as that field contains a stringvalue : "x" <--

Public Function mGetValue(vFieldName$, id, Optional optSrc$ = srcDB) As String
' Reads out a fields content of the source table,
' and returns it as string
'On Error Resume Next
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim var As Variant
 
 
Set db = CurrentDb                 ' Datenbank definieren
strSQL = "SELECT * FROM " & optSrc   ' beliebigen SQL definieren //          " & vFieldName & "
Set rs = db.OpenRecordset(strSQL)  ' Recordset öffnen
 
rs.Move (id)
        If rs(vFieldName).Value = Null Then
            var = " "
        Else
            var = rs(vFieldName).Value
        End If
            mGetValue = var
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

It worked previously, but thats 2 weeks ago, now i dont see why it wont work.
Maybe i just delcared somethign wrong.

Anybody see's my mistake?

Thanks
Arjuna

 

[genesis]

just to mention:

was tmp and i variable properly set?

 

[Arjuna80]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim var As String
    var = ""

I changed it to this, but problem persists.
Intersting is, the error now raises at "var = rs(vFieldName).Value",
previously it raised at: "mGetValue = var".

 

[genesis]

what i meant is for this one

tmp = mGetValue("perDu", i)
If BooleanHandler(tmp) = True Then
rs.Fields("PerDu") = True
Else
rs.Fields("PerDu") = False

its not clear. was that complete already?
End If

 

[genesis]

got to go. maybe someone will assist you. hang on.

 

[Arjuna80]

'On Error GoTo myError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer, varSrc As Integer
Dim tmp As Variant
Dim chkBox As CheckBox

Set db = CurrentDb                 ' Datenbank definieren

strSQL = "SELECT * FROM " & srcDB   ' Source öffnen
Set rs = db.OpenRecordset(strSQL)  ' Recordset öffnen

If Not rs.EOF Then rs.MoveFirst    ' Zum ersten Datensatz
Do While Not rs.EOF
    varSrc = varSrc + 1             'Anzahl einträge auslesen
    rs.MoveNext
Loop


strSQL = "SELECT * FROM " & tarDB   ' beliebigen SQL definieren //
Set rs = db.OpenRecordset(strSQL)  ' Recordset öffnen

MsgBox mGetValue("PerDu", 1)

For i = 1 To varSrc Step 1
    rs.AddNew
    rs.Fields("EasyLine") = mGetValue("Easyline", i)
    rs.Fields("Firma") = mGetValue("Anrede Firma", i)
    rs!Titel = mGetValue("Titel", i)
    
    rs!Nachname = mGetValue("Nachname", i)
    rs!Anrede = mGetValue("Briefanrede", i)
    rs!Vorname = mGetValue("Vorname", i)
    rs.Fields("Strasse-Nr") = mGetValue("Adresse 1", i)
    rs.Fields("Postfach") = mGetValue("Adresse 2", i)
    rs!PLZ = mGetValue("PLZ", i)
    rs!Ort = mGetValue("Ort", i)
    rs!LänderPrefix = mGetValue("Landcode", i)
    
    rs!Kontrollshild = mGetValue("Kontrollschild", i)
    rs!Club = mGetValue("Club?", i)
    
    rs.Fields("Tel-Privat1") = mGetValue("Tel 1", i)
    rs.Fields("Tel-Privat2") = mGetValue("Tel 2", i)
    rs.Fields("Tel-Office1") = mGetValue("Tel 3", i)
    rs.Fields("Tel-Office2") = mGetValue("Tel Reserve", i)
    rs.Fields("Email-Privat") = mGetValue("Adr Reserve", i)
    
    rs.Fields("Last-Mail") = mGetValue("last mail", i)
    rs!Kategorie = mGetValue("Kategorie", i)
    
    rs!Artikel = mGetValue("Artikel", i)
    rs!Erstelldatum = mGetValue("Erstelldatum", i)
    
    rs!Farbe1 = mGetValue("Farbe A1", i)
    rs!Farbe2 = mGetValue("Farbe A2", i)
    rs!Farbe3 = mGetValue("Farbe A3", i)
    
    rs!Magazin = mGetValue("Magazin", i)
    rs.Fields("Magazin-Format") = mGetValue("Format", i)
    
    rs.Fields("Kunde-seit") = mGetValue("Kunde seit", i)
    rs!Kundenalter = mGetValue("Alter", i)

this as is beeing proccess previously and it worked well, it just stops at that part with the "tmp" variable.

 

[namliam]

rs(vFieldName).Value = Null

This is your problem, nothing ever = Null

You need to use the specific Null handlers:
- Field Is Null
In sql

- Isnull(Field)
In VBA

- NZ(Field, "")
In either sql or vba to replace any null by ""

Good luck.

 

[Arjuna80]

Thank you namliam

i now changed it now to:

        If IsNull(rs(vFieldName).Value) Then
            var = ""
        Else
            var = rs(vFieldName).Value
        End If
            var = Nz(var)
            var = CStr(var)
            mGetValue = var

but now it raises a data-conversion (3421) error on its first call

    rs.AddNew
    rs.Fields("EasyLine").Value = mGetValue("Easyline", i)

 

[namliam]

data-conversion error means you are fetching a string, but trying to load said string into a number field... or something simular... Which you cannot do..

Also your mGetValue function is TERRIBLY in-efficient... opening and closing a recordset for each seperate field is terribly slow.
More so your using a row number, which is per definition not fixed unless you use a specific and explicit Order By in your SQL Statement...

You seem to be copy-ing data from one table to another, if I glance the code.
Two comments about that...
1) Why?? It is very unusual to do this unless your importing data or something. Even then why not import directly into your target table?

2) Why not use an append query? This is much faster and much less cumborsome.

 

[Arjuna80]

I had to rewrite the mGetValue for Int and Datefields, to solve the data-conversion.

to 1)
its some kind of migration, some fields of the old table were Textfields, and are now Boolean fields, as example

to 2)
i'm actualy new to access vba, and i could not think of another way.
is there a diffrence between the ACCESS- .AddNew or the "Append" you mentioned, which i have not available in access2000 VBA.

Since i have to read the whole table over 9000 rows, i work through from top to botten, so in the end (from my current view) it doesnt matter which row i'm currently working on, as long all rows get copied as you figured right, but until now, the order was just the same as in the source table.

 

[namliam]

1) Still no excuse to work with this code, append queries can do the job just fine.

2) LOL, no Append I mention is a query form, not a direct 1:1 replacement for .AddNew

The append query was / is present in Access 1.0 and every version beyond it...

As to the order, in 99.99% of all queries the table order is 'fixed' and is used as per order of data entry, probably no real thing to worry about. Thing I am saying is there is no guarantee in order unless you guarantee it yourself using an Order By statement.

Again if you are migrating/copying from one table to another en-masse then using an append query is much faster vs using any code. Then using your code, sorry but that is going to be even more slow....

 

[Arjuna80]

Where could i read about that append query?

MOST important, would that append query, let me CHANGE the values of some fields while reading out the old table?

remember, its not just copy data without changes, the data that has to be copied AND changed is: a) boolean b)listfield (previously textbox) c) date (prev: text), d) integer (prev: text) and even between the booleans i have diffrent a

how could i increase your rep..
i mean after i click on that symbol, what to do then?

srever dear ot nuf syawla stI

 

[namliam]

An Append query is basicaly a normal select query, just changed (Query > append query) when in design view.

You can do anything you do in select queries including changing, converting, formatting, splitting and concattinating data between the tables.

Cint
Cdbl
Dateserial
Left
Right
Mid
IIF

Read about these functions in the access help you will need these (and probably more) to convert everything to what you want it to be.
In the code you have posted you do not convert anything it is just a straight 1:1 copy, if the data types are changing this is done with implicit conversions (allowing access to guess what you want).

This is BAD, you never want implicit conversions you want EXplicit conversions, where YOU tell access how to convert data. This is what the functions (among others) are for. The ones mentioned are the most commonly used, click and search a little to find more.

 

[Arjuna80]

Thank you very much.

Since this is only for a one-time-use performance doesnt matter.
But thanks for this info too, i'll get into it for next time!