Scratching my head

[geoffcodd]

I have the following data in a table

Invoice_Number
619915233807
*619915233807
*619915233807
*100099830014
100099830014
IN065356
1215
1115
27SIN012947
200848
*27SIN010092

And I need to remove the * from all records which contain it the problem I have is that * is a wild card

Does anyone have any ideas

Thanks
Geoff

 

[Friday]

You need to run an update query in which you will first test for the existence of the * , then on those field only you need to update that field. Get the length of the data, then update the field with the value of Right(field, lengthValue)-1. The formula should be: Right ([InvoiceNumber], Len([InvoiceNumber])-1). So, if your table name was sample, the update SQL would look like this: UPDATE sample SET sample.InvoiceNumber = Right([InvoiceNumber],Len([InvoiceNumber])-1)
WHERE ((Left([sample]![InvoiceNumber],1) Like "[*]"));

 

[Len Boorman]

This is only a thought so test it carefully

If you wrap the * in quotes you have a problem with its interpretation. Try wrapping "*" in single quotes as well

ie. '"*"'

I think that the single quotes will be stripped of and the result interpreted i.e *as text

As I said test it carefully

Len B

 

[Friday]

This is only a thought so test it carefully

If you wrap the * in quotes you have a problem with its interpretation. Try wrapping "*" in single quotes as well

ie. '"*"'

I think that the single quotes will be stripped of and the result interpreted i.e *as text

As I said test it carefully
Len B

From MS:
To match the special characters left bracket ([), question mark (?), number sign (#), and asterisk (*), enclose them in brackets.