Date Not Working
By Richard Rost
15 months ago
Fix DSum Date #Name? Errors in Calculated Fields
In this Microsoft Access tutorial, I will show you how to fix the common #Name? error when using the DSum function with date criteria in calculated fields. We will cover common pitfalls like proper syntax, date formatting, and concatenation to ensure your calculations run smoothly.
Riley from Lake Forest, Illinois (a Platinum Member) asks: I'm trying to make a calculated field on my customer form to sum up all of their orders in the past year using DSum but I keep getting a #Name? error. What am I doing wrong?
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, DSum date criteria error, DSum Name? error, Access calculated fields date, solving DSum errors in Access, conditional DSum error Microsoft Access, Microsoft Access date criteria error, common Access DSum mistakes, Access DSum troubleshooting, date math in Access functions
Transcript
Today we got that dreaded pound name error again. We're going to talk about what to do when your date criteria doesn't work in your Microsoft Access Calculated Fields. Today's question comes from Riley in Lake Forest, Illinois, one of my platinum members. Riley says, "I'm trying to make a calculated field on my customer form to sum up all of their orders in the past year using D-Sum. But I keep getting a pound name error. What am I doing wrong?"
Well, this is something that I see a lot, and a lot of people make this mistake, Riley. So don't feel bad about it. It's an easy mistake to make. In fact, I make it too from time to time. Pound name basically means that Access has no idea what you're talking about. If you're trying to use a function like date, Access is like, "I don't know. I don't know."
So let's see exactly what you got going on here. And by the way, Riley, as soon as you sent me some screenshots of your database, I knew exactly within three seconds. It's a common mistake. Let's walk through it.
Before we do, the whole prerequisite is this is an expert-level class. That means it's a little more advanced than just the basics, but it's not quite developer-level. We don't have to do any programming. This is all just regular functions and some of this stuff.
Let me show you exactly what you should watch before watching this video. Of course, we're going to be using D-Sum, and that lets you sum up the values in another table or query. So if you're on the customer form, you could sum up their orders, for example. So if you don't know what D-Sum or D-Lookup is, go watch this first.
Also, make sure you understand concatenation. One of the things that throws people off a lot when putting together multiple criteria in a D-Sum function or D-Lookup or any of those D-functions is messing up the concatenation. So go watch this video too, and go watch my little video on date math. There are lots of other date, first tons of date functions and stuff. We're going to use simple date math today. So go watch these videos first. They're free. They're on my website. They're on my YouTube channel. Go watch them. Come on back.
Okay, here I am in my TechHelp free template. This is a free database you can grab from my website if you want to. It's got customers, and customers can have orders. And as you can see, I got one order in here for $42.00, and another one that's not paid. And all of these orders are summed up in this order summary queue. It's basically got each order and the date it was placed on, and the order total is over here. If you want to learn how I built all of this, go watch my invoicing video. I'll put a link to that down below as well.
Now let's say on the customer form, you want to put a field down here showing their order total of all their paid orders in the last year. Okay, so we're going to use D-sum for that. I've already got another currency value here. Let's copy this one, this credit limit field, we'll call this order total.
All right, let's open up the properties. Here's the properties for this guy. Let's start off simple. Let's change the name to just order total. The control source, delete credit limit, and we're going to put a calculated value in there. I'm going to shift F2 so you can see what I'm typing. It's a little easier.
Let's start off simple without any criteria except the customer ID. Let's say I want to just see all of this customer's orders, period. Okay. What we're going to do is, we're going to do the order total. And if you're not sure what the field names and stuff are, go look in the query. Where we get it from, that's that order summary queue. That does all the math for us. And the criteria is customer ID equals and customer ID. That little concatenation there says, take customer ID equals, take the customer ID on the current form, slap it in there. So the function gets customer ID equals four. Can you put this inside there? Sometimes you can't, sometimes you can. I always just put it outside there so it gets evaluated first and sent into the function.
All right. Now, let's save it, close it, open it back up again. All right, there we go. 4560, that seems about right. All right, I got 4200 and 360. Now this one's not paid. Usually you don't want to see unpaid orders in an order total, right? That's just calling for a quote. So let's add another criteria. This is great practice for criteria folks.
Back to design view. Let's go back into that guy's control source. There's shift F2. Okay, right here, after that, we're going to add, I want the paid value to be true. And that's "is paid." So we're going to say, more concatenation, space. Don't forget your space in there. You got to have spaces in here. And "is paid equals true." Now that you can put inside there because true is a constant. But make sure you got a space. The spacing is very important, folks. Lots and lots of times people post stuff in my forums or they send me emails, and it's all due to a missing space. Okay, because it's got to be "customer ID equals three space and space is paid equals true." So watch your spacing.
Oh, and someone's beaming in. Hold on. Hold on. Energize. I love that. That's my hourly chime on my clock for those of you who don't know.
Okay, hit okay. All right, let's save it. Close it. Close it. Open it. All right. 4200. That's exactly what I expect. 4200 and that. OK. Now, the next one. Now, the tough one. Now I'm going to add the date criteria. I want to see all the orders in the past year. How many simple date math? Yes, there's date add date. If all those date functions, but in Access, as we know from watching the date, math video, a value of one is one day. So it's close enough. If you just want a rough order total, it's close enough to say "date minus 365." That's today's date minus 365 days. Is it perfect? No, but it's good enough. If all you want is a quick tally of how much they bought in the last year, it's good enough. Close enough for government work.
Okay. Here, and we'll add this. All right. One more criteria right there in front of the quotes space and it's going to be "order date." The name of the field is greater than or equal to? And then outside the quotes, we're going to say ampersand "date minus 365." That's one year ago. So the order date is greater than or equal to one year ago.
All right. Hit OK, save it, close it, close it, open it, and there's your pound name. OK. What does that mean? Access has no idea what you're talking about. Why is that? Well, let's take a peek at it. Back to design view. Let's go take a look at it again. Now, take a look at exactly what Access did to that criteria. Take a moment, pause the video if you have to, see if you can figure it out. Anybody? Bueller? Bueller? Bueller? And if you don't get that, you're too young for my classes.
All right. Look what it did to date. It put "date" inside of square brackets. That's Access trying to be nice. If it doesn't recognize someone like customer ID earlier, it puts it inside square brackets, meaning I'm going to look for the customer ID field. It's looking for a field called date, and that doesn't exist. It shouldn't exist. You shouldn't use date as a field name. It's a reserve word. All right. So this is one of those instances where you have to say "date," open, close, parentheses. That will now use the date function. The date function returns the current date. All right. Now hit OK. Save it. Close it. Close it. Open it. The error's gone. But, hmm. Correct me if I'm wrong. Let's take a look at the order date. OK. Today is currently 2024, September 15. So I should only be seeing orders after September 15 of 2023. So this is too early. This should not be showing up. What's going on here? Technically, I shouldn't be seeing anything because the other orders that paid. So why is it not obeying my criteria?
All right. Go back and take a look at it one more time. I promise this is it. I like to show all the wrong things that people do first. So you better appreciate the right way to do it. This is another mistake that I personally make a lot. Date values in Microsoft Access have to be encapsulated inside of these guys. All right. You need a hashtag there before and after the date. OK. Otherwise, this will get treated as a number. And it won't compare properly to the date field in the table. OK. Now when you do it, save it. Close it. Close it. Open it. There. That's what I was expecting. I should get a null value because there are no orders in that date range. If I change this date, let's make this 2024. I got to refresh the form here. So I'll hit F5. And there you go. Now it's working. OK. If you want to handle that, you can use the NZ function. Whoops. You can handle it. Well, let me put that date back first. Let's put this back to 2023. All right. F5. And now it's blank. If you want to put a zero in there, use the NZ function. That'll convert the whole thing. If it's null, to zero. NZ. And then put this whole thing inside of NZ. What value do you want if it's null? Give me a zero. All right. I got a whole separate video on NZ. I'll put a link to it down below if you want to see it. And now we're good. OK.
So those are the big mistakes that people make when they're trying to do something like this with date criteria. The first thing is they're not using the date function and Access is switching it to a field named date, which you should never have. OK. And you got to make sure you put it inside of octothorpes. Yeah, they got many different names. Octothorpes, hashtag, pound sign, tic-tac-toe board. Any other names you know? Other things? I had to ask about it. OK. Hashtag, we got Octothorpe is the technical or formal name for it. The pound sign in North America, press the pound key. Number sign, obviously, the hash sign in the UK. The sharp, I should know this. I'm a musician. The sharp sign. There are sharps and flats. I haven't read music though since I was in high school. The hex symbol. That's right. You put that in front of the hex numbers and the grid. You didn't put tic-tac-toe board on there. Yeah. Tic-tac-toe was another informal name.
All right. That's it for today. If you like this kind of stuff, I got lots of expert lessons on my website. If you don't necessarily want to learn VBA programming, but you want help with all this function stuff and criteria and concatenation and formulas and things with moving parts and molecular structures - yeah, expert lessons. They're more than the basics but not quite into programming. I got lots of lessons on my website covering all stuff like this.
But that's going to do it for your TechHelp video for today. I hope you learned something. I hope this helps somebody. If this helps you, put a comment down below. Let me know. It makes me feel warm and fuzzy to know that I'm helping people. All right. You'll make my day. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Pound name error in Microsoft Access calculated fields
Date criteria not working in calculated fields
Using D-Sum to sum orders
Avoiding the pound name error
Concatenation in D-functions
Ensuring correct function names in Access
Adding criteria to D-Sum functions
Including paid orders only in sum
Using date math in criteria
Handling dates within D-Sum criteria
Using the date function correctly
Encapsulating date values in hashtags
Troubleshooting date-related errors
Using the NZ function to handle null values
COMMERCIAL:
In today's video, we tackle the dreaded pound name error when your date criteria doesn't work in Microsoft Access Calculated Fields. We'll show you how to correctly use the D-Sum function to sum up orders in the past year on your customer form. You'll learn about common pitfalls like misusing the date function and concatenation errors. We'll demonstrate how to properly format your date criteria with hashtags and handle null values using the NZ function. By the end, you'll have a smooth-running calculated field. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What does the pound name error typically indicate in Microsoft Access?
A. The formula has been entered correctly
B. Access does not recognize the field name or function used
C. The calculated field contains non-numeric data
D. The table being referenced is missing
Q2. In the video, Riley wants to sum up orders using which function?
A. D-Lookup
B. D-Sum
C. D-Max
D. D-Count
Q3. What is the prerequisite level of knowledge required for understanding the tutorial?
A. Beginner
B. Intermediate
C. Expert
D. Developer
Q4. Which of the following concepts is important to understand before using D-Sum or D-Lookup functions?
A. Conditional Formatting
B. Table Relationships
C. Concatenation
D. VBA Programming
Q5. When modifying a calculated control in Access to sum all of a customer's orders, which initial criterion was added to filter the results?
A. Order Date
B. Product ID
C. Customer ID
D. Payment Date
Q6. Which keyword does Access mistakenly consider a field name, causing an error, if not used correctly inside the D-Sum function?
A. Now
B. Current
C. Date
D. Today
Q7. What method was suggested to correctly handle date values in the D-Sum function?
A. Use an absolute date like '1/1/2023'
B. Remove square brackets around date
C. Encapsulate the date value within hashtags
D. Add quotation marks around the date
Q8. How can you handle a situation where the calculated field returns a null value, to display a zero instead in Access?
A. Use the IIf function
B. Use the DateDiff function
C. Use the NZ function
D. Use the Format function
Q9. If the calculated field still returns incorrect results after using the date function and the correct concatenation, what final adjustment should be applied to ensure proper date filtering?
A. Include the exact time with the date
B. Add a leading zero to the date value
C. Enclose the date in square brackets
D. Enclose the date in octothorpes (hashtags)
Q10. Why did placing the function 'Date' within square brackets cause an error in the tutorial?
A. It turned the function into a literal string
B. It referenced a non-existent table
C. It made Access look for a field named 'Date'
D. It changed the date format to text
Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-C; 7-C; 8-C; 9-D; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone addresses the common "#Name?" error you might encounter when your date criteria don't work in Microsoft Access Calculated Fields. Riley from Lake Forest, Illinois, one of our platinum members, reached out with a question about this issue. He said, "I'm trying to make a calculated field on my customer form to sum up all of their orders in the past year using DSum, but I keep getting a '#Name?' error. What am I doing wrong?"
Riley, this is a frequent issue, so don't worry; it's a mistake many people make. Essentially, a "#Name?" error means Access has no idea what you're referring to. When you use a function like "Date," Access might get confused if it's not used correctly.
First, let's make sure you have everything set up properly. If you haven't already, you should familiarize yourself with the DSum function, as it allows you to sum values in another table or query, such as summing customer orders. Additionally, understanding concatenation is crucial when working with multiple criteria in DSum, Dlookup, or other D-functions. If you're unsure about these topics, refer to my previous tutorials on these subjects before proceeding.
We'll use the TechHelp free template, which you can download from my website. It includes customers and orders data. In our example, we'll add a field on the customer form to show the total of all paid orders within the last year. We'll use the DSum function for this task.
First, add a new field on the form and change its name to "OrderTotal." In the properties, set the control source to a calculated value. Start by summing all of the customer's orders without any additional criteria:
"CustomerID = " & [CustomerID].
Next, introduce criteria to only include paid orders. Add the condition:
" IsPaid = True"
Remember to include spaces appropriately. These spaces are critical for the function to work correctly.
Now, let's add the date criteria. You want to see orders from the past year. In Access, a value of 1 represents one day. To get the date one year ago, use:
"OrderDate >= Date() - 365".
Initially, this might result in a "#Name?" error because Access tries to interpret "Date" as a field name, not a function. To correct this, ensure you write "Date()" with parentheses.
Also, date values in Access need to be enclosed in # symbols:
"#" & Date() - 365 & "#".
If you still don't see the expected results, it's likely due to how Access compares date values. Adding the "#" symbols ensures Access treats the value as a date.
Finally, if the result is null and you want to display zero instead, wrap the DSum function in the NZ function:
NZ(DSum(...), 0).
This converts null values to zero, making the output more user-friendly.
These common errors—misusing the Date function and failing to enclose date values in # symbols—are easy to fix once you know what to look for. By ensuring the correct use of functions and proper formatting, you can avoid these mistakes and get accurate results.
For more expert-level lessons and detailed instructions on these topics without diving into programming, visit my website. This tutorial aims to help those ready to move beyond the basics but not yet into developer-level content.
Find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Pound name error in Microsoft Access calculated fields
Date criteria not working in calculated fields
Using D-Sum to sum orders
Avoiding the pound name error
Concatenation in D-functions
Ensuring correct function names in Access
Adding criteria to D-Sum functions
Including paid orders only in sum
Using date math in criteria
Handling dates within D-Sum criteria
Using the date function correctly
Encapsulating date values in hashtags
Troubleshooting date-related errors
Using the NZ function to handle null values
|
