change a text format to number format

  I need to link to someone elses table but they have used a different format 
type for a field called ACCOUNT.  I am a beginner and do not know how to 
write SQL but I was wondering if it is possible to use an update query  to 
change a text field in this situation into a number format. I am trying to 
run a mismatch query on this table with another table in my database that has 
a field called ACCOUNT but my table has a number format.  I am trying to come 
up with a list of ACCOUNT numbers that are common in both tables.  

Thank you in advance for any direction you can provide
0
Utf
3/19/2010 2:50:01 AM
access 16762 articles. 3 followers. Follow

6 Replies
1048 Views

Similar Articles

[PageSpeed] 56

Let's say your account table is tblAccount2 and the one with the text 
accounts is tblAccount1.

Create one query that converts your account field to text ( just in the 
query not in the table).
Something like:
SELECT CStr([Account]) AS AccountAsText
FROM tblAccount2;

Then create a second query that joins the first query and tblAccount1.
Run the query and you have your answer.

SELECT tblAccount1.Account, qryToString.AccountAsText
FROM qryToString INNER JOIN tblAccount1 ON qryToString.AccountAsText = 
tblAccount1.Account;

Regards

Kevin


"ferde" <ferde@discussions.microsoft.com> wrote in message 
news:350743E5-2E4C-4EDF-B897-FF4A71A3B942@microsoft.com...
>  I need to link to someone elses table but they have used a different 
> format
> type for a field called ACCOUNT.  I am a beginner and do not know how to
> write SQL but I was wondering if it is possible to use an update query  to
> change a text field in this situation into a number format. I am trying to
> run a mismatch query on this table with another table in my database that 
> has
> a field called ACCOUNT but my table has a number format.  I am trying to 
> come
> up with a list of ACCOUNT numbers that are common in both tables.
>
> Thank you in advance for any direction you can provide 


0
kc
3/19/2010 3:59:34 AM
ferde,

No, you can't change a fields Data Type by running an Update query.  Try 
creating a query with the linked table and for the field ACCOUNT put 
(copy/paste)...

AccountNumber: Val([ACCOUNT])

....in the *Field:* part of the query replacing the ACCOUNT field.

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"ferde" <ferde@discussions.microsoft.com> wrote in message 
news:350743E5-2E4C-4EDF-B897-FF4A71A3B942@microsoft.com...
  I need to link to someone elses table but they have used a different 
format
type for a field called ACCOUNT.  I am a beginner and do not know how to
write SQL but I was wondering if it is possible to use an update query  to
change a text field in this situation into a number format. I am trying to
run a mismatch query on this table with another table in my database that 
has
a field called ACCOUNT but my table has a number format.  I am trying to 
come
up with a list of ACCOUNT numbers that are common in both tables.

Thank you in advance for any direction you can provide 

0
Gina
3/19/2010 4:09:29 AM

"Gina Whipp" wrote:

> ferde,
> 
> No, you can't change a fields Data Type by running an Update query.  Try 
> creating a query with the linked table and for the field ACCOUNT put 
> (copy/paste)...
> 
> AccountNumber: Val([ACCOUNT])
> 
> ...in the *Field:* part of the query replacing the ACCOUNT field.
> 
> -- 
> Gina Whipp
> 2010 Microsoft MVP (Access)
> 
> "I feel I have been denied critical, need to know, information!" - Tremors 
> II
> 
> http://www.regina-whipp.com/index_files/TipList.htm
> 
> "ferde" <ferde@discussions.microsoft.com> wrote in message 
> news:350743E5-2E4C-4EDF-B897-FF4A71A3B942@microsoft.com...
>   I need to link to someone elses table but they have used a different 
> format
> type for a field called ACCOUNT.  I am a beginner and do not know how to
> write SQL but I was wondering if it is possible to use an update query  to
> change a text field in this situation into a number format. I am trying to
> run a mismatch query on this table with another table in my database that 
> has
> a field called ACCOUNT but my table has a number format.  I am trying to 
> come
> up with a list of ACCOUNT numbers that are common in both tables.
> 
> Thank you in advance for any direction you can provide 
> 
0
Utf
3/19/2010 4:36:01 AM
Thank you so much...works great

"Gina Whipp" wrote:

> ferde,
> 
> No, you can't change a fields Data Type by running an Update query.  Try 
> creating a query with the linked table and for the field ACCOUNT put 
> (copy/paste)...
> 
> AccountNumber: Val([ACCOUNT])
> 
> ...in the *Field:* part of the query replacing the ACCOUNT field.
> 
> -- 
> Gina Whipp
> 2010 Microsoft MVP (Access)
> 
> "I feel I have been denied critical, need to know, information!" - Tremors 
> II
> 
> http://www.regina-whipp.com/index_files/TipList.htm
> 
> "ferde" <ferde@discussions.microsoft.com> wrote in message 
> news:350743E5-2E4C-4EDF-B897-FF4A71A3B942@microsoft.com...
>   I need to link to someone elses table but they have used a different 
> format
> type for a field called ACCOUNT.  I am a beginner and do not know how to
> write SQL but I was wondering if it is possible to use an update query  to
> change a text field in this situation into a number format. I am trying to
> run a mismatch query on this table with another table in my database that 
> has
> a field called ACCOUNT but my table has a number format.  I am trying to 
> come
> up with a list of ACCOUNT numbers that are common in both tables.
> 
> Thank you in advance for any direction you can provide 
> 
0
Utf
3/19/2010 4:37:01 AM
You're welcome!

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"ferde" <ferde@discussions.microsoft.com> wrote in message 
news:587350FC-B470-42A5-9657-61C383EAA2C3@microsoft.com...
Thank you so much...works great

"Gina Whipp" wrote:

> ferde,
>
> No, you can't change a fields Data Type by running an Update query.  Try
> creating a query with the linked table and for the field ACCOUNT put
> (copy/paste)...
>
> AccountNumber: Val([ACCOUNT])
>
> ...in the *Field:* part of the query replacing the ACCOUNT field.
>
> -- 
> Gina Whipp
> 2010 Microsoft MVP (Access)
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>
> http://www.regina-whipp.com/index_files/TipList.htm
>
> "ferde" <ferde@discussions.microsoft.com> wrote in message
> news:350743E5-2E4C-4EDF-B897-FF4A71A3B942@microsoft.com...
>   I need to link to someone elses table but they have used a different
> format
> type for a field called ACCOUNT.  I am a beginner and do not know how to
> write SQL but I was wondering if it is possible to use an update query  to
> change a text field in this situation into a number format. I am trying to
> run a mismatch query on this table with another table in my database that
> has
> a field called ACCOUNT but my table has a number format.  I am trying to
> come
> up with a list of ACCOUNT numbers that are common in both tables.
>
> Thank you in advance for any direction you can provide
> 
0
Gina
3/19/2010 8:31:09 PM
Thank you Keven this was very helpful.

"kc-mass" wrote:

> Let's say your account table is tblAccount2 and the one with the text 
> accounts is tblAccount1.
> 
> Create one query that converts your account field to text ( just in the 
> query not in the table).
> Something like:
> SELECT CStr([Account]) AS AccountAsText
> FROM tblAccount2;
> 
> Then create a second query that joins the first query and tblAccount1.
> Run the query and you have your answer.
> 
> SELECT tblAccount1.Account, qryToString.AccountAsText
> FROM qryToString INNER JOIN tblAccount1 ON qryToString.AccountAsText = 
> tblAccount1.Account;
> 
> Regards
> 
> Kevin
> 
> 
> "ferde" <ferde@discussions.microsoft.com> wrote in message 
> news:350743E5-2E4C-4EDF-B897-FF4A71A3B942@microsoft.com...
> >  I need to link to someone elses table but they have used a different 
> > format
> > type for a field called ACCOUNT.  I am a beginner and do not know how to
> > write SQL but I was wondering if it is possible to use an update query  to
> > change a text field in this situation into a number format. I am trying to
> > run a mismatch query on this table with another table in my database that 
> > has
> > a field called ACCOUNT but my table has a number format.  I am trying to 
> > come
> > up with a list of ACCOUNT numbers that are common in both tables.
> >
> > Thank you in advance for any direction you can provide 
> 
> 
> .
> 
0
Utf
3/20/2010 3:34:01 PM
Reply:

Similar Artilces:

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

incorrect check / cheque number printing
When generating a cheque run, let’s say cheque 61, 62, 63 and 64, the information printed on cheque # 62 is actually cheque # 64 and vice versa. This does not happen every cheque run, but it does happen fairly often. I have not seen GP do this. What version and service pack of GP are you using? Are you 100% sure the check stock itself is not out of sequence? Do you have any customizations? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Syed" <Syed@discussions.microsoft.com> wrote in message news:3B02ECEF-7436-4CF3-930E-AA4D2DFDF117@microsoft.com... ...

Territory Change
Our Sales VP has restructured all of the geographical territories that we had set up in Microsoft CRM. Our many thousands of Accounts are associated with territories. Obviously it is ludicrous to think that we would have to go one by one and change the territory on each account to the new territories. However, I have heard that there are strict rules for making changes directly to the backend SQL database. If we develop a SQL statement to change the territories assigned to each account to the new territories, are we going to be breaking anything? We don't want to screw up any de...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Changing font in Money 2004 register?
Is there any way to change the font(s) used in MS Money 2004's registers? The default font is too small and difficult for me to read. Also, is there any way to change the color scheme to something more pleasing to my eye (like you can do in Quicken...) Thanks. Nope and Nope. See http://umpmfaq.info/faqdb.php?q=136. "Debbie R." <debbimsr@bellsouth.net> wrote in message news:f5ff01c43e15$e2ae3700$a401280a@phx.gbl... > Is there any way to change the font(s) used in MS Money > 2004's registers? The default font is too small and > difficult for me to read....

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Phone Number Filtering
I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? The only effective way I know how to do this, is to use macros. Your samples a & b, are fairly simple to do. Sample c, could be d...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Roman and Arabic numbers in SEQ
Running 2003 on XP. Got a doc that has SEQ numbering. The code is: ARTICLE{seq level0\h\r0}{seq level1\h\r0} etc. to level 7 and the last entry is {seq level0\r1\*ROMAN) This displays ARTICLE I The next level down is has this code: {seq level0\c\*ROMAN}.{seq level1\r1 \*arabic} This displays as : I.1, with numbers until article five displaying as V.1, V.2, etc. I want it to display as 1.1, 1.2, etc. I tried replacing the ROMAN with arabic, but it didn't change anything. I have spent hours trying to figure this out. Using reveal formating and styles, they are no styles...

Invoice Numbers 10-27-07
We produce reports that are invoices.. The reports are really a group of compined reports if this matters... When we print the reports I would like to have printed consecutive invoice numbers. If possible I would like to have the number apprear as AS-00001, AS-00002 ect.. I am not really interested in storing the invoice numbers I just need them on the printed invoice as it is made of of groups of various data that is stored... Thank In Advance for you help. Bob If you just want a consequetive numbering on the report, all with an AS- prefix, see: Numbering Entries in a Report o...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Money 2000 Account Balance Changed Inexplicably
Opening Money 2000, which I've used without problem since late 1999, I noticed that my checking account balance was overstated by almost $2000! I went to the register to see if there was a false transaction entered and it appears that this balance change goes back years with no obvious single entry being the culprit. When I run the "balance this account" function, it shows that the closing balance from my last statement, which was correct and rectified, is now also wrong and reflects the higher balance. What do I do now? The only thing that I can think of is to restore...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

parameter for text
In Access 2003. In a form I have a parameter to search for a word at a a time Like [Froms].[Form].txtWords]&"*" which works well. .. In order to enter more than two oarameter separated by commas in txtwords I have been tryin the Str function like this "Instr([txtWords])" "Instr[txtWords]" The query displays no records at all. Could you suggest me how to do it Many thanks I would suggest separating by space and using this -- Like "*" & [Froms].[Form].txtWords] &"*" Remember that when you type in 'bell...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

How do I bind a XAML text box control to a dataset?
Hello; I am new to using WinFx and I am having trouble figuring out how to bind a text box to a field in a dataset. I found an MSDN article: http://msdn2.microsoft.com/en-us/library/ms752057.aspx My question deals with the text box code: <TextBlock Text="{Binding Path=ISBN}" Grid.Column="1" /> How does this text box know to bind to the field "ISBN" in "myDataSet"? How does the control implement the dataset? Thank you Monty ...

Changing language
I am running the Swedish version of Excel for XP and I need the US (or English) version. Is there a way to convert the language and all the settings associated with it? Using the swedish version is rather annoying since the formula names are translated to swedish. Thank you, Magnus ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Changing a profile on Microsoft Outlook 2003
I set up two profiles through the control panel and directed Outlook to prompt me for which profile to use each time it was opened. But now it skips the prompt and goes straight to one of the profiles. I need to restore that prompt, but it won't respond to the instructions in the control panel Mail dialogue box Hi Chris, did you get the same behavior after a restart of the computer? You could try "Sart/run/fixmapi.exe" (you don�t get any confirmation message) and restart the computer again. If this wouldn�t wotk, I would create a 3rd (test) profile. Maybe Outlook don�t ...

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...