Using Replace need help with blanks

[mattc66 via AccessMonster.com]

I have the following in my query. It works great if it finds the "A/P"
records, but I have some records that are blank. Which I want them to remain
blank. When I run the query the blank ones show up as #Error. What can I do
so they just show up blank on those records?

CK_BOOK_ID1: Replace([CK_BOOK_ID],"A/P","SBC2")

Thanks
Matt

 

[John W. Vinson]

I have the following in my query. It works great if it finds the "A/P"
records, but I have some records that are blank. Which I want them to remain
blank. When I run the query the blank ones show up as #Error. What can I do
so they just show up blank on those records?

Check for null values first:

CK_BOOK_ID1: IIf IsNull([CK_BOOK_ID],Null,Replace([CK_BOOK_ID],"A/P","SBC2"))

This will of course not be editable.

 

[mattc66 via AccessMonster.com]

I get an error message telling me to enclose the IIF statement. So I add the
"(" after the IIF. I then get an error that I don't have the correct syntax.
So I add 3 closed parentheses and get an error that I have too many... Crazy!

CK_BOOK_ID1: IIf (IsNull([CK_BOOK_ID],Null,Replace([CK_BOOK_ID],"SBC","SBC1"))
)

I have the following in my query. It works great if it finds the "A/P"
records, but I have some records that are blank. Which I want them to remain
blank. When I run the query the blank ones show up as #Error. What can I do
so they just show up blank on those records?

Check for null values first:

CK_BOOK_ID1: IIf IsNull([CK_BOOK_ID],Null,Replace([CK_BOOK_ID],"A/P","SBC2"))

This will of course not be editable.

 

[John Spencer MVP]

An alternative that will work is

CK_BOOK_ID1: Replace(CK_Book_ID & "","A/P","SBC2")

This will return a zero-length string for nulls so it may not be appropriate
for you to use.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have the following in my query. It works great if it finds the "A/P"
records, but I have some records that are blank. Which I want them to remain
blank. When I run the query the blank ones show up as #Error. What can I do
so they just show up blank on those records?

Check for null values first:

CK_BOOK_ID1: IIf IsNull([CK_BOOK_ID],Null,Replace([CK_BOOK_ID],"A/P","SBC2"))

This will of course not be editable.

 

[mattc66 via AccessMonster.com]

That worked... Thank you

An alternative that will work is

CK_BOOK_ID1: Replace(CK_Book_ID & "","A/P","SBC2")

This will return a zero-length string for nulls so it may not be appropriate
for you to use.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
[quoted text clipped - 6 lines]

This will of course not be editable.

 

[fredg]

I get an error message telling me to enclose the IIF statement. So I add the
"(" after the IIF. I then get an error that I don't have the correct syntax.
So I add 3 closed parentheses and get an error that I have too many... Crazy!

CK_BOOK_ID1: IIf (IsNull([CK_BOOK_ID],Null,Replace([CK_BOOK_ID],"SBC","SBC1"))
)

I have the following in my query. It works great if it finds the "A/P"
records, but I have some records that are blank. Which I want them to remain
blank. When I run the query the blank ones show up as #Error. What can I do
so they just show up blank on those records?

Check for null values first:

CK_BOOK_ID1: IIf IsNull([CK_BOOK_ID],Null,Replace([CK_BOOK_ID],"A/P","SBC2"))

This will of course not be editable.

You're difficulty is here:
IsNull([CK_BOOK_ID],

You are missing the close parenthesis in the IsNull function.
IsNull([CK_BOOK_ID]), etc....