puzzling data mismatch error

I have a table where one of the fields contain values such as
26-14-2C-R1-K1
15-13-1C-R3-K6 and so on.
this is a text field

I am trying to find records based on the part: R1-K3, R2-K4, R5-K1
etc.
I am getting weird data type mismatch error when searching with my
query.

For example this works:
SELECT myfield as P FROM myTable
WHERE myfield Is Not Null AND right(myfield,len(myfield)-9) Like 'R1-
K1'
ORDER BY myfield

but this one using a nested OR doesnt and fails with a data mismatch
error:
SELECT myfield as P FROM myTable
WHERE myfield Is Not Null AND
(right(myfield,len(myfield)-9) Like 'R1-K1' OR
right(myfield,len(myfield)-9) Like 'R2-K2')
ORDER BY myfield

also where it seems that using LIKE works (as seen in the first
example)
using =sign fails with the data mismatch error as this doesnt work:
SELECT myfield as P FROM myTable
WHERE myfield Is Not Null AND right(myfield,len(myfield)-9) = 'R1-K1'
ORDER BY myfield

can someone explain what is happening?
Thanks.
0
Jesper
6/7/2010 8:00:21 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
2190 Views

Similar Articles

[PageSpeed] 55

Jesper,
just wondering, but why not use something like

SELECT myField
FROM myTable
WHERE myField LIKE "*R1-K3*";

the only reason I can see for using Right$ and Mid$ (I would use the string
version, not the variant version) is if you can have the string you're
searching for appear in multiple places in the field.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1

0
PieterLinden
6/7/2010 8:26:19 PM
> just wondering, but why not use something like
> SELECT myField
> FROM myTable
> WHERE myField LIKE "*R1-K3*";

wow, don't know why I didn't think of this. I am using criteria for
searching within the first part of the field too,
but the Rx-Ky part is always at the end.
And to add to it - this now works! :

SELECT myField as p,
FROM myTable WHERE myTable.myField Is Not Null AND
(Left(myTable.PlaceringID,2)='26') AND (mid(myTable.PlaceringID,
3,2)='15') AND (mid(myTable.PlaceringID,5,2)='2C') AND (myField LIKE
'*R1-K1*' OR myField LIKE '*R1-K2*')

(extended as this is what I'm actually doing)
For the first section of the WHERE part I do need to search for 26 and
15 in certain places.
But it works now. Awesome - thanks for pointing me in that
direction :-)

Jesper
0
Jesper
6/7/2010 8:56:03 PM
Reply:

Similar Artilces:

Strange error message
Hello, I am getting a strange message when I am trying to copy a graph that I use in a payroll document that we use. It contains data and a graph for each employee. It worked just fine for 80% of the document then all of a sudden I started to get this message: "No more new fonts may be applied in this workbook" with an OK button. I can hit the OK button and it will eventually continue on (after multiple clicks). But each time I copy and paste the graph, it gives me this message. I am using Excel 2002 on Win 2000 Pro with all the latest updates and patches available. T...

Split Forms
Is it at all possible to hide the Data Sheet portion of a split form at run time in 2007? The only solution I have found is to play with the SplitFormSize property, but I am afraid that it does not support different display sizes. Any suggestions ? Thanks ...

Excel data disappeared after getting message about compatibility M
I tried to save changes to a spreadsheet, and received the following message: "Compatibility Report for New Customer List.xls Run on 4/6/2010 19:52 The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format. Minor loss of fidelity Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available." I clicked OK, because fidelity is not imp...

