string comparsion using access--help

Hi
I using access 2003. I am trying to compare data from two tables. Table A 
and Table B. Table A has 12 digits in 1 column and table b has 10 digits in 
1 column. I need to compare Table A data starting in position 2 and ending 
in positon 11. I tried using instr function but it did not work.

SELECT  A.ItemLookupCode, B.ItemLookupCode
FROM            A, B
WHERE   InStr(2, A.ItemLookupCode, B.ItemLookupCode,11, COMPARE)



Thanks

Cbs

0
Cheryl
6/28/2007 5:56:39 PM
access 16762 articles. 3 followers. Follow

2 Replies
891 Views

Similar Articles

[PageSpeed] 41

On Jun 28, 1:56 pm, "Cheryl" <cboxiesc...@mindspring.com> wrote:
> Hi
> I using access 2003. I am trying to compare data from two tables. Table A
> and Table B. Table A has 12 digits in 1 column and table b has 10 digits in
> 1 column. I need to compare Table A data starting in position 2 and ending
> in positon 11. I tried using instr function but it did not work.
>
> SELECT  A.ItemLookupCode, B.ItemLookupCode
> FROM            A, B
> WHERE   InStr(2, A.ItemLookupCode, B.ItemLookupCode,11, COMPARE)
>
> Thanks
>
> Cbs

use the "mid()" function

0
zionsaal
6/28/2007 6:12:34 PM
Your syntax for InStr is incorrect (and it's not the correct function 
anyhow).

 SELECT A.ItemLookupCode, B.ItemLookupCode
 FROM   A INNER JOIN B
 WHERE Mid(A.ItemLookupCode, 2, 10) =  B.ItemLookupCode



-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Cheryl" <cboxiescott@mindspring.com> wrote in message 
news:46DB7710-8B0B-4A17-BF40-4F2339D4BFBC@microsoft.com...
> Hi
> I using access 2003. I am trying to compare data from two tables. Table A 
> and Table B. Table A has 12 digits in 1 column and table b has 10 digits 
> in 1 column. I need to compare Table A data starting in position 2 and 
> ending in positon 11. I tried using instr function but it did not work.
>
> SELECT  A.ItemLookupCode, B.ItemLookupCode
> FROM            A, B
> WHERE   InStr(2, A.ItemLookupCode, B.ItemLookupCode,11, COMPARE)
>
>
>
> Thanks
>
> Cbs
> 


0
Douglas
6/28/2007 6:14:21 PM
Reply:

Similar Artilces:

Help for ICT Gcse c/w
Hi Basically I am making a model for my ICT coursework (AQA), it's about dog training courses for a vets surgery. For part of it, we have to work out the minimum number of dogs needed for a course to run (has to be less than �50). I have made a table, which links to other worksheets.. No. of dogs: Cost per course: Cost per dog: Can course run? 1 �348.35 �348.35 No 2 �350.60 �175.30 No 3 �352.85 �117.62 No 4 �355.10 �88.78 No 5 �357.35 �71.47 No 6 �359.60 �59.93 No 7 �361.85 �51.69 No 8 �364.10 �45.51 Yes 9 �366.35 �40.71 Yes ...

Using IF statement
Excel 2003 Trying to write an IF statement in a particular cell and don't know i it's possible. I have the following example data in spoecified cells: B2: 131 B3: 130 B4: 131 B5: 138 B6: 139 Target Value Cell C1: 136 Is it possible to write a statement, in say cell D1, such that that th entire range of values B2 to B6 is compared to the target value in cel C1. For example, if *ANY* value in B2:B6 is less than the target the print "Yes", otherwise print "No". So for the case above a "No" should be the result. Obviously, it ca be done if the state...

GoToRecord (Specific record) in subform using listbox
I have a form with a listbox. I have a subform below bound to the recordset of the listbox. I want to be able to click on the row and push an edit button to have the selected record shown in the subform below for editing. I'm doing something like this on the edit button: Private Sub CommandButton_Click() Dim ID As Integer Me!SubForm.SetFocus DoCmd.GoToRecord , , acGoTo, ID End Sub The problem is the ID I'm passing and the ID it's pulling aren't the same thing. If the ID I'm trying to pull is too high, I will get a Run-Time error 2105 - You can't go to the specif...

Need to group elements using XSLT
Hi, I am trying to group nodes in a huge xml file by one of the common fields. I have tried using "for-each-group" and "group-by" but am unable to get it working. Here, I have a list of Ports under each DSLAM entry and would hence like to group in a hierarchy all such Ports under each of these DSLAM entries as shown below. Here's a sample xml file (similar to the one I am trying to transform, but simplified to remove a lot of junk fields) and the required xml output - ----Sample XML file----- <?xml version="1.0" encoding="UTF-8"?> <Conv...

Select records from query using added checkbox
Hallo, I have a subform in which I show records resulting from a query displaying certain data from two tables. I would like to add a checkbox to every entry of this query, so the user can select the query-records he wants to use to perform additional tasks with. E.g. Say the query collects data on persons and their jobs based on certain criteria. The user must be able to (de-) select the persons he would like to leave out of a report that will be made once the user has decided on the persons. Also I would like to have all the checkboxes initially checked, so the user only has to deselect s...

Help! ESEUTIL /r (3 character logfile base name)
Who knows the (3 character logfile base name) mean? Thank you so much! "Liu222" <liu222@discussions.microsoft.com> wrote in message news:<02cb01c4842f$028dbff0$a301280a@phx.gbl>... > Who knows the (3 character logfile base name) mean? > > Thank you so much! usually E00 "Liu222" <liu222@discussions.microsoft.com> wrote in message news:<02cb01c4842f$028dbff0$a301280a@phx.gbl>... > Who knows the (3 character logfile base name) mean? > > Thank you so much! usually E00 ...

Aligning text to right margin using right tab
Hi, I am trying to create a heading in a Publisher 2003 document, the first part of which starts flush with the left margin and the second part of which ends flush with the right margin. I have done this before in Word using left aligned text, with a right tab stop at the right margin. However, this doesn't seem to work in Publisher - the tab seemingly has to be in indented from the actual margin position, otherwise the text wraps onto the next line. Am I doing something wrong, or have I missed something? I don't really want to create a separate text box to achieve this, as ...

How can I use ClassWizard in Visual C++ 2005 ?
Any help will be appreciated.Thanks! View->ClassView -- Vipin Aravind http://www.explorewindows.com "howen" <howen@actions-semi.com> wrote in message news:e87ZX6MQGHA.2108@TK2MSFTNGP10.phx.gbl... > Any help will be appreciated.Thanks! > > What used to be ClassWizard has now just been integrated into the right click menus. For example, if you open a dialog and right click on a control, you'll see items for most of the ClassWizard functionality. Unfortunately, the traditional ClassWizard was removed in version 7 in difference to conforming the VS ...

BSOD in RtlStringCbVPrintfA() when using %wZ format specifiers on XPSP3
I get a BSOD when using this func with the UNICODE_STRING format specifier %wZ (the bug is actually in vsnprintf. when the UNICODE_STRING is empty. ie, a valid buffer (zeroed or unzeroed makes no difference) with the Length and MaximumLength set to zero. Now I know these specifiers aren't documented, but they exist, and are well known, and used. This is the crash: 0: kd> !analyze -v ******************************************************************************* * * * Bugcheck Analysis * * * ****************...

Divide by 0 Help
=(SUM(E98:F98)+D98/2)/(I98-G98) Any suggestions on how I can rewrite this to return 0 if I get a Dicide by 0 Error in any of the calculations. I am a bit of a novice. Check your earlier post. On 05/17/2010 06:48, joey_boy wrote: > =(SUM(E98:F98)+D98/2)/(I98-G98) > > Any suggestions on how I can rewrite this to return 0 if I get a Dicide by 0 > Error in any of the calculations. I am a bit of a novice. joey_boy wrote: >=(SUM(E98:F98)+D98/2)/(I98-G98) > >Any suggestions on how I can rewrite this to return 0 if I get a Dicide by 0 >Error in any of the c...

how to properly create new profile to use with existing pst file
OL 2002 SP3 Win XP HE SP1 *Follow-up to: microsoft.public.outlook* hi, it is possible that my current (default) OL profile has become corrupted. i need to create a NEW profile for troubleshooting purposes and see if it resolves current problems. the most important issue is: how do i create this new profile so that it uses my *current* pst file so that i can have a seamless process and continue where i "left off" in terms of all of the items contained in the *current* pst file, emails, appointments, addresses, etc.? do i just create new profile and tell it to "use *curr...

I just got office 2007 help!
After downloading both steps on MS site. I was upgraded for each utility.....ie excel word etc. except outlook it still remains 2003 why? Everything else went to 2007 "Rob" <Rob@discussions.microsoft.com> wrote in message news:9C6D0578-01E5-4E49-BCC1-A1B2B3A1AD69@microsoft.com... > After downloading both steps on MS site. I was upgraded for each > utility.....ie excel word etc. except outlook it still remains 2003 why? > Everything else went to 2007 You need to uninstall Outlook 2003 first...... Because you must first uninstall Outlook 2003 before you install ...

Program trying to access address book
I am running Outlook 2002(XP) and there is a small window that is continuing to pop up on my screen asking me if I will allow access to my address book? It just started this about 3 weeks ago and I have run all of the McAfee virus protection sofware out there and cannot find out why it is happening. I am also running a Dell Axim 5 PDA and would like to know if there is a way to check the PDA for a virus. Please help! Jimmy It's probably your PDA sync software. If you use Chapura's PocketMirror (supplied with Palm's), go to www.chapura.com and get the required update for ...

Excel Date
want to be able to have a cell auto fill the first time the sheet i saved so i have the creation date. Then if poss - never let them chang the cell so it keeps record of when the file was first created. Thanks - any help will get some free chocolate! ;-) Arie Astor Chocolate Corp. www.astorchocolate.co -- Message posted from http://www.ExcelForum.com Try some code in the workbook module ; e.g. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Range("Sheet1!A1") = "" Then Range("Sheet1!A1") = Date End If End Sub Then set Sheet1...

Data Access Pages Data Source
I have several data access pages I created with tables as the source. The tables are in Microsoft SQL Server 2005 Express. I have since created queries and copied them into the database as a View. How do you change the data source from the linked tables to the Views? Thank you. it's not good idea to uase data access pages - they are not supported in latest Access versions. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Arturo" wrote: > I have several data access pages I created with...

Loading Speed of Form
Hi all, I have a question regarding the loading speed of forms. I have about 6 forms in my database, each is loaded with lot of controls and VBA driven action that calls numerous queries. So as expected, they do take a while to load, 10 - 30 seconds. For some remote desktop users, it usually take even longer. These forms, however, do not have any onLoad. procedures. All the code are in click of buttons of afterUpdate of dropdown menu and others. The interesting thing is, these form load much much faster (3 secs) when one form opens the next, instead of manually open it from the database wind...

NTBackup question
Good morning! Quick question regarding the use of NTBackup to backup the Information Store in both Exchange 2003 SP2 and Exchange 2007 SP1 running on Windows Server 2003. Question Number 1: I am curious as to the VSS Provider | VSS Writer used for this. Let's assume that there is nothing else on the system (think Acronis) that *might could* have a VSS Provider...so, we are talking only the "Microsoft Software Shadow Copy provider 1.0". I do the good ole "vssadmin list providers" and see what is there. I then do the "vssadmin list writers" ...

Import or link a Label from Word to Access
How do I import or link a Label created in MS Off 2007 Word to a MS Off 2007 Access in order to to link it to a Table. My Label in Word contains more lines to the Label in Access. -- Roger You can't import the Word label. You will need to recreate the label in Access by using the label wizard. You will see it when you press New on the Report tab of the database window. "Roger" <Roger@discussions.microsoft.com> wrote in message news:1DE7F240-F56C-4188-A754-A3C49AC10C5B@microsoft.com... > How do I import or link a Label created in MS Off 2007 Word to a MS Off &...

HELP!! Formula for calculating overtime
I'm hoping someone can help me. I can't seem to figure out a formula to calculate OT for my staff. Regular hours per day are 7.5. I'm looking to create a formula for banking time which will break out in two columns hours worked over the regular 7.5 hours per day up to 11 hours per day, and in the second column the hours worked over 11 hours per day. Does anybody have any ideas? Thanks so much!! Something like this Hours Bank1 Bank2 6 0 0 7.5 0 0 8.5 1 0 9.5 2 0 10.5 3 0 11.5 3.5 0.5 12.5 3.5 1.5 13.5 3.5 2.5 14.5 3.5 3.5 15.5 3.5 4.5 assum...

Formatting Odd Number Strings
I have imported a text report into Excel. For some reason the date string on the text file reads as 20/05/0728. Is there a quick way to reformat the string so it reads 7/28/2005 or even 2005/07/28, for that matter. I really don't want to manually edit 300+ lines! Thanks. Assuming your fubared date is in column A, insert a new column immediately to the right of that, and enter this formula: =DATE(LEFT(A1,2)&MID(A1,4,2),MID(A1,7,2),RIGHT(A1,2)) This parses the existing text string into arguments used by the DATE function, which generates a date useable by Excel. What is the cell...

Using Money 2006
I have been using Money 2006 for a few years primarily to keep track of my portfolio which is mostly bonds. Last March my 2 years was up and I got a bunchj of nag messages to buy the new version which I ignored. The program has been working about the same before the expiration - when I log in my stocks and bonds are updated to today's values - bonds as usual are only updated manually by me. The only change is if I try to update prices from within the program, I get the expiration message. The price update occus OK when I open the program. When I close the program it looks like ...

HELP -- XP Address Book: No Microsoft Solution Listed
What is the plain and step-by-step simple solution for this very annoying error: "A new entry could not be added. You must have a personal address book (PAB) to create an entry. Add a PAB to your profile using the E-mail accounts command on the Tools Menu." It doesn't allow me to create one!!! Even when I do this: right click on my contacts folder, click on 'Properties', then 'Outlook address book' tab. The option 'show this folder as an email address book' is blanked out and not available for me to check or uncheck. HELP You first. What is it t...

Converting a 2003 Access Database to a 2007 Version
At my work, we have an Access database that was created in 2003 version. And now we have a new employee with Microsoft 2007 on her computer and she is unable to use the Access Program. I am guessing because it is an old version. Is there an easy way to either convert the database or do i need to do something else? Any advise is appreciated!! thanks She should be able to use the 2003 Access program. What happens when she tries? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Bonnie" wrote: >...

Reporting invoiced sales using sale line items via smartlist
I use the sales order processing module to enter invoices and returns. I created a report that reports sales and returns by sales line items for posted and unposted invoices via the smartlist. However, the report is not accurate because it includes invoices that were voided and adds the amounts for RETURNS to the total. As a result, the results are overstated. 1. What steps do I need to take to ensure that these voided invoices are not included? 2. What steps do I take to ensure the the sales line item for returns are subtratced from the total not added to the total? For voided ite...

Need help formatting text
I am using OE 6 to read newsgroups and I sometimes post articles that I find on the web, often copying and pasting articles into new messages. However, when I do this, it formats it in such a way that when I open my posts, the paragraphs are separated by at least 3 lines, rather than 1 line. I have tried adding an additional step of pasting articles as unformatted text in word before I paste them in a new message in OE. It seems that no matter what I do, however, I get this weird extra spacing between the paragraphs. Anyone have any idea how to solve this problem? -- Aloha, G-Ride &qu...