text conversion to number on select query

Hi,

In a Select Query I'm joining 2 tables by Item ID (unique value, similar to 
Social Security Number) but 1 table created by IT has Item ID as a "number" 
value and the other table has it as a "text" value.

How can I in a Select Query, create a formula that can either have the text 
as a number value and vice versa so i can link the 2 without getting "type 
mismatch in expression." 

I think I can use Cdbl Value or something like that in the formula but not 
sure.

Thanks!
0
Utf
3/8/2010 10:01:01 PM
access 16762 articles. 3 followers. Follow

5 Replies
1447 Views

Similar Articles

[PageSpeed] 14


"inspirz" wrote:subed going to jail

> Hi,
> 
> In a Select Query I'm joining 2 tables by Item ID (unique value, similar to 
> Social Security Number) but 1 table created by IT has Item ID as a "number" 
> value and the other table has it as a "text" value.
> 
> How can I in a Select Query, create a formula that can either have the text 
> as a number value and vice versa so i can link the 2 without getting "type 
> mismatch in expression." 
> 
> I think I can use Cdbl Value or something like that in the formula but not 
> sure.
> 
> Thanks!
0
Utf
3/8/2010 10:33:01 PM
On Mon, 8 Mar 2010 14:01:01 -0800, inspirz <inspirz@discussions.microsoft.com>
wrote:

>Hi,
>
>In a Select Query I'm joining 2 tables by Item ID (unique value, similar to 
>Social Security Number) but 1 table created by IT has Item ID as a "number" 
>value and the other table has it as a "text" value.
>
>How can I in a Select Query, create a formula that can either have the text 
>as a number value and vice versa so i can link the 2 without getting "type 
>mismatch in expression." 
>
>I think I can use Cdbl Value or something like that in the formula but not 
>sure.
>
>Thanks!

I wouldn't recommend CDbl: Double Float numbers might give you roundoff
problems. If the ID is less than 2147483647 you can use CLng; or you can use
CStr() on the number field and join on that instead.

-- 

             John W. Vinson [MVP]
0
John
3/8/2010 11:40:45 PM
inspirz wrote:
> 
> In a Select Query I'm joining 2 tables by Item ID (unique value, similar to 
> Social Security Number) but 1 table created by IT has Item ID as a "number" 
> value and the other table has it as a "text" value.
> 
> How can I in a Select Query, create a formula that can either have the text 
> as a number value and vice versa so i can link the 2 without getting "type 
> mismatch in expression." 

In this example, item_id is autonumber in Table1 and text data type in 
Table2.

SELECT *
FROM
     Table1 INNER JOIN Table2
     ON Table1.item_id = CLng(Table2.item_id);

The CLng function casts the text item_id as a Long data type, which 
matches with the autonumber in the other table.
0
Hans
3/8/2010 11:49:45 PM
"inspirz" <inspirz@discussions.microsoft.com> wrote in message 
news:4E2BCB1D-0662-44CB-BE79-D3AE57BB0EF5@microsoft.com...
> Hi,
>
> In a Select Query I'm joining 2 tables by Item ID (unique value, similar 
> to
> Social Security Number) but 1 table created by IT has Item ID as a 
> "number"
> value and the other table has it as a "text" value.
>
> How can I in a Select Query, create a formula that can either have the 
> text
> as a number value and vice versa so i can link the 2 without getting "type
> mismatch in expression."
>
> I think I can use Cdbl Value or something like that in the formula but not
> sure.
>
> Thanks! 

0
De
3/13/2010 5:36:14 PM
jkjkjk

"inspirz" <inspirz@discussions.microsoft.com> a écrit dans le message de 
groupe de discussion : 4E2BCB1D-0662-44CB-BE79-D3AE57BB0EF5@microsoft.com...
> Hi,
>
> In a Select Query I'm joining 2 tables by Item ID (unique value, similar 
> to
> Social Security Number) but 1 table created by IT has Item ID as a 
> "number"
> value and the other table has it as a "text" value.
>
> How can I in a Select Query, create a formula that can either have the 
> text
> as a number value and vice versa so i can link the 2 without getting "type
> mismatch in expression."
>
> I think I can use Cdbl Value or something like that in the formula but not
> sure.
>
> Thanks! 

0
joelgeraldine
3/17/2010 1:51:15 PM
Reply:

Similar Artilces:

Re: ODBC driver for CSV/TEXT file on Windows 2008
Additional info: VS 2005 - 32 bit app. I had no problem accessing various SQLserver databases in our network. Bill "Bill N" <billn@jaco.com> wrote in message news:... I need to know how to obtain and install ODBC text driver on a WIndows 2008 server. CUrrently, the only driver installed is SQLServer v 6.01.7600 The code below worked on my PC but not on the server (as a client machine). Dim csvConnection As OdbcConnection Dim ConnectionString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & mcsvPath & ";" YOur...

Report: "You have no transactions for the item you selected."
Hi, I've run into a glitch I can't figure out. I'm working with the Monthly income and expenses report. There are amounts listed under the Income - Unassigned category. When I double-click the category to see the transactions, I get the message in the Subject. I did a split-half search to find the supposed account that it registering the amount, but cannot find any transactions that are in error. I tried a Standard Repair without any change. Help! I should add that this is Money 2007. "Robert Berus" <rberus@columbus.rr.com> wrote in message news:OFn...

numbering rows
Is there a way to number each row in Excel so that when it prints out the rows all have numbers? I couldn't find it in Msft online Help but I guess that's no surprise. thanks! Jane Two options. 1. In page setup>sheet select "row and column headers". Note this will print the column headsers also. 2. Insert a column to left of your original columns(will become column A) Enter in A1 =ROW() and drag/copy down the column as far as you wish. Gord Dibben Excel MVP On Fri, 23 Apr 2004 17:15:10 -0700, "jane" <jfnysf@hotmail.com> wrote: >Is there a ...

To have a selection of year to merge
Hello, In my Form there is a commandkey to merge to the All records and below is the VBA, my question is how can we make it like a combo box to chose the year, because the user is not a programmer, and is not able to open it in design view and change the VBA. Private Sub cmdMergeAll_Click() Me.Refresh MergeAllWord ("select * from PelayanJemaat Where TahunPel=2008") 'Note that you could use a condtion in the above sql End Sub Thanks in advance for any help provided. -- H. Frank Situmorang Put a control on your form where the user can enter the date. For exa...

Case select returning error when cell contains #N/A : how must i avoid this error
Title says it all Thanks, Luc maybe this will do what you want Sub test() If Not IsError(Range("C1").Value) Then Select Case Range("C1").Value Case 1 MsgBox "1" Case 2 MsgBox "2" End Select End If End Sub -- Gary Keramidas Excel 2003 "Luc" <lferr@live.be> wrote in message news:7AB07996-F1EE-41C1-8D9D-8A7DA54CED91@microsoft.com... > Title says it all > > Thanks, > > Luc Thanks for your help !!!!! Luc "Gary Keramidas" <GKeramidasAtMSN.com> schreef in berich...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

Query cross two table
Hi, I have two tables, tbAdmission and tbCode. In my tbAdmission, I have Code1, Code2 and Code3. In my tbCode, I have Code, Description1 and Description2. In my Form, frmAdmission, I have txtCode1, txtCode2 and txtCode3 that are all bounded to tbAdmission. And txtDescription1Code1, txtDescription2Code1, txtDescription1Code2, txtDescription2Code2, txtDescription1Code3 and txtDescription2Code3 that are unbounded and only for displaying the descriptions. txtCode1, txtCode2 and txtCode3 all refer to Code in tbCode to retrieve Description1 and Description2 for displaying in the unbou...

Conversion Errors Table
Hello, I'm new to working with Access, I just converted an Access 97 databas into Access 2002. It tells me there were errors, and to look at th Conversion Errors Table. But nowhere in the message or in the MS Hel is there anything telling me where to find this table. Can someon help? Thanks Patric -- psha ----------------------------------------------------------------------- pshaw's Profile: http://www.officehelp.in/member.php?userid=493 View this thread: http://www.officehelp.in/showthread.php?t=125029 Posted from - http://www.officehelp.i I'd expect to find it in the new...

lotus approach queries VS access queries.
Hi, We are migrationg from approach to access. My basic underastanding of the procedure is that the data has to be migrated and all the other features like forms and reports have to be recreated. Is 'Approach query' different from MS Access query? Can this be assumed to be replaced by Access query? cheers, Nuti ...

Date Query 12-07-07
Hello I'm trying to build a query that shows me all records where a field is older than 90 days from today or are null. I've tried using <Now()-"90" Or Is Null but this doesn't give the desired results. What am I getting wrong? Thanks Assumption: Your field is a date field Field: YourDateField Criteria: Is Null Or <DateAdd("d",-90,Date()) Or try dropping the quotes in your expression so it reads Field: YourDateField Criteria: <Now()-90 Or Is Null -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management Un...

automatically numbering rows
I wish to have rows automatically numbered in column "A" according to the content in column "B". If column "B" has text or numbers in a cell I do not want to number that row in column "A". If column "B" is blank in a cell I want to number that row in column "A". I found the following formula that works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" . What would the formula be if I wanted to numerically count the blank cells in "B" and skip the conte...

Number Format
Is this available/possible? The difference between engineering and Scientific is that engineering is always in the form of "11.11En where "n" is a multiple of 3. Thanks, Bill Hi AFAIK this is not possible in Excel -- Regards Frank Kabel Frankfurt, Germany "Bill Allen" <ballense@cox.net> schrieb im Newsbeitrag news:1eq6d.26462$7k.7198@okepread05... > Is this available/possible? > The difference between engineering and Scientific is that engineering is > always in the form of "11.11En where "n" is a multiple of 3. > > Thanks, &g...

Pivot Text
I know that excel is probably not the place to be doing this but..... I am wanting to create a matrix of information that is currently all in columns text. I have tried to use pivot tables but obviously whenever I drop text into the values area it is ecpecting a number. What can I use as an alternative Excel 2007 PivotTable With Text (and tricks) http://www.mediafire.com/file/yt1zy2nwgm0/12_11_09.xlsm http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_11_09.xlsm ...

Select Working TV Signal
Hey peeps, I've been looking for an answer to this problem for a couple weeks, done lots of google searches and searched around these forums to see if my question had already been answered.. as far as I can see it has not been. If it has, please point me to a link. thanks. PROBLEM Can not get Media Center TV Setup (win7) to see STB signal. The STB itself is a Scientific America with 1xCoaxial in, and 1xCoaxial out. *NO* RCA/COMPOSITE/HDMI. It is connected to the computer from the Coaxial out to the antenna in on my Hauppauge PVR-150 It asks me if I have a set-top box. ...

how to create leading zeros on variable length numbers
I have a column of variable length numbers/text (6-12 characters). I need to place leading zeros in each of the cells to create a standard length of 15 characters. What's the easiest way to do this? sleect cells or column then pull-down: Format > Cells... > Number > Custom > and enter 000000000000000 in place of general -- Gary's Student "MVPitts" wrote: > I have a column of variable length numbers/text (6-12 characters). I need to > place leading zeros in each of the cells to create a standard length of 15 > characters. What's the easie...

Deleting data from a table through a query
I have a database that is designed to update a list of credit union members from a master list so that vehicle insurance coverage can be tracked. I have been able to run an unmatched query to achieve a list of old members who have either paid off their vehicles or moved their loans to other locations. What I need to do now is delete these people from the main table. I have the cascade update and delete funtion in place to delete the vehicle information once the member is deleted, but I don't know how to take the information found in the unmatched query and delete those members fr...

SQL Query in VBScript
I am using the following code to perform a SQL query and return a recordset I am getting the error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" This error occurs when I open the record set. What am I doing wrong? Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "DSN=ChartMES;" objRecordset.CursorLocation = adUseClient strQuery = "Use...

centering text box in a table cell
Trying to do a project. I am attempting to center a text box in a row of a particular table. If I put it inline, I can kind of get it centered, but, it stays to the left side in the cell, doing about 32 of them, and they are all the same, tried adjusting, but must be doing something wrong. Select the text box, click on the dog icon on the picture toolbar, click none. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Bruce" <notreally@idontwantoo.com> w...

An error number : 424 problem
I'm having trouble with an attachment field. On a report I can see the picture and the filename of the selected attachent properly. Now in a SQL UPDATE string that i'm making I always get the error 424 Object required. Here is the line where it happens : 'mySQL = mySQL + ", scan_g = " & prn_du_pied_g.FileName scan_g is a Text field prn_du_pied_g is an attachment All I want to do is to use the filename of the attchment. How do I solve this Thank you for the help -- Eric "ericb" <eric@b.com> wrote in message news:9A...

Multiple serial numbers when making adjustments to material receip
When doing material receipts we have the ability to add multiple serial numbers. For issues (adjustments) this must be done one by one.We need the ability to issue multiple serial numbers on the same transaction. For instance, we have to issue ten cable reels in ten different lines versus one with ten serial numbers from the list. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to ...

Replacing ~ with text
i have a spreadsheet that gets delivered and some of the cells simply contain a "~". i want to replace the "~' with "N/A". however, when I use the Edit:Replace command, get a message back that says: "Microsoft Office Excel cannot find any data to replace. Check if your searc formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet." This worksheet is definitely NOT protected. Help! Hi ~ is a special character to Exc...

Pivot Table Selection
I used to have check boxes in my drop down boxes, but now they are gon anyone know the answer? I have had this happen to me, seemed to depend on where the drop down boxs are. It they are at top of page there are no check boxs. If they are futher down in the Pivit Tabel thet will have check boxs. >-----Original Message----- >I used to have check boxes in my drop down boxes, but now they are gone > >anyone know the answer? >. > ...

Counting Lines of Text in TextBox
In a form, I have a text box in which memo text wraps. Is there any code that can count the number of lines of text that display in the textbox? My only workaround at this point is to use a non-proportional font like Courier, count the characters and divide by the number of characters that a line might contain. Unfortunately, this approach only gives me a rough estimate of the number of lines. And I'm stuck with Courier when I prefer Arial. Is there another way to count lines of text? Thanks. Jim Why? What will knowing the number of lines of text allow you to ...

How to associate combo box with text box
I have a combo box named comOCCode on frmContract form. The combo box lookup the O C codes from table tblOCCode. The tblOCCode has two fieldd, OCCode and OCType. I also have a txtOCType. The txtOCType display the O C Type associate with the O C Code selected in the comOCCode combo box. How do I associate the comOCCode combo box control to the txtOCType text control? Thus, what is the macro, or how do is change the property for the combo box and the text controls? Thanks RD Paul, If I understand your question, you want the text box txtOCType to show the OCType for whatever OCCode is...

report the date found in a cell of text
Hi, I'm trying to grab and use the dates from thousands of cells of text. I think I'm making this too complicated. My horrible formula, so far, sort of reports on dates that use "/" and I can tell I'm going in the wrong direction with it. There is very little consistency in the text length or date. I've been looking at this for too long. Does anyone have any advice, please? =IF(OR(RIGHT(MID(SUBSTITUTE(MID(A1,SEARCH("/"&"*"&"/0", A1,1)+3,8),"0",""),1,7))="/",RIGHT(MID(SUBSTITUTE(MID(A1,SEARCH(&quo...