replacing *'s

[rpatel18]

I have a table where I need to replace all the fileds that contain "**" with nothing, and one containging *##(# acually representing 2 numbers) with just ##. The issue I run into is that the * selects all the fields and characters, is there away to disable that feature or something along those lines?

 

[PHV]

Use this criteria:
Like '*[*][*]*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rpatel18]

Doesn't seem to work, the "**" are acually just ** in the fields and not like "**", hope this helps.

 

[PHV]

What have you tried and which results have you got with which input ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rpatel18]

i've tried to use the build query, i've tried =**, ="«Expr» * «Expr» *", and the one that you suggested, none with any results. It says at the bottom left that its "running query" but the status bar just sits there half way through.

 

[PHV]

What is the SQL code of your try with my suggestion ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rpatel18]

UPDATE [AL IRS] SET [AL IRS].ReturnsEIC1998 = ""
WHERE ((([AL IRS].ReturnsEIC1998) Like '*[*][*]*'));

 

[mrkshpntf]

rpatel did you try using 'Find' and 'Replace'?

Here's how you can do it:

First, Very Important - MAKE A BACKUP COPY OF YOUR TABLE!
Check properties of the column, For Example, so that it allows nulls, set property to "Allow Zero Length".

Now select whole column in which you want data replaced by single-clicking header of column

Once whole column is highlighted, press Crtl + F

Click on 'Replace' tab

Enter value you want to replace in 'Find' field and new value want in column in 'Replace'. Leave 'Replace' field blank if you want to record to be blank.

After you have entered everything correctly, click on 'Replace' button.


I hope this is what you wanted and my suggestion helps.

mrkshpntf.

 

[PHV]

are acually just ** in the fields
And what about this ?

UPDATE [AL IRS] SET ReturnsEIC1998 = ''
WHERE ReturnsEIC1998 = '**'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rpatel18]

datatype mismatch in criteria selection

 

[mrkshpntf]

"datatype mismatch in criteria selection"

rpatel

Go in design mode of your table. Change the datatype there.

Do this on a copy of your table, there is some data loss during datatype switching.

 

[PHV]

What is ReturnsEIC1998 in [AL IRS] ?
How a numeric field (if it's the case) may store ** values ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rpatel18]

All of this data came of a CD(we bought teh data from a company) and then it got exported to excel, then from excel, it got exported to access.

 

[rpatel18]

the fields right now are defined as text fields but they only contain numbers.

 

[PHV]

the fields right now are defined as text fields
So I can't understand your 'datatype mismatch' error, sorry.

 

[mrkshpntf]

rpatel,

Is the original data in Text form?

If so, you have to be careful when you import to Excel. Excel sometimes acts weird and keeps reverting to default datatype. This mostly happens between Text and Number datatypes. I can't explain why but I have experienced this.

I manually changed format of data in the cells in Excel as the last resort.

Check the datatypes of newly imported table.