Courses - Microsoft
Access 310 very strongly recommended|
Profit, Loss, Unit Cost, Dynamic SQL, Format Function, SQL
This course is valid for Access 2000 through 2003.
If you are using Access 2007 or 2010, you will still benefit from this course.
You will find that the concepts are the same, but there are cosmetic differences between the versions.
Order before 1/26/2018 to
get a FREE upgrade to our
Access 2010 version!
Click here for details
Access 311 covers adding cost
to our database so we can track net profit or loss for our sales. We'll
also build a custom sales report and sales form for reporting.
Finally, we'll create a form that has dynamically generated SQL
to control what records and sorting are in the form.
First, I'd like to be able to figure out how much
profit I'm making on each order by knowing the cost of my labors
and materials. We'll start out by adding UnitCost info to our
OrderDetailT and Order Subform. We'll add subtotals to the form as
Next, we'll create a SalesReport query and
report to show each order, what our cost is on that order, and what our
net profit is.
Using our Sorting & Grouping levels and
the Format() function, we'll create a custom format and breakdown
for all of our sales by month.
We'll then create a simple form to set the limits
(begin date and end date) for our sales report. Using the Forms !
FormName ! Field notation and the Between keyword to limit
our sales query / report based on this information.
Next comes something that will add a whole new
dimension to your form design... creating forms with a dynamic SQL
statement controlling their records. In this case, we'll make our
Order List form able to show quotes, invoices, paid invoices, unpaid
invoices, and both quotes and invoices all by just selecting the option
from a combo box. Our VBA code will construct an SQL statement which
will update the records.
We'll also create a combo box to change which
field our records are sorted by (company, name, amount due, order
total, date, etc.) and a Sort A-to-Z or Z-to-A button to control
ascending/descending sorts. This might sound easy, but we have to do it
all using VBA code and events - take nothing for granted!
ACCESS 311 - Course Outline
0. Introduction - 2:42
1. Unit Cost on Orders - 13:16
Being able to calculate profit by knowing item costs
Adding UnitCost info to OrderDetailT
Creating an ExtendedCost field (UnitCost * Quantity)
Adding UnitCost and ExtCost to our OrderDetailF
Adding calculated totals to footer
Adding Cost fields to our Product Table
Get the product cost when a product is selected from ComboBox
2. Sales Report - 12:07
Creating a Sales Report with Cost & Profit
Add cost information to OrderListQ
Create the SalesReport Query and Report
Format() function to force numbers to show as currency
3. Sales Report & Form - 13:49
Adding totals to our Sales Report
Remember you can't put SUMs in your Page Footer
Create a Report Footer
Sort our Sales by Date - Sorting & Grouping Level
Group Header & Footer ON
Group On: Month
Format() Function: Format(OrderDate,"yyyy mmmm")
Criteria form to set begin date and end date for sales report
The Between keyword (remember me?)
[Date] is no good. Use Date()
4. Dynamic SQL Form 1 - 10:10
Clean up Customer List Form
Added Browse Customers and Add New Customer Buttons
Clean up Order List
Create an OrderList2Q so we can make more calculations
Add AmountDue information to OrderList form
Discuss what the Dynamic SQL Form is going to do
5. Dynamic SQL Form 2 - 14:44
Create a combo box with a list of options:
- Show Invoices & Quotes
- Show Quotes Only
- Show Invoices Only
- Show Paid Invoices Only
- Show Unpaid Invoices Only
These are the options we want to see in our Order List Form
Set the default value for the combo box
Create an unbound text box to hold our SQL statement
Review of SQL Statements
SELECT Fields FROM Table WHERE Criteria ORDER BY Field
Create the BuildSQLStatement sub
Creating your own Private Sub
Set the form RecordSource equal to our SQL statement
Me.RecordSource = MySQL
Using VBA Code to build the SQL statement based on selection
Using a SELECT CASE statement instead of IF THEN statements
Creating another combo box for sorting:
- Sort by Order Date
- Sort by Company Name
- Sort by Last Name
- Sort by Order Total
- Sort by Amount Due
Add another SELECT CASE to handle the ORDER BY clause
6. Dynamic SQL Form 3 - 17:07
Checking our Sort Combo Filter
Put a call to BuildSQLStatement in the SortCombo build event
Put a call to BuildSQLStatement in the form OnOpen event
DEFINITION - right click on a function or sub name to jump
Create our own Sort A to Z (ascending / descending) button
Capturing an image on the screen with Print-Screen
Drop that capture into Windows Paint
Cut out the buttons you want to "borrow"
Paste the buttons into your form.
Change the border style (raised, sunken)
Change the button names: SortAZ, SortZA
Make SortAZ visible, SortZA not visible
If the SortZA button is visible, add "DESC" to SQL Statement
Make OnClick events for each of these buttons
Change Enabled = YES for these buttons
"You can't hide a control that has the focus"
Shift the focus with DoCmd.GoToControl
Hide your SQL text box
Add buttons to browse orders, add new order
Change SortAZ and SortZA buttons to IMAGES not OLE objects
7. Review - 2:26
Microsoft Access 311
Richard on 1/1/2008:
Access 311 covers adding COST to our product table and order details table so we can calculate job cost and net profit; dynamically generating SQL statements to control the records on a form; advanced sorting and grouping on reports; more with the Format() function; major overhaul to the OrderListF form.|
Alan Hill on 1/11/2008: Some SQL's get pretty complicated. I found creating a query and switching to SQL mode, copying and pasting can be helpful. You did not cover that alternative. I thought the image AZ and ZA buttons was a neat trick. That was new to me.|
Richard Rost on 1/12/2008: I believe I covered this in Access 202. I showed you the button that switches between DESIGN mode and DATASHEET mode also can show you the SQL of a query. You're right though - I should have mentioned it again when we started covering dynamic SQL for forms.|
Harry on 4/11/2009: Is there an advantage to joining a first and last name using the record source of a text box on a form vs creating a custom field in the underlying query? I tend to prefer the latter as it seems a little quicker in rendering (esp for continuous forms) but I'm not sure.|
Harry on 4/11/2009: Whatis the purpose of a colon after each case statement? I've never seen that done nor used it myself. I can see it works, but what does it do?|
Harry on 4/11/2009: How can one open a form using dynamic sql (the dynamic part I can figure, the open part gives me fits) and avoid docmd.openform which seems to grab all records and then filter them?|
As an aside, apart from increased network traffic in a multi user environment, the filters can be accidently messed with by the user causing no end of confusion. Any comments?
Richard Rost on 4/14/2009: Harry, both work just fine. I prefer the latter as well for continuous forms. For a single form, doing it on the form is OK, but the query is more efficient.|
Richard Rost on 4/14/2009: Harry, the colon is a habit I picked up because (a) it's old school, and (b) it allows you to create a SINGLE-LINE case statement like this:|
case "A": msgbox "do stuff"
case "B": msgbox "do other stuff"
case "C": msgbox "do yet more stuff"
And so on. You can't do this without the colon, so it just kinda stuck with me and I use it all the time.
Richard Rost on 4/14/2009: Harry, just don't specify a recordsource when you build the form. It will open blank. Then change the recordsource yourself in the OnOpen event to a single record with an SQL statement.|
BRYAN binkerd on 6/5/2009: cool beans rick, you rock|
Jane Hu on 7/31/2009: I see a VB code in the course:|
Private Sub CompanyName_DblClick(...)
DoCmd OpenForm "CustomerF",,,"CustomerID=' & Cust....
Could you explain the use of ",,," in the code.
Also I can't see the whole VB code. Could you also explain please.
Thanks very much!
Richard Rost on 7/31/2009: Jane, there are extra parameters in there (additional options you can specify) that aren't important at this time. Don't worry about them. I'll cover them in future lessons.|
David Leech on 9/28/2009: One of my favourite classes so far! I love that you decided to use the az za button for this. In my opinion the command buttons in access are pretty ugly,I anticipate using images alot more. much more fun :)|
Really liked all these lessons. really cool stuff
Benjamin Chua on 10/28/2011: Class Access 311 Lesson 2 Time 11:52|
In question of the Amount not showing as a Currency.
What I did, was I put a Round before the DSUM in the Query.
Eleanor Mason on 7/3/2013: Time Stamp 16:49 Changed the AZ/ZA buttons to image. removed the GoToControl.|
But my AZ/ZA buttons don't work now, I am using Access 2010.
Joni Moore on 8/21/2013: If this comment has already been brought up, I apologize...|
Wouldn't it be better to have your price be a certain percentage of your cost? Then when your costs go up, your prices go up by a certain percentage automatically and you're not having to update twice the number of fields?
Reply from Richard Rost:
That's certainly an option for some situations. If you want to set up your database that way, then that's fine. What if you have a particular product that is a good seller and you advertise it for $14.99, then your costs go up a little bit. You don't want to kill your promotion over a few cents. Always making your prices a function of cost can be troublesome.
Clay FULGHAM on 4/2/2015: I ran into a little issue here, the FilterCombo initially wasn't selecting the value although the dropdown worked, nothing would be selected when clicked. After a little digging I figured out that we'd set the OrderList FORM PROPERTIES: Allow Edits to "No" some time ago in the 2010/2013 Expert Series. Changed it to "Yes", now working perfect. Carry on.|
Reply from Alex Hedley:
Thanks for sharing.
Robert Whishaw on 10/23/2015: When we initially made the AddProductButton on click event using VBA to perform dlookup's for notes, istaxable etc... it never worked for me so I just added those fields to the combo box & it worked great. |
Now that we're adding more data to be looked up I'd really like to get to the bottom of why the VBA dlookup's aren't working.
Here's a list of all the combinations I've tried:
UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID=" & ItemCombo)
UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID=" & ItemCombo.Column(0))
UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID" = ItemCombo)
UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID=" & [Forms]![OrderF]![OrderDetailF].Form![ItemCombo])
UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID" = [Forms]![OrderF]![OrderDetailF].Form![ItemCombo])
The field formats I'm looking up are all currency or number, no text fields involved.
I'm looking up UnitCost in the ItemXVendorT since I have a many to many relationship with products, vendors & their prices.
I've scanned forums for many hours without success.
Are there any common issues that I may be overlooking?
Any help would be much appreciated :)
Reply from Alex Hedley:
If you Debug.Print your ItemCombo what value are you getting?
Is your ItemID a Number Field
If it is text you would need "ItemID= ' " & ItemCombo & " ' " (i.e. wrapped in quotes)
Does your ItemXVendorT have values that match your ItemID
With it being a Junction Table are you likely to only have 1 ItemID
Robert W on 10/31/2015: Thanks Alex!|
That was it, the ItemID fields were text so I needed those extra quotes. Thanks a million!
Reply from Alex Hedley:
No problem, glad it's working.
Dennis Owens on 9/19/2016: Access 311 #5 Dynamic SQL. I set up the select case and I have 8 cases. 7 out of 8 work correctly, however the 8th one will not add the Where. I even moved one of the other case that was working to #8 and it reacted the same way, it would not add the Where. What might I be doing wrong?|
Reply from Alex Hedley:
Can you share the code in a gist or pastebin and share the link, or post it here?
You may want to read these articles from the 599CD News: