How to Use Functions in MS Access Queries

In this video, we are going to learn how to use functions of different kinds in our MS Access queries. Using functions is a nice way to process very complex calculations by creating a calculated column in your query.
We will cover the usage of both Access “built-in” functions that are available to everyone, and custom functions that you can program yourself using VBA. You’ll find that there is a tight integration between Access and VBA, and this gives an incredible amount of power to query builders on this platform.
Related Videos:
How to Use Union Queries in MS Access
• How to Use Union Queri...
How to Split One Column Into Two in Access Using the Split Function
• How to Split One Colum...
How to Query the Last Row in a Series in Microsoft Access
• How to Query the Last ...
How to Format Dates in MS Access Queries, Forms, and VBA Code
• How to Format Dates in...
How to Use Nz in Microsoft Access to Handle Null and Empty Values
• How to Use Nz in Micro...
How to Use Crosstab Queries in MS Access
• How to Use Functions i...
How to Use Functions in MS Access Queries
You are watching this video now!
Follow us on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
MS Access Project? Contact me today!
Check out some of my earlier videos on MS Access:
“MS Access Option Groups - How to Use Radio Buttons”: • MS Access Option Group...
"How to simulate Lag and Lead Window Functions" : • How to Simulate Lag an...
“How to Format Dates in MS Access Queries, Forms, and VBA Code” : • How to Format Dates in...
"VBA Mod Function with DoEvents: Prevent Not Responding Messages and Provide Feedback on Access Forms ": • VBA Mod Function with ...
"Iif, If Then Else, and Select Case in MS Access" : • Iif, If Then Else, and...
"How to Query the Last Row in a Series in Microsoft Access": • How to Query the Last ...
"How to Make Cascading Combo Boxes in MS Access": • How to Make Cascading ...
"How to Open a RecordSet in Access VBA and Loop Through the Records" • How to Open a RecordSe...

[@imstevemcqueen]

Sean should have many more views of his excellent content...ppl are missing out

[@seanmackenziedataengineering]

Thanks!!

[@miles6875]

Hi Sean, per usual - found a use case for this. Love your channel, thanks!

[@giuseppesantoro4572]

Le tue lezioni sono ben fatte e impirtanti... Seguirò le lezioni anche se non parlo inglese.... Grazie molto.

[@seanmackenziedataengineering]

Thank you for your nice comment! I am happy you enjoy the channel.

[@oparesimon]

Thanks for your nice tutorials.

[@seanmackenziedataengineering]

Glad you like them!

[@724sd]

Thank you so much! This was very helpful!

[@seanmackenziedataengineering]

Glad it was helpful!

[@javedahmed8343]

Very subtle explanation

[@seanmackenziedataengineering]

Thanks!

[@andjelijaradovic]

Awesome video, thanks Sean!

[@seanmackenziedataengineering]

Glad you enjoyed it!

[@Nilkorbi]

I have a table with years. months and three other fields, 'assets' 'loans' and 'deposits'. I want the user to be able to chose through a Form (check boxes) which fields to to seelct for a query output. I have already set the criteria for selecting 'years from and to' and 'months from and to', but cannot figure how to make it possible for the user to seclect assets, loans and deposits (one of them, two of them or all three). Any help would be muhc appreciated

[@seanmackenziedataengineering]

Great question! You can use some VBA for this in order to check which fields you will use and then dynamically set the SQL in a QueryDef: kzbin.info/www/bejne/l4Suip2NhJiNgLM Give it a try!

[@AmpentoDAJHS]

Please lets say you have a data of some numbers like 1,2,4,8,2,3. Can we get a query or a formula that will pick the least two figures and sum them up?

[@seanmackenziedataengineering]

Yes - perhaps just sort them as I show here: kzbin.info/www/bejne/pJyvi3WarJqjras Then pick the first two in the array and add them. After sorting according to this video, you could just go: intSum = arData(0) + arData(1)

