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
1013 Views

Similar Articles

[PageSpeed] 35

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:

numbering
Is there a way to format a cell so that you can put in a 1 and then the next time you print it out that number will change to 2 and so on? ...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Changing which database Business Portal points to
If I install Business Portal 2.5 (GP) and point it to our Great Plains development server with an old Dynamics database, once we decide to take it live, how do I go back and point it to the live Great Plains server? Is this something that can only be done during the installation? Let me preface my response by saying I only have experience with BP 3.0.... In BP 3.0 several tables are created in the Dynamics database to support the various BP functions. Unless you plan to move or copy those tables you will probably have to reinstall BP 2.5 in order to have them created. Our attempt to cop...

transfer inbox in table format to word
In my older version I could cut and paste the table format in Outlook to a word file. Now I don't seem to be able to do it although I can print the file in that format within Outlook. When I export the inbox to word I get the whole text not just the headings. Is it possible to transfer the table format to a word file? ...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

adding a leading 0 to only the numbers with 4 digits
I'm converting a bunch of data and during the import Excel dropped the leading zero from all the lip codes. Since this is about 15,000 records, I'd like a way to add a leading zero to only the zip codes with 4 characters. Any ideas? thanks Swamp, Format/Cells/Special/Zip Code Beege "SwampYankee" <johndillworth@gmail.com> wrote in message news:1147451549.294898.147230@v46g2000cwv.googlegroups.com... > I'm converting a bunch of data and during the import Excel dropped the > leading zero from all the lip codes. Since this is about 15,000 > records, I&...

Wrapping text in a cell
In a single cell, suppose I want text to appear on two lines. Viz: Case One Case Two How do I do that so that I specify the wrap point? Thanks! If you are typing the data into the cell use Alt-Enter between each string to indicate where you want a line break to occur. Case One<Alt-Enter>Case Two Alt + Enter -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27741 View this thread: http://www.excelforum.com/showthread.php?threadid=476428 If you...

Is it possible to highlight text?
I'd like to highlight just some of the text in a cell - not the entire thing. Thanks Select the cell and in the edit bar you can then select (highlight) some of the text with the mouse and format how you wish, eg bold, different colour, different background colour, different font/font size etc. Hope this helps. Pete This will NOT work in a formula. Works only if all text. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Pete_UK" <pashurst@auditel.net> wrote in message news:1139215455.134469.285660@z14g2000cwz.googlegroups.com... > Select the cell ...

Changing Titles by a formula
From cell A1:A10 I have standard titles that are linked to another sheet. I only need to change the office and date per each title. Is thier a way to do that making the office and date a formula in another cell? example of title ABC Comp, Office 38L Sales as of 6/07/2004 I need to only keep changeing the 38L and date. Any suggestions----Thanks Say Office # is in Column 'C' in cell A1: ="ABC Comp, Office "&C1&" Sales as of "&Today() ----- Heather wrote: ----- From cell A1:A10 I have standard titles that are linked to ...

Page Number in Repeat Rows area
Is there a way that I can put a Page Number in the Repeat Rows area of a sheet - and get it to update when I print? I have found one piece of VBA that puts a page number within a cell but it is only updated if the cell is outside the Repeat Row area. When it is within the Repeat Rows area I just get a page number of 1 corresponding to the original row location.. I do some VBA programming but am not an expert. TIA cheers Chris Nothing comes to mind that doesn't require VBA code. You can paste the following in the sheet header (File | Page Setup) and page x of y will print on ea...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

can't change/delete bills and deposits
suddenly one day i realized that when i tried to change a bill, i could click "ok" but nothing would happen. I thought it was because i upgraded to 2004, so i deleted the bills and rescheduled. about 5 of the bills will not delete either. the error message is: "Money could not write to your Money file. The operation could not be performed or another application finished this task before you. Please try again." Now some of the newly scheduled bills will not allow me to change the properties, i click ok but nothing happens when i edit the series. I have repaired ...

