Creating a numberic field from Not IsNull 3

[Stevehewitt]

Hi Guys,

Bit of a newbie to this so please bare with me!

I have a text field called "RejectReason". I'm trying create a field on the fly in a query which returns a 1 when there is a entry in the RejectReason field, and a 0 when there is nothing in the field.

This is so I can create totals in a PivotTable.

Any help apprecicated.

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Remou]

Hi
How about (?):
IIf(Trim([RejectReason]) & "" = "",0,1)

 

[JerryKlmns]

Stevehewitt,
In query design view, add this field to your query
EmptyReason:iif(len(trim([RejectReason]))=0;0;1)

 

[Stevehewitt]

Hi Guys,

Thank you both. I tried both solutions, but I couldn't get JerryKlmns SQL to work in the query. Thanks for the help.

Remou - Your worked a treat. Thank you very much! - A star.

Cheers again guys,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Stevehewitt]

Sorry guys, just based on the above string, how would I do this if the field it's based on (RejectReason) is a Yes/No field? I've tried to tinker with it but it doesn't want to work!

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[JerryKlmns]

EmptyReason:iif([RejectReason]=True;0;1)

0=if true
In SQL View the "[blue];[/blue]" changes to "[red],[/red]

 

[PHV]

Simply this:
EmptyReason: Abs([RejectReason])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Stevehewitt]

Cheers JerryKlmns, spot on! Works great - thank you!

(I've been playing with the IFF function and reading it on MSDN... sure I'll get there eventually ;-))

Thanks again,




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[JerryKlmns]

PHV, you never cease to amaze me! A * 4 u
(1=True)

 

[Stevehewitt]

Shocking! Cheers guys. If I could give more stars I would!

Ta,




Steve.

"They have the internet on computers now!" - Homer Simpson