IIF Statement with many Values

[jsbarroso]

I am trying to create an IFF statement in a query that if the field HRID is populated and the space category field equals the wrong space type than my results should be "Check Occup". I have tried several combinations but I still cannot get this to work. Any ideas?
=IIf([HRID]>"" And [SCat Text]="BRR_Restructuring" Or "Building_Common" Or "Floor_Common" Or "Vacant_Available","Check Occup")
Thank you in advance.
:banghead:

 

[pbaldy]

With multiple tests, you have to repeat the field:

Field = "A" OR Field = "B"

You may also have a parentheses problem, when mixing And & Or.

 

[boblarson]

You have to refer to the field for each or use an IN

.
IIf(Len([HRID] & "") > 0 And [SCat Text] In("BRR_Restructuring","Building_Common","Floor_Common", "Vacant_Available"), "Check Occup", "")
.

The other way is

.
IIf(Len([HRID] & "") > 0 And [SCat Text]="BRR_Restructuring" OR [SCat Text]="Building_Common" OR [SCat Text]="Floor_Common" OR [SCat Text]= "Vacant_Available", "Check Occup", "")
.

 

[jsbarroso]

Just tried =IIf([HRID]>"" And [SCat Text] In ("BRR_Restructuring","Building_Common","Floor_Common","Vacant_Available"),"Check Occup") which seems to be working.Thank you,

 

[Pat Hartman]

[HRID] > "" isn't doing what you think it is.
If the field can contain nulls, you need to test for them specifically -
[HRID] Is Not Null

OR if the field might be null or contain a ZLS, then you can combine the test this way:

[HRID] & "" <> ""

This expression concatenates the column with a ZLS and then tests to make sure it is not = to a ZLS. This works because when you use the & to concatenate a field that might contain a null, the expression returns whatever you concatenated it to. So, [HRID] & "a" <> "a" would work just as well for this purpose.

 

[jsbarroso]

good point. Thank you,

 

[jsbarroso]

Paul,
On another subject. Are you similar with scanning to an access 2007 form? I have a business group looking for the ability to scan invoices, docs, ext. or attached PDF files to a form. They would like to see the scanned image on the form so they can approve it electronically. I have the approval process down but, I cannot get the scanning piece to work. Would you have any recommendations?
Thank you in advance.
Jerry

 

[pbaldy]

I'm not similar with a form...I'm a lot taller.

Seriously, I haven't scanned directly into Access or tried to control a scanner with it. I've got apps where a user can navigate to scanned files and I save the path, but I don't try to view it within Access, I just open it in its default application (PDF in Adobe Reader, etc).

 

[Pat Hartman]

Unless you are scanning into an OLE field, you wouldn't use Access to control the scan. You would scan the document and give it a "good" name. Then in Access, use the common file dialog to locate the .pdf and store it's address. Then use the FollowHyperlink method in the double-click event of the address field to open the document. FollowHyperlink is better than other methods since it relies on the Windows file association tool. As long as Windows "knows" what program opens files of a certain type, the FollowHyperlink will activate that program an open the doc. I've used it with .dot, .dotx, .xls, .xlsx, .html, .pdf, .txt, .bmp, .gif, and even URL's to web pages. Don't use the hyperlink data type though. Just store the file name as text.

 

[michaeljryan78]

Then in Access, use the common file dialog to locate the .pdf and store it's address. Then use the FollowHyperlink method in the double-click event of the address field to open the document. FollowHyperlink is better than other methods since it relies on the Windows file association tool. As long as Windows "knows" what program opens files of a certain type, the FollowHyperlink will activate that program an open the doc. I've used it with .dot, .dotx, .xls, .xlsx, .html, .pdf, .txt, .bmp, .gif, and even URL's to web pages. Don't use the hyperlink data type though. Just store the file name as text.

This is a fantastic suggestion. I would store the document links in a separate table to allow for a relatioship just in case 1 record needs many docs linked and approved.