[@m.arifganai9371]

Great plz let me know how to use sub routine functions in query or in update query based on criteria

[@seanmackenziedataengineering]

You can use a Sub by converting it to a Function, say with a return value of True or False. Then, you can still plug the function like I show in this video. Or, you can just make a Function with a single line that calls your Sub. Plug the function into your query. Remember that it will execute for every row returned by your query though, so use with caution!

[@m.arifganai9371]

OK great

[@satyabanukil779]

I have simple question. I have a query and there are two columns which are expressions. I want to know can I add those two expressions to another expression field. For example Expression 1 has 20 and Expression 2 is 10. Now I want to build a third expression column which will add expression 1 and expression 2 and expression 3 will show 30. If yes, what is the syntax. Regards.

[@seanmackenziedataengineering]

You can do something like: Field1: Expression1 Field2: Expression2 Field3: (Expression1) + (Expression2)

[@satyabanukil779]

@@seanmackenziedataengineering Thnx a lot Sir. For years together I have been approaching you again and again but has received an affectionate suggestion which has helped me in all my problems. Thnx and regards once more. 2. It is just a curiosity Sir. I have more or less searched most of youtube tutorials on Access but failed to watch to get a tutorial on how to apply a sum in a form level code. It is not easy as it is in Excel. I tried this - (GotFocus event) FieldName = Sum (field1, field2 and so on). Additionally I don't know how to create a sum module. Therefore, if you get time, kindly present a tutorial on it as it would be a great help for beginners like us.

[@oparesimon]

Please, I have a challenge in a students results that I am currently analysing. It is possible to get help from you?

[@seanmackenziedataengineering]

Sure, what is your question?

[@shafameen4535]

Hi Sean, very useful vedio. Can you do a vedio on how to calculate sumifs using custom function in Access please.

[@seanmackenziedataengineering]

Thanks for your feedback! Yes, you can do the equivalent of sumif by using DSum in your query instead of DMax as I used in this example. ex. DSum("Quantity", "SALES_TABLE", "Product = 'Apple'") I will make a note to consider a video for this! Cheers

[@artistryartistry7239]

Hi there, can NZ() be put directly into the field in, say, a continuous field on a form vs. the QBE?

[@seanmackenziedataengineering]

You can set a field's control source to =Nz([MyField], 0) but continuous forms are tricky and it may show the same value on every row. You're better off to put the Nz function in a query and then show that in your continuous form. Then it will display different values on every row of the continuous form.

[@artistryartistry7239]

@@seanmackenziedataengineering Thank you so much Sean. Appreciate your time!

[@rohitgondhali4679]

Hi Sir Can you upload Detailed information video on Dlookup Function in MS Access It will very helpful Thank you

[@seanmackenziedataengineering]

That's a great idea for a video, thanks! I'll do that one soon.

[@lucagiraldes9981]

Hello! Can you tell me what i can do when i have two different tables, with the same column's name? How can i reference just one of these tables? Example: Function test (varID from table1)? Thanks a lot!!

[@seanmackenziedataengineering]

In that case, you will specify the table in your query. Select MyColumn from TableA and Select MyColumn from TableB. Then, apply your function test.

[@interestingamerican3100]

Is there a way for a function to pull values from not only the column it was placed (varID) but also another column related to that record? Basically allows me to use the primary key of the record for nefarious purposes...... (: D) Nevermind. Using [ ] in the function. I payed better attention. Thanks a lot!!!!!

[@seanmackenziedataengineering]

Nice!

[@rincondeolocuilta]

Hello! Thank´s for sharing knowledge. Can you tell me what a “?” do in the immediate window. Is that the way that immediate window recognizes that you are doing a “direct”test of a function?

[@seanmackenziedataengineering]

Yes, that's right. The ? asks for an immediate execution of the function for a return value. If you created a Sub instead of a function, you can execute it just by typing the Sub name without the ? symbol. However, for functions you must use ?.