Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #11 (permalink)  
Old March 7th, 2006, 01:26 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"

  #12 (permalink)  
Old March 7th, 2006, 01:30 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
  #13 (permalink)  
Old March 7th, 2006, 01:50 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


  #14 (permalink)  
Old March 7th, 2006, 01:54 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
  #15 (permalink)  
Old March 7th, 2006, 03:07 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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







Similar Threads
Thread Thread Starter Forum Replies Last Post
IIf umeshtheone Beginning VB 6 3 June 21st, 2007 12:24 AM
Nested Query with an iif statment Corey Access 7 October 17th, 2005 04:40 PM
Nested Query with an iif statment part 2 Corey Access 1 October 17th, 2005 12:47 PM
IIF in a query for a logical jgsteel VB How-To 1 December 28th, 2004 04:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.