Help with IsNull function

[spudracer]

I'm trying to set up a field to pull from two separate fields in the current record.

One of the fields is simply titled INST_NO, the other SERIES_NO. I need to separate the two with a "." and that part I have no problem, but in the instance there is nothing supplied in the SERIES_NO I need there to not be a period after what's supplied in INST_NO.

I'm sure this is easy, I'm just not catching it.

 

[pbaldy]

I do this type of thing:

INST_NO & ("." + SERIES_NO)

 

[spudracer]

PERFECT!! Thanks man!

 

[spudracer]

Actually, now I've got a problem.

I'm needing to use what was inserted into the merged field in a report. I know I don't know a lot about Access, but I'm beginning to think I don't know anything about Access.

 

[pbaldy]

I'm not sure I understand the problem. What I gave you should work in a query, report, etc.

 

[spudracer]

Ok, yeah, I just figured it out. Thanks again!

 

[spudracer]

Actually, I take that back.

Instances on a report where there is nothing in the SERIES_NO field, returns an #Error in every field supplied from the form. Using the expression you posted.

Of course, that is when I combine four different fields in one box. That might be my problem, correct?

 

[pbaldy]

I just tested and it worked fine in a report (make sure you precede with "="). Can you post a sample db?

 

[spudracer]

I can't post a sample, but I'm kinda confused, because looking at the entries that HAVE something in the SERIES_NO field, they show up without any problem.

The entire expression goes something like (I've got it at work and I'm at home right now):

=[SHORTPLA]&""&[DIR_TYPE]&" "&[INST_NO & ("." + SERIES_NO)]

Is the fact that I have brackets around the last section the reason why those without an entry in the SERIES_NO field are showing an #Error?

It has to go where it's at, so moving it to the front of the expression is out of the question.

When I get to work in the morning, I'll take another look and see if maybe I added an extra space or a special character somewhere.

Again, thanks for all your help!

 

[pbaldy]

You don't want brackets around the whole thing like that.

 

[spudracer]

Ok,
I'm at work now and removed the brackets, tested it, still receiving #Error on records without an entry in the SERIES_NO field, below is what expression is in the report:

=[DIRECTIVES.SHORTPLA] & "" & [DIRECTIVES.SHORTDIR_TYPE] & " " & [INST_NO] & ("."+[SERIES_NO])

What is really driving me crazy is that on any other record with an entry in the SERIES_NO field, everything shows up fine, but with that field remaining empty, it causes everything else to be an error.

 

[MSAccessRookie]

Shouldn't that be a "&" instead of a "+"?

=[DIRECTIVES.SHORTPLA] & "" & [DIRECTIVES.SHORTDIR_TYPE] & " " & [INST_NO] & ("." & [SERIES_NO])

 

[spudracer]

No, that returns the same error.

 

[MSAccessRookie]

Is Series_No a text field? You can only concatenate a text field. If it is integer or other number, then convert it before you concatenate it.

 

[spudracer]

See the thing is is that INST_NO is also an integer. So when there is something in both INST_NO and SERIES_NO, it works perfect. When there is something missing from the SERIES_NO field is where it messes up.

 

[pbaldy]

Shouldn't that be a "&" instead of a "+"?

No; I'm taking advantage of a difference between the two. The "+" propagates Nulls, and the "&" does not. Thus, if the field with it is Null, you will not get either the field or the period.

This is a guess, but try

...& ("."+CStr([SERIES_NO]))

 

[spudracer]

That returns the same error, still only on records without an entry in the SERIES_NO field.

 

[LPurvis]

...& ("."+CStr([SERIES_NO]))

Paul - that'll negate the possibility of the field being Null (as CStr can't accept NUlls - and returns a string regardless - which would prevent your propagation of Null).

I'd ask - do you have a control on your report bound to SERIES_NO?
Add one - hell, name it SERIES_NO too. Make it invisible.
Report's ability to refer to the underlying recordsource fields as impicit properties is not as consistent as that of forms.

Cheers.

 

[spudracer]

Leigh,
Did that and even returned the fields expression to the one Paul previously posted, still no dice.

Kinda going off the subject here, but I was wondering if I would be able to have a drop-down set up so that whatever value I select from the drop-down will populate two other fields with all the information.

This isn't that big of a database, but it's becoming a monster to get it to function the way I want it to.

 

[pbaldy]

Paul - that'll negate the possibility of the field being Null

Good point Leigh. I clearly had a brain cramp there, didn't I?