troubleshooting Question

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

Avatar of Mark Drelinger
Mark DrelingerFlag for United States of America asked on
Microsoft Access
10 Comments1 Solution65 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
John Tsioumpris
IT Supervisor
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros