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

Similar Articles

[PageSpeed] 57

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:

Template Wizard with Data Tracking
I have been using this add-in just fine for about a year, gathering data from about 20 users onto an Excel Database by having them all use the template. But I recently made some changes and then began having the following problem: After a worksheet has been created using the template, and saved as a new record in the database, and then we want to go back in to change the record and update it in the database, the option does not appear to update the database; the only options continue to be to create a new record or to proceed without saving to the database. It is as though the sys...

Fatal Error: Language database file C:\PROGRA~1\FRXFIN~1\FRXFIN~1.
I recently had a user experience the following error when generating a report and thought I would share my fix in case any one else runs into this issue. Run-time error '-2147220503 (800403e9)' Fatal Error: Language database file C:\PROGRA~1\FRXFIN~1\FRXFIN~1.5\FRxXML4.dat not found. Also Received this error: Error 59999 Application-defined or object-defined error To fix this issue I reran the setup.exe and it corrected the issue on my computer on the users I had to run it twice and it corrected it. Troy, Interesting! We recently had a similar issue and if I recall correctly ...

Great Plains SQL 2000 login error
Hi, My boss have restored the Great Plains database on a new server - Windows 2003 R2 Enterprise Edition. I've installed SQL 2000 and then upgraded to SP4. When I try to access the restored database I get the following error : "Youre attempting to login from a data source using a trusted connection. Update the SQL server settings for this data source to disable trusted connections and try loggin in again" When I setup the ODBC driver under Administrative tools, I make sure that the setting is changed to SQL log in and I do enter the SQL username and Password. The databas...

i lost my data
i lost my data earlier today and did not have a current back up, since back up does not allow you to back up to CD - i dont have a floppy drive. everytime i exit money automatically updates the information i have on my hotmail.com website and i can see my information there. is there any way i can possibly get the information back from the website to my computer? When you create a new file, setup MSN Money Synchronization by clicking on Tools, Access My Money Data on the Web. Setup the synchronization and be sure to choose to combine the information in your file and on the web. This wi...

New Domain
Hello Everybody! I'm not really a very experienced Windows-Admin - the same is about Exchange Server. The facts: - We have a domain with some windows 2000 servers, about 400 Users - We have also an Exchane 2000 Server with about 400 Mailboxes Now we began to migrate to windows 2003. Because there were problems earlier (I think when we installed Exchange 2000 first time) we have to do some "hacks" in the AD with a tool to manually edit the LDAP-Directory ... Thats why we want to install the servers new - we will not upgrade with the setup-programs to 2003. I think there...

Can I get Keyboard data not using Controls?
Is there any way I can detect a keyboard event while a Form is open, WITHOUT having to use the keyboard event handlers from the Control that has the Focus? For Example: Let's say I have Form that has only one Control, say a Textbox, and this Textbox is DISABLED so it can't take the Focus and receive keyboard events. Can I still somehow get the data coming from the keyboard and say, put it in a String. Of course, if the textbox were enabled and had the focus, I would want the data to ALSO go in the Textbox as it normally would. Thanks for any thoughts. hi, Ken ! > Is the...

I get a Visual Basic Error When I try to print from the File Menu
Whenever I try to print from the file menu, I get a Visual Basic Error stating "The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros." I can print from the Print Preview Page, but I cannot change my printing options. What do I do. It would appear that you have a macro hiding someplace - probably in an add-in or your Normal template or, if it's only certain documents that are affected, a specific document template - that's intercepting the FilePrint command. Star...

CRM Error
Hello All, We were trying to install SharePoint Portal 2003 on the same server with CRM and after noticiying that it'll not work I manually removed SPS completely. Now when I try to go back to my CRM it gives me the following message on the Browser. An attempt was made to reference a token that does not exist [COMException (0x800703f0): An attempt was made to reference a token that does not exist.] Microsoft.Crm.Platform.ComProxy.CBizUserClass.WhoAmI() +0 Microsoft.Crm.Security.UserCache.GetCurrentUser(Cache cache, String username) +227 Microsoft.Crm.Security.UserCache.GetCurre...

How to add records to a table through a data access page?
I have set up tables and queries in a database, and am outputting them to a report, however what i want is to have users enter the data to the required fields through a Data Access Page. I have set up the DAP with all of the info, however when i include the ADD command, and try and use it, i am prompted with the error message that i cannot add because the recordset for the page is read only. I have tried the access help info to discable, however it says to find the read only property which i have not been able to locate. Any help that can be given will be greatly appreciated. Thanks ...

Error while creating new attribute (Picklist)
HI there, I am getting error message as below, while creating picklist or updating picklist: (I unable to Create new picklist or update in CRM 3.0). First Error Message: Could not complete the operation due to error 80020101. Second Error Message: To create nodes of type "ELEMENT", a valid name must be specified. kindly help me. Warm Regards, Syed I have noticed that i am getting error on page small icon at the bottom and for creating any attributes..... and unable to save any attribute can anyone tell me its top urgent. much appriciated for your early response. i am usi...

formating imported data
I know the basics for Excel, but as far as this is concerned, I'm a little lost. I have a set of data coming to me via a tab deliniated txt document, and I know how to import it in and set it up so that every time the user opens the excel file it asks the user for the txt file to input and removes some of the unnecessary columns. I've got that working fine. However, I need to be able to dynamically reformat what comes in, respacing some of the rows, removing the txt file's header, and fiddling around with small things like that. I have no idea how to do this. If I do any fiddling...

Error
I am getting this error when I try to Send/Receive. I only have one account - using Outlook 2003 (11.5608) Reed ...

update sheet with data at bottom of present data
I have 2 excel 2003 workbooks. Workbook one has 5 column with headings. Workbook 2 has same five colums with headings. 2 gets updated every month with data in A2:E5 (under the headings). I need this data range to go into the workbook 1 at the bottom of the last entry. This is 4 rows and five columns of data. I Need it to update only when called to update. On Mar 9, 9:42=A0pm, outrigger <outrig...@discussions.microsoft.com> wrote: > I have 2 excel 2003 workbooks. =A0Workbook one has 5 column with headings= .. =A0 > Workbook 2 has same five colums with headings. =...

Data Entry Form
I have 11 colums of data with headers and I would like to use a dat entry form. One column is a date format which returns a day name in th next column. One column is a time format and the balance are tex columns. The data base is a customer call log. When I click data and then form from the main tool bar I get generated form that won't allow additional entries. What is the bes way to create a data entry form in Excel 2003 -- Message posted from http://www.ExcelForum.com Hi have a look at http://j-walk.com/ss/dataform/index.htm -- Regards Frank Kabel Frankfurt, Germany > I have 11...

error occurred.CCRMDateTime::ReadDate
create fields on DepMeng. CFDSupportEndDate (DateTime) and when i create Account on SDK alert this eror: ---------------------------------------------------------------------------- ErrorMessage: SOAP Server Application Faulted detail.outer 80040216An unexpected error occurred.CCRMDateTime::ReadDate: 12.05.2004.D:\crm\Build\3297\src\platform\include\Util\CrmDateConvert.h242 Source: System.Web.Services ---------------------------------------------------------------------------- My Project relation source code: ---------------------------------------------------------------------------- .....

Error message 02-21-10
I am receiving the following error message in the send/receive window 0x8007000E 'Out of memory or system resources'. It has caused me to lose my inbox and sent records and no address library. Please help. John wrote: > I am receiving the following error message in the send/receive window > > 0x8007000E 'Out of memory or system resources'. > > It has caused me to lose my inbox and sent records and no address library. > > Please help. We are to guess as to WHICH version of Windows that you use? We are to guess as to WHICH version o...

Data file did not close properly?
Whenever I come out of standby and then start Outlook 2007, I get a message that "A datafile did not close properly last time it was used and is being checked for problems". It almost a minute for Oulook to start. Has anyone else seen this? All the time. Outlook 2007 is too new to function reliably. It has major performance issues and is too slow to disconnect from its information store before your PC goes into standby or shuts down. I would never leave Outlook running. Make sure you exit it completely for at least a minute before you shut down or go into standby. -- Russ Va...

