Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

News      User Comments     History     Notify Me

6/30/2013 10:55:12 PM
Microsoft Access Expert 8
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
Microsoft Access Expert Level 8 is 1 hour, 55 minutes long and focuses beginning to build our Order Entry System. We'll start out by learning how to create Calculated Query Fields. We'll calculate an extended price and sales tax for our order items. We'll learn how to properly Round values to avoid fractional penny errors. We'll build an Order Form and Order Details Form (for line items), and lots more. Topics include:

- Order Entry System
- Order Form and Details Subform
- Calculated Query Fields
- Figuring Sales Tax if Taxable
- IIF Function (If/Then/Else)
- Proper Rounding of Values
- Bankers Rounding
- Nesting Functions
- Final Product and Tax Totals

Click here for more information on Access Expert Level 8, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 7.

Permanent Link
Course Link: Microsoft Access Expert 8
Keywords: access expert 8 order entry calculated query fields iif function rounding sales tax
Page Tag: whatsnew
Post Reply

Junction Table Comment from Mike Holmes @ 5/21/2016
This is probably a basic questions on junction boxes but I need to ask anyway.  I have created a junction box with Owners and Sites.  I have formed relationships in the junction T with the Owner IDs and Site IDs.  When I do a Query to get information from the OwnerT and SiteT, I bring the * in for the Owner T and fields from the SiteT.  However, when I run the query, there is no information about the Owners or Sites.  

My question is, do I have to physically input information again from the OwnerT and SiteT so that the OwnerSiteQ will show the information?


Reply from Alex Hedley:

You have OwnerT info and SiteT info
In your JunctionT you will need OwnerID and SiteID so 1 | 1, 1 | 2 etc then in your Query join OwnerID from OwnerT on OwnerID in JunctionT and the same for Site
Show Just This Thread        Post Reply
Cartesian Product Comment from Virginia Mergl @ 5/14/2016
I posted a question yesterday but I figure it out myself. Because I forgot a relationship between PetID and PriceID, the query was showing one pet with all price codes (42). Now that I figure it out it is working properly. Sorry about that, thank you
Show Just This Thread        Post Reply
Cartesian Product Comment from Virginia Mergl @ 5/13/2016
Hi, I am having a problem with a query to make up my invoice prices, I have only one record on my tables and when I run the query it shows 42 times the same record. I can not figure out what I have done wrong. Thank you.


Reply from Alex Hedley:

It's probably a cartesian product.
How is your JOIN set up.
Show Just This Thread        Post Reply
Storyboard Comment from Eric Michalek @ 3/19/2016
Storyboard.
Up to Expert 7, it seemed good enough to understand each step individually.  The fundamentals now understood, I m having a rough time keeping up with the concepts of what the lessons are trying to accomplish.
The classes have been excellent in showing how things are done.  But for the expert level classes, I need more focus/detail on what each video is trying to accomplish.  To explain, I watch the entire lesson to understand what the objective was.  And then, I watch again to understand the process needed to accomplish that objective.  Finally, I m ready to watch the lesson to see how everything works together.
In the end, I m re-watching each lesson several times before I m ready for the next.  I shouldn t complain.  After all, I really like your lessons.  They are really informative and fun to watch.  Much better than listening to a college professor drone on.  I just wish I didn t have to re-watch so many times for the full benefit of your classes.
Some of my college professors had solutions for this.  They made use of  storyboards , a pictorial representation (not an outline) but a picture of what is to be accomplished.  Then the picture is annotated/marked up to show what is needed to make it work.
This would be very helpful if incorporated into your video, even better, the handbook for each lesson.  I would certainly buy all those handbooks.  Then I could use it as a tool before watching, and as a reference while watching the first time.



Reply from Alex Hedley:

