f



Linking A Text Field With A Number Field

Is there any way to link a field I'll call StudentId in Table1 with
StudentId in Table2?  In Table1, StudentId is a number and in Table2
it's a text field.  The values match up fine so other than one being a
number and the other a text field, everything else would be okay.  I
know I can create an append query to copy, say, Table2 to e.g. Table3
and make StudentId a number field in Student3 but wondered if there's
a more "elegant" solution.

Thanks,

Sheldon Potolsky
0
SHPsalm139 (70)
3/6/2009 7:42:38 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

4 Replies
1573 Views

Similar Articles

[PageSpeed] 55

On Mar 6, 11:42=A0am, Sheldon <SHPsalm...@aol.com> wrote:
> Is there any way to link a field I'll call StudentId in Table1 with
> StudentId in Table2? =A0In Table1, StudentId is a number and in Table2
> it's a text field. =A0The values match up fine so other than one being a
> number and the other a text field, everything else would be okay. =A0I
> know I can create an append query to copy, say, Table2 to e.g. Table3
> and make StudentId a number field in Student3 but wondered if there's
> a more "elegant" solution.
>
> Thanks,
>
> Sheldon Potolsky

You could use Cstr() or Clng() functions - depending on which side
you're doing the connection.  If you are going from a number to text
convert it to Text with a Cstr() or vice versa.

Sol.
0
3/6/2009 9:00:37 PM
Sheldon wrote:
> Is there any way to link a field I'll call StudentId in Table1 with
> StudentId in Table2?  In Table1, StudentId is a number and in Table2
> it's a text field.  The values match up fine so other than one being a
> number and the other a text field, everything else would be okay.  I
> know I can create an append query to copy, say, Table2 to e.g. Table3
> and make StudentId a number field in Student3 but wondered if there's
> a more "elegant" solution.

As Sol Shapiro noted, use the CStr() or CLng() functions.

SELECT T1.*, T2.*
FROM Table1 T1 INNER JOIN Table2 T2
ON CStr(T1.Column) = CStr(T2.Column)

OR

SELECT T1.*, T2.*
FROM Table1 T1 INNER JOIN Table2 T2
ON CLng(T1.Column) = CLng(T2.Column)


I've seen significant slowdowns using this, so you want to index the join 
columns.


> Thanks,
>
> Sheldon Potolsky 


0
nospam8963 (52)
3/7/2009 4:28:35 AM
Builder wrote:
> Sheldon wrote:
>> Is there any way to link a field I'll call StudentId in Table1 with
>> StudentId in Table2?  In Table1, StudentId is a number and in Table2
>> it's a text field.  The values match up fine so other than one being a
>> number and the other a text field, everything else would be okay.  I
>> know I can create an append query to copy, say, Table2 to e.g. Table3
>> and make StudentId a number field in Student3 but wondered if there's
>> a more "elegant" solution.
> 
> As Sol Shapiro noted, use the CStr() or CLng() functions.
> 
> SELECT T1.*, T2.*
> FROM Table1 T1 INNER JOIN Table2 T2
> ON CStr(T1.Column) = CStr(T2.Column)
> 
> OR
> 
> SELECT T1.*, T2.*
> FROM Table1 T1 INNER JOIN Table2 T2
> ON CLng(T1.Column) = CLng(T2.Column)
> 
> 
> I've seen significant slowdowns using this, so you want to index the join 
> columns.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Indexing the JOIN columns won't affect the slow down 'cuz the query must
scan the whole table to evaluate the functions for each row (record).

You can change the data type of the column like this:

In the SQL view of a QueryDef place something like this:

   ALTER TABLE table2 ALTER COLUMN StudentID Long

Then run it.  It will change the data type from Text to Long.  Of course
all the values in that column must be legitimate numbers that can
translate to Long numbers (or whatever number data type you use in the
above command).

-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbL8dYechKqOuFEgEQJQAQCffqdU/iEx7Lt3lgVkyulgk9vWAuEAoKFc
YH43wa5eG5I7E3U1/mC7LFYG
=9ZqQ
-----END PGP SIGNATURE-----
0
me2968 (1474)
3/7/2009 11:00:10 PM
Thank you for all the good suggestions.  I've been out but will now
look to implement a solution.

Sheldon
0
SHPsalm139 (70)
3/12/2009 6:11:19 PM
Reply:

Similar Artilces:

How to link fields in MS word document to Access database
Anyone have experience in linking fields in an MS Word document to an access database? What I am considering doing is taking an existing fill-in-the fields word document, for which I cannot now save the data, and allow it to be opened with no data, have the fields filled in and then save the data in an Access database. I also want to later select the document (from a file in the database), open the word document and populate the fields from the database. Of course if there are then any updates I want to put them back into the database. Workable? Anyone with sample code? Bob Alston On Jun 10, 6:21=A0pm, Bob Alston <bobalst...@yahoo.com> wrote: > Anyone have experience in linking fields in an MS Word document to an > access database? Hello Bob, Instead of Word documents I use RTF-documents. RTF-documents act just like Word documents in Word. The difference is that RFT-documents are plain text-files, and can easily be processed in Access to find - with the appropriate placeholders - the filled in information. HBInc. > > What I am considering doing is taking an existing fill-in-the fields > word document, for which I cannot now save the data, and allow it to be > opened with no data, have the fields filled in and then save the data in > an Access database. =A0I also want to later select the document (from a > file in the database), open the word document and populate the fields > from the database. =A0Of course if there are then ...

How to convert Number Field to Text Field
I'm newbie in Filemaker, currently I'm using Filemaker pro v 6.01 on Windows XP. I have a small problem. I'm trying to make a solution for Hotel Rate. I want to change the USD. 0.00 to "Included" for breakfast rate which already included in the room rate. Anybody can help, will be much appreciated. Thanks. "Adhie" <priyadi77@yahoo.com> wrote in message news:ba1b5c27.0404080446.21e110b2@posting.google.com... > I'm newbie in Filemaker, currently I'm using Filemaker pro v 6.01 on > Windows XP. I have a small problem. I'm trying to make a ...

