Need help with Access Expression 1

[OracleVictim]

(if you've seen this post before, sorry....I accidentally posted this as a response)

I'm trying to get a simple expression in the Access report writer to work properly. Essentially if I use:

=IIf([COMPLETION_CODE]="1","Conducted","Cancelled&quot or

=IIf([COMPLETION_CODE]= 1,"Conducted","Cancelled&quot

I get an error. Yet, if I run the following:

=IIf([COMPLETION_CODE],"Conducted","Cancelled&quot

I actually get the word "Conducted" to print. However, this is nonsense. Anyone have a clue as to why this is acting so oddly?

Also, the source database is Oracle and the source field type is character.

OracleVictim

 

[LNBruno]

What's the error message?

< M!ke >

 

[SteveR77]

The manner in which the IIF statement works is

IIF(Expression to be evaluated, Action for True, Action for False)

In the last case you should get Conducted as your output

the express will always evalutate to being true because

the field exist.

If your completion code field from the Oracle DB is evaluating a true/false value try changing the IIF statement to reflect True instead of 1. In the case of Access the value for True is a -1 and False is a 0.


HTH,

Steve

 

[MichaelRed]

If the recordset is in Oracle (passthrough query?) hte more commopn text dellimiter is a single quote. The double quote is correct for Ms. A. - but passthrough queries need to use the syntax of the target db engine. The same applies to &quot;The value&quot; for comparision, it needs to be based on the actual content and syntax of the targes db engine. &quot;True&quot; may be represented by any symbol, s you need to check the 'real' db field type and it's representation in the target db engine syntax.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[OracleVictim]

Thanks

Actually this is a query on an original passthrough. I've tried changing my double quotes to single quotes and am still not having luck. How would you fix the following to make it go?

=IIf([completion_code]='1',&quot;Complete&quot;,&quot;Cancelled&quot

Thanks again

Oraclevictim

 

[MichaelRed]

I'm no Oracle &quot;Oracle&quot;, so my thoughts are just generic, but the line still contains double quotes? If (THEOracle requires single ones, replacing just the ones in the condition statement doesn't seem like it is sufficient.

You might also try just returning a flag (True / False) in the query and dealling with the literal in on hte client (Ms. A.) side?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[OracleVictim]

Thanks. I used single quotes in the source SQL statement and it works just fine..... IIf([completion_code]='1','Complete','Cancelled'). If I use this expression on the report (e.g. expression builder), I get a #Error.

But this works for Now. I'm assuming there must be something about using expressions and the report writer that I don't understand.

Thanks Again

OracleVictim

 

[MichaelRed]

hmmmmmmmmmmmmm,

Again, claiming no understanding of at least a large part of it, but in Ms. A. (report thingggyyyy) the syntak needs to be a-la-Ms. Whatever (Access, SQL Server, etc) so i would THINK (always dangerous) that in that millieu the double quotes would be required? It must depend of the scope of the expression, which I assume (even worse than thinking) that the MS 'report builder' / 'expression builder' is working with a recordset object which exists in Ms. A. therefore uses that syntax, while the query, deriving the recorrdset FROM Oracle needs to use the syntax for Oracle?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places