Synchronize data across 2 sheets
Hi all Excel 2007 Workbook with a sheet called 2010 around 700 rows/records and a sheet called Previous around 5000 rows records. Both have 31 columns and identical column headers On the 2010 sheet there are records that have changed, how do I get the changed records details onto and overwite the same record on the Previous sheet. Not sure if it would help but each record has a unique reference number. How do i do this ? Hope this makes sense thanks for any help offered Hi If you know which colums are to be changed I would use vlookup function. Vlookup(lookup valu...

WIN98 Outlook 2000 Distributed Authentication Error
Using WIN98 2nd Edition and Office2000 Outlook. Dial-up connects to internet fine and operates fine, Outlook comes up fine and logs into e-mail server, however just before e-mail is supposed to start downloading I get the following: "An error occurred loading the Distributed Password Authentication Components." After clicking OK, get message box "Microsoft Server Extensions" and message "Unable to read configuration information for Microsoft Personal Web Server error code 1723." Uninstalled Office2000 and totally reinstalled, only to get the same exac...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Authentication Error after restoring SQL databases
Our CRM Development Team messed up our dev server and asked our SQL dba to restore a two day old SQL backup of the MSCRM and METABASE databases. After the restore, the CRM app. is unaccessible through the browser ( Authentication Error. Microsoft CRM could not log you on to the system. Make sure your user record is enabled and that you have been assigned at least one security role. For more information, contact your system administrator. ) Using the Deployment Manager only the License Manager information are visible and neither Server Manager nor User Manager. It seems like the applicat...

Error 0x80ccc0f
This is becoming a real PIA! I can be using Outlook for hours or only minutes and get this error. When I shut the program down and re-start it, it again works for hrs or minutes. Nothing I do seems to make a difference one way or another. Anyone have a suggestion? Thanks, Larry Outlook 2000 SP-3 Ver. 9.0.0.8954 Webmail Accounts Google Yahoo Mail Plus (default) OS Name Microsoft Windows 2000 Professional Version 5.0.2195 Service Pack 4 Build 2195. Submitted using http://www.outlookforums.com Are you using a Linksys router? Linksys routers have a history of problems with ma...

Credit Card Expiration date error
We are getting an error message for credit cards with expiration dates greater than 2010. Has anyone else seen that? We are currently on RMS 1.2 using PC-Charge. It takes expiration dates through 2009. hi Rick, There is update for 1.2 to 1.3 and also in 1.3 there are patches available kindly download from customer sources this will resolve the issue "Rick@ASP" wrote: > We are getting an error message for credit cards with expiration dates > greater than 2010. Has anyone else seen that? We are currently on RMS 1.2 > using PC-Charge. It takes expiration dates thr...

How to total cells in a range with data input
Greetings to all members I am running Excel 97. An office colleague presented me with an issue that might be of interest to any Excel whiz and seasoned programmers. I would certainly appreciate any pointers in solving it. So here goes. The set of values includes 33 cells ranging from B4:D14. The data type is numeric. Data is only input in a few cells. QUESTION What would be the function to enter in, say, cell A15 to indicate the total number of cells in range B4:D14 that have received data input? Or does it involve some behind the scene VBA programming? EXAMPLE B6 = 9 C9 = 4 D11 ...

Insert Data From Multiple Worksheets Into One (With Criteria)
Ok, I have created a sales report list that tracks what customers we are contacting, how we are contacting them, if they are a new or existing customer and if we sent a quote, along with some other details. There is a worksheet the salesmen fills out for each day of the month (its linked to a calendar) and that all goes into a "dashboard" that shows the breakdown with graphs. Now what i would like to do is have another worksheet list all the customers that we sent quotes to so they can mark if the quote was won or lost , state sales projections and give reasons why won ...

Invalid sender domain
Your message did not reach some or all of the intended recipients. Subject: RE: Boat Sent: 4/17/2006 11:19 AM The following recipient(s) could not be reached: 'Fahlahas@Surgv.com' on 4/17/2006 11:19 AM 554 Fahlahas@Surgv.com: Recipient address rejected: invalid sender domain Dan Klinge <Yomama@kdkdjkfjdekfjdkfj.com> wrote: > Your message did not reach some or all of the intended recipients. > > Subject: RE: Boat > > Sent: 4/17/2006 11:19 AM > > The following recipient(s) could not be reached: > > 'Fahlahas@Surgv.com' on 4/17/2006 11:19 ...

Creating a word2000 document with access 2000 data
I have a database in access 2000 it is basically customer info. address and dates and timesof appointments. I need to place a button on the formpage that prints a letter using the customer details and the date & time entered in the database. Anyone have any clues as to the easiest way to do this.sort of done it using mailmerge but not what I want. Actually want a word document to pop up that the staff can just double check and press print. Any help would be appreciated even just a point in the right direction to a good source of information. to see if the following website's offer...

LDAP Error in Outlook 2003
I just upgraded to Office 2003, and now when I go to Outlook to try to send an email, I get an error that says "Can't contact LDAP server (81)". I can eventually send the email, but I get that message several times. -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspector, the Award Winning Anti-Spam Filter http://mail.giantcompany.com You receive a "Can't contact LDAP Directory server (81)" error message when you open the Address Book in Outlook: http://support.microsoft.com/default.as...

joining data points on graph
I am merging two graphs and one has 23 data points, the other has 9. The category axis will have 23 data points but the 9 points are spread out, i.e. they may be at points 1, 3, 7, 15 etc of the 23-point scale. At the moment the graph will only join points that are beside each other; how do I get them to connect to the points that have spaces between them? minihana wrote on Fri, 10 Feb 2006 11:29:04 -0800: m> I am merging two graphs and one has 23 data points, the m> other has 9. The category axis will have 23 data points but m> the 9 points are spread out, i.e. they may be...

Outlook2003 error
Hi all, I am getting an error message when i try to compus a new message...''The messaging interface has returned an unknown error...'' OS=W2K with SP4 Office2003 Please help me Thx in ADV. ...

CRM 4.0 "An error has occurred"
Hi: I did an upgrade from CRM 3.0 to CRM 4.0. First I upgrade Sql2000 to Sql2005 SP2 and then upgrade CRM. I have issue when create new case and tried to assign responsible contact. The application show “An error has occurred”. This form did have a customization in java to filter the contacts to show only the contacts for the account. I removed this customization for the form, but still have the error. I look for any Service Pack or Hot Fix but no found any. Any idea will be appreciated, Regards, -- Jose Valentin Rodriguez MCP-GP MCBMSS-GP Hi Jose, the problem is that you used a (very...

Dynamic data label placement for graphs
I've read the article KB179199 on how to create custom data labels for charts and it's good, however doesn't meet all my requirements. Is there a way to have the data labels automatically move to a desired location relative to the graph? For instance, I'm using 50 bar graphs per work book and would like each data label to be just above their respective bar. TIA Hafeez I looked up article 179199 and got this: BUG: Grid Based on .QPR File Appears Blank in Visual FoxPro 5.0 which probably isn't the one you meant. By above the bar, are they horizontal bars and you ...

Error 553....rcpthosts...
I am receiving the following error message: Your message did not reach some or all of the intended recipients. Subject: After Upgrading to 5.0 Receive Fatal Error 0 Message on Build Sent: 10/24/2003 10:43 AM The following recipient(s) could not be reached: 'someoneh@somewhere.com' on 10/24/2003 10:43 AM 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) I have viewed some of the other posts and done what is recommended, such as authenticate outgoing messages with my username and password. This does not work. I have 3 di...

Error 0x800CCC13 in Outlook
Hi, We have several clients using a variety of Outlook versions (2000/XP/2003). Our mailserver is Exchange 5.5 running on a NT4.0 server, all availbale patches applied. All of a sudden clients can't send messages, the get error 0x800CCC13. I've looked up in TechNet and removed IE and OE completely, rebooted and reinstalled IE and OE, because of KB312349. Without any improvement. The problem is gone when removing the MS Exchange Server from the accountlist. Sending and recieving using POP3/SMTP does work. When the Exchange Server is selected but the primary storage location is ...

Separating data in a column
the spreadsheet i am working on has 1 column with 2 possible answers, i.e. yes/no. what i need to do is separate the replies and then total the yes replies in another cell and also total no replies in a different cell. How do i do it? The following COUNTIF function can be used =COUNTIF($A$1:$A$21,"yes") The sort or data filter command can be used to separate the replies. "Gemgirl" wrote: > the spreadsheet i am working on has 1 column with 2 possible answers, i.e. > yes/no. what i need to do is separate the replies and then total the yes > replie...

error bars
I have been trying to formulate error bars for a graph. Each point on the graph is made of 3 data points. I am having trouble getting started. The help option odes not seem to be very helpful. It states to click on tools then data series, which does not seem to be an option for me. I can not find such an option. Does anyone know how to do this. If so please help. ...

Windows Installer Error 1635
I am getting the following error message when I try to import a CSV file to the calander. Starts off by saying can not start the required translator, tries to install then I get the error message. ...

Merge data by macro
Hi all, I got data in column A and B as show below. Row A B ......col 1 ID DATA ...headings 2 XY01 Record 3 Time 4 Left 5 XY02 Time 6 Right 7 XY03 System 8 Record 9 Time 10 Left I need macro which should merge column B data and put result in column C as shown below Row A C ......col 1 ID DATA ...headings 2 XY01 Record Time Left 3 4 5 XY02 Time Right 6 7 XY03 System Record Ti...

Grouping Same Data in column
Hi Guys, Using: Excel 2000 Issue: Is there a way to create groupings based on same values down particular column. Example: I have phone numbers listed down column L. But some of th numbers appear more than once. Can I automatically create groups base on the same phone numbers so it would be easier on the eye to follow. Hope this makes sense. TIA Jonas :cool -- Message posted from http://www.ExcelForum.com You could sort the list by the phone numbers, and use conditional formatting to highlight the rows that contain a duplicate phone number: Select a cell in the list Choose Dat...