Access 2007-2016
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  


Microsoft Access Forum
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

This forum is for the discussion of Microsoft Access.

Access is my personal specialty. Have questions? Comments? Want to discuss how to do something? Post it here. Also, I get a LOT of questions sent to me in Email from people around the world. I'll post the interesting ones in here. Feel free to comment on them.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft access forum
Post New Topic

Read Dates by Selby H @ 8/26/2018
Dear Richard, Can you please help. I have one date field and want to show the days between each record in a text box or label when I move from one record to the next. Want to display the number of days from the last record date to the next date in the table. Is that possible. I have been looking at the DateDiff function, but seems like you need a start date and end date. I want to move between one date field and show the days. It might be like the running sum technique, not sure.
Your Help would be greatly appreciated
You might have already covered this and have searched everywhere with my records. Cannot find.
All the Best Selby.

Reply from Alex Hedley:

You could use a combination of DMAX, DMIN, DateDiff
Show Just This Thread        Post Reply
Read Production Schedule Updates by Richard S @ 4/30/2018
Hi folks,

Was wondering when to expect Access Developer-8;  please advise when you get a moment.

Hope all is well & till later,
Rick S.
San Antonio, TX

Reply from Alex Hedley:

Should be released soon.
Show Just This Thread        Post Reply
Read Dates by Selby H @ 3/19/2018
Dear Richard, How can I convert a date from 07/01/2016 to 01/07/2016

Really appreciate your help
All the Best Selby

Reply from Alex Hedley:

Use the Format function.
Show Just This Thread        Post Reply
Read Need Help Working with Dates for a Report by Dorothy A @ 5/22/2017
I've got 2 tables, "Cycles Table" which includes a Start and End date field and a "Holidays Table" which lists all names of holidays in one column and their corresponding dates in the other.  I need two things: (1) to show all holidays that fall between in the date range of each cycle in a report and (2) count the number of days the cycles meet between the start and end date minus those holidays.  I've tried several methods and nothings seems to work. Any advice would be appreciated.

Reply from Alex Hedley:

What have you tried?

You could use a BETWEEN or Date >= x AND Date <= y.
You can use a GROUP BY and COUNT.
Show Just This Thread        Post Reply
Read managing dates yearly by Bonnie S @ 3/17/2017
I need to track addresses which change seasonally.  So every year CustomerA needs their winter address from Nov to Apr, and their summer address from May to October.  How would I handle this?  I have tried various things unsuccessfully.  I would like to at least track it by month, but preferably by a starting date and an ending date for each address -- but it happens every year, so is not based on a specific date.  I have looked at Richard's date functions and don't fine something along this line. Thanks for your help! Bonnie

Reply from Alex Hedley:

You could add a Start/End Date Field to your Address Record and put in the Dates then use a Query to get the correct address based on the chosen/current date.
Show Just This Thread        Post Reply
Read dates returned incorrect by Aaron @ 11/21/2016
Have msaccess form that has 2 date fields and an amount field, the idea is to allow the user to apply a filter to the form to filter results between 2 dates and above a certain amount. However the results are inconsistent, and it tends to list both dates that are in the mm/dd/yyyy and the dd/mm/yyyy format in results. Where I only want dd/mm/yyyy which is what the short date and system dates are set to.

strCriteria = "([Order Date] >= #" & Me.txtOrderDateFrom & "# AND [Order Date] <= #" & Me.txtOrderDateTo & "# And [Order Amount]>= " & (CLng(Me.txtSumOfLinePrice)) & ")"

task = "select * from OrderListQ where (" & strCriteria & ") order by [order date]"

DoCmd.ApplyFilter task

Reply from Alex Hedley:

I've had many problems with dates over the years, DLOOKUPs especially. Try wrapping the date in a Format and setting it to American mm/dd/yyyy format.
Show Just This Thread        Post Reply
Read Query for 2 specific dates by vicki H @ 11/16/2016
I actually meant to sent this in the access forum. Would it work the same?

Reply from Alex Hedley:

It's in the Access Forum now, yes in your Query Editor.
Show Just This Thread        Post Reply
Read Query for 2 specific dates by vicki H @ 11/14/2016
I have a table of entries that contain two specific dates. Some entries have one date or the other, and some have both dates. What criteria do I ask for to get only the entries with both dates?