Type of Data
Hello Guys, I have a not-so-interesting issue, but damn frustrating: Let say I import a lotus Notes file (format wk4). Now if I do an formula such as concatenate, or sum, or whatever, it either gives m back #Value, or O... Now if I click in the cell, and then validate, it works... I'm not real good in magic, but I do have very little patience, an clicking in 2053 cells doesn't sound right... Any Idea??? Another question, how can I replace a value to be treated as a text (b adding ' in front of the cell?) => Replace * by '* doesn't work... know search with * do wor...

Error msg when sending an email with attachment
Error msg: Your message did not reach some or all of the intended recipients. Subject: FW: Sent: 7/27/2004 10:26 AM The following recipient(s) could not be reached: Thomas Lam on 7/27/2004 10:28 AM No transport provider was available for delivery to this recipient. I can send email to the same recipient without the attachment. I don't know what to do .... please help Hi Thomas, please read this to solve your problem http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com: 80/support/kb/articles/Q197/4/17.ASP&NoWebContent=1...

Transfer data from Excel col. A to multiple columns in the same sh
A Excel 2003 spreadsheet has only one column of player data: column A. The first three data items in column A are the same for every player: Name, Address and Phone. Every player also has at least one comment but could have any number of comments. Each player’s data is separated from the next by a blank cell in column A. Sometimes, a player’s last comment is blank resulting in two blank cells in column A before the data for the next player. I need an Excel 2003 VBA macro to: 1. Copy just the player’s name, but not the Name: label, to column B as shown. 2. Copy just the addre...

free / busy data in schedule
One of my users was trying to reschedule a meeting, and trying to see when all attendees were available. He opened her meeting, and went to scheduling and all free / busy data was hatched out. When moused over, it said " No free or busy data was retrieved ". When the same users were readded at the bottom of the list, the free / busy data was displayed correctly. We tried to click options and refresh free / busy data, but it did not work. Is there something that will automatically retrieve updated free / busy data? ...

Runtime Error #2
When trying to open Money I receive the following error: Program C:\PROGRAM FILES\MICROSOFT MONEY\SYSTEM\MSMONEY.EXE Abnormal Program termination. I have removed money and reinstalled it and it still won't work. Any ideas?? Version? Have you tried opening the sample file--in Windows Explorer go to the Money directory and double-click on the sample data file? "Jen Hughes" <mikejennhugjr@comcast.net> wrote in message news:0e2501c38cd2$2d4c6cf0$a401280a@phx.gbl... > When trying to open Money I receive the following error: > Program C:\PROGRAM FILES\MICROSOFT MONE...

Plot Visible Data in Excel 2010
I have grouped columns which are hidden and charts based on those columns don't display the data. I can change this for each chart using the design gtab on the ribbon under the Select Data Option but this is on a chart by chart basis. In previous versions of Excel you could control this generically thought Tools > Options. Can I set this generically in 2010 or do I have to do it for each chart? Regards Andy Win XP Pro Office 2010 ...

CRM DATA Base
I have 3 MS CRM/SQL Data bases from Backups 1. _Crm = 95Megs 2. _Metabase =10Megs 3. _Distribution = 3.5Gigs The Server Crazed It was a Single server Deployment This Company hired me to help bring there MS CRM server back up again Question 1 Which data base is the main data base? And why is the _distribution data base so big? Question 2 What Do I need to do to make this Work Again? Do I use the DMF to get the data back in to the System OR Can I Do a Data base restore and than just in stall CRM? Pleas Help the important databases are the CRM the Metabase th...

Upgraded using disk to 1.3 now MSDE error
I used the disk to upgrade MSDE on my "server". We are a peer-to-peer running XP Pro. I followed the instructions in the HTML file. It said to un-install both MSDE and MS RMS, then run the RMS 1.3 setup and it would install the new MSDE. It did that just fine. But, when it went to start the service, I got the message "cannot find CLUSAPI.DLL" something about re-uinstalling but after a few seconds the service does start. I ran a "repair" and still got the error. I can connect into my new database from other computers. Is this a problem? Thanks. Mickie, ...