Help with IsNull function

[LPurvis]

>> "Did that and even returned the fields expression to the one Paul previously posted"
Yes - that's exactly what you should have done.

The inability to post a sample is going to be hindering you now then.
If everything is present as it should be - it could be anything - including corruption.

Just to re-iterate... you have
=[DIRECTIVES.SHORTPLA] & [DIRECTIVES.SHORTDIR_TYPE] & " " & [INST_NO] & ("."+[SERIES_NO])

Do all those referenced fields have related bound controls on the report (visible or not).
Why are SHORTPLA and SHORTDIR_TYPE prefixed with the table name?
Are those fields returned more than once in your query?

Obviously - the control you're entered this expression into isn't named as any of your bound field names is it - otherwise you'd be getting #Error all the time.

 

[LPurvis]

I clearly had a brain cramp there, didn't I?

Bah - no worries about that, I'm currently having forum cramp.
Don't even know why I'm posting anywhere. Just isn't feeling fun at the moment. :-s

 

[spudracer]

Leigh, I don't have bound controls on the report, I will try doing that and post the results.

 

[spudracer]

Now whenever I run the report it says it can't find two fields used in the expression, even though I have them bound to that report.

 

[spudracer]

Ok, I know you guys are trying to help and I sincerly appreciate it. I was reluctant to post a sample of the database simply because I didn't want to have to pull all the data I had on it.

I've trimmed it all down, created a couple dummy records, one for each different type and trimmed the report down to the neccessities.

Hopefully you can figure out why my database isn't working properly.

 

[pbaldy]

I took the criteria off the query so it returned records (easier than figuring out what the criteria need to be). The report shows the field as expected, with no period and no error on the second record. I'm using Access 2007 on XP Pro. I'll be interested to see if it fails for Leigh. The only thing I can think to check is the references in the VBA editor, because it's the usual culprit when something works on one computer and not another.

 

[spudracer]

Paul,
Yeah, I set that up because I wanted the report to be specific to each record displayed through the form. Is there a way to still use the critera and have it work correctly?

 

[spudracer]

Actually, to save lots of time and trouble, I just realized something. The report I need to generate is really only for records with something in both the INST_NO and SERIES_NO fields.

Would there be a simple code to allow ONLY files categorized as Instructions to open a specific report and nothing or a dialog box saying something to the tone of "NOT REQUIRED FOR THIS TYPE OF DOCUMENT" for other files when a user clicks the report button?

If it requires a code through VBA, I'm gonna need all the help I can get because I'm a terrible when it comes to that stuff.

 

[LPurvis]

FWIW, as Paul did, I also had to relax the query criteria a bit (and/or edit some table data) to get the report's query to give results - but once doing so the report behaved as it should.

 

[spudracer]

FWIW, as Paul did, I also had to relax the query criteria a bit (and/or edit some table data) to get the report's query to give results - but once doing so the report behaved as it should.

Ok, but did you read my post right above yours?

 

[LPurvis]

There are almost too many options to address.
You could just limit the report's source to those type of rows - and if the report is launched and returns no records report that back in the OnNoData event and Cancel the report there.
Alternatively - from the form check (yes this is all in VBA) if the record is of the correct "type".

I can't offer specifics - as I'm not sure what you're asking to happen.
Perhaps Paul will come back with something for you - if you want to give the example scenario that needs to be handled.

 

[LPurvis]

Bah - I can drag an ounce more effort out I'm sure.. ;-)
Your command button which launches the report - might just have code like

    If Me.DIR_TYPE = "Instruction" Then
        DoCmd.OpenReport "DEPTREVIEW", acPreview
    Else
        MsgBox "NOT REQUIRED FOR THIS TYPE OF DOCUMENT"
    End If

instead of it's current OpenReport method.
Not sure if that's all you want though still.
See how you go.

 

[spudracer]

Ok, thanks for the help. I really do appreciate it! I'm a little bogged down with work right now, but I will try to put this in either today or Monday.

 

[LPurvis]

I suspect Bob will have a tutorial for this sort of thing in his pictorial examples...
Hmm can't find it...
OK - a quick one of my own. (Unlike me to do one of these - I like my words ;-)
When you view a control's properties, you choose [Event Procedure] for the event in question. Click the [...] button to the right and it will open up the event procedure for you in that form's class module. This is where the code mentioned must go - as it refers to the form controls itself.

 

[spudracer]

Leigh,
Still an error whenever I try to run that code. I'm going to try it at home and see if I get anything different. I'm on a government computer, so I might be restricted somehow.

Also, I had this as a control on my form "=[SHORTPLA]&""&[DIR_TYPE]&" "&[INST_NO & ("." + SERIES_NO)]", but now all it will return is #Name?

I checked and re-checked everything I could to make sure all the fields were typed right and they were. It was working fine yesterday, now it won't do it at all.

I'm getting ready to scrap this whole thing and start over from scratch.

 

[LPurvis]

You'll need to copy and paste the actual exact expression in use for us to be able to offer anything constructive.
Or the MDB again...

 

[spudracer]

The exact expression is:

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

 

[LPurvis]

That still looks typed. (Copy Paste is a wonderful thing in development).

So did you have that expression working correctly earlier then?
Did you look at the results mentioned by Paul and I?

 

[spudracer]

I do this type of thing:

INST_NO & ("." + SERIES_NO)

Leigh,
I used the above post at first, then I wanted to add a couple different fields in order to have it displayed as it is on the actual document.

 

[LPurvis]

I think you'd be best off attaching a failing example MDB again.
Make a copy, cut out the bits you don't need. Then open it and test it - make sure it works but is still failing. Then post that. We'll have a peek.