empty string vs. null

[SUZYQ]

I have a fixed width file I am importing into a staging table in
Access. I use the Mid function to parse the data. If the length of a
field can be 50 but the text is only 20 I get the text followed by 30
spaces.

When I then move the data to production, I was using the RTrim function
to get rid of those trailing spaces. My problem is that if there's
nothing in the field I'm getting an empty string as opposed to Null.
For the purposes of this database I would rather have nulls (believe it
or not).

Is there a way to do this?

 

[Tony D'Ambra]

You can try this function:

Public Function fnConvertToNullIfEmpty()
Dim varFieldData As Variant

varFieldData = RTrim(varFieldData)
varFieldData = IIf(varFieldData = "", Null, varFieldData)

Debug.Print IsNull(varFieldData)

End Function


Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net

 

[grzegorz nieoczym]

lohll ;lll;kl;
''

'h;jmh;l;klll