Switch not returning what I expected, all I get is #Error

[SaskiFX]

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:

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!

[June7]

LIKE operator is meaningless without wildcard, might as well just use = sign.

SWITCH([Quantity] LIKE 'AR*' OR [Quantity] LIKE 'EST*',[Cost Each], [Quantity]<>'0',[Cost]*[Quantity])

or

IIf([Quantity] LIKE 'AR*' OR [Quantity] LIKE 'EST*', [Cost Each], Cost*[Quantity])

Quantity is a text field?

[Dal Jeanis]

SWITCH(
[Quantity] LIKE 'AR',[Cost Each],
[Quantity] LIKE 'EST',[Cost Each],
[Quantity] NOT LIKE '0', tblPrice.Cost*[Quantity]) 
AS [Total Cost],

What happens in that switch statement when [Quantity] is LIKE '0'?

You might want to add ",True, 0" immediately before the close paren in your switch statement.

[SaskiFX]

IIf([Quantity] LIKE 'AR*' OR [Quantity] LIKE 'EST*', [Cost Each], Cost*[Quantity])

Quantity is a text field?

The IIf statement worked! The Switch statement didn't. I had tried looking into an IIf statement but I was making things overly complicated.

Quantity is a text field so it can hold AR and EST as well as numbers. When it's displayed things seem to work ok.

SWITCH(
[Quantity] LIKE 'AR',[Cost Each],
[Quantity] LIKE 'EST',[Cost Each],
[Quantity] NOT LIKE '0', tblPrice.Cost*[Quantity]) 
AS [Total Cost],

What happens in that switch statement when [Quantity] is LIKE '0'?

You might want to add ",True, 0" immediately before the close paren in your switch statement.

In this setup the Quantity should never be 0, I was using that for a True if everything else fails bit, since some help page on Switch suggested I make sure something evaluated true or the Switch might not work.

Thanks so much on this one, I know my way around programming but am a novice to applying it in access.