Sorting a Text field like a Number Field
I have a table with around 18,000 records in it. One of the fields is a Text field, but 85% of the things in that field is a number (The other 15% is a number with a text suffix). I was wondering if there was an easy way to Sort this field as if it were a number (because, when it sorts as text it looks like this... 1 11 12 14 2 21 24 3 35 So far, the only thing I came up with was to make a query with this expression... TempSection: CDbl([Section]) But that either produces a #Error for those rows where the Section has a text character in there or it won't even open the query in datasheet view because of Data type mismatch in criteria expression. What can I do? Can I create an iif statement to catch that error? or Something? Thanks! -- Nathan Try this: sc:iif(isnumeric(x!y),cdbl(x!y),0) where X is table and Y is field. Sort on this but display y. This will place all fields containing text at the top and then sort numbers. If you want all text fields at the bottom then change 0 to 99999999. If you really need to sort the text contianing fields as well you will need a funtion to extract the numeric part and use that as the arguement to cdbl. You wouldn't need the iif function then. Good Luck Ira Solomon On 5 Oct 2003 16:31:25 -0700, ngiven@hotmail.com (Nathan Given) wrote: >I have a table with around 18,000 records in it. One of the fields is >a Text field, but 85% of the things in that fiel...

Accessing text form fields name=field[label]
I'm pretty much a JavaScript novice. I'm good at learning by example and changing those examples to suit my needs. That said .... ..... I have some select fields in a form I created for a database search that I am unable to figure out how to access. (The search is implemented in Php/MySQL.) The user enters search values for: name, address1, city, .... etc., ..... and for each of these they also select whether the search should look for the field to be equal to, not equal to, Like, or Not Like the value in the database. So in the form I have: <input type=text name=city> in combination with <select name=func[city]> <option value='='>=</option> <option value='!='>!=</option> <option value='LIKE'>LIKE</option> <option value='!='NOT LIKE>NOT LIKE</option> </select> For an example, this search could be to find an entry "WHERE city $func[city]] $city. (Php - e.g., WHERE city = 'Denver'). This works fine - in Php. I use similar combinations for name, address, etc., ... Okay, here's the JavaScript problem. For the Reset action I have a JavaScript function to Reset all the form fields. I want to Reset the selected indexes for the "func" fields (func[name], func[city], func[state], ...) using a loop that goes through the associative array, "func." I cannot find the right syntax to address them as an array and I am beg...

text field only for text
Hi I would like some help about the following : I have a text field and I don't want it contains any numbers. How can I limit this situation ? So far, I couldn't find literature exacly about this here in the newsgroup. TIA Paulo "Paulo Rodrigues" <amigo.alf@netvisao.pt> wrote in message news:<newscache$kgmtmh$bb1$1@newsfront4.netvisao.pt>... > Hi > > I would like some help about the following : > > I have a text field and I don't want it contains any numbers. How can I > limit > this situation ? > > So far, I couldn't find literature exacly about this here in the newsgroup. > > TIA > Paulo Paulo, the online help is your FRIEND. Private Sub Text0_KeyPress(KeyAscii As Integer) If KeyAscii >= 48 And KeyAscii <= 57 Then KeyAscii = 0 End If End Sub Just put this code in the KeyPress event of whatever text field you do not want to contain numbers. OR you could glue thumbtacks to the number keys on all your users keyboards... Thought I'd add my 2 cents worth. Avoid magic numbers, especially when you can use builtin VBA constants. The following is a little more intuitive to read and a future developer does not have to lookup 48 and 57 to see exactly what is being blocked. Private Sub Text2_KeyPress(KeyAscii As Integer) 'Cancel any numeric keys If KeyAscii >= vbKey0 And KeyAscii <= vbKey9 Then KeyAscii = 0 End If End Sub The above solu...

SQL Server View integer field shows as text field in Access
Hi All! I have an SQL Server View where my field in the view is an integer field and works well. When I link to it from Access, the field is defined as a text field in Access. Does anyone have any idea why this is happening or how to fix it? Thanks in advance! KB On Sun, 3 Aug 2008 20:31:43 -0700 (PDT), kevinfbutt <kevin.butt@iavalley.edu> wrote: That's quite unusual. Can you post the SQL for that field in that view? What version of Access and SQL Server? Fully patched? Can you replicate the problem with a view in one of the SQL Server sample databases? -Tom. Microsoft Access MVP >Hi All! > >I have an SQL Server View where my field in the view is an integer >field and works well. When I link to it from Access, the field is >defined as a text field in Access. > >Does anyone have any idea why this is happening or how to fix it? > >Thanks in advance! > >KB Is the column in the view the result of a calculation? If so, try applying the CAST or CONVERT function to coerce the result back into an integer. "kevinfbutt" <kevin.butt@iavalley.edu> wrote in message news:162bd7b3-e29e-4aba-bb57-e92bfd689684@2g2000hsn.googlegroups.com... > Hi All! > > I have an SQL Server View where my field in the view is an integer > field and works well. When I link to it from Access, the field is > defined as a text field in Access. > > Does anyone have any idea why this is happening or how to fix it? > >...

access97, sql2005 memo field in a linked table shows as a text field
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd table today, it is linked as a text(255) field, ditto for the first table if I link it today if I link the 2nd table using access2003 (and the same odbc data source) it is linked as a memo field I don't think anything was changed on sql2005 or access97 since linkage of the first table produce the correct field type does anyone know what is required to get access97 to link a memo ...

multiple conditions in a join, access doesn't like on (table1.field=table2.field and table1.field=table3.field)
Hi I have written the following query and am having problems with the last outer join in ms access... it doesn't like the multiple conditions that I have in there, if I have just one or the other its OK but not both... and I have tried putting the second condition in a where clause but then i don't get an outer join i'm pulling my hair out on this one... i would be extremely greatful for some ideas... SELECT ro.thickness,ro.width,ro.grade,sum(ro.pcs) as pieces, ro.length,GT.thicknessid,t.thicknessid,gt.gradethicknessgroupid, gtd.gradeid, gr.gradeid FROM(((NewOrders as RO LEFT O...

text fields to memo field
Is there a way to combine text fields into one memo field? This is an existing database. Some people in the database may have several "contact" entries where they have a "note" in a text field. I would like to figure a way to combine all the "note" fields for each person into one memo field. Thank you "RR" <texson552000@yahoo.com> wrote in message news:<S6HRb.969$ee6.74@fe2.texas.rr.com>... > Is there a way to combine text fields into one memo field? > > This is an existing database. Some people in the database may have seve...

Forcing a New Line in a Text Field in MS Access using VB
Heya! I feel I'm beating an old horse to death with this question but I can't get around the problem. I want to update a text field on a form with a string from a VB module. I need enter/linefeeds in the string. I've tried: Chr(10) & Chr(13) Chr(13) & Chr(10 vbnewline vbcrlf none of which seem to work. I've set the properties for the text box to [Enter Key Behavior] = 'New Line In Field' and [Can Grow] = 'yes'. At the moment, my code looks like this: celsql = celsql _ & "Program: " & encname _ ...

MS Access 2002 problem: linked form fields in ODBC queries
Ok, my problem is the following: I have very complicated Access 97 databases that link through ODBC to Sybase databases. Now in some of the forms controls I had queries that used as "where clause" parameters form field values; For example: select foo from bar where a_colmun = [forms]![MyForm]![MyField] This worked in Access 97 but fails in Access 2002 (ODBC failure) when the table *bar* is an ODBC Sybase (in my case) linked table. If *bar* were a local Access table, the query would do fine. if i did select foo from bar where a_colmun = (select [forms]![MyForm]![MyFi...

HELP! Retriving field names and properties from MS Access database using ASP
Hi everyone! I have a really frustrating error and need help. I&#8217;m trying to do a function that gets the properties and names of the fields in a MS Access database using ASP. I haven&#8217;t programmed in a while so I&#8217;m quite lost. Once I have the my database structure I will insert the corresponding fields from a web form but the database is huge and I want to be able to change the database and form without changing the ASP code all the time. This is my function that is not working yet. I cant seem to connect properly to the database... Function InsertDataBase() ...

Changing Text Field to ported field
I did a search, and maybe I'm looking things up using the wrong criteria. I apologize if this has been discussed before, and if it has, please let me know where to find it. Thanks. FMPro V6.04 Windows XP Our users are currently entering in all SW code associated with a problem into a text field, using paragraph returns or comma's as delimiters. To make metric gathering more useful, I created a child database to collect the SW mods individually (with all the details that go with them), and have created portal on my main database to this child database so that they can enter in the ...

Changing drop-down field to text field.
I am working on creating an editable grid (for use in adding, deleting, and editing rows back to an Oracle database). I have a JSP that posts back to a servlet, which in turns posts to a WebLogic SFSB and, from there, to the database. On the front end, all cells appear as text fields. However, for certain cells, when the user clicks on the cell, the text field turns into a drop-down field (i.e. Select object), defaulting to the value in the text field (unless the field is blank, in which case the drop-down defaults to the first entry in the drop-down). Once the user has selected a new en...

update text field based on selection field
ok, this is gonna seem stupid, but it has been stumping me all afternoon. I have two fields on a HTML page. HTML looks like this... <html> <head> <title>New Page 3</title> </head> <body> <form method="POST" action="index.asp"> <select size="1" name="shiptype" onchange="crazyJavascriptFuntion()"> <option selected>SELECT ONE</option> <option value="3">GROUND (3 DAY)</option> <option value="2">2ND DAY AIR (2 DAY)</option> <option value=&qu...

Adding a number of fields for another computed field.
Hello, I am trying to compute a field called "Total" using the formula language. The 42 fields used in the calculation are named m_1, m_2, m_3, m_4,.... and are of the type number. I could use m_1 + m_2 + m_3 + .... But I was hoping to use the @For command or create some other loop. I have tried coding the @For, but have not be successful. Thanks, Mike K. ...

copying the contents of an autonumber field to a number field
I have a form and a subform. The main form has order id field which is an autonumber type. The subform also has order id field which is a number type. I want the subform order id to have the same value and the mainform order id. I used the following code set to subform order id on focus. Me![order id] = Forms![Enter Work Order] I get error 2113, the value you entered isn't valid for this field. How should I code this? Thanks Greetings, By convention, the mainform will be based on the main table and the subform will be based on the related detail(s) table(s). If t...

Converting a text field to a date field
I need to convert a Text field containing both auto and manually entered dates over to a Date field. The records that were autoentered move over without a problem. But those that were edited or entered manually using a two digit year do not. For example: 1/25/02 converts to 1/25/1902. Is there any workaround to solve this problem short of manually correcting the converted data. Thanks Carl if( year(DateField)<1920, date(month(DateField), day(DateField), year(DateField)+100), DateField) This will obviously take any year before 1920 and add a century to it. If you might legitimately...

Create date field from substring of text field
I am trying to populate a field in a SQL table based on the values returned from using substring on a text field. Example: Field Name = RecNum Field Value = 024071023 The 7th and 8th character of this number is the year. I am able to get those digits by saying substring(recnum,7,2) and I get '02'. Now what I need to do is determine if this is >= 50 then concatenate a '19' to the front of it or if it is less that '50' concatenate a '20'. This particular example should return '2002'. Then I want to take the result of this and populate a field called TaxYear. Any help would be greatly apprecaietd. Mark Mark, Assuming both RecNum and TaxYear fields are in the same table, you can use this script to populate TaxYear: update YourTable set TaxYear = case when SubString(RecNum,7,2) >= '50' then '19' + SubString(RecNum,7,2) else '20' + SubString(RecNum,7,2) end Shervin "Mark" <markcash@Hotmail.com> wrote in message news:57bdc737.0310151257.1dc4d0a9@posting.google.com... > I am trying to populate a field in a SQL table based on the values > returned from using substring on a text field. > > Example: > > Field Name = RecNum > Field Value = 024071023 > > The 7th and 8th character of this number is the year. I am able to > get those digits by saying substring(recnum,7,2) and I get '02'. Now > what...

Click link in popup window and insert link text in a field of mother window
http://asianmachinetrader.com/dbase/add.htm For the "Category" field in the page above, is it possible to click on the category (in the "Show me" popup window) and have the category inserted into the field? I have searched http://developer.irt.org/script/form.htm but could not find anything related. Thanks. Hi Blue "Blue�" <mrblue@pd.jaring.my> schrieb im Newsbeitrag news:bktbof$6j$1@news6.jaring.my... > http://asianmachinetrader.com/dbase/add.htm > > For the "Category" field in the page above, is it possible to click on the > ca...

Re: Click link in popup window and insert link text in a field of mother window
I tested that and it is not working. Please note that the category list is on a popup new page. Clicking the link will insert the category into the Category field of the mother page. I have tested putting the JS link in the mother page and popup page - both didn't work. Can help further. Appreciate. ========================================== the following is the start of your categories.htm on http://asianmachinetrader.com/dbase/add.htm. I added the function from my last posting: <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>AMT: Possible Machine Categories</title> <script language="javascript"> function insertIntoOpener(inStr) { window.opener.document.forms[0].Category.value=inStr; window.close(); } </script> </head> [..] Then you have to change your list of possible categories like in the following snipplet: [..] <a name="Agricultural">Agricultural</a></font></b></p> <blockquote> <p><u>Categories</u><br> <a href="javascript:insertIntoOpener('Cultivator')">Cultivator</a><br> <a href="javascript:insertIntoOpener('Plunger Pump')">Plunger Pump</a><br> <a href="javascript:insertIntoOpener('Brush Cutter')">Brush Cutter</a></p> </blockquote> [...

Text versus Number field.
I see other posts where some say fields that will hold a number with leading zeros should be stored as text instead of numbers. This is very inefficient though, as a string of digit characters takes up much more memory than a 32bit or even a 64bit int. Storing as a number still allows for formatting when displaying the number, correct? I am creating a table and would like opinions regarding this please. I work for a telephone company and there is a great deal of difference between local UK numbers (one leading zero), international numbers (double zero) local numbers (no leading zeros). Y...

Checking a text field with numbers
RE: Access 2003 I have an app that use a CheckNumber column. This check number column is set to text because leading zeros are required on check numbers. The table has approx. 100k rows. Here is what the QBE window built, which runs populates a combo box for the user to choose from: "SELECT DISTINCT CHECKNUM FROM MyTable WHERE (((CHECKNUM)>"0")) ORDER BY CHECKNUM DESC;" This column is indexed as "Duplicates OK". Is there a way to index only the rows that have a value? Would that make it run faster? Thanks. *** Sent via Developersdex http://www.developersdex.com *** ...

number zero in text field
Hi heros, I have a text field that stores numbers like 06-1234. I created a combo box to look up values on a form based on the number. However, I can't get it formatted correctly. It is formatted @@-@@@@. The results are 0-61234 in the combo box. Can you assist me? Thank you Charlie -- Message posted via http://www.accessmonster.com hireagenius wrote: > Hi heros, > I have a text field that stores numbers like 06-1234. I created a combo box > to look up values on a form based on the number. However, I can't get it > formatted correctly. It is formatted @@-@@@@. ...

Web resources about - Linking A Text Field With A Number Field - comp.databases.ms-access

Object Linking and Embedding - Wikipedia, the free encyclopedia
Object Linking and Embedding ( OLE ) is a technology developed by Microsoft that allows embedding and linking to documents and other objects. ...

Linking To Advertisers’ Facebook Pages Lowers Costs
Advertisements on Facebook yield more paying customers when the promotion links to a page on the social network instead of an external website. ...

An Industry Standard for Linking on Mobile
Linking is an important part of what makes the web great, but right now it's too hard to implement and expose deep links on mobile. There's no ...

Facebook vs. Google: the battle for deep linking supremacy
Facebook and Google are in an arms race, but this time not over active user counts. Both tech giants are making big moves in the deep linking ...

Mobile app deep-linking and new Cards - Twitter Blogs
Twitter Cards are being used by more than 10,000 developers, mobile apps and websites to richly represent content on Twitter, including article ...

App Store - LinKing
Get LinKing on the App Store. See screenshots and ratings, and read customer reviews.

Flickr: Linking Paths' Photostream
Explore Linking Paths' photostream on Flickr. This user has 368 photos on Flickr.

SOPA & PIPA HORROR STORY UK citizen extradited by US for LINKING to a copyrighted site - YouTube
Originally Uploaded by JeepersMedia on Jan 18, 2012 (by Mike Mozart) STEAL THIS VIDEO! Re-Upload to YouTube and Everywhere! Evidence Blog ht ...

Israel's Benjamin Netanyahu stirs trouble by linking Muslim leader to Holocaust
Israeli Prime Minister Benjamin Netanyahu provoked controversy ahead of a visit to Germany by saying the Grand Mufti of Jerusalem convinced Adolf ...

Syrian Opposition Claims It Has Evidence Linking Assad Forces To Recent Chlorine Gas Attack
BEIRUT (Reuters) – Syrian opposition activists have posted photographs and video that they say shows an improvised chlorine bomb to back up claims ...

Resources last updated: 3/20/2016 3:31:23 PM