Iif then statement needed for Access 2007 please

[Heather]

Hi all,
I'm trying to figure out how to write an Iif Then statement in access 2007
for:

iif columnZ = 10 then columnZ = A

please help, SQL and query table builder

Thanks
Heather

 

[Jeff Boyce]

Heather

I'm not clear why you need to use an IIF() expression...

Have you looked into creating an update query? (CAUTION: backup before
running a query that alters your data!)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[bhicks11 via AccessMonster.com]

Are you doing this in an update query or in VBA?

VBA:

iif(me.ColumnA = 10, me.ColumnZ = "A","")

Update Query:

In the ColumnA column criteria: 10

ColumnZ update to: "A"

You will need to clarify your data types. ColumnA assumes numeric.

Bonnie
http://www.dataplus-svc.com

 

[Jerry Whittle]

Below should work in a query.

IfZ10: IIf([columnZ] = 10, "A", [columnZ])

If columnZ is a text field, then you may need double quotes around the 10.
Also note that if columnZ isn't 10, it will return what is in columnZ instead.

 

[Heather]

Thanks for your replies, I've tried the following, but it didn't work?
ColumnZ is text, so I put the double quotes, but still to no avail?

Any ideas please
Heather

Below should work in a query.

IfZ10: IIf([columnZ] = 10, "A", [columnZ])

If columnZ is a text field, then you may need double quotes around the 10.
Also note that if columnZ isn't 10, it will return what is in columnZ instead.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi all,
I'm trying to figure out how to write an Iif Then statement in access 2007
for:

iif columnZ = 10 then columnZ = A

please help, SQL and query table builder

Thanks
Heather

 

[bhicks11 via AccessMonster.com]

Post how you did it - show us the SQL if you used a query or the VBA. Maybe
someone will see what's wrong.

Bonnie
http://www.dataplus-svc.com

Thanks for your replies, I've tried the following, but it didn't work?
ColumnZ is text, so I put the double quotes, but still to no avail?

Any ideas please
Heather

Below should work in a query.

[quoted text clipped - 13 lines]

 

[Heather]

Hi Bonnie,

I'm trying to do it as an update query using the query design interface, I
don't even know where to begin with VBA or any scripting lanuage.

I've tried your suggestion, but it doesn't like this. The numbers remain as
10, not changed to A. I've tried it with the original column as text or as a
number, but to no avail!?
Thanks for your interest and help,
Heather

 

[bhicks11 via AccessMonster.com]

Just to be sure:

While in the query design interface, click Query in the top menu, then click
Update Query. Make sure you have ColumnZ in the query. In the ColumnZ
column, CRITERIA row put "10", then in the UPDATE TO row, put "A". This
should work.

Bonnie
http://www.dataplus-svc.com

Hi Bonnie,

I'm trying to do it as an update query using the query design interface, I
don't even know where to begin with VBA or any scripting lanuage.

I've tried your suggestion, but it doesn't like this. The numbers remain as
10, not changed to A. I've tried it with the original column as text or as a
number, but to no avail!?
Thanks for your interest and help,
Heather

Are you doing this in an update query or in VBA?

[quoted text clipped - 23 lines]

 

[Heather]

Thanks Bonnie,
but thats definetely what I did, and it didn't work?
Not sure what to try now...
Heather

Just to be sure:

While in the query design interface, click Query in the top menu, then click
Update Query. Make sure you have ColumnZ in the query. In the ColumnZ
column, CRITERIA row put "10", then in the UPDATE TO row, put "A". This
should work.

Bonnie
http://www.dataplus-svc.com

Hi Bonnie,

I'm trying to do it as an update query using the query design interface, I
don't even know where to begin with VBA or any scripting lanuage.

I've tried your suggestion, but it doesn't like this. The numbers remain as
10, not changed to A. I've tried it with the original column as text or as a
number, but to no avail!?
Thanks for your interest and help,
Heather

Are you doing this in an update query or in VBA?

[quoted text clipped - 23 lines]

Thanks
Heather

 

[bhicks11 via AccessMonster.com]

Post your SQL.

Bonnie
http://www.dataplus-svc.com

Thanks Bonnie,
but thats definetely what I did, and it didn't work?
Not sure what to try now...
Heather

Just to be sure:

[quoted text clipped - 22 lines]