Can you map certain fields on an new Access table to an existing Access table?

[Castillb]

I've imported an Excel file into Access as a textfile. All of the information on the Excel spreadsheet was succefully imported in Access. The only problem that I have is that some of the fields on the table have quotation marks (e.g. "Stults, David, "KWK, Inc.", "7,860.27", etc.) My first question is whether these quotation marks can be removed from the table.



My second question - There is information on this table that needs to be manually entered on a data entry screen in Access. Is there anyway to automate this process?

Please help...

[rzw0wr]

Try in a query, look: Replace([mystring],'"',"")

Change mystring to the field that has the double quotes.
You can change look to any name you like.
This does not change the data in the table, just the output of the query.


Dale

[rzw0wr]

if you want to change the data in the table try a update query.
SQL= UPDATE Table2 SET Table2.mystring = Replace([mystring],'"',"");

Change Table2 to your table name and change mystring to your field name.

You should back up your database or at least your table before trying this.


Dale

[Castillb]

Dale,

Do you mind telling me how I would go about this? I've ran the query and have just the information I want/need. I currently have the query up in front of me, on Datasheet View.

I'm new to to Access and have been using forums and other online sites for help. If you woulndt mind explaining or providing me with the info I should be searching for online, I'd really appreciate it.

Try in a query, look: Replace([mystring],'"',"")

Change mystring to the field that has the double quotes.
You can change look to any name you like.
This does not change the data in the table, just the output of the query.


Dale

[rzw0wr]

If you just want the data displayed and not changed.
In query design view, in an empty column, Insert the replace function I gave you.
If look is OK as a field name, leave it alone. I f not change look to whatever you like.
Change "mystring" to the field name that holds the data with the double quotes.
Be sure to use [], brackets around you field names if the have a space.

Let us know if you have trouble.
Dale

[Castillb]

Dale,

When I try this I get the following error message:

Syntax error (comma) in query expression '[Allocation Schedule].[Replace([Replace9[Iff Legal Fees Allocations],"",""]'.

If you just want the data displayed and not changed.
In query design view, in an empty column, Insert the replace function I gave you.
If look is OK as a field name, leave it alone. I f not change look to whatever you like.
Change "mystring" to the field name that holds the data with the double quotes.
Be sure to use [], brackets around you field names if the have a space.

Let us know if you have trouble.
Dale

[rzw0wr]

Syntax error (comma) in query expression '[Allocation Schedule].[Replace([Replace9[Iff Legal Fees Allocations],"",""]'.

Are you trying to remove the comas?
try Look:Replace([Legal Fees Allocations],"','"
Single quote around coma.

Dale

[Castillb]

Dale,

I'm trying to remove only the quotation marks such as the ones in line 1 shown as "XYZ, Inc."

The commas, I dont mind being displayed on the table.

Are you trying to remove the comas?
try Look:Replace([Legal Fees Allocations],"','"
Single quote around coma.

Dale

[rzw0wr]

If you just want the data displayed and not changed.
In query design view, in an empty column, Insert the replace function I gave you.
If look is OK as a field name, leave it alone. I f not change look to whatever you like.
Change "mystring" to the field name that holds the data with the double quotes.
Be sure to use [], brackets around you field names if the have a space.

look: Replace([mystring],'"',"")

Note: copy and paste this in your query.
Notice that the first double quote is delimited with 2 single quotes. 1 before and 1 after.
the coma is a function separator. The 2nd set of double quotes say to replace a single double quote with nothing.

Let us know if you need more help. I am here till about 9:00 Eastern time.
Dale

[rzw0wr]

YourFieldName:Replace([Legal Fees Allocations],'"',""

Should look something like this when you are finished.


Dale

[Castillb]

Dale,



I made the enty as shown in your last post, but now I'm getting a the following error message:

"the Expression you entered contains invalid syntax"

I really appreciate you helping me on this.

YourFieldName:Replace([Legal Fees Allocations],'"',""

Should look something like this when you are finished.


Dale

[rzw0wr]

Have you added the table to the query?
Have you added the field[Legal Fees Allocations] to the query?

Can you upload a copy of your db.

Dale

[Castillb]

Dale,

Yes, I've done both.

Have you added the table to the query?
Have you added the field[Legal Fees Allocations] to the query?

Can you upload a copy of your db.

Dale

[rzw0wr]

In the jpeg you sent, you have the wrong function .
Replace the one with the iif to the one with just the replace I sent you.

Can you upload your database?

Dale