Reply from Alex Hedley:

IS NOT NULL in both
AND across
OR down
Show Just This Thread        Post Reply
Read Dcount between dates by Brandon Jackson @ 10/30/2016
Thank you, sir!  That was exactly what I needed! I really appreciate the help!
Show Just This Thread        Post Reply
Read Dcount between dates by Brandon Jackson @ 10/29/2016
I have an issue with my function: =DCount("*","ReworkSearchQ","ReworkDate >= DateSerial(Year(Date()),1,1)  AND < DateSerial(Year(Date()),2,1)")
I'm using this as the control source for an unbound text box on a form but it just gives an #ERROR message.  However, if you remove the inequalities and use the BETWEEN keyword, then it works, but this isn't what I need.  I need just the dates for January.  If I put the where condition part of this in the criteria section of a query, it works then.  Can you please tell me what I'm doing wrong?  Thank you very much!

Reply from Alex Hedley:

=DCount("*","ReworkSearchQ","ReworkDate >= DateSerial(Year(Date()),1,1) AND < DateSerial(Year(Date()),2,1)")

I think you're missing what you are comparing on the second check, try adding the Field "ReworkDate" before the "<"

=DCount("*","ReworkSearchQ","ReworkDate >= DateSerial(Year(Date()),1,1) AND ReworkDate < DateSerial(Year(Date()),2,1)")

You might also need to wrap the dates in "#"s.
MyDate >= #10/30/2016# And MyDate < #11/30/2016#
Show Just This Thread        Post Reply
Read View Multiple Dates with multiple Customers by vicki H @ 1/20/2016
​I cannot for the life of me figure out how to normalize this table/form information. I have 10 fields for the TrimDate (the actual trim date entered by user). I need to see the information in this form design (similar to a spreadsheet).  But if I duplicate the Trimdate text box on the form, the information entered also duplicates. I understand why, but don't know how to avoid all these unbound fields. They will use this to input all the trims that are done then I will print out a report of what horse is due for a trim.
hope this makes sense

HorseT HorseID, HorseFarmName, etc...
TrimT  TrimID
​       HorseID   foreign key to HorseT
TrimmerNameT TrimmerNameID            
             TrimmerName foreign key TrimmerNameT

form record source  TrimQ
HorseT ---> TrimT ---> TrimmerNameT

Reply from Alex Hedley:

You create a Form bound to TrimT
Add in all the Fields once,
Then set it to a CONTINUOUS Form
You can then add a new record for each Trim.
Have it open from the HorseF and set the HorseID = to Forms!HorseF!HorseID given your Horse Form called HorseF.
This will auto populate the Trim with the HorseID and then you can add as few/as many Trims as you need.
Show Just This Thread        Post Reply
Read Between Two Dates by rajesh @ 6/1/2015
I have two fields namely fromDate and ToDate.  Both as date value.  Example 23/03/15 and 25/04/15.
Here is my requirement : I have a form with two date fields. Suppose I select from  01/04/2015 to 30/04/2015. Than it should query on two fields of table and give me the total no.  Of days between these fields between chosen dates.

I tried with datediff but it's not coming up.

Reply from Alex Hedley:

See this Tip

Or Access Beginner Level 5.
Show Just This Thread        Post Reply
Read Validation of dates acces 2007 by benjamin ole w @ 4/21/2015

I am doing a database on patient medical records. I have 4 consecutive dates i.e. Date of birth, date of diagnosis, date of remission and follow up date.

I would like to generate a validation rule that says "date of diagnosis is after date of birth" And does not allow you to enter a date that does not comply with this rule?

Thanks in advance!

Reply from Alex Hedley:

Validation rules have recently been covered in Expert 28.
Show Just This Thread        Post Reply
Read Dates in Append Query by Pamela F @ 1/30/2015
The date names are Fsent. Can you show me an example, details?

Reply from Alex Hedley:

I've created a Test db with 2 Tables, each with a DateTime Field called 'MyDateField'

I then created an Append Query with the following SQL:

INSERT INTO Table2 ( MyDateField )
SELECT Table1.MyDateField
FROM Table1;

Try this and see if that works.
Then if it does you could try the same for yours.
Take a copy/backup first.
Show Just This Thread        Post Reply
Read Dates in Append Query by Pamela F @ 1/29/2015
Queries with dates. When I am using my append queries to pass date fields from one table to another table in my database the date field information is blank. For some reason the dates do not show up in the 2nd database. They are all designated as short dates. Hope someone can quickly sort this one out for me.

Reply from Alex Hedley:

What are the names of your Date Fields?
Might need to wrap them in #
Show Just This Thread        Post Reply
Read Using Dates in SQL with the WHERE clause by Bernt E @ 1/11/2015
How do I put a variable for date in a SQL statement with a WHERE clause?
I want to open a listbox where the rowsource is equal to my SQL statement but I can't get it to work
I would like to :SELECT * FROM OrderT WHERE Orderdate >= myDate

OrderDate is the field name in OrderT , and I have:
Dim myDate as Date
myDate = Date() - 30

The SQL statement works without the WHERE clause.
The error is that the listbox remains empty after requerying the listbox

I have tried the following SQL statements:
1."SELECT * FROM OrderT WHERE Orderdate > " & myDate
2."SELECT * FROM OrderT WHERE Orderdate > '" & myDate & "'"
3."SELECT * FROM OrderT WHERE Orderdate > #" & myDate & "#"

Nothing seems to work

Reply from Alex Hedley:

I've just tried:

Dim myDate As Date
myDate = Date - 30
Dim mySQL
mySQL = "SELECT * FROM OrderT WHERE Orderdate > " & myDate

Which worked.
Are you setting your ROWSOURCE in code too?
Show Just This Thread        Post Reply
Read Conditional Formating for Dates and Exporting by Peter K @ 11/5/2014
I'm having trouble coming up with a solution for my Date data type fields. The end-user is wanting to see only month/day hour:min AMPM format within the form that he runs, but also wants to export this data to Excel. The problem is that he also wants conditional formatting on the date, so when something is 3 days or older, it will turn orange, and when 5 days or older, then it will turn red. The problem is that if I use the Format() function within my query, it turns my dates into a String, which doesn't allow me to use conditional formatting properly when using the "mm/dd hh:nn AMPM" formatting. However, if I switch it around and instead of the query formatting, I do the formatting within the form in the format property field, then the conditional formatting works properly, but when I try to export to Excel, I get the error message: "We found a problem with some content in 'Filename.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

I am stuck at this point because I am not sure how I could get both conditional formatting working and the export to Excel to work as well. If I use the Format() function within the query, then conditional formatting doesn't work but I can easily export to excel without any issues.

Thanks in advance,

Reply from Alex Hedley:

How are you exporting the data? Are you just using the ribbon?

You should be able to format the Textbox on the form so the underlying data isn't changed.
The conditional formatting can be gone against the field.
Then just export the underlying query.
You could always write some automation to format the data in the excel sheet after it has been exported
Show Just This Thread        Post Reply
Read Limiting updates to records once initially saved by shaun H @ 10/17/2014
I want to prohibit updates to certain fields in records where users utilise combo boxes to make a selection. I've looked in the properties for each combo box and selected lock but that seems to then lock those fields for when i create new records for data entry via the form. How do i do this for limited fields where for example i wouldnt want a user to change suppliers of an identical product once i've orginally created the purchase order record?

Reply from Alexander Hedley:

Hi Shaun,
You could hide or disable the control if there is a value in that field.
So when the Form opens check if a value exists, if so lock the field since it has already been added.
Show Just This Thread        Post Reply
Read String dates based on a key by Genevieve S @ 8/24/2014
I have a need to create a variable string of dates to display on a form.
Based on the key the display should look like 12/1/2014, 1/3/2014, 2/5/2013


Reply from Richard Rost:

Are these individual records in a table? If so, the only NON-programming way I can recommend to display them is as fields in a SUBFORM.

Otherwise, if you NEED to have them in a single string, you'll have to use a RECORDSET to loop through the records and CONCATENATE a string yourself.
Show Just This Thread        Post Reply
Read The perils of working with dates by Alan Hill @ 6/1/2014
I thought this would be easy but nothing ever is, for me anyway.
I used Dlookup when the form opens to read the perameters values stored in a table. No problems.
Sometimes the user makes silly typos like 1/2/2011 when the range is between 2013 and 2014
When he runs the report date to date the records never show up.
Note the date layout may seem strange I live in Australia. Beautiful one day, perfect the next. (Except for fires and floods)

Private Sub IsDate_BeforeUpdate(Cancel As Integer)
If IsDate < StartDatePerameter OR IsDate > EndDatePerameter Then
MsgBox IsDate & vbNewLine & "Date Is Out Of Range"
     Cancel = True
End If
' does not work
End Sub

So I hard coded the dates to see if that worked. It did not

Private Sub IsDate_BeforeUpdate(Cancel As Integer)
If IsDate < "#1/7/2013#" OR IsDate > "#30/6/2014#" Then
MsgBox IsDate & vbNewLine & "Date Is Out Of Range"
     Cancel = True
End If
' does not work
End Sub

Finally I tried something I have never come across before DateValue()

Private Sub IsDate_BeforeUpdate(Cancel As Integer)
If IsDate < DateValue(StartDatePerameter) Or IsDate > DateValue(EndDatePerameter) Then
    MsgBox IsDate & vbNewLine & "Date Is Out Of Range"
    Cancel = True
End If

' IT WORKS. Access evaluates correctly
End Sub

I hope this may be of help to someone
Regards Alan Hill

Reply from Richard Rost:

Thanks for sharing. Looks like your parameter values are being treated like strings. DateValue converts them to actual date values. I use the CDATE() function which works very similar. The reason why your hard coded values didn't work is because you used quotes TOO. Just: #1/5/2001# not "#1/5/2001#"

Show Just This Thread        Post Reply
Read Access Security Updates by Marcia M @ 3/13/2014
There is a need to make updates to some 2007 Access databases to make them more secure and locked down. Are the databases able to be used while updates are being made to them or will everyone have to be out of it? The updates will involve enhancements to the security, workflow,  encrypting data, and splitting the database. Also, if you have at least 100-150 people needing to access the database, do you have any suggestions on what would be the best option to be able to have multiple people accessing it at the same time and still run normally?

Reply from Richard Rost:

If you're locking down, securing, and splitting the database, then I'd say YES, you would definitely want to kick everyone out of it that day until you're finished. The good thing is that once you're done, you'll be able to make front-end updates without kicking anyone out. Just distribute the new front-end when you're finished.

If you've got 100-150 constant users, you might want to consider moving your back-end up to SQL Server. That's a lot of concurrent traffic for an Access database alone to handle. I've had success with databases that had 20 or even 30 users, but 150 is pushing it. Give it a try first, but if things are running slow, that's your problem.

Show Just This Thread        Post Reply
Read Concatenate with Dates in Query Criteria by Dean F @ 1/22/2014
For anyone that comes across this post, I sorted it out in the couple of weeks between when I posted it and it showed up in the forum. I learned about the Nz function. Here is an example,
Between Nz([Forms]![MainNav]![StartDate]) And Nz([Forms]![MainNav]![EndDate],CDate("31/12/9999"))
As I understand it the Nz acts as essentially a wild card in that it returns a zero if either the start or end date is left blank. In this case it would mean date zero. As I have learned, in Access, date zero is 12/30/1899. So what this function accomplishes is if the fields are left blank, it includes everything from 12/30/1899 through 12/31/9999.
Show Just This Thread        Post Reply
Read Dates by Lance Shore @ 1/14/2014
I've got a question about a date search that I'm adding to a multi line search screen. I'm wanting to use a pair of fields that will accept a before and after date. I've been using a between statement in the criteria box under the query. The problem is I don't need to do a date search all the time and I don't want the pop up windows coming up all the time. Any suggestions?
Show Just This Thread        Post Reply
Read Concatenate with Dates in Query Criteria by Dean F @ 1/8/2014
I have built a form similar to Rick's search form to generate a report. Everything works as designed. One of my criteria takes values from StartDate and EndDate textboxes  in the form as so:

>=[Forms]![ReportDesignF]![StartDate] And <[Forms]![ReportDesignF]![EndDate]

All of the other report criteria have the wild card options as Rick taught so that leaving the option blank returns all records, but I can't seem to get this to work with the date criteria. I'm guessing I just don't understand how to properly concatenate the wild card with the query as written. This forces the user to put dates in the text boxes or they get a blank report. Can anybody point me in the right direction?
Show Just This Thread        Post Reply
Read Calculating Dates by Neil R @ 11/16/2013
Thanks Richard - I'll give it a go :-)
Show Just This Thread        Post Reply
Read Calculating Dates by Neil R @ 11/12/2013
I have a field of dates for when batteries were installed and another field for how long they should last (5 years).  What I'm trying to do is enable another field to calculate how long the battery life will be taking the current date into consideration.

Reply from Richard Rost:

Use a Calculated Field in a query or form. If it needs to be exactly 5 years, use the DATEADD function. If not, you can just say MyDate+(365*5) which is close enough.
Show Just This Thread        Post Reply
Read Data updates wrong by Sandy Reikofski @ 8/13/2013
Hello there! I am trying to figure out what i've done wrong. I've attached a subform and also have made relationships between 2 other tables. When I enter something in one column on one customer it adds the same information on everyones. I can't figure out if the error is in the table or in the relationship. I hope i've explained this so you understand what I mean!

Reply from Richard Rost:

Sounds like something isn't bound to the right table or field. Without looking at your database this is very hard to diagnose. Sounds like a form text box isn't bound to a field.

Show Just This Thread        Post Reply
Read comparing Data from different dates by Paul C @ 1/6/2013
Here is my question I'm using ACCESS2010.  
I am building a QUERY and attempting to insert a calculated field that resembles the data fields below.  
I need to see the difference between [RESULT1] and [RESULT2] based on a specific set of dates.  
I inserted a calculated field using the builder [DATE5] (original date minus 5 days) Works fine.  THE PROBLEM:  when I attempt to create a field that equals [RESULT2] WHICH is or should be the  ( [RESULT2] = [DATE5] is get a zero.  
My goal is to be able to locate the difference in [RESULT1] and [RESULT2] based on an OFFSET of X Days.  In this case 5 days.  
Im usually very good at this stuff, but in this case Im stuck.  I used the BUILDER, no luck.  I still get the result of zero.  
What class should I purchase that will show me how to resolve this issues and tell me what Im  doing wrong.  

Reply from Richard Rost:

I can't wrap my head around your question. Can you ask it again using a generic example and perhaps show me some data? Your field names are doing nothing for me. Sorry.
Show Just This Thread        Post Reply
Read Between Two Dates by Perry @ 12/26/2012
Thanks for the quick response I'll try it this evening
Show Just This Thread        Post Reply
Read Between Two Dates by Perry S @ 12/26/2012
I have purchased several of your classes and have learned a great deal. 101-329 ETC....

My question is in regards to a Query.
I'm running a Query between dates. 12/11/12 - 12/25/12
It is excluding the 25th, I know that you want over this but I'm not sure what lesson. I remember that it looks at the dates as 12:00 midnight and my data is after 8 am on the 25th. I'm unable to remember how to have it include all of the 25th.
Can you point me to the lesson or help me out with this.

Thank you


You have to say:

>=[StartDate] AND <[EndDate]+1

So if your dates are 1/1 and 3/1 you would say:

>=#1/1/2012# AND <#3/2/2012#

I cover this in Access 5 or see THIS tip.
Show Just This Thread        Post Reply
Read Subtracting Dates From Records by Susan @ 11/5/2012
Show Just This Thread        Post Reply
Read Subtracting Dates From Records by Susan @ 11/2/2012
I have a table with the following fields:
Date, Left Door, LDDiff, Right Door, RDDiff

Each day, the number of people who came to the library will be entered into the Left Door field. So, on Oct. 31, the left door count was 10652 and on November 1, the left door count was 8956. I'd like the LDDiff to display the difference between the the two dates.

Reply from Richard Rost:

First, don't use "Date" as a field name. It's a reserved word in Access. You should use "MyDate" or something like that. Trust me.

If you have all consecutive dates in your database, you could create a calculated query field with DLOOKUP like this:

YesterdaysAmount: DLOOKUP("LeftDoor","MyTable","MyDate=#" & MyDate-1 & "#")

This says "look up the value LeftDoor from the MyTable table where the date of that record is equal to ONE DAY BEFORE the date of the current record.

Now you can perform your subtraction.
Show Just This Thread        Post Reply
Read date time updates by Monty @ 10/6/2011
I have a form with DueDate, CurrentDate and DaysPastDue on it. What I need is a button on the form that when it's clicked on, all the CurrentDate fields in the record are updated to the current date/time. Currently I have this code below on the click event of a button I've labeled as Update Button:

Me.CurrentDate = Now()

Problem here is that it only updates one record (the first one in a column list of records)but does not update the rest. Putting =Now() as the default will only update new records. I've tried on the form open event to put =Now() but once again only one record gets updated. I need it so that all get updated. I have a calculation on the DaysPastDue field that will show me the days the bill is past due. I need to be able to click on this update button each week and get a new updated list of number day's past due. Hope I've given enough information.

Reply from Richard Rost:

If you want to change ALL of the values in EVERY record in your table, then you'll need to use an UPDATE QUERY. However, I'm curious as to WHY you need to keep a field for the current date when you can readily grab it on the fly with =NOW() to perform any calculations for days-past-due. You shouldn't need to store it in a field.
Show Just This Thread        Post Reply
Read Nested IIf Statements Dates and VBA by Access Noob @ 5/9/2011
You are awesome, Richard.

**T H A N K Y O U **

It works - WOO HOO!!!

Reply from Richard Rost:

[Takes a bow]
Show Just This Thread        Post Reply
Read Nested IIf Statements Dates and VBA by Access Noob @ 5/9/2011
Hi Richard:

So close, Richard.

Initially, it threw errors until I kept the module name as DateCheck and renamed the function DateChecker. I read in some forum to do that. I don't know if it really makes a difference. Did seem to work, though.

Now, the code works like a champ for cells where I have dates. I get #errors for cells with no date in them. Many dates are blank, because some of the "t3r1" dates are either pending or non-existent.

Any suggestions for how to handle blank date values through the code? I have used Nz before, but I'm not sure if this is the correct place for it - or where I would put it.

Thanks for the help thus far, Richard.

Reply from Richard Rost:

Hmmm... I didn't think that DateCheck was a RESERVED WORD in Access. It doesn't look like it is, but when I type it in, Access "Camel Cases" it (changes "datecheck" to "DateCheck") which tells me it exists SOMEWHERE... but even a Google search doesn't turn it up. Oh well. The NAME of the function isn't important as long as it works.

Yes, you can use the NZ() function to fix the error. Just wrap each variable inside NZ before you send it to the function:

MyValue: DateChecker(NZ(v1), NZ(v2), NZ(v3))

Show Just This Thread        Post Reply
Read Nested IIf Statements Dates and VBA by Access Noob @ 5/9/2011
I will give the above a shot. *Thank you* for taking the time to answer my question.

I will work my way toward the 300-level VBA course...need to finish the seminars, first. I am learning t o n s from you ;)

The intimidating part about VBA is not knowing where to begin. I have this project that I am working on, and I see where I need to automate things, but I wouldn't know even where to start. What variables do I need to state? How? It's a mystery to me.

Also, I've learned that I don't really have a normalized Db for the project. That's part of the trouble I am having...I have a flat file Excel sheet that I'm trying to manipulate in Access. Ooops.

Heh - I'm not Access Noob for nuthin' you know : )

Again, thanks for posting the code. I will give it a try tonight.

Reply from Richard Rost:

That's what the Access 300-series is for... teaching you HOW automation and VBA work in Access, WHERE to put your code, WHAT events to use, etc.

Working without a properly normalized database will drive you crazy. You might want to fix it for them. Just sayin'. :)
Show Just This Thread        Post Reply
Read Nested IIf Statements Dates and VBA by Access Noob @ 5/9/2011
I have a little problem. I am running a query on twelved fields that contain a date. Basically, I want my new field, AY2009-2010, to indicate if people are within this date range or outside of it. My small nested IIF workds great up to the 10th field in the table:

