Unwanted Access Null Value data

[MrTK]

Just converting a Access 97 to 03, and discovered that I'm not in Kansas anymore! 03 is quite different. But here's my problem (not the only one, but the one I'm working today). I have a "read only" linked tbl, where one tbl is set to "numbers" and the other to "date". The problem is that in 97, null values would mail merge into Word as null (No data), but in 03 the null value in number field = 0 and the date field defaults as todays date. Both of which I don't want. Is this a conversion problem or does 03 default null values this way?

Any suggestions on how to eliminate the unwanted data?

Thanks

MrTK

 

[dweis]

Not sure if you checked this or not & I'm using XP not '03, but check the default value in the underlying table for each field in question. In XP, a number defaults to 0 instead of null. Not sure on the date issue as XP will not provide a default date. Hope this helped.

 

[MrTK]

Yea, that's really my problem, the underlying table in XP is 0, and the underlying table is "read only", I was hoping for a simple fix to change is back to null.

Not sure about the default date thing. All I can tell you is that where there used to be no date and no data in the field in Access, however when I merge into Word, the null value of the date is today's date. I don't why.

Don't know why they had to mess with the null!

MrTK

 

[dweis]

Are you mail merging or exporting a table to word? You could try using a query instead and create a custom field in the query that states iif(number=0,"",number)and try a date field that iif(isnull(mydate),"",mydate)and then send it to Word to see what you get.

 

[MrTK]

I am trying to export a query to Word. It's strange. When I look at the fields in Access the field is empty, but when it pulls the query in, it has the 0's and date in there. I went to the design mode in Access and typed in your (number=0,"",number) and Access said "Data type mismatch to criteria expression". Now what's going on? I rechecked the data type and it's number field in the underlying table. I don't understand what's going? Any ideas?

 

[PHV]

You can't assign "" to a numeric field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrTK]

So there's no way get rid of the unwanted data that's coming from Access fields?

 

[dweis]

First, there is no quotes around the 0. It's iif(number=0,"",number)
Which is "if number equals 0 then use blank else use number."
How exactly are you exporting this? Are you opening the query and then using file, export? Is there a button you push to export? Are you mail merging?
Another thing you could try is using criteria in your query - that will get rid of the unwanted data - however if you're exporting more than just the number field and the date field then the entire record will be left out. Basically, in the query, under the date field, in the "where" section put Is Not Null and on the next line down in the "where" section under the number field put <>0. If you inadvertently put the 2 criteria on the same line in the query then the query will drop all records that have BOTH a blank date and a 0 for the number field. If you put the criteria on 2 lines then it will drop records where the date is blank OR the number is 0.

I would think that the iif statements that I mentioned earlier should work without a hitch because you're exporting to Word which is only concerned with text, not field types - not like Excel where a field is formatted as a particular type. Or are you importing into a word document that has a table embedded in it?