I have a Form, and on that form I have a sub form that is being generated by a Query, and another Query nested under that one. (The form is going to be used for showing prices for parts from different vendors.)
When I look at my parts list table, I have a Quantity field that is a text format, as I can have a static quantity of an item such as 12, or I have entries EST or AS for Estimated or As Required. I have another table that has the list of vendors, and another one that has prices for each item from each vendor. Everything is working as I want, but I have an issue with the AR and EST entries.
Currently when I click the + to expand an entry that has an actual numerical quantity, everything works. If there is an AR or EST I would like it to sub in a 1 for the Quantity, but right now I just get an error.
Here is the currently SQL code for the Query:
Code:
SELECT tblPrice.Cost AS [Cost Each],
tblVendors.[Vendor Name],
SWITCH([Quantity] LIKE 'AR',[Cost Each],[Quantity] LIKE 'EST',[Cost Each],[Quantity] NOT LIKE '0',tblPrice.Cost*[Quantity]) AS [Total Cost],
tblBOM.[Part Number],
tblPrice.[Price Date]
FROM tblVendors INNER JOIN ((tblParts INNER JOIN tblBOM ON tblParts.[Part Number] = tblBOM.[Part Number]) INNER JOIN tblPrice ON tblParts.[Part Number] = tblPrice.[Part Number]) ON tblVendors.VendorID = tblPrice.VendorID
ORDER BY tblPrice.Cost;
Below is how it looks on my screen if it helps.

I have tried all the GoogleFu I know, and I'm stumped. Any ideas?
Thanks!