By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

Limiting a Report to a Date Range

P: 59
I followed the instructions of Method 2 on the http://allenbrowne.com/tips.html for limiting a report to a date range which worked perfectly for my report.

However, because of incorrect dates being inserted into a Due Date field on the form (which has to be in datasheet view) I had to create a combo box with a list of correct dates which are stored as text and linked to another combo box. This also works well.

Now though when I run my report from the StartDate and EndDate form no records appear. Iím assuming it has to do with the dates being stored as text? I tried storing them as dates but when I tried inserting the date from the combo box in the datasheet I got an error saying "The value you entered isnít valid for the field".

Is there something I can change in the code to get the form working again?
Mar 9 '17 #1
Share this Question
Share on Google+
19 Replies


PhilOfWalton
Expert 100+
P: 1,430
The dates for your combo box should be dates, not text.

Without knowing what the query is for your report but is should have something like

Expand|Select|Wrap|Line Numbers
  1. WHERE DueDate >= #" & StartDate & "# AND DueDate <= #" & EndDate & "#"
  2.  
Note the # either side of the date to tell access it is a date.

If that doesn't work, post your Report's RecordSource.

Phil
Mar 9 '17 #2

P: 59
Phil, originally I made the combo box a date field but when you choose a date from the list in the combo box you get the message "The value you entered isn’t valid for the field", that's why I changed it to a text box.
Mar 10 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
What exactly is the RowSource for your Combo boxes and which is the bound column?

Is there a Macro or VBA happening on the AfterUpdate of the Combos?
If so, temporarily remove it, so that we can isolate where your error is occurring.

Phil
Mar 10 '17 #4

P: 59
My row source for Dates is
Expand|Select|Wrap|Line Numbers
  1. SELECT Dates.*FROM Dates;
Row source for Quarter Dates is
Expand|Select|Wrap|Line Numbers
  1. SELECT Quarters.lngStoreID, Quarters.strStoreName
  2. FROM Quarters
  3. ORDER BY Quarters.strStoreName;
  4.  
There is an Event Procedure in the Quarter Date After Update combo box
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboStore_AfterUpdate()
  2.     Dim sManagerSource As String
  3.  
  4.     sManagerSource = "SELECT [Dates].[lngManagerID], [Dates].[lngStoreID], [Dates].[strManagerName] " & _
  5.                         "FROM Dates " & _
  6.                         "WHERE [lngStoreID] = " & Me.cboStore.Value
  7.     Me.cboManager.RowSource = sManagerSource
  8.     Me.cboManager.Requery
  9. End Sub
  10.  
Mar 10 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
The RowSource for Dates isn't very helpful. What is the table design and which is the bound column?

Where does this new reference to a Quarters table come into the equation. Again what is is structure and what is the relationship to Dates?

Phil
Mar 10 '17 #6

P: 59
Phil,

Firstly let me explain the names in the tables. I copied the codes and tables from a sample database but when I tried to change the names and codes to my own, errors appeared so I reverted back to the original names and added a Caption in the tables.

The Quarters table is where the frequency of the payments is stored, IngStoreID and strStoreName (Monthly, Quarterly, NSQ, OSQ, etc).

The Dates table is where the dates for the Quarters are stored IngManagerID, IngStoreID, StrManagerName (1 March 2017, 1 April, 2017 and so on).

They are bound by IngStoreID so when a Quarter is chosen from the cboStore combox box it limits the list to the dates in the cboManager combo box.
Mar 10 '17 #7

PhilOfWalton
Expert 100+
P: 1,430
Sorry, this is getting more & more confusing.

I strongly suspect that your data is not normalised, so could I ask you to send an image of your relationships but ensure that the tables are shown full length so that I can see all the fields.

You still haven't told be which is the bound column in your Dates Combo box.

Phil
Mar 10 '17 #8

P: 59
Sorry, I'm self taught with Access so my terminology and phrasing may be incorrect. Image attached. The bound control in my Dates table is IngStoreID.
Attached Images
File Type: jpg Capture.JPG (34.9 KB, 60 views)
Mar 10 '17 #9

PhilOfWalton
Expert 100+
P: 1,430
Ah, were nearly all self taught, but answering questions on this website is a great teacher.

Your relationships look really weird as they all appear to be 1 to 1 joins, and the table names seem to bear no resemblance to the fields they contain. The table linked to LeaseID is missing. Is anything else missing?

A minor point is that it is inadvisable to have spaces in field names, because you have to surround them with square brackets [] when you refer to them. [Service Charge] as opposed to ServiceCharge.

Could I suggest that you start from the beginning and tell us what the database is for. Give as much information as you can, but without referring to your existing tables.

I am guessing that you have stores with managers. Is there a single manager per store, or can there be more than 1 manager for that store?
What are the payments for and who is making the payment. Which field(s) are represented by the payment.
Many of the items you are showing in your Payment look like fixed items but are they for a store or a lease?

Very Confused

Phil
Mar 10 '17 #10

P: 59
Phil, there are no stores. That was something I took from a sample website but couldn't get it to work properly when I changed the names so left as is.

The database is for properties owned and leased out to tenants. One property may have many tenants or just one. Each tenant may have many properties with separate leases for each property. The tenant is invoiced either monthly or quarterly at different times of the year depending on the terms of the lease. A record is kept of all tenants lease details (lease start, lease end, rent review, break notice, contact details, invoicing address). A record is also kept of their rents due (even past rents due for reference).

I took the bones of the Microsoft Real Estate sample database and built up from there, adding things I picked up on the way from searching online, to the way I would like it to work/look. A screen shot is attached of the relationships.

