Remove Quotation Marks and Convert Text to Number (1 Viewer)

Robert_C

New member
Local time
Today, 13:17
Joined
Dec 4, 2019
Messages
6
I have a front end database that has a linked .txt file.

Each week, a new file is produced, and during a series of Queries, the Back End Table is cleared, the linked .txt file is then copied to the Table in the Back End Table, in a different order than input.

A few weeks ago, some of the columns in the linked .txt file now has fields that are numbers, but have Quotation Marks around them. With this change, the number fields are no longer receiving data, as the Quotation Marks are in Short Text format.

How do I remove the Quotation Marks from the numbers, but still ensure that the output of the data type is still in the Number Format?

I already use [Replace("""","""","")] in another function... but that is simply in a Text Field that needs to stay as a text field.

I'm having an issue with how to combine that Replace AND changing the format from Short Text to Number.

Any assistance would be greatly appreciated!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:17
Joined
Oct 29, 2018
Messages
13,741
Hi Robert. Welcome to AWF! Try adding Val(). For example, Val(Replace(...))
 

Micron

AWF VIP
Local time
Today, 13:17
Joined
Oct 20, 2018
Messages
3,471
Val will remove spaces between number sets, so that could be an issue in some cases. I'm fuzzy on what symbols will trip it up (don't think $ is one of them).
You might also consider a conversion function after removing the quotes (e.g. Cdbl).

Then there would be linking to the source file (maybe not if it has a different name each time) and using a query to append to the table. You didn't say how you get the data into a table in the first place. Some methods will get tripped up by your issue, but appending from a linked source to a table that's properly set up usually takes care of that.
 

Robert_C

New member
Local time
Today, 13:17
Joined
Dec 4, 2019
Messages
6
Then there would be linking to the source file (maybe not if it has a different name each time) and using a query to append to the table. You didn't say how you get the data into a table in the first place. Some methods will get tripped up by your issue, but appending from a linked source to a table that's properly set up usually takes care of that.

I have linked files. Each week, we export from our archaic HR system and the .txt files are saved to a shared location. I double check that the data is in the same order/style as expected (or re-link a new file) prior to running them through a series of queries to export pretty(er) Excel files... or clean files for SQL consumption.

I spoke with someone today to understand WHY there are quotation marks in a field that's all about numbers... and he stated that it was because the quotations were to ensure that the commas stay in the dollar format. Which makes me want to kick someone.

So... it now appears that I need to remove quotation marks AND a comma... just to be able to store them as actual numbers... in order to format them to add a comma and a dollar sign.
 

Users who are viewing this thread

Top Bottom