Tables with 'empty' fields causing grief

[Locust2000]

We use a DOS based program that uses dbase files. I've made a bunch of reports etc through Access for various things that are much faster and nicer in most cases, and simply not posible in some cases using our original setup.

I'm having a problem with tables that have an empty field.

The fields in question are Numeric. In dbase, and empty field is automatically assumed to be 0. In access it deems it does not.

My current string is:

[INBRANCH]![SOH_OPEN]+[INBRANCH]![SOH_RECTS]+[INBRANCH]![UNITS_TM]-[INBRANCH]![SOH_TRFRS]+[INBRANCH]![SOH_ADJ])<>0

This works perfectly as planned, EXCEPT if any of the fields are 'empty' When this happens, the record is omitted from the final query result.

Ideally, I don't want to run an Import Routine to replace Empty with 0 as that's chaning the original DBF which I don't want to do if possible to avoid.

Help!

 

[jaydwest]

In relational databases there is a difference between an empty field and a field with a value of 0. To resolve your problem use the NZ function. The function lets you replace the value of a field or variable if it is null with any value you want.

I suggest you replace each field in your expression with for example

NZ([INBRANCH]![SOH_OPEN],0)

You must do this with each field. A Null in any value in your expression will return null for the value of your expression.

This will replace the value [INBRANCH]![SOH_OPEN] with zero it is is empty or null.

If you have any questions, please let me know.

 

[Locust2000]

Didn't work?

Maybe it's me, but it didn't work?

Here's my EXACT query (without the Where condition which is quite long)

SELECT BRANCH.NAME, BRANCH.[NO], INBRANCH.PART_NO, INMASTER.PART_DESC, NZ([INBRANCH]![SOH_OPEN],0)+NZ([INBRANCH]![SOH_RECTS],0)+NZ([INBRANCH]![UNITS_TM],0)-NZ([INBRANCH]![SOH_TRFRS],0)+NZ([INBRANCH]![SOH_ADJ],0) AS Expr1, INMASTER.PRICE2
FROM (BRANCH INNER JOIN INBRANCH ON BRANCH.[NO] = INBRANCH.BRANCH) INNER JOIN INMASTER ON INBRANCH.PART_NO = INMASTER.PART_NO

My WHERE condition references a completely seperate field and would have nothing to do with the 'NZ' issue above.

 

[Locust2000]

When I say didn't work, I mean didn't give the desired results. Nothing changed in the Query table that gets returned.

 

[jaydwest]

When you say that the results are not correct, what are you expecting and what did you get?

I assumed that the values of the variables were Null. Could you test this assumption by using the NZ function on one of the variables that is known not to exist. If it is not Null, it might be Empty. You can test this using the ISEmpty() function.

Please let me know what you find.

 

[Locust2000]

Yes they are empty, not null's.

When I said didn't give desired results, it's doing the same thing - omitting the lines that contain an empty cell.

I guess what I want is a NZ function except Empty -> 0 instead of null?

 

[jaydwest]

You can create a function to convert Empties to Zero. Here's the code

Public Function EZ(pasValue as variant, pasOnEmpty as variant) as variant

If IsEmpty(pasValue) Then
EZ = pasOnEmpty
Else
EZ = pasValue
End if


End Function

----------------

Let me know if you have any questions or if this works.

 

[Locust2000]

Sorry for being such a beginner..... I can do layout stuff etc but haven't made custom functions before in Access and it's been years since I did any programming(mostly C).

I created a new 'Module' called EZ and pasted your code straight in. The 'Object' was under General, and the 'Procedure' is called EZ.

I changed my query string from NZ to EZ and it came back:

Undefined function 'EZ' in expression.

I'm sure it's something simple and silly

 

[Locust2000]

I tried EZ as a standard module and as a Class module.

 

[jaydwest]

You probably want to place the function in a Global Module if you need this in multple forms in your database. The name of the Global Module doesn't affect this but as a general rule, Modules are usually named something like bas_ . So you could name this bas_GlobalFunctions.

Make sure the Function you created is a Public Function. It should start with the Word Public. If it starts with Private, the Form you are using will not be able to see it.

Please advise if this is the problem.

 

[Locust2000]

Yep it's publin not private.

I created a NEW module(were no existing ones), pasted the code into the blank window that comes up, saved it, and that's it. If I have to 'enable' it somehow I don't know.

This isn't called from a form or anything. I only ever use it by either running the Query directly or opening a report which is just the query formatted into a new page per customer.

Under Modules, I have 1 called 'EZ', and in 'EZ' there's only the 1 function. It does have a line above it though that was already there:

Option Compare Database
Public Function EZ(pasValue As Variant, pasOnEmpty As Variant) As Variant

If IsEmpty(pasValue) Then
EZ = pasOnEmpty
Else
EZ = pasValue
End If


The Option Compare Database is above a thin line though?

 

[jaydwest]

Do you also have the line

End Function


at the end?

 

[Locust2000]

I exported the functoin to a text file:


VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "EZ"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Compare Database
Public Function EZ(pasValue As Variant, pasOnEmpty As Variant) As Variant

If IsEmpty(pasValue) Then
EZ = pasOnEmpty
Else
EZ = pasValue
End If

End Function


I added the End Procedure and no change....

Frustrating because I know it should be a really simple thing....