substitute value for Dlookup() if null is returned

[aero.space.junkie.1]

hi all,

I am writing lines to a txt from a module. here's a sample line that I have:

Write #1, "332: " & DLookup("[STATION IDENT]", "STATIONS", "[STATION IDENT] = 'YYY' AND [REGION] = 'ZZ") 
 
& " / #Plans With Reference: " & DCount("[FIX]", "PLANS", "[FIX] = 'AA' AND [REGION] = 'QQ' 
 
AND [PLAN IDENT] = 'N17' AND [AREA] = 'CHINA'") & " (needs to be > 0!)"

Sometimes these lookups return null values because the lookup failed. That's fine, but the txt receives a zero length string instead of "null", which is what access returns by itself. I know there are multiple issues with sending data to txt files from visual basic, as the stream manipulates some of the values when and where it needs to. Is this one of those issues?

I need an indicator in the txt that tells me if my lookup returned null or not. but the only thing I can think of is to write something like this:

Write #1, "332: " & IIF(DLookup("[STATION IDENT]", "STATIONS", "[STATION IDENT] = 'YYY' AND [REGION] = 'ZZ") = NULL, 
 
"NULL", DLookup("[STATION IDENT]", "STATIONS", "[STATION IDENT] = 'YYY' AND [REGION] = 'ZZ")) 
 
& " / #Plans With Reference: " & DCount("[FIX]", "PLANS", "[FIX] = 'AA' AND [REGION] = 'QQ' 
 
AND [PLAN IDENT] = 'N17' AND [AREA] = 'CHINA'") & " (needs to be > 0!)"

But that is just way to cumbersome to sift through in a module. I have 350 of these code lines right now, but only 100 or so have anything in them. The rest just return zero-length strings because there are no tests associated with them.

Does someone here know how I could differentiate the failed tests from the dummy tests? The failures cannot return blanks. That's the issue. thanks!

 

[spikepl]

NZ function

 

[aero.space.junkie.1]

NZ function

I have always disliked NZ() for various reasons. So I've never used it. and thus, never think about doing so. If it really is that simple, I thank you sir!

that is something that I can use find/replace to put in. Manual labor is not an option with this much coding.

 

[aero.space.junkie.1]

NZ function

thanks spike. Yes, that works fine. The coding is a bit tedious, as I just got to this.

 

[boblarson]

I have always disliked NZ() for various reasons.

Would like to know what those reasons are. I can't see living without it, myself.

 

[mafhobb]

So on something like this...

Dim FDate As Date
FDate = DLookup("SubcallDate", "Stats Spec Customer First Date")
Me.txtFirstIssue = FDate

How would you use it? Can the value returned be replaced to be 0/00/00 instead of the error?

mafhobb

 

[mafhobb]

I've tried this and I get an overflow error. I think it has to do with the fact that I am dealing with a date as the 00/00/0000 gets formatted into 0 / 0 / 0 when I type it.

FDate = Nz(DLookup("SubcallDate", "Stats Spec Customer First Date"), 0 / 0 / 0)

mafhobb

 

[mafhobb]

This returns a type missmatch error. This may be due to the actual variable being a long date

FDate = Nz(DLookup("SubcallDate", "Stats Spec Customer First Date"), "00/00/00")

mafhobb

 

[tehNellie]

Would like to know what those reasons are. I can't see living without it, myself.

I'll chip in with one, because it's driven me batty for years.

It's unreliable.

It'll work fine in a query or a piece of code until it suddenly doesn't and returns an "Unrecognised Function" error message for no reason I can discern.

I'd love someone to shed some light on why this happens but I've resorted to iif([something] IS NULL, replacementvalue, [something]) because NZ breaks eventually whenever I use it in Access and I've no idea why.

 

[mafhobb]

This still gives me type missmatch even though the date format is exactly as it appears in my tables

FDate = Nz(DLookup("SubcallDate", "Stats Spec Customer First Date"), "00/00/0000 00:00:00 AM")

Thoughts?

mafhobb

 

[mafhobb]

Solved it with this:

FDate = Nz(DLookup("SubcallDate", "Stats Spec Customer First Date"), Now())

 

[tehNellie]

This returns a type missmatch error. This may be due to the actual variable being a long date

FDate = Nz(DLookup("SubcallDate", "Stats Spec Customer First Date"), "00/00/00")

mafhobb

You're trying to place a string in a datefield.

#00/00/00# is also invalid in this context because it's not a valid date.

 

[Kiwiman]

Howzit

THis will also work I believe. This will, in this case give you 1/1/1900 where null

FDate = Nz(DLookup("SubcallDate", "Stats Spec Customer First Date"),dateserial(1900,1,1))

 

[mafhobb]

Thanks TehNellie and Kiwiman. This helps in understanding the all important details that usually catch me off guard!

Mafhobb

 

[boblarson]

Howzit

THis will also work I believe. This will, in this case give you 1/1/1900 where null

FDate = Nz(DLookup("SubcallDate", "Stats Spec Customer First Date"),dateserial(1900,1,1))

Why add the overhead of the DateSerial function when

FDate = Nz(DLookup("SubcallDate", "Stats Spec Customer First Date"),#1/1/1900#)
Will work without the extra function.