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

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

FROM CGEQUP_Base

WHERE (((CGEQUP_Base.ActiveCusto

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

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.

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.

If this is the case then add to the query a IsNumeric(Casted(ActiveCus

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"

1st option:

SELECT CGEQUP_Base.StockNo, CGEQUP_Base.ActiveCustomer

FROM CGEQUP_Base

WHERE (((CGEQUP_Base.ActiveCusto

Error "Data type mismatch"

2nd option:

SELECT CGEQUP_Base.StockNo, CGEQUP_Base.ActiveCustomer

FROM CGEQUP_Base

WHERE (((CGEQUP_Base.ActiveCusto

Error "Data type mismatch"

John, I did as suggested and it sorts correctly, screen shot attached.

Capture.JPG

Hmm strange

I noticed that on the CustomerNumber construct you use**Val **and on the Where you use **INT**...any case this might affecting

I noticed that on the CustomerNumber construct you use

Tried both with same error.

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.

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

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

Mark

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

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.