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 7494 articles. 0 followers. Follow

2 Replies
1422 Views

Similar Articles

[PageSpeed] 38

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:

Lost data in office 2007
Hi all, It happened few times in three months. I built a database and did save data all the time, but excel suddenly shut down and it appeaed "damage data". Even I tried to recover it, it was not a complete database that I built . What should I do? Does it always happen in office 2007? Claire ...

Runtime Error with Excel VB Sub
I set up a 4-sheet Excel workbook and I have a VB subroutine that manipulates ranges across all the pages. The subroutine works ok if called from sheets 2, 3, or 4 but not from sheet 1. I set up a button on sheet1 that has this action: ---- TestLog.xls - Sheet1 Private Sub CommandButton1_Click() Call ActiveWorkbook.CalcUnique End Sub ---- CalcUnique is my subroutine that begins this way: ---- TestLog.xls - ThisWorkbook Sub CalcUnique() Dim Band As Integer ' other Dims deleted [UCalls3].ClearContents 'Clear Unique list for all bands ---- When called from th...

Order Data in Pivot Table Non ascending or Decending
I've had problems ordering my data in a pivot table. How do I order the data to be formatted the way I want it to be? e.g Want: Year 1 Year 2 Year 3 Sales 1000 1250 1500 Less: Cost of Goods Sold 500 450 500 Gross Profit 500 800 1000 Selling Expenses 200 400 600 Instead of: Gross Profit 500 800 1000 Less: Cost of Goods Sold 500 450 500 Sales 10...

Profiler Puzzle
In Visual C++ 6, I made a simple MFC app. I'd like to profile a function that contains mainly calls to external C functions, but when I run the profiler, I get an error as soon as that function starts to execute. Doesn't the profiler work in such cases, or am I overlooking something? Brad Hi, Problem solved. The current directory is different when the profiler is being used, so the app wasn't finding all the files it needed. I copied some files and resolved the prob. Brad "Internet Citizen" <internetcitizen@earthlink.net> wrote in message news:tqA_d.13789...

Separating Data
Hi...I have 4045 rows of data that look like this: 16TH FL BENN TOWER/4385 3900 CHESTNUT/6178 I need to separate out the 4 diget code after the /...so it's in a column by itself. Can you help me with the code. Thank you. DaveB =RIGHT(A1,4) If you want the information before the "/" in a seperate cell: =LEFT(A1,LEN(A1)-5) -- HTH, Laura Cook Appleton, WI "DaveB" <dberger16@comcast.net> wrote in message news:079e01c3672f$2d2d4050$a001280a@phx.gbl... > Hi...I have 4045 rows of data that look like this: > > 16TH FL BENN TOWER/4385 > 3900 CHEST...

smtp error message
hi to all i'm receiving this error message: source: smtpsvc eventid: 2013 SMTP could not connect to any DNS server. either none are configured or all are down. please help, thanks in advance =?Utf-8?B?YXJ0?= <art@antcom.co.il> wrote in news:542485F4-2D68-4569-AF75-6A83E88459DD@microsoft.com: > i'm receiving this error message: > source: smtpsvc eventid: 2013 > SMTP could not connect to any DNS server. either none are configured > or all are down. Do you have DNS servers correctly defined in your network settings? Regards, -- Arlo Clizer Exchange MVP FAQ: htt...

PowerPoint Movie Error Code 0x80040E23
I recently began using Windows Movie Maker (came loaded with Vista on a Dell computer purchased in April of 2009). Today, I took a video clip and edited it down to use in a PowerPoint. I "published" it to "this computer." It was saved as a wmv file. When I attempted to drop it into PowerPoint, it would not play. I tried putting it in through "insert" (movie and sounds) and also by pulling it in as a media clip. When trying to pull it in as a media clip, an error box came up with the message "Clip Organizer cannot complete the operation. ...

Database Query while retaining other data
Hi all, I have a spreadsheet that is designed to be used as an Asset tracking tool. It has an MS Query returning asset purchasing data like 'Item Description', 'Unit Price' etc. This data changes daily and the query is set to update whenever the spreadsheet is opened. There are also columns within the spreadsheet that are to be used for data that doesn't come from purchasing: like Serial Number, Make, Model, etc. My intention is to have the MSQuery data interspersed with the asset data. However, whenever the data refreshed and new rows are added the asset data do...