AY2009-2010: IIf([t1r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t4r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010","OUT")))))))))))))

But it completely fails when I try to add the remaining fields for my test. Access 2010 says that it is too complex. In other words, the below fails.

AY2009-2010: IIf([t1r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t4r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t4r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010","OUT"))))))))))))))

There must be some way to do this using VBA. All I want to do is check each of my 12 fields for a data range between #7/1/2009# And #7/1/2010#, and display the text, AY2009-2010 if the record is in the range or “Out” if they are not in the range.

1. I have no idea where to begin with VBA
2. I persume that I would have to write what I don't know in visual student express (I have it)
3. I presume that I would have to call the VBA code in the query builder, but I don't know how to do that either.

Can anyone help-a-noob? I would really like to make this work, and it would go a long way toward helping me understand how to make VBA work. I would be very grateful.

Thanks in advance.

Reply from Richard Rost:

Yes, your statement is too long. It's probably due to the number of overall characters. You could TRY cheating and shorten your text value from "AY2009-2010" to something like "X" and that MIGHT work. If not, you would need to make a custom function in VBA to do the trick. It's not terribly hard. I would recommend you watch my Access 300-series which covers VBA programming, but IN A NUTSHELL, here's what you do:

First, create a module (Home > Create > Module). Now you can make your custom function. From looking at your IIF statements, it would look something like this:

Public Function DateCheck(D1 As Date, D2 As Date, D3 As Date) As Boolean

    DateCheck = False
    If D1 >= #7/1/2009# And D1 <= #7/1/2010# Then DateCheck = True
    If D2 >= #7/1/2009# And D2 <= #7/1/2010# Then DateCheck = True
    If D3 >= #7/1/2009# And D3 <= #7/1/2010# Then DateCheck = True

End Function

Now I only built it for 3 date fields, but you can add as many as you like. This function is going to look at your date fields, one after the other, and if any of them are within those dates, it will set the value of the function to TRUE.

Now from inside your query, you can say:

MyValue: DateCheck(T1R0, T1R1, T1R2)

Again, you can add as many date fields here as you need to. This "MyValue" field in your query will then have either a TRUE or FALSE value as to whether or not any of those dates fall in that range.

See? Not too hard once you know how to do it. Now go watch my Access 300-level classes. :)

Show Just This Thread        Post Reply
Read Batch Updates by Fred @ 9/8/2010
I am using the Update to update 3 fields in my 2007 Db. I want to use BatchUpdate instead of the Update.
I can provide the code.
Show Just This Thread        Post Reply
Read if statements for dates by darren @ 5/6/2010
hi, thanks in to access trying to set up a query that will return with a field has expired...basically i have id# product, date expired and status..i want the status to return either "current", or "expired" this is what i did:
IIf([Date Expires]>Date(),"current","expired")

i can save it, but when i open it there are no values, no list, just one row with nothing.

any help would be appreciated

Answer from Richard Rost:

Your IIF function looks correct, but where are you putting it? I would make that a separate field:

IsExpired:IIf([Date Expires]>Date(),"current","expired")

Now you can set your criteria to "expired" if you want for that field to see JUST the expired records. Get it?
Show Just This Thread        Post Reply
Read Retrieving Dates (Query) by Lisa @ 4/9/2010
Hi Richard

I am trying to query a table in access to return specific dates.  I need to return records that have been in existence between 0-4 years; 5-9 years and 10 years + from the current date.  The date field is formatted as Date/Time.  I have set a query for the 0-4 years which seems to be working using the following criteria:  Between Date() And DateAdd("YYYY",-4,Date())  However I also need to retrieve records in existence between 5 - 9 years and 10 years +.  What criteria can I use which will do this?  I am currently using Access 2003 SP3 and consider myself to be an intermediate Access user....Hope you can help!

Answer from Richard Rost:

You've kind of answered your own question. Just make another query with different criteria. For 5 to 9 years, just say:

Between (DateAdd("YYYY",-9,Date()) AND Between (DateAdd("YYYY",-5,Date())

And then older than 10 years would just be:

< Between (DateAdd("YYYY",-10,Date())

Easy enough?
Show Just This Thread        Post Reply
Read Auto Populate Dates in Access 2007 by Marilyn @ 4/5/2010
Hi. I'm building a request form in Access and need to auto populate the days for the forecasted date of return. If someone is leaving on a certain day, I need the forecasted date of return 150 days from the date the person left on travel. Can you help accomplish this? Please let me know if I need to build it in vb, expression builder, modules, etc.

Thank you so much for your help!

Answer from Richard Rost:

You can do this as a calculated field in a query. If your original date is MyDate then your field will be:

NewDate: MyDate+150

See this tutorial for more information:

It sounds to me like you just need to learn how to properly create CALCULATED QUERY FIELDS. You can make a query that will perform math operations for you automatically.

Please see the following FREE video tutorial on my web site for step-by-step instructions on how to create calculated query fields:
Show Just This Thread        Post Reply
Read Access Dates Formats by Kim @ 1/26/2010
I have a report which groups data by weeks.  How do I place the week ending or starting date into the group?

Answer from Richard Rost:

If you create a group header or footer and then place the date field in there, it should show you the current date that it's on. So for the week ENDING date, put it in the group footer. This should be close enough for most situations. Anything else will involve a little creative function work.
Show Just This Thread        Post Reply
Read Access 2007 sort dates by Ron @ 12/19/2009
yes, it is a weird thing.
And it only happens at work, not on my home PC, which makes me suspect the Regional Settings.
But I'll try the DMax if it happens again. Thanks!
Show Just This Thread        Post Reply
Read Emails about forum updates by Richard Rost @ 12/14/2009
Greg, you are absolutely correct. This was a bug in my web site's code. The function that checked for new posts wasn't looking at the "IsApproved" variable. So if anyone posted a new comment, it would generate the update email even though I had not approved the message yet. My bad! It's fixed now.
Show Just This Thread        Post Reply
Read Access 2007 sort dates by Richard Rost @ 12/14/2009
I just tested a bunch of date query "stuff" in Access 2007 with GROUP BY and sorting, and I didn't have any problems.

Access SHOULD know the difference between mm/dd/yy and dd/mm/yy. They're just displayed differently. Access stores the dates internally as numbers. HOWEVER, if your PEOPLE typed the dates in wrong (thinking it was the other format, for example) that could be a problem.

You can FORCE a specific format for your dates in your queries by using the FORMAT command or property.

Also, instead of using the FIRST and LAST commands in a query, if you just need to find one date, why not use a DMAX or DMIN function?
Show Just This Thread        Post Reply
Read Emails about forum updates by Greg Beben @ 12/11/2009
Just a nitpick.  Why am I getting emails every day saying that the forum has been updated, when the last posting is in November and it's now 12/11?  It's a bit annoying to check the forum and find nothing new.  Can you just email when there really IS something new?  Thanks.  And this is a nitpick - I love your stuff and the forum.
Show Just This Thread        Post Reply
Read Access 2007 sort dates by Ron @ 12/9/2009
I don't know if this would be called a bug, but I've been having a problem where Access 2007 will give me the 'First' value instead of the "Last" value when I sort on dates. I think this is because in Canada, we show dates as dd/mm/yy instead of the American mm/dd/yy. Also some people don't know how to set the date format in Control Panel | Regional Settings, so I have some PCs with dd/mm/yy and some PCs with mm/dd/yy.
Show Just This Thread        Post Reply
Read Access 2003 Dates to populate Calendar by Richard Rost @ 10/15/2008
Mike, you can certainly automate Outlook to do all of these things. Unfortunately, that's a whole class in and of itself. I'll try to cover this in an upcoming lesson. It's not HARD, but it involves a lot of code.
Show Just This Thread        Post Reply
Read Access 2003 Dates to populate Calendar by Mike @ 10/7/2008
I have an Event database that I created for training scheduling purposes. I would like to populate a calendar program, preferably Outlook, with the event dates, titles and maybe the attendees names.
thank you in advance for your help.

Show Just This Thread        Post Reply

Collapse All Topics


Post Your Comments or Subscribe
    If you would like to be notified of new posts on this forum,
    just enter your email address below. It will be kept private.
If you just want to subscribe to get email updates when this forum 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


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 4+5:
  Notify me when the this forum 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:

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
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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