Exchange O for 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having problems combining two separte entities. One records the name of
something as 0123 and the other O123, I need to change the 0 to a O in my
query but not in the actual table. I have tried to make a query and tell it
to substitute a O for a 0 but I really cannot get it right. I need this
query to tell another table that these fields are related to something else.
Probably makes no sense but the tables look like this:
A B
A234 Cow A234
0123 Cow O123

The query tries to get a combination of the second column of A into B which
works fine in the case of A234 but does nothing for O123.

I hope this makes sense I said this but of course it does not remove the 0

BLOCKS: IIf([BLOCK_ID]>"1",+[BLOCK_ID],IIf([BLOCK_ID] Is Null,+"O"+
Code: 
,0))

Thanks
 
You can use the Replace function to replace 0 with O

BLOCKS:Replace([BLOCK_ID],"0","O")

*************
Or, if you want to change only the first chr, try

BLOCKS:IIf(Left([BLOCK_ID],1)="0","O" & Mid([BLOCK_ID],2),[BLOCK_ID])
 
Thank you the front only was what I was looking for as I have 0 in other spots.

Ofer Cohen said:
You can use the Replace function to replace 0 with O

BLOCKS:Replace([BLOCK_ID],"0","O")

*************
Or, if you want to change only the first chr, try

BLOCKS:IIf(Left([BLOCK_ID],1)="0","O" & Mid([BLOCK_ID],2),[BLOCK_ID])


--
Good Luck
BS"D


Arlene said:
I am having problems combining two separte entities. One records the name of
something as 0123 and the other O123, I need to change the 0 to a O in my
query but not in the actual table. I have tried to make a query and tell it
to substitute a O for a 0 but I really cannot get it right. I need this
query to tell another table that these fields are related to something else.
Probably makes no sense but the tables look like this:
A B
A234 Cow A234
0123 Cow O123

The query tries to get a combination of the second column of A into B which
works fine in the case of A234 but does nothing for O123.

I hope this makes sense I said this but of course it does not remove the 0

BLOCKS: IIf([BLOCK_ID]>"1",+[BLOCK_ID],IIf([BLOCK_ID] Is Null,+"O"+
Code: 
,0))

Thanks[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Back
Top