By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,326 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

How do I export null values in MS Access 2007

P: 20
I have a macro that I'm writing with VBA that will export a text file. It worked great in testing until I started adding additional columns with null values. I need the null values to be included. Any suggestions?
Feb 14 '14 #1
Share this Question
Share on Google+
11 Replies

P: 294
It might be helpful to get answers to your question if you tell us what kind of information the Macro is exporting. Are we talking Dates, Numbers, Text?

What columns contain the Null data?

Also, are you getting an error? Mis-formatting?

Edit: By the way, you have not listed how familiar you are with MS Access / Functions, etc.
That being said - Check out the Nz() function.
Feb 14 '14 #2

P: 20
I'm exporting a 12 column file table with text columns and 2 numeric columns. Both types contain nulls.

I'm getting a run-time error '94' Invalid use of null. The macro runs fine when I comment out the columns with the nulls.

I've been dabbling with VBA in Access off an on for 3 years to generate reports.

I looked at the Nz function but it seemed to only add a zero in the place of a null and I need to keep the nulls or at least blanks. Any suggestions?
Feb 14 '14 #3

P: 294
Instead of 0 in the Nz function, did you try ""?

Expand|Select|Wrap|Line Numbers
  1. Nz(Variant, "")
Feb 14 '14 #4

Expert Mod 15k+
P: 31,768
You seem to have a problem with your VBA code. Including the VBA code in the question seems like it would make sense.
Feb 16 '14 #5

P: 20
Code is fine... works with the trouble data (nulls) commented out.

Just need some logic to say if variable is null then leave blank. The code is a bit PII sensitive... I'll clean it up and provide the crux of what I'm getting at.

Expand|Select|Wrap|Line Numbers
  1. Public Function CreateTextFile()
  3. 'This function creates a fixed-width text file using the Orders table
  4. 'in CDA Fixed File Import tool. The CHRGAMT field will be Right-aligned
  7.  Dim strRECTYPE As String * 4 'specifies width of 4 characters
  8.  Dim strPROVNUM As String * 20  'specifies width of 20 characters
  9.  Dim strPCN As String * 50    'specifies width of 50 characters
  10.  Dim strCHRGCODE As String * 12 'specifies width of 12 characters
  11.  Dim strFILLER1 As String * 18 'specifies width of 18 characters
  12.  Dim strCHRQTY As String * 7 'specifies width of 7 characters
  13.  Dim strCHRGAMT As String * 15 'specifies width of 15 characters
  14.  Dim strSRVCDATE As String * 8 'specifies width of 8 characters
  15.  Dim strPROC As String * 8 'specifies width of 8 characters
  16.  Dim strORDERMD As String * 22  'specifies width of 22 characters
  17.  Dim strORDERMDTYPE As String * 2    'specifies width of 2 characters
  18.  Dim strFILLER2 As String * 34 'specifies width of 34 characters
  19.  Dim mydb As DAO.Database, myset As DAO.Recordset
  20.  Dim intFile As Integer
  22.  Set mydb = CurrentDb()
  23.  Set myset = mydb.OpenRecordset("GenTable", dbOpenTable)
  25.  'myset.Index = "PrimaryKey" 'DET table must have primary key for export?
  26.  intFile = FreeFile
  28.  Open "c:\GenTable.txt" For Output As intFile
  32.  'This section puts the records from the GentTable in the text
  33.  'file.
  34.   myset.MoveFirst
  35.   Do Until myset.EOF
  36.       LSet strRECTYPE = myset![RECTYPE] 'Field name in brackets
  37.       LSet strPROVNUM = myset![PROVNUM]
  38.       LSet strPCN = myset![PCN]
  39.       LSet strCHRGCODE = myset![CHRGCODE]
  40.       RSet strCHRQTY = myset![CHRQTY]
  41.       RSet strCHRGAMT = myset![CHRGAMT]
  42.       LSet strSRVCDATE = myset![SRVCDATE]
  43.       LSet strPROC = myset![PROC]
  44.       LSet strPROVNUM = myset![PROVNUM]
  45.       LSet strORDERMD = myset![ORDERMD]
  46.       LSet strORDERMDTYPE = myset![ORDERMDTYPE]
  47.       LSet strFILLER2 = myset![FILLER2]
  49.       'Concatenate all of the variables together as in the following:
  50.       Print #intFile, strRECTYPE & strPROVNUM & strCHRGAMT & strPCN & strCHRGCODE & strFILLER1 & strCHRGQTY & strCHRGAMT & strSRVCDATE & strPROC & strORDERMD & strORDERMDTYPE & strFILLER2
  51.       myset.MoveNext
  52.   Loop
  54.   Close intFile
  55.   myset.Close
  56.   mydb.Close
  58.   MsgBox "Text file has been created!"
  60. End Function
Feb 17 '14 #6

Expert Mod 5K+
P: 5,397
String variant will not accept a null value.
You must either use the NZ() using the
Expand|Select|Wrap|Line Numbers
  1. ""
zero length string for the null return or change your string typecast to the "Variant" cast.
Feb 17 '14 #7

P: 20

I finally pulled my head out and used the correct Nz() formatting (listed below).

Expand|Select|Wrap|Line Numbers
  2.   var1 = ""
  4.       LSet strPROC = Nz(myset![PROC], [var1])
  5.       LSet strPROVNUM = Nz(myset![PROVNUM], [var1])
  6.       LSet strORDERMD = Nz(myset![ORDERMD], [var1])
  7.       LSet strORDERMDTYPE = Nz(myset![ORDERMDTYPE], [var1])
  8.       LSet strFILLER2 = Nz(myset![FILLER2], [var1])

Feb 17 '14 #8

Expert Mod 5K+
P: 5,397
Expand|Select|Wrap|Line Numbers
  2. (...)
  3. Dim strRECTYPE As String * 4 'specifies 
  4. (...)
  5. LSet strRECTYPE = nz(myset![RECTYPE],"")
  6. (...)
not variant and nz() but one method or the other
unless you would like to do this

Expand|Select|Wrap|Line Numbers
  2. (...)
  3. Dim varHolder As Variant 
  4. Dim strRECTYPE As String * 4 'specifies 
  5. (...)
  6. varHolder = myset![RECTYPE]
  7. LSet strRECTYPE = nz(varHolder,"")
  8. (...)
Seems to be a tad more work that way (^_^)
Feb 17 '14 #9

Expert Mod 15k+
P: 31,768
The LSet string parameter (Their name not mine - Found to the right of the "=") is actually a string parameter so requires a value that is a string.

Field references can often contain Nulls, so such a reference cannot reliably be used in an LSet statement. Nz(), however, can be used to force the return of a string value when Nulls are involved. Simply use Nz([FieldReference], ""). No cause to bring in another variable.

PS. Your code sample was fine. As the problem occurred only on the LSet lines, you could have got away with including much less. Always a good idea with failing code to identify the error message and the line of failure. I hope you still aren't inclined to consider the code as fine now you realise where it's wrong.
Feb 17 '14 #10

P: 20

I'll be more specific next time. Thanks for all the great feedback!
Feb 17 '14 #11

Expert Mod 15k+
P: 31,768
It's a learning curve. People assume posting questions properly should be easy. It very rarely is.

Getting the right information to share is much harder than it appears. Putting it across in a forum thread also requires knowledge of how they work. Even experienced posters get this wrong a lot. We try to nudge in the right direction, but we do understand it's not as easy as it looks.
Feb 18 '14 #12

Post your reply

Sign in to post your reply or Sign up for a free account.