UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Nested If Then Statement, Office 2003    
 
   
MrFormula
post May 5 2012, 11:37 AM
Post #1

UtterAccess Veteran
Posts: 461
I am making a modification to an existing DB. I need to be able to pull out the tax rate and put it in this record. The problem is I can't get the if then logic to work.

What I need to do is check to see if this SKU's department is taxabble, if it is then set the trate in OrderDetail to Setup.Taxrate
I would also like to make PartTotal Include the tax rate it is >0

Thank in advance for you help.

Here is my table structure.

Setup (this table only has one record)
TaxRate


Departments:
Department
Taxable (yes/No)

Inventory
SKU (PK)
Sell
Department

OrderDetail
SKU
Sell
TRate
PartTotal


CODE
                stAr = Split(Me("M_" & Intbtn).ControlTipText, "_")
                If stAr(2) <> "-1" Then
                    Set dbs = CurrentDb
                    Set rs = dbs.OpenRecordset("orderdetail", , dbAppendOnly)
                    With rs
                        .AddNew
                            !SKU = (stAr(0))
                            !OrderID = Nz(Me.OrderID, 0)
                            !Description = stAr(1)
                            !Qty = CLng(intQty)
                            !UnitPrice = stAr(2)                      
                            !Date = Date
                            !SoldTo = Forms!OrderSingle.ContactID
                            !shipTo = Forms!OrderSingle.ContactID
                            !Dept = (stAr(3))
'                            !trate =
                            !PartTotal = CLng(intQty) * stAr(2)

                        .Update
                    End With
                End If
Go to the top of the page
 
+
Jeff B.
post May 5 2012, 02:57 PM
Post #2

UtterAccess VIP
Posts: 8,170
From: Pacific NorthWet
Not sure from your description, but I wonder if you would be able to use a query to do that calculation?
Go to the top of the page
 
+
MrFormula
post May 5 2012, 08:11 PM
Post #3

UtterAccess Veteran
Posts: 461
Do you think that I could do it with a sql statement in the VBA?
Go to the top of the page
 
+
Jeff B.
post May 6 2012, 08:07 AM
Post #4

UtterAccess VIP
Posts: 8,170
From: Pacific NorthWet
An Access query is, underneath, a SQL statement.

Consider building the query in Access, converting the view to SQL, then using that.
Go to the top of the page
 
+
MrFormula
post May 6 2012, 06:53 PM
Post #5

UtterAccess Veteran
Posts: 461
I built it in access query and then pasted the SQL to VBA.

CODE
    !trate = "SELECT Inventory.SKU, Departments.Taxable, IIf([taxable]=-1,([salestax]),0) AS ttrate" & _
             "FROM Setup, OrderDetail INNER JOIN (Inventory INNER JOIN Departments ON Inventory.Department = Departments.Department) ON OrderDetail.SKU = Inventory.SKU" & _
             "GROUP BY Inventory.SKU, Departments.Taxable, IIf([taxable]=-1,([salestax]),0);"


I am getting a data type conversion error.

So how do I convert my findings to a number. I know that trate is a number field.

Thanks for the help.

This post has been edited by MrFormula: May 6 2012, 06:54 PM
Go to the top of the page
 
+
Jeff B.
post May 7 2012, 09:50 AM
Post #6

UtterAccess VIP
Posts: 8,170
From: Pacific NorthWet
Take a look at the CInt() (or CLng(), or C...) function to convert-to-...
Go to the top of the page
 
+
« Next Oldest · Access Modules · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 23rd May 2013 - 10:02 PM