Do you have an example you could share i.e. an image?
Take a course you've done and produce the storyboard, upload it to one drive or something similar and share the link.
I write most of the handbooks and happily take any suggestions for improvements.
Show Just This Thread        Post Reply
SUM ExtPrice Error Comment from Ryan Weemhoff @ 2/9/2016
Following up on my initial follow up post. I realize I was jumping ahead to access 12.1 with an OrderListF question. I now know the correct way to do it using a query with an aggregate sum function. However, I'm simply experimenting on my own as I work through the lessons. I thought setting the source of an OrderListF to the OrderT and then setting the control sources for the CustomerName and Total to the OrderF would work...but it didn't. Not sure why though, relative to all the internal logic of access/database design. If there is an easy explaining please provide. Otherwise feel free to dismiss question and forego publishing these comments in the forum. Thanks.


Reply from Alex Hedley:

You can only set Controls to be bound to a value on the underlying record source
Show Just This Thread        Post Reply
SUM ExtPrice Error Comment from Ryan Weemhoff @ 2/5/2016
Thanks for your reply. I figured it out. I was putting the sum control on the order form instead of the suborder form. However, I am trying to create an OrderListF (record source: OrderT; displaying orderID, CustomerID, OrderDate, and Total), but I cannot get the total to work. My control source is OrderDetailF.Form!Total but my return is "#Name?"


Reply from Alex Hedley:

Do all Total values contain a number?
Show Just This Thread        Post Reply
SUM ExtPrice Error Comment from Ryan Weemhoff @ 1/21/2016
When I use =SUM(ExtPrice) as suggested in Expert 8.6 (~18:45 min) my form displays "#Error". Please comment.


Reply from Alex Hedley:

Do all ExtPrice have a value?
Show Just This Thread        Post Reply
Updatable Recordsets Comment from John Newton @ 10/10/2015
Hi, In Lesson 1 of 2013 Expert 8; Richards mentions that sometimes fields are not update-able when Forms made from Record Sources from Queries with Many-to-Many relationships.  Why is that and is there a work around?
I guess I'm having trouble with Many-to-Many in general, but specifically adding records.  
Any help or guidance would be greatly appreciated.
FYI I have viewed all Expert Courses 1-30

Thanks
John



Reply from Alex Hedley:

Do you need all the fields in your Query?
You could just remove the join that is causing the record set not to update and work with that instead

See an explanation here.
Show Just This Thread        Post Reply
Product database Comment from Brian Merrick @ 8/18/2015
I am making a product database for my company Vinny's Workshop.  It consist of Product name, type of wood and hardware table.  I created the product Table, Wood table and a Hardware table.  I already made the hardware subform.  I am stuck on the wood table and the product table.  Instead of making three separate table can i put the wood table into the product table?


Reply from Alex Hedley:

Are you wanting to store 3 lists? 1 containing product, 1 wood and 1 hardware?
Just repeat the process you did making the first form with the other two.
Show Just This Thread        Post Reply
Total Customer Payments Comment from James Childers @ 8/11/2015
I am trying to add up all the payments from a customer and then deduct them from the total loan amount the customer owes to show a current balance. I have created a query that adds up the payments but when I try to calculate this it only will deduct one payment from the total loan amount.  Can you tell me how to fix this please?
Thank you,
Jimmy


Reply from Alex Hedley:

What do your Queries look like?
Show Just This Thread        Post Reply
Main Menu Comment from Nour Raslan @ 7/5/2015
Hi Richard
How Can I start the main menu from desk top "out of Access"


Reply from Alex Hedley:

Can you elaborate?
Show Just This Thread        Post Reply
SubForm Comment from Spiros Poulis @ 6/9/2015
Dear Sir, , why you does not include in the subform the foreign key OrderID, in order to connect the parentID and the ChildID, when the 2 forms are connected? Which field links the two forms?


Reply from Alex Hedley:

The OrderID exists in the RecordSource of the Form as it is in the Query, it doesn't always have to be on the Form but it must be in the RecordSource.
Show Just This Thread        Post Reply
Trouble with IIF Comment from Nour Raslan @ 4/4/2015
Dear Alex
i tried this:

SalesTax:IIf(istaxable,[ExtPrice]*0.08,0)
i copy and paste the your statement still givs me the error and pointin on the ","before the opening "[" I tried that more than 4 times the same error
thank you
Nour


Reply from Alex Hedley:

Have you seen this Tip?

=IIF(Condition, Value If True, Value If False)
=IIF(State="NY", "Yes", "No")

Can you wrap the true condition in ()
Are the Fields named the same in your Table/Query?
Show Just This Thread        Post Reply
Trouble with IIF Comment from Nour Raslan @ 3/31/2015
Hi Richard you are doing great in the every lesson. I have Problem with IIF i entered same as you did i have this error .
SalesTax:IIf(istaxable,ExtPrice]*0.08,0)
i get this message :
"YOU ENTERED Invlid character or Comma,
Or You entered without Surrounding with Qutations Mark".
please corect me about this.


Reply from Alex Hedley:

Looks like you missed an opening square bracket "["

SalesTax:IIf(istaxable,ExtPrice]*0.08,0)
=>
SalesTax:IIf(istaxable,[ExtPrice]*0.08,0)
Show Just This Thread        Post Reply
Required Record Comment from Maged Al Shaer @ 2/22/2015
I would like to know how to make record is required by the form not by the table and if I didn't fill in the required field it gives me another color with message



Reply from Alex Hedley:

You could use the following Tip to Check a Form for Blank Controls.
Show Just This Thread        Post Reply
Sub Form Items Comment from Nicholas S @ 12/27/2014
I cannot get the subform to only show the items for that particular order.  It shows all of the details on all of the orders, what is going wrong?

thanks


Reply from Alex Hedley:

Nicholas if you click on the subform in Design View, in the Property Sheet there is link Master/Child Fields.
Check these are filled in using your JOIN Field
Show Just This Thread        Post Reply
Open Form and Find Specific Data Comment from Patrick H @ 12/19/2014
Hi Alex,
Yes I've done a little research online and found that this seems to be a 2007 issue, so I am going to upgrade to office 365. I've tried it a number of different ways and it's very consistently not working.
Pat


Reply from Alex Hedley:

Unfortunately 2007 was extremely buggy, I've kept away from it as much as I can.
Hope the upgrade helps
Show Just This Thread        Post Reply
Open Form and Find Specific Data Comment from Patrick Hoffmann @ 12/16/2014
Hi Richard,
In Access Expert 8 Lesson 1 at around 4:25 I ran into the same issue that Michael posted about on 11/27/2014, when trying to do the Command Button to open the ProductF. I chose the "Open the form and find specific data to display", but did not have any of the fields display for the ProductSubf and had all the fields display for the ProductF. I did have the ProductSubF open by itself at the time.
Thanks for any advice you can give me

Pat


Reply from Alex Hedley:

Hi Patrick,
Which version of Access are you using.
People have had this issue when using 2007, if that's the case I'd advise upgrading if you can.
Could you try with the SubF not open and see if that helps
Show Just This Thread        Post Reply
work around solution Comment from Michael @ 11/27/2014
Richard, as a work-around solution for the issue with the command button wizard problem that I ran into, I went into the Build Event property and changed the code.

For anyone that runs into this same issue, here's what I did:

I created a command button on the ProductSubF to open all records. This at least allowed me to get a working command button onto ProductSubF. Remember, I couldn't open specific records because the ProductSubF fields were not appearing in the left pane in the command button wizard.

I then accessed the code behind the command button throught the BuildEvent property.

I compared this code to the code for a working command button that would open a specific record on a form from a command button on a subform.

I deleted all of the code for the command button on the ProductSubF and brought over the working code for the other forms.

I made name and reference changes where needed. Here's the code:

Private Sub OpenSelectedProductButton_Click()
On Error GoTo Err_OpenSelectedProductButton_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "ProductF"
    stLinkCriteria = "[ProductID]=" & Me![ProductID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenSelectedProductButton_Click:
    Exit Sub

Err_OpenSelectedProductButton_Click:
    MsgBox Err.Description
    Resume Exit_OpenSelectedProductButton_Click

End Sub

I would still be interested to know why the fields aren't/weren't visible in the left pane of the command button wizard.

M.



Reply from Richard Rost:

This may work, but you shouldn't have had to do it. This is way beyond Expert 8 material, and I wouldn't have shown it in class if this was required. Something else went wrong while you were creating your button.

Show Just This Thread        Post Reply
command button to the ProductSubF Comment from Michael @ 11/27/2014
In Access Expert 8 Lesson 1 at 4:25, you add a command button to the ProductSubF to open the ProductF. When I attempt to replicate this using the command button wizard, Access does not display the fields for the ProductSubF.It does however, display all fields for the ProductF. As a result, I can't link the ProductSubF to the ProductF using the wizard. Interestingly, the record source is the same for both forms - Product T. Also interesting, when I tried going the opposite way - putting the button on the ProductF to open the related record on the ProductSubF, Access displayed all fields for both forms and allowed me to make the link. Any idea why Access would "see" all fields in both tables in one instance, but not in both? I ran into this same problem when trying to link the ProductVendorSubF to the VendorF. I was able to make the connection between the VendorProductSubF and the ProductF. Thanks in advance for any insights you can provide.


Reply from Richard Rost:

Were you working on ProductSubF while you still had ProductF open in design view? It's more reliable to CLOSE ProductF and then work on ProductSubF by itself. I'd have to recreate my steps from the video to tell you exactly what happened, but that's how I generally recommend you do it if you have problems. Could have just been a glitch too.
Show Just This Thread        Post Reply
Enforce Referential Integrity Comment from Clay F @ 11/25/2014
Problem Solved...I had a dirty record in the OrderDetailF from class and the dirty record didn't allow the Enforce RI.  THANKS, carry on!
Show Just This Thread        Post Reply
Enforce Referential Integrity Comment from Clay F @ 11/25/2014
Hello, On Expert8 Lesson5, at the 12:59 mark, I was unable to enforce referential Integrity with the OrderID from OrderT and OrderDetailT, the error message says "Access can't create this relaionship and enforce referential integrity", then Data in the table DetailT violates R.I. rules.  Please help, I tried adjusting the Default value of the OrderID but to no avail.  Thanks!


Reply from Alex Hedley:

Check that all the Records contain data in your JOIN Fields.
Show Just This Thread        Post Reply
IIF comma comma comma Comment from Adi @ 9/12/2014
Hello Richard,
this is how I learn about IIF from your Access Expert 8. I'd like to share with you and the class.

We use decimal comma in general arithmetic. So the "IsTaxable" value 8% is written as = 0,08 instead of 0.08

The problem is, if I write IIF([IsTaxable],[ExtPrice]*0,08,0) then access will warn me for syntax error.
So I replaced 0,08 with 8/100.

IIF([IsTaxable],[ExtPrice]*8/100,0)
well it works partially for the TRUE-part. The FALSE-part show NULL (blank) instead of 0 (zero).

When I recheck the function, somehow it turns into
IIF([IsTaxable],[ExtPrice]*8/100)
See? the False-Part simply disappears. {IIF(Condition,True,False)}

I then realize, that Access assumes the last {,0} not as the {False-Part}, but as a part of {100,0} = 100.

So i modified the IIF function into
IIF([IsTaxable],8/100*[ExtPrice],0)
Now it works perfectly as expected, although I'm not sure if it is the ideal solution for the case.
Another solution that works is, when I put 0,08 inside a bracket IIF([IsTaxable],[ExtPrice]*(0,08),0)

What will be the more elegant solution for this case? Do you have any tips about how MS Access handles comma and point as decimal marks? I'm afraid, my database will behaves differently on the other half of the world if there is no agreement about this simple issue :D


Reply from Richard Rost:

I really know very little about how other currencies are handled in Access. My experience is limited to the US and Canada. If putting the currency value inside of parentheses works, I'd just stick with that.

IIF([IsTaxable],[ExtPrice]*(0,08),0)

Seems like a reasonable solution to me.
Show Just This Thread        Post Reply
Comment from Brian Farley @ 9/9/2014
Something worth mentioning, probably in reference to the previous questions.

If you have chosen the default value in the OrderID field in the OrderDetailT to NULL or empty, like I did, it will allow a record without a OrderID, so leave it default to 0

Show Just This Thread        Post Reply
IIF function income expense Comment from Pru Hewett @ 6/27/2014
Hi Richard.  I'm trying to create an IIF function where, if the data in my column headed IncomeExpense is Expense, the amount in my Amount column is changed to a negative figure.  Following your Expert Level 8 lesson 4, I have typed in: IIF([IncomeExpense]=[Expense],[Amount]*-1). When I run it, a popup box asks for the parameter value of IncomeExpense.  Where am I going wrong please?


Reply from Richard Rost:

Well, is IncomeExpense a TEXT field that says "Expense"? If so, you'd need:

IIF(IncomeExpense="Expense",Amount*-1,Amount)

If you put something like that in [brackets] then you're telling Access it's a FIELD NAME. You don't need the brackets if your field names don't have spaces in them (which is what I teach in the very first Beginner lessons). However, if you want the VALUE of the data in that field, you CAN'T use brackets.

I'd recommend a Yes/No field. Make YES an income and NO an expense. Then you could just say:

IIF(IncomeExpense,Amount,Amount*-1)

The field will evaluate to TRUE or FALSE based on it's value and you'll get either the Amount or it's negative.

Show Just This Thread        Post Reply
break up FirstName LastName Comment from Alex Hedley @ 2/15/2014
If you pull your data into Access you can do it there.

Tip
http://www.599cd.com/tips/access/131007-update-query-separate-first-name/

Full Lesson
http://www.599cd.com/site/courselist/access2013/expert/x14/
Show Just This Thread        Post Reply
break up FirstName LastName Comment from Nicko @ 1/28/2014
Richard
Great Lessons. Have not started building database just watching videos
Have a lot of contact data info in spreadsheets. A problem I have is  contact name (Full Name) is one cell is their a function in excel I can use to break this down into two cells Frist Name Last Name
Nicko
Show Just This Thread        Post Reply
Comment from John Miller @ 12/11/2013
Mr. Rost, I had a problem with "Referential Integrity" not working. Expert 8 Lesson 5 TIME INDEX 11:30. I reviewed all of the previous lessons from lesson 2. What I finally came up with was that the default values in any number field, i.e. foreign Key Fields, except the Main Key field have to have a default value set to 0 (zero). Not sure if you cover this or not in any previous lessons or not. If you did then I missed it and apologize for bringing it up.
Show Just This Thread        Post Reply
Enforce Referential Integrity Comment from John Miller @ 12/11/2013
Having a problem with the "Enforce Referential Integrity". I have followed the video (starting at the 11:30 mark) to the end. I have done this several times and I keep coming up with the same result, it allows me to add line items in the "OrderDetailF" with out giving a warning that I can not do this. It will also not give a warning directly in the "OrderDetailT". I have even looked at the HandBook for this lesson and it still comes up the same. What is wrong? one other piece of info I am running Access 2010 not 2013.
Show Just This Thread        Post Reply
Comment from Annette Richard @ 12/11/2013
Following through Video 5 using Access 2010.  I applied the relationships described from minute 11 to min 13.  I went back to the order detail table and am still able to pick a product without an orderID.  In the OrderDetail Table, there is no zero.  Any ideas as to why I can still do this?
Show Just This Thread        Post Reply
Microsoft Access Expert 8 Comment from Bonnie S @ 10/31/2013
Richard, near the end of lesson 5, after you have enforced referential integrity for Customer ID in customerT and OrderT, and have enforced referential integrity and set cascade delete for OrderT & OrderDetailT (on OrderID), at timeslot 14:00,  when you try to add another record you get an error message. I have worked this through more than 2 times, and then started fresh again from the end of Lesson 4, but I do not get that error message. Rather, it allows me to create the new record. I can't figure out what I have done wrong, since I have marked and saved the referential integrity as you showed us. What other setting could be allowing this?  Thanks for your help. Bonnie, as always, appreciating such good teachings.
Show Just This Thread        Post Reply
calculated field Comment from John Borrelli @ 10/7/2013
In lessons 7 & 8 you talked about calculated fields in Queries and reports and they are working well. thank you. However, I need to create a report that only shows the totals. My boss wants a summary of sales showing only the totals ... My reports all show the sales details then sum them. How do i get only the totals into a report?
Show Just This Thread        Post Reply
Comment from John Borrelli @ 10/6/2013
thank you for taking the time in Expert 8/5 for going back to the relationships and showing them. Please do that more often. All the issues i am having in my work database are from pp table design and incorrect relationships.
Show Just This Thread        Post Reply
calculated fields in the form Comment from Candice Garman @ 10/3/2013
When you add the calculated fields in the form - SUMExtPrice, SUMSalesTax, SUMExtPrice+SUMSalesTax does that value save in a table or query or not at all.  Is there value in doing these calculations in a Query instead of the Form?


Reply from Richard Rost:

Those values are not saved anywhere, just displayed for you. WHERE you calculate them does matter sometimes. If you have a HUGE dataset you may find performance is a little better if you calculate them in a query. It will load a little slower initially, but then the results will scroll faster. In a form, each page view is calculated as you scroll. Loads faster, but slower on the scroll.
Show Just This Thread        Post Reply
Comment from John Borrelli @ 10/3/2013
With each lesson you discuss reading the Forums associated with that lesson. I of course only read the exerpts displayed with each lesson. Today I followed your instructions (after 16 lessons) and read the entire forum, which lead me not only to many issues i am encountering but also to a slew of free tips techniques and mini lessons....THANK YOU. ok next time I'll follow instructions(maybe).
Show Just This Thread        Post Reply
serial number Comment from John Borrelli @ 10/3/2013
In your examples in Lesson 14, branched from this lesson 8 you used two seperate tables. I have one table with, along with other part related information, has Part number and Serial number. I want my form to display the selected part number and serial number. My combo box, with Part Number and Serial Number works fine for the Part number.Regardless of what i select in my combo box my Text box for serial number only displays the first serial number associated with that part. Can I get it to display the serial number selected with the part number.
Show Just This Thread        Post Reply
Continuous Form instead of Combo Box Comment from Maurice C @ 9/18/2013
Richard,
Say I've got a very large list of products and using a combo box for selection is becoming too awkward to use (for whatever reason), could I create a button next to the ProductID field that opens up a continuous form showing all my products and I can select the product from there. Do you foresee any problems with this method?


Reply from Richard Rost:

Sure. I have that for my course list in my customer database since my list of courses is so long. You may also want to consider putting your products into categories and using cascading combo boxes.
Show Just This Thread        Post Reply
calculate the following 3 fields Comment from Jim Pigeon @ 9/16/2013
Richard, I am trying to calculate the following 3 Query fields --

BaseRate/lb: Round(IIf(([FlockSalesT]![baserate]+[npvdiff])>[FlockSalesT]![bottomrate],([FlockSalesT]![baserate]+[npvdiff]),([FlockSalesT]![bottomrate]+[FlockSalesT]![BaseRateAdj])),4)    Fixed Format   Result = 0.0524


BasePay: [baserate/lb]*[goodlbs]   Currency Format  Result = $62,011.84



BasePayFt: ([basepay]\160000)  Currency Format  Result = $0.00

The last of the 3 BasePayFt is calculating the pay per square foot which is a fixed 160,000 sqft.  The result should be $0.39.  I have changed number formatting to Currency on all 3 fields and the result continues to be returned as $0.00.  Any suggestions?
Show Just This Thread        Post Reply
Error on Totals Comment from Jim P @ 9/14/2013
I figured it out... I was trying to add a sum of a calculated form control to the form footer.  The problem corrected itself when I moved the calculation to the query.  Funny thing was, it also goofed up all the other form footer totals I had built.  Thanks for the reply. I have learned so much from your courses, wish I had found these about 8 years ago.
Show Just This Thread        Post Reply
Repetition Comment from Elaine @ 9/13/2013
Richard, tell your son that our heads are not as young as his. I for one love the repetition, the junction table finally clicked, and I have been struggling with understanding it for a while. Even when I did the 2003 courses. Thanks a little repetition never hurt anyone.


Reply from Richard Rost:

Will do. :)
Show Just This Thread        Post Reply
Error on Totals Comment from Jim P @ 9/6/2013
I am having trouble adding totals in the form footer. I have tried multiple times and keep getting #Error in the text box.  What am I missing? I have checked text box size, names and formatting.


Reply from Richard Rost:

Jim, without seeing your formula, I can't tell you what's wrong. What do you have in the Control Source, EXACTLY?
Show Just This Thread        Post Reply
Affect Effect Comment from Bruce Reynolds @ 8/5/2013
Changing price will affect current orders! Affect, not effect. Affect is a verb, and effect is a noun.


Reply from Richard Rost:

You are correct, sir. Did I type it wrong somewhere? Sometimes I get going so fast and I forget to proof read. Other times I use voice dictation software and it messes up (sure, blame the voice rec!)
Show Just This Thread        Post Reply
Access Functions Comment from Bruce Reynolds @ 8/5/2013
You should set up an Access Seminar that does nothing but go over all of the functions available in Access (or at least the 100 most used functions). This would be cool.


Reply from Richard Rost:

I plan on doing this in the Expert series videos coming up, much like I did for Excel in it's Expert series.
Show Just This Thread        Post Reply
Bankers Rounding in Excel Comment from Bruce Reynolds @ 8/5/2013
What is the equivalent of bankers rounding in Excel?



Reply from Richard Rost:

Excel, unfortunately, doesn't support Bankers Rounding. See this Microsoft article for details.
Show Just This Thread        Post Reply
Access PRODUCT function Comment from Bruce Reynolds @ 7/11/2013
Does Access have a product function like Excel? For example, could you enter the formula ExtPrice: Product(Price, Quantity) in this example?