Error while archiving
Trying to do file archive sent items to a personal network share. Trying to archive items older than 1/1/2003. Estimate 4000 items fall in this range. I get an error message "Error while archiving... folder .... mailbox.... Some items could not be either moved or deleted or access was denied." I'm using Outlook 2002. I've installed all Office serivice packs and fixes. ...

Error 1311
When installing MS Office Publisher 2007 this message pops up. "Error 1311 source File, E:"\Y1561401.CAB. What does this mean and why am I not able to continue installing? I am wourking with MS Vista Home Premium. Try copying the entire contents of the CD to a folder on your hardrive. Run the setup.exe from there. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "PixelKing" <PixelKing@discussions.microsoft.com.> wrote in message news:786F8592-F3B1-4B3A-82AD-7F2D57082AF9@microsoft.com... > When i...

Error using derived simpleType as attribute in XSD -> DataSet
I have been given a schema, instances of which I'm required to be able to consume and generate. I'd like to be able to manipulate these instances as DataSets internally in my application. The schema defines the following simpleType: <xs:simpleType name="cs"> <xs:restriction base="xs:token"> <xs:pattern value="[^\s]*"/> </xs:restriction> </xs:simpleType> If I'm reading that correctly, it's not that different from a normal xs:token - it simply imposes the extra restriction that the token contain no whit...

How to import data from PDF?
Does anyone have any suggestions on how to import data from PDF? For example, http://www.rvd.gov.hk/en/doc/statistics/his_data_17.pdf Thanks in advance for any suggestions Eric Hi Eric Try, Able2Extract http://www.investintech.com/prod_a2e.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Eric" <Eric@discussions.microsoft.com> wrote in message news:15F6326B-17C8-4DC5-ABFF-63345303492B@microsoft.com... > Does anyone have any suggestions on how to import data from PDF? > For example, http://www.rvd.gov.hk/en/doc/statistics/his_data...

Where is my data?
I have a listbox on a form that is not showing the data anymore. It seems to be associated with a particular table. I can create new listboxes on the form using a different table and it works fine. I try to create a listbox on a different form using the table in question and the results do not show. I do not see anything wrong with the table. All the data is still there. We got new computers which upgraded our Office product from MS Office 2002 to MS Office 2003. Any ideas what the problem is and how it can fix this? Thanks in advance Dwight Unfortunately, you've apparen...

SOP transaction transfer error
we are getting errors whrn transfering Quotes to orders or transfering Orders to Invoices. The document transfers but leaves the source doc. unposted. error is cannot incert duplicate key row in object sop30200 with unique index AK4sop30200. we also get stored procedure soptrxDeleteWork returned the following results. DBMS:2601, GP:0 Not we just had to combine a back up table and a live company tables to replace documents that were deleted. these errors are happening on newly created documents. The error message says the software is trying to insert a record into the SOP History ta...

Merge Data
I would like to compare quotes and orders for clients. On both QUOTE and INVOICE sheets within workbook I have following columns headings which are identical: QUOTE: COLUMN H = CLIENT COLUMN I = 2007 COLUMN J = 2008 COLUMN K = 2009 INVOICE: COLUMN H = CLIENT COLUMN I = 2007 COLUMN J = 2008 COLUMN K = 2009 The rows do not correspond with each other between 2 tabs. However, I would like it to combine data i.e. CLIENT will be merged where duplicated and then in columns will be 2007, 2008, 2009 quoted and invoiced. Is it possible to automatically merge and populate this in order to calculate ...

Duplicate data
my column is long list with much same entries i want to delte all duplicate entries in column is easy way? ty Select all the data Use Data | Filter | Advance Filter | Unique records only Copy the visible cell to another part of worksheet (or another worksheet) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Paula" <PaulaPos@msn.com> wrote in message news:me37k3d1ne8umavgvf5rsijvmb615dpplb@4ax.com... > my column is long list with much same entries > > i want to delte all duplicate entries in column > >...

Filter Data
Hello All, I got a colum A with data like above and need a formula in order t filter it to colum B with only unique items: A B John John Ann Ann Rick Rick John Ann Thanks Very Muc -- Busk ----------------------------------------------------------------------- Buska's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1170 View this thread: http://www.excelforum.com/showthread.php?threadid=31382 Hi Buska You can use Advanced filter http://www.contextures.com/xladvfilter01.html#FilterUR Or use my Add-in that have many options http...

ODBC Driver/Import Data
I have previously used ODBC drivers to import data from some systems utilised at work. These have worked fine for data imports within both Excel and Access (2003). More recently there have been problems with the importing of data from Excel, yet Access remains fine. Have tried re-installing the driver software to no avail, am just wondering if something has triggered the drivers to not function properly in Excel. Any help/ideas. Can provide more specifics if required. Thanks ...

DATA EXPORT
Hi, Can anyone tell me whats the easiest way to export ALL of my lead information into excel or Access. I have brought a new marketing list containing leads and want to deduplicate it against what we already have. I propose doing the following: 1. Import new list into Access 2. Export existing leads in MS CRM into Access 3. Duplicate check in Access 4. Delete the duplicates that originated from the new list 5. Import whats left over into MS CRM Can anyone think of a better/ easier way? Thanks Lee use advanced find to get a list of your lead data then hit export to excel or run a lead re...

Stacking data in a comment
How can I stack numeric data like 4.60 in a Comment.Text instead of vectorizing it? My current output looks like this: 3.50-2.75-3.68-4.25 etc. My current code is: Comment.Text Text:="" & (Range("F5").Comment.Text & "-" & Format(Range("F6"), "0.00")) Instead I would like to output: 3.50 2.75 3.68 4.25 Thank's ahead for any suggestion Separate your strings by vblf (linefeeds). comment.text text:="asdf" & vblf & "qwer" & vblf & "zxcv" Bobby wrote: > > How can I stack ...

data validation challenge
Hi, I'd like to set up data validation so that the only data that can be entered into cells is twofold: 1. a number between 1980 and 2050 2. the text "Phase 2" or "Phase 3" I know how to do #1, but is there a way to also allow the text of #2, in place of #1? Either one or the other. Thanks, Harold One way: Allow: Custom Formula: =IF(ISNUMBER(A1),AND(A1>1980,A1<2050),OR(A1="Phase 2",A1="Phase 3")) In article <edRLW3CnEHA.3292@TK2MSFTNGP15.phx.gbl>, "HGood" <hcgood@hotmail.com> wrote: > Hi, > ...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

Change "FALSE" to "NO DATA"
Right now I have the following function in a cell column: =IF(P18="PMR",E$26) But when the referenced cell does not equal "PMR" it turns up as "FALSE". Can I change this so it turns up as "NO DATA"? Thanks in advance. The below will do what you're looking for. Rgs, Rob =IF(P18="PMR",E$26,"No Data") "AAA1986" <AAA1986@discussions.microsoft.com> wrote in message news:5960938B-F8A9-4F80-A865-3E5FEDF9CEFC@microsoft.com... > Right now I have the following function in a cell column: > >...

how can I view all the data in a cell?
I'm working in Excel 2003. Throughout the entire spreadsheet there are cells that when printed do not show all of the data. We have tried formatting the height and width, wrap text but nothing seems to work. Does anybody have any ideas? RC Excel Help on "limits" or "specifications" reveals that Excel will allow 32,767 characters to be entered in a cell. However, it goes on to state that "only 1024 characters will be visible or can be printed" To work around this limitation, stick a few ALT + ENTERs in at appropriate spots, about every 200 characters.. ...

Data aligning with axis
I have a 2 line chart in 2007 Has two different values for same time periods, but some prior data is missing for one time period. a b FY02 .17 FY03 .25 FY04 .33 .18 FY05 .42 .26 FY06 .49 .33 It keeps moving my "b" line back to FY02??? Not starting underneath my "a" line in FY04? Any suggestions? Thanks, -- Charlie Hi, Using a normal line chart I can not duplicate this in xl2007. Line b starts at the 3 category. If I change the chart type to Stacked Line then it appears that line B co...