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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
An Experts Exchange subscription includes unlimited access to online courses.