Reply from Richard Rost:

Not that I'm aware of. You would have to write a custom function to do it.
Show Just This Thread        Post Reply
Single vs Double Comment from Bruce Reynolds @ 7/2/2013
Could you use the Single data type in place of the Double data type for the Quantity field? If so, what's the difference?


Reply from Richard Rost:

Yes, you could. Single offers less precision and a smaller range of values but saves a some space. I always say if you're not sure, stick with Double. Unless space (size of the database) is a concert, go with Double. If you start running into the 2 GB file size limit, then you may want to consider scaling back to Single. I don't remember the exact sizes off the top of my head (I covered them in Access Beginner 3, Lesson 1) but you can Google them (or watch that lesson).
Show Just This Thread        Post Reply
PEMDAS v BODMAS Comment from Alex Hedley @ 7/1/2013
PEMDAS - I was taught this as BODMAS.

B Brackets first
O Orders (ie Powers and Square Roots, etc.)
DM Division and Multiplication (left-to-right)
AS Addition and Subtraction (left-to-right)



Reply from Richard Rost:

Whatever works for ya. :)
Show Just This Thread        Post Reply
units of measure Comment from James Gray @ 7/1/2013
Richard,

Perhaps in one of your advanced classes you can deal with the topic of units of measure as it relates to how you stock, order and price inventory.  I am dealing with those issues now in my ambulance database.  We use items typically in each units but must purchase in case or odd lot quantities.  As you know, that affects how we purchase, receive and stock our supplies.  Very interesting and complex issue but fun to figure out how I make that an automated process.  I would appreceiate any advice you might have on that topic.  Thanks for your help as always.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 3+7:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP