Query with IIF

[canmex]

I would love if someone could help me out. What I would like to do if possible is reference a text string in a IIF function within a query.

This is what works for me.
PriceUSDCode: IIf([Invoicebody].

="536" Or [InvoiceBody].[Code]="537",([PriceUSD]*0.8),[PriceUSD])

What I would like is this.
PriceUSDCode: IIf([forms]![menu]![DiscountString],([PriceUSD]*0.8),[PriceUSD])

The text box [DiscountString] would hold the string that could be changed thus having this applied in my query. 

I may be going about this in the wrong way so if you have any suggestions. Any help would be greatly appreciated.

here is the complete complete SQL
SELECT Invoices.WorkOrderNo, Invoices.CustNo, Invoices.DateIssueSOR, Invoices.Exchange, Invoices.InvPaid, Invoices.invwotype, Customers.NAME, [sorprifix] & "-" & [SOR] & "-" & [SORext] AS SORNo, InvoiceBody.InvoiceNumber, InvoiceBody.QTY, InvoiceBody.PriceUSD, InvoiceBody.Code, IIf([Invoicebody].[code]="536" Or [InvoiceBody].[Code]="537",([PriceUSD]*0.8),[PriceUSD]) AS PriceUSDCode, InvoiceBody.CostCenter, Round([invoicebody].[qty]*[PriceUSDCode],2) AS ExtUSD, Invoices.DollarInvoice, IIf([Invoices].[DollarInvoice]="1",Round([PriceUSDCode]*[forms].[menu].[TipoDeCambioAnt2],2),[PriceUSDCode]) AS PricePesos, Round([PricePesos]*[invoicebody].[QTY],2) AS ExtPesos, Invoices.SOR, IIf(IsNull([code])=True,"",[code] & "-" & [CostCenter]) AS codigo, IIf([dollarInvoice]="1",[extUSD],0) AS USD, IIf([dollarInvoice]="2",[extPesos],0) AS MN
FROM ProgramParamiters, (Invoices INNER JOIN Customers ON Invoices.CustNo = Customers.CustomerNum) INNER JOIN InvoiceBody ON Invoices.WorkOrderNo = InvoiceBody.InvoiceNumber
WHERE (((Invoices.DateIssueSOR)<=[forms]![menu]![AntigDate2]) AND ((Invoices.InvPaid)="1") AND ((Invoices.invwotype)="WO") AND ((InvoiceBody.QTY) Is Not Null))
ORDER BY InvoiceBody.Code;

 

[Bat17]

not sure which way around you want it, but if you want to compare the Code to the form then something like this should do it.

PriceUSDCode: IIf([Invoicebody].

= [forms]![menu]![DiscountString],([PriceUSD]*0.8),[PriceUSD])

HTH

Peter

 

[canmex]

Logical

That makes sense; I will give this a try.

 

[canmex]

Not Working

Not working as I would like.

Basically what I am trying to do is replace the evaluation part of the IIF with a Text String.

IIF(Text String From Text Box, apply discount, no discount)

I have tried these strings in my text box:

="536" or [code]="537"
[InvoiceBody].[code]="536" or [InvoiceBody].[code]="537"
Neither one works

This allows my operators to change this string and apply the discounts necessary. My idea is in the end, use a drop down box with the various different discount strings. Pick and automatically its applied to the printed report.

Thanks for your help.