question about query 1

[ksbigfoot]

I have a parameter (Param1)
I am using the following code in my select expert:

If {?PropertyID} = 0 Then
  {v_TabInsert.PropertyID} Is NULL
Else
  {v_TabInsert.PropertyID} = {?PropertyID}

I get the message, "The remaining text does not appear to be part of the formula."

How do I add a part of the formula to check for Nulls?
Thanks

 

[ksbigfoot]

I think I figured it out.
I changed the select expert to:

If {?PropertyID} = 0 Then
  1 = 1
Else
  {v_TabInsert.PropertyID} = {?PropertyID}

Thanks

 

[BobSuruncle]

Or you could write it this way:

( {?PropertyID} = 0
or {v_TabInsert.PropertyID} = {?PropertyID} )


Bob Suruncle

 

[ksbigfoot]

Howdy Bob,
I tried your way and I don't get any records back.
I only pass 0 into ?PropertyID if PropertyID is NULL.
So if ?PropertyID = 0, I would like to say something like:
AND {v_TabInsert.PropertyID} Is NULL

I used the code this way and it returned no records:
If ( {?PropertyID} = 0
or {v_TabInsert.PropertyID} = {?PropertyID} ) Then
{v_TabInsert.PropertyID} = {?PropertyID}

Thanks
ksbigfoot

 

[lbass]

If you want to return records where propertyID is null when the parameter is 0, then use a formula like this:

(
{?PropertyID} = 0 and
isnull({v_TabInsert.PropertyID})
) or
{v_TabInsert.PropertyID} = {?PropertyID}

-LB

 

[ksbigfoot]

Hi LB,
I used your format this way:

If ({?PropertyID} = 0 and isnull({v_TabInsert.PropertyID})) or {v_TabInsert.PropertyID} = {?PropertyID} Then
{v_TabInsert.PropertyID} = {?PropertyID}

I think I am missing something here as no records are returned. I think it is because in my table, the PropertyID field is either null or contains a number.
I only pass in a 0 into the parameter of the propertyID as it requires me to.
If the PropertyID is 0, I do not want the statement

{v_TabInsert.PropertyID} = {?PropertyID}

put into my query.

I am assuming that I am missing something in the code snippet I took from your post.

Thanks

 

[lbass]

Not sure why you didn't enter it just as I suggested, which should have worked. If you want to use the if/then, it should be:

if {?PropertyID} = 0 then
isnull({v_TabInsert.PropertyID}) else
{v_TabInsert.PropertyID} = {?PropertyID}

-LB

 

[ksbigfoot]

Howdy lbass,
I did enter your code above and I am not sure why it didn't work. Thank you, I gave you a star as your last piece of code worked like a charm.

Thanks again,
ksbigfoot