Outlook 2007: When i Save a New Rule outlook Change it automatically...
Hi people! I have this problem with Outlook 2007. I have 15 e-mails addresses in my outlook, but i can=B4t create the rules for send all the received mails to each emails folder. Example: I want to create a simple rule that move all the e-mail that i get from "example1@example1.com" to the folder "Inbox/ example1@example1.com". When i create the rule, all works fine, i select the correct e-mail account name, the correct destination folder, etc... but when I hit the SAVE button in rules window, Outlook 2007 changes the account automatically to another one. Not automatically...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

Changing Functional Currency #4
Try to change the functional currency to Z-CNY. ERROR = Activity for this currency already exist. Finance made 2 GL transactions. Can we remove the transactions (Tools >> Utilities >> Financial >> Remove History) or (File >> Maintenance >> Clear Data ) then change the functional currency? Which tables should I remove history or clear data. Can someone on the board help us? regards, Once you process a transaction, you are stuck with the functional currency. I have not seen a table fix for this problem on the board. Consider creating a new database an...

Insert Page Numbers on Worksheet in Excel 2007
In Excel 2003, if you wanted to put page numbers on multiple worksheets in a workbook, you grouped the worksheets and then added a header or footer, using the page number function. All of your grouped worksheets would shows its own page number. But in Excel 2007, only my first worksheet is numbered when I do this (as Page 1). What's the problem? Do I actually have to put a page number, one by one, on each worksheet? I cannot replicate your problem with 2007. Grouped sheets behave exactly as 2003 did. After grouping and adding a header of Page 1 did you do a print p...

Change default "SaveAs" to *.msg rather than *.html
Hi!, Using Outlook 03, I regularly need to save emails and prefer to save as *.msg so the attachments are contained within the one file. How can you change the default SaveAs command to Save as Type in a *.msg format (rather than *.html) so the type is already *.msg without having to use the pull down? Thank you Troy Outlook provides no way to change that. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "TroyB" <boeky72@hotmail.com>...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

Export Format not avaiable
"The Format in which you are attempting to output the currentobject is not available." I hate access sometimes. It just get's weird, throwing bogus error messages all over the place. I have about 30 seperate queries that I run out to spreadsheets via macro. I have already found out that things can get all screwed up, (meaning it bombs) when those spreadsheets already exist, so the first thing I do is delete the existing spreadsheets, then let them rip. I run into this every once in a while: 20 or so queries into my macro, a query will fail with the above er...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...

Changing of range (Address) to (Cell)
Hi people I have encounter a problem with the use of range From a recorded macro, it's listed this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2,A24:M28"), PlotBy:=xlRow And I edit it to this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2," & Cells(StartX, StartY), Cells(LastX, LastY)), PlotBy:=xlRow And obviously VBA compiler won't let me go this easily, it happen to give an "evil-comment" on my source range, May i know how can i solve this Thank You Hi Kaiyang, Try this, assuming the...

xy scatter format
Hi Hope someone can help. I have a chart i want to make look a little more professional. The chart plots three points within two boxes. the points should be inside the box box 1 has the following coordinates x y 15 70000 15 115000 21.5 155800 30 155800 30 115000 25 70000 15 70000 the chart scale is x 14-32 & y 60000-160000 I would like to make the plot area outside the box black to indicate the no go area. Is this possible? Then to take it further there should be a further smaller box (coordinates unimportant) inside the 1st box to indicate a warning. I would like the plot area ...

Net change and percentage of change
Hi, I have a list of parts that have had price increases. One column has the old price the other has the new price. I need to show the difference, whether it is positive or negative and the percentage of the increase or decrease. The difference is not a problem. The problem lies in calculating the percentage. I don't how to do that. Can someone tell me? Thanks, Richard =(B1-A1)/B1 "Richard" wrote: > Hi, > > I have a list of parts that have had price increases. One column has the old > price the other has the new price. I need to show the difference, whethe...

SOP Original Number on RM Detail Hist Aged Trial Balance
I'm trying to add the SOP Original Number (SOP Order Number) to the RM Detail Historical Aged Trial Balance. I've linked the Sales Transaction History Table to the RM_HATB_Document_TEMP table. I then added a dummy Restriction where Original Number = Origianl Number. However, every time I try to run this new report, I still get the following error: Inconsistent restriction/sort expression. Please add the following restriction 'D03304Original Num = D03304Original Num'. Has anyone successfully added this field to this report? What am I doing wrong? I was able to link...