I'm very sorry for confusing you but I hope this helps you understand what I'm doing (and trying to do) and I'm also very grateful for your help.
Attached Images
File Type: jpg Capture-3.jpg (40.8 KB, 52 views)
Mar 13 '17 #11

PhilOfWalton
Expert 100+
P: 1,430
Excellent, at least I now understand what it's all about.

So looking at your relationships (and again I find it very odd that it is not showing with a 1 on one side & an ∞ on the other)



Above is an example from one of my Dbs.

That may depend on which version of Access you are using (I am on Office 2010)

I have already remarked on the inadvisability of spaces in field names.

OK. The Lease table joins the tenants to the properties, so it should have a joiny unique key of TenantID & PropertyID. That way you get what you need of tenants having multiple properties, and properties with multiple tenants, but you get a duplicate key error if you try to add the same property and tenant as is already in the database.

Can you clarify what the TenantContact table is for. My guess is that it it is all part of the lease, in which case, it may not need to be in a separate table. The question is can there be multiple TenantContacts (Notes) for 1 lease.

The way I would handle things is in the Lease table, I would hold Frequency which I would guess would be either 1 (Annually), 4 (Quarterly), 12 (Monthly) or 52 (Weekly) and the start date.
I would then have a table of rents for each lease and the start and end date for rent at that rate. This would include management fees, Service charge etc. So we then have a record of the rent and frequency and date the rent is due for each lease.

From this "Invoices" could be generated and payments received against those invoices. The payments would then just have a reference to the invoice number, date paid and amount.

I appreciate that this will be a major rethink, but it usually pays to normalise your tables, and a very crude explanation is that each table should be about 1 subject. I am particularly concerned about your payment table which appears to contain duplicate information from the lease table. What happens when say the 2 management fees are different?

Phil
Mar 13 '17 #12

P: 59
Phil, I am using Microsoft 2010.

You're right in saying the TenantContact table is related to the Lease table. There are multiple contacts - accounts query, agents, managers, etc that are specific to the unit.

Yes, the frequency is the period when the rents fall due. English quarters. Scottish Quarters and so on as well as monthly. The Dates table is the dates when the Quarters fall due, ie English Quarters (25 Mar/24 June/29 Sep/25 Dec), Scottish Quarters (2 Feb/15 May/1 Aug/11 Nov). These are the combo boxes mentioned in my first post.

The Payments table is as you suggest in table of rents - StartDate and EndDate at that specific rent and Management and SC along with DueDate (from a list of 25 Mar/24 June, etc as chosen above).

Re Payments table and ManagementFee, this is the same info as in the Lease table. I tried to get the info from the Lease table in a query but something was wrong so to avoid this I put the info in the Payments table. I know this is doubling up but for some reason my query wouldn't work.

I'm off to remove the spaces in my table names and try and normalise as you suggested.
Mar 13 '17 #13

PhilOfWalton
Expert 100+
P: 1,430
Good luck with the spaces.

If the Scots leave the UK, you may well find that rent is in Euros!!!, so you may well need a currency field.

I feel there is a lot of redundancy with quarters, dates & frequency.

Now referring to the lease table, presumably, if the frequency is 4 and the area is England, you use the English quarter dates, and if the area is Scotland, you use the Scottish quarter dates.

If the frequency is 12 you can set a day, probably 1 for 1st of month or 15 for 15th month.

Are there any other date combinations?

If not, the dates in the payment table are all calculations based on information in the Lease table.

Phil
Mar 13 '17 #14

P: 59
Ha ha! Another two years of politics ahead!

Yes, the other variants are quarterly 28 Jan/Apr/Jul/Oct, 28 Feb/May/Aug/Nov and another is monthly on 1st of the month (I also need yearly but don't know how to do that one!). That's the reason for the combo box so when the Quarter date is chosen the Due Date will be limited to either of those dates listed. The calculations are produced in a query dependent on which Quarter is chosen.
Mar 13 '17 #15

PhilOfWalton
Expert 100+
P: 1,430
Given a start date say 28 Jan,and knowing that a payment is due quarterly, all we need to do is use the DateAdd function to add 3 months

Expand|Select|Wrap|Line Numbers
  1. NextDate = DateAdd("m", 3, #28/01/2017#)
  2.  
Similarly for an annual contract

Expand|Select|Wrap|Line Numbers
  1. NextDate = DateAdd("y", 1, #05/04/2016#)
  2.  
There is a modification required to the above needed if you fix the start date that you need to work out the difference between the current year and the original saved date to work out how many months / quarters / years to add. So if the original Lease Due date is 28/4/2015 and the lease is quarterly, then we need to keep adding 3 months till the date becomes > 31/12/17 for this year.

Phil
Mar 14 '17 #16

P: 59
Phil, are you saying I should not use the drop down boxes I'm using?
Mar 14 '17 #17

PhilOfWalton
Expert 100+
P: 1,430
I am basically saying that the dates for the lease are calculated from the frequency and the start date.

My guess is that you are really trying to get a report that says "What rent am I to expect between 14th March and 30th April?"
So you should be able to use any date range you fancy for your reports

Phil
Mar 14 '17 #18

P: 59
Yes, that's correct. But I also need the correct date put into the NextDue field, hence the reason for the combo boxes.
Mar 14 '17 #19

PhilOfWalton
Expert 100+
P: 1,430
The next Due Date is the lease start date + no of periods (Months / Quarters / Years) between that lease start date and the dates in which you are interested.

So a lease start date of 28/4/15 and a quarterly payment and you are interested in the period from 14th March to 30 April 2017 then we have 8 quarters so the next due date is 28th April 2017

Phil
Mar 14 '17 #20

Post your reply

Sign in to post your reply or Sign up for a free account.