Query criteria - porting from excel, a bit lost!

[Kiwiman]

Howzit

David thanks for the info re Optional - it will definitely come in handy somewhere down the track.

Although not my issue , i have tested this and it does work from within the immediate window - using your tests above. But when I passed a null date via a query, it returns #Error.

 

[RCheesley]

I think this is where the problem lies - when passing it via a query. As you say, it should work in theory, the logic appears sound, but the query is bombing out with null values.

Ruth

 

[DCrake]

So now we have identified that yes it does work via the immediate window, but does not work via the query. So what do we do? we go to the query and change the functionality slightly to

Loc:Location(Nz(Field1,0),Nz([field2],0))

That way it avoids a null value being passed to the function, but the function can still test for a 0 value. Tad ahh!

David

 

[RCheesley]

In awe of the awesomeness

Many thanks for your help David, I can use this approach for a huge range of other things in my databases so has been a real help!