Query Sql IIf Statement return value

[ice051505]

Hi all, I have a query with 5 field, [Country],[Province],[GroupingInfo],[City],[Area]
I want to write or build a new query to have something on the [City] critiria like:

IIf [City]="NoInfo" And [GroupingInfo]<=1 Then [City]=[Province], else [City] = [City]

This is on the Query Sql, not on the VBA Code



Thanks So Much!

[pbaldy]

What have you got that isn't working? You practically have it written already.

[ice051505]

I worte this:
IIf("NoInfo" AND [GroupingInfo]<=1, [City]=[Province]) Or IIf(Not Like "NoInfo", [City]=[City])

It returns nothing on my query...

[ice051505]

Never Mind, somehow it worked.....I changed it to IIf("NoInfo" AND [GroupingInfo]<=1, [Province]) Or IIf(Not Like "NoInfo", [City])

But I do have another question:

I need the following expression
IIf "NoInfo" AND [GroupingInfo]>1, I want it return all [city] = [Province] where they share the same [Province] value

I have trouble to do the Where clause in Sql, Can you tell me how to do that

What have you got that isn't working? You practically have it written already.

[ice051505]

Oh no! I meet a huge problem here, my query won't change its [city] value to [Province] when it is "Noinfo", I think I maybe need to do the update query, but I do not want to change my original table, Can anyone tell me how to write sql to let [City]change value to [Province] in the query only?

[pbaldy]

Sorry, I didn't realize you were trying to use this as a criteria. I've never done that. Your IIf() to simply return values would be:

IIf([City]="NoInfo" And [GroupingInfo]<=1, [Province], [City])

[ice051505]

Thanks, but this piece of code will eat all the row that [city]="NoInfo" and return to me a query that only contain the value that [City] does not contain "NoInfo"

Do you have any idea to solve it?

Sorry, I didn't realize you were trying to use this as a criteria. I've never done that. Your IIf() to simply return values would be:

IIf([City]="NoInfo" And [GroupingInfo]<=1, [Province], [City])

[pbaldy]

Sorry, I'm not really clear on what the goal is.

[ice051505]

errr....maybe I'm not clear here, After the query run, I want it return to me the value to replace the value before my query runs.

I want all the "NoInfo" value in [City] would show me the value in [Province],

But the sql only return me the value that is not "NoInfo" in [City], won't show any row with "NoInfo" before and now should replace to province

Ex:
Before Sql
[Country][Province] [GroupingInfo] [City] [Area]
Country1 Province1 1 NoInfo NoInfo
Country1 Province2 0 NoInfo NoInfo
Country1 Province1 1 City1 Area3
Country2 Province3 1 City2 Area4

I want it give me
[Country][Province] [GroupingInfo] [City] [Area]
Country1 Province1 1 Province1 Province1
Country1 Province2 0 Province2 Province2
Country1 Province1 1 City1 Area3
Country2 Province3 1 City2 Area4

But it only give me the two rows with City1 and City2
Country1 Province1 1 City1 Area3
Country2 Province3 1 City2 Area4

Do you get the problem? Please help me, Thanks

[pbaldy]

The formula I gave you would do that. You want it as a field, not a criteria.

[ice051505]

Thank you!!!!
I got this one right, Could you tell me how to write sql
IIf "NoInfo" AND [GroupingInfo]>1, I want it return all [city] = [Province] where they share the same [Province] value

The problem is on how can I write the expr to show the change is on all the [city] value that share same [Province] value, and how to write the where clause on the field expr?

Again, thank you for your useful tip!

The formula I gave you would do that. You want it as a field, not a criteria.