Access 97 - Problem with Null-Values in Fields (Script, Module, Query)

[frag]

Hi!

I have a major problem with Access...

There is a table myTable_1 with some fields (int, text, long). What I do is run a query on this table and insert the result in a second table myTable_2. myTable_2 has only text-fields, so the int and long values will get converted.
No problem so far...

After that I try to export myTable_2 with a module:

Private Function putDifs()


Dim DB As Database
Set DB = CurrentDb

Dim MD As Recordset
Set MD = DB.OpenRecordset("myTable_2")

Open "C:\myFile.txt" For Output As #1

MD.MoveLast
MD.MoveFirst
Do Until MD.EOF

  Print #1, ; MD![Field1]; "|"; MD![Field2]; "|"; MD![Field3]; "|"; MD![Field4]; "|"
    MD.MoveNext
Loop
    
MD.Close
Close #1


End Function

This works as well... but there is one strange thing going on. I told the fields in myTable_2 to allow values of zero-length and the default value should be "" (empty string). Some fields of myTable_1 have NULL-Values and after "copying" the fields in myTable_2 and exporting the table I will have "NULL" as values in my textfile but I want "" (empty string).
What am I doing wrong?


Cheers

frag

patrick.metz@epost.de

 

[cheerio]

Use the Nz function

eg nz([field1],"&quot will pick up the value of [field1] if not null or it will supply a zero length string.

So in your append query, append nz([field1],"&quot not [field1].

Null means that we don't know what the value is. It is not the same as a zero or a zero length string which are very specific values.

 

[TonyJollans]

Hi frag,

As well as specifying Allow Zero Length, you must also set the Required Property to "Yes". If you don't do this, although you should, via SQL, be able to set values explicitly to an empty string, empty fields populated via the normal Access interface will be set to Null.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[frag]

cheerio,

thank you for your answer. i am sure that this would work but my query is already running for about 2,5 minutes and i think that this would decrease the performance even more. i think i will let 'sed' do the job and replace every NULL with "" in the textfile, this will hopefully be a lot faster.

i am just wondering when the default-value for a field will be used if not in the case of a NULL-value...

thx you again...

patrick.metz@epost.de

 

[frag]

@TonyJollans:

If I set Required to "Yes" I will get an error-message when inserting the values from my query:

Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 139127 record(s) due to validation rule violations.

.
.
.

patrick.metz@epost.de

 

[cheerio]

That's why I said to use NZ. As it's a native Access function the extra time should be quite low.

 

[TonyJollans]

Hi frag,

So what is your Validation Rule that is violated?

The error message does NOT say that you have a problem converting Nulls to default zero-length strings, which should work as you say you want.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.