Avatar of Mark Drelinger
Mark Drelinger
Flag for United States of America asked on

ACCESS QUERY RESULTS GIVE DATA TYPE MISMATCH ERROR WHEN FILTERING AS A NUMBER

I've converted a text field (ActiveCustomer) parsing out a CustomerNumber, but the result gives me an error when i try to filter on the value.
I used Val() to convert string to a number and the result  'looks' like a number (right justified) on the screen.

The error is "data type mismatch"

Access Query:
SELECT CGEQUP_Base.StockNo, CGEQUP_Base.ActiveCustomer, CGEQUP_Base.InventoryStatus, DateValue(Left([CGEQUP_Base].[ActiveCustomer],6) & "20" & Mid([CGEQUP_Base].[ActiveCustomer],7,2)) AS DemoStartDate, Date()-DateValue(Left([CGEQUP_Base].[ActiveCustomer],6) & "20" & Mid([CGEQUP_Base].[ActiveCustomer],7,2)) AS DemoDays, CGEQUP_Base.EquipmentModel, CGEQUP_Base.EquipmentMake, Val(Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1)))) AS CustomerNumber
FROM CGEQUP_Base
WHERE (((CGEQUP_Base.ActiveCustomer) Like "*DEMO*") AND ((CGEQUP_Base.InventoryStatus)<>"V") AND ((Int(Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1)))))<>99));

My results look good if I remove the filter, but I need to remove all records with customer number of 99

Results
StockNo      EquipmentModel      EquipmentMake      InventoryStatus      ActiveCustomer                                                      DemoDays      CustomerNumber      DemoStartDate
24517      130G                      JOHN DEERE      D      01/09/20                AMUSEMENT NATIONAL 1925 DEMO              21                      99                              1/9/2020
25118      700K                      JOHN DEERE      D      01/20/20                MOSSING CORP 10597 DEMO                              10                      10587                      1/20/2020
26017      75US-5                      HITACHI      D              01/14/20                G & T BROS CONST 4000 DEMO                      16                      4010                      1/14/2020
Capture.JPG
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Jim Dettman (EE MVE)

You want it in quotes then:


WHERE (((CGEQUP_Base.ActiveCustomer) Like "*DEMO*") AND ((CGEQUP_Base.InventoryStatus)<>"V") AND Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1))))<>"99";


Type mis-match means you are supplying a string when it wants a number, or a number when it wants a string.


Jim.

Jim Dettman (EE MVE)

You could do it the other way too:


Val(Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1))))<>99


Jim.

John Tsioumpris

DO a sort ascending and then descending to see if you actually have the value of the ActiveCustomer correcly casted as number...probably a data type mismatch will popup...
If this is the case then add to the query a IsNumeric(Casted(ActiveCustomer)) and filter out the bad ones.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Drelinger

ASKER
HI T=Jim, I found that Both ended up with the same error:

1st option:
SELECT CGEQUP_Base.StockNo, CGEQUP_Base.ActiveCustomer, CGEQUP_Base.InventoryStatus, DateValue(Left([CGEQUP_Base].[ActiveCustomer],6) & "20" & Mid([CGEQUP_Base].[ActiveCustomer],7,2)) AS DemoStartDate, Date()-DateValue(Left([CGEQUP_Base].[ActiveCustomer],6) & "20" & Mid([CGEQUP_Base].[ActiveCustomer],7,2)) AS DemoDays, CGEQUP_Base.EquipmentModel, CGEQUP_Base.EquipmentMake, Val(Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1)))) AS CustomerNumber
FROM CGEQUP_Base
WHERE (((CGEQUP_Base.ActiveCustomer) Like "*DEMO*") AND ((CGEQUP_Base.InventoryStatus)<>"V") AND Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1))))<>"99";

Error "Data type mismatch"

2nd option:

SELECT CGEQUP_Base.StockNo, CGEQUP_Base.ActiveCustomer, CGEQUP_Base.InventoryStatus, DateValue(Left([CGEQUP_Base].[ActiveCustomer],6) & "20" & Mid([CGEQUP_Base].[ActiveCustomer],7,2)) AS DemoStartDate, Date()-DateValue(Left([CGEQUP_Base].[ActiveCustomer],6) & "20" & Mid([CGEQUP_Base].[ActiveCustomer],7,2)) AS DemoDays, CGEQUP_Base.EquipmentModel, CGEQUP_Base.EquipmentMake, Val(Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1)))) AS CustomerNumber
FROM CGEQUP_Base
WHERE (((CGEQUP_Base.ActiveCustomer) Like "*DEMO*") AND ((CGEQUP_Base.InventoryStatus)<>"V")) and Val(Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1))))<>99

Error "Data type mismatch"
Mark Drelinger

ASKER
John, I did as suggested and it sorts correctly, screen shot attached.
Capture.JPG
John Tsioumpris

Hmm strange
I noticed that on the CustomerNumber construct you use Val and on the Where you use INT...any case this might affecting
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark Drelinger

ASKER
Tried both with same error.
ASKER CERTIFIED SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mark Drelinger

ASKER
Thanks team... I created a function and all is well.  My gut says it something within the initial "ActiveCustomer" field (pulled from xml call in another application).

I appreciate all of the suggestions and guidance - always greatly valued.

Mark
Dale Fye

Another way to address this might be to modify the select so that it looks like:


SELECT SQ.*

FROM (

'Insert your SELECT query here

) as SQ


Then, you can display the results of the subquery in design view and apply the criteria to the field you want, that way instead of using all of the functions in the WHERE clause, you can actually use the name of the computed field.


Dale


I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck