Null Values in a Table

[cakoliver]

I'm using Access 2003 to output records to an existing database.
For whatever reason, the existing database uses the null value as one would normally use carriage Return and Line Feed.
As I try to create this particular table entry, as soon as I add the chr(0) all subsequent characters are being ignored in the write to the access work file.
If I replace the chr(0) with chr(10) and Chr(13) Access will accept the subsequent characters but the external database ignores the CHR(10)/CHR(13) and everything comes out on one line.
Any help would be greatly appreciated.

Thanks,

Jeff Oliver

 

[jedraw]

A little context please.

I'm using Access 2003 to output records to an existing database.

Is this access also, and/or is this the external data base you mention?[/quote]

Are you modifying the database for some reason? Do you have specs for the external database?

 

[cakoliver]

For purposes of this discussion the only pertinent thing about the external database is that for this field in this file they have chosen to use the null chr(0) as the character to determine, for display purposes, that any subsequent characters will display on a new line.

I am using Access / VBA to write to a work file, giving the user a chance to review his work before he clicks a button to add the record to the external database.
The field that I'm building is coming from multiple records in an access table. If a particular record has been selected, it's values are concatenated to all previously processed records and the external database wants these records separated by the null chr(10) character.

If I separate them with any other character, Access has no problem and will write all of the values to the work file field. However, if I do that, the external database will not recognize(in the display program) that it is time to start a new line.

If I separate them with the null character chr(10) Access will ignore all characters after the first null it finds and only output the first record to the Access work file.

 

[dhookom]

I am a bit confused about all of this. I do know however that a carriage return/line feed combination is Chr(13) & Chr(10), not the other way around.

Duane
Hook'D on Access
MS Access MVP

 

[cakoliver]

OK, let me simplify this:

I have an access table that has a memo field in it.

If I write "AAAAAAAAAAA" & chr(10) & "BBBBBBBBBB" & chr(10) & "CCCCCCCCC" the resulting output would be AAAAAAAAAAA*BBBBBBBBBB*CCCCCCCCC, where the * represents the little square box for chr(10)

If I write "AAAAAAAAAAA" & chr(0) & "BBBBBBBBBB" & chr(0) & "CCCCCCCCC" the resulting output would be AAAAAAAAAAA

Access will not put the null value or anything after it in the memo field.

 

[PHV]

Access will not put the null value or anything after it
That is standard behaviour for any decent SQL engine ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cakoliver]

The external database does have some "undocumented features" and is Btrieve.
Is there any way around this that you know of ?

 

[PHV]

No, sorry.

 

[cakoliver]

FYI - I was able to output the nulls by changing chr(10) to NULL keyword

 

[dhookom]

I'm curious as to why you thought CHR(10) would be the equivalent of NULL.

Duane
Hook'D on Access
MS Access MVP

 

[cakoliver]

Sorry I meant chr(0)...