Access Q&A: Dates, DCount - Computer Learning Zone

Access Q&A: Dates, DCount
By Richard Rost

18 years ago

Here are some Access questions I've answered recently:

hello richard, i have question regarding how to set up a validation for the date field, where the date can be just within the current month. can u pl help? thanks, sapna

Make your ValidationRule property for this field:

Month([MyDateField])=Month(Date())

Of course, this will only work while the data is being entered.

Hi Richard. I am trying to query some data for a period from today's date back to 30 June. I will always want to query from today's date (whatever it may be). I tried "Between 30/06/2007 And Now()" as a criteria, but it doesn't appear to do the trick. Can you suggest a solution?

I tried it, and it worked fine for me, but make sure you have the "#" symbols around your dates. Access cannot figure out what you mean otherwise - it will try to treat it as a division problem. I'm in the US, so my date format would me the criteria:

Between #6/30/2007# and Date()+1

The reason I use Date()+1 is because I want to INCLUDE today's data. Remember, dates are treated as of midnight, so by making it tomorrow at midnight, I get all of today's values. Sure, you can use Now() if you want it accurate to the second.

Read more of my Access tips at www.599cd.com/tips

Hello!
I have an application with two tables (simplified version)

table person
column id
column name

table shift
column id
column person_id

There is a one-to-many relationship between the shift table and the person table. So many people can work the same shift. However, i need to limit the number of people on a shift to some number, say 5. How can I do this? I assume I'd need to use some code in the "Before Insert" on the form where I'm adding the records, but I have no clue how to do it.
Thanks for taking the time to read this.

I would use the DCOUNT function to see how many people are already in that shift.

NumWorkers = DCOUNT("*","ShiftTableName","ShiftID=" & ShiftID)

In english, this says, "create a variable called NumWorkers. Set it equal to the count of ALL records (*) in the ShiftTableName table, where the ShiftID equals whatever the current ShiftID on my form is."

I would make a form showing the workers in each shift, and put an unbound combo box with a list of your workers in the FOOTER of the form along with a button to add another worker. Have this code run in the button BEFORE the worker is inserted.

I cover this technique in my advanced Access classes starting with Access 301 (www.599cd.com?goac301). I teach the DLOOKUP function in Access 302 which is almost exactly like DCOUNT.

Read more of my Access tips at www.599cd.com/tips

Comments for Access Q&A: Dates, DCount

Age	Subject	From
12 years	Notifications	Alexander H
12 years	Notifications	Jacqueline
13 years	Calculating Dates based on Day of the Month	Bernadette Torres

Subscribe to Access Q&A: Dates, DCount
Get notifications when this page is updated