Not yet, no discount is applied to anyhting not even the recods with the 537 or 536 codes,

Do you have a big gun. not for my head but for my computer.

PriceUSDCode: IIf([forms]![menu]![PriceDiscount]=[code],[PriceUSD]*0.8,[PriceUSD])
Text Box String:
[invoicebody].[code]="537"

Is there any sensitive information in this DB? If not, perhaps we could take this offline and I could take a look at the database as a whole in the morning and see if I can help you out that way. I worked on something similar today that involved dynamically generating the query from within VBA. A little complex, but the result was pretty sweet.

Let me know,

Mike

Mike
EchoVue.com

Not really much sensitive, allot of invoices. The front end and back end of this DB is about 70 megs. I could delete a bunch of records in the backend to get the size down. Zip would reduce it a bunch also. How would you want me to get these file to you.

You are very kind to make this offer, thanks

Last night in my web searching for a solution I was reading about dynamically generating queries. I think I may have no choice. I could use it in other areas of this system as well.

http://www.fontstuff.com/access/acctut17.htm

No worries at all. What I would do is copy the DB, delete all the forms, queries and tables, except the ones involved, Zip it, and then send it to me at...

mike - at - echovue - dot - com

Mike

Mike
EchoVue.com

Mike, first off I would like to say thanks for all your help. I am impressed with your prompt responces.

I got it working using a dynamically generated query.

Here is my late night code that generates the query. Will clean it up in the morning.

Private Sub Command762_Click()
    Dim Results As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("ProvisionWO-2")
    strSQL = "SELECT InvoiceBody.Code, IIf(" & Forms!menu!PriceDiscount & ",[PriceUSD]*0.8,[PriceUSD]) AS PriceUSDCode, [sorprifix] & ""-"" & [SOR] & ""-"" & [SORext] AS SORNo, Invoices.DateIssueSOR, Invoices.InvPaid, InvoiceBody.QTY, Invoices.invwotype, Invoices.WorkOrderNo, Invoices.CustNo, Invoices.Exchange, Customers.NAME, InvoiceBody.InvoiceNumber, InvoiceBody.PriceUSD, InvoiceBody.CostCenter, Invoices.DollarInvoice, Invoices.SOR, Round([invoicebody].[qty]*[PriceUSDCode],2) AS ExtUSD, IIf([Invoices].[DollarInvoice]=""1"",Round([PriceUSDCode]*[forms].[menu].[TipoDeCambioAnt2],2),[PriceUSDCode]) AS PricePesos, Round([PricePesos]*[invoicebody].[QTY],2) AS ExtPesos, IIf(IsNull([code])=True,"""",[code] & ""-"" & [CostCenter]) AS codigo, IIf([dollarInvoice]=""1"",[extUSD],0) AS USD, IIf([dollarInvoice]=""2"",[extPesos],0) AS MN" & _
             " FROM (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 ((InvoiceBody.QTY) Is Not Null) AND ((Invoices.invwotype)=""WO""))" & _
             " ORDER BY InvoiceBody.Code;"
    Debug.Print strSQL
' Results = MsgBox(strSQL, vbInformation)
    qdf.SQL = strSQL
    DoCmd.OpenQuery "ProvisionWO-2"
' DoCmd.Close acForm, Me.NAME
    Set qdf = Nothing
    Set db = Nothing

End Sub


Again, Thanks