473,324 Members | 2,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Limiting a Report to a Date Range

62 32bit
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
19 1322
PhilOfWalton
1,430 Expert 1GB
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
glat
62 32bit
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
1,430 Expert 1GB
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
glat
62 32bit
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
1,430 Expert 1GB
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
glat
62 32bit
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
1,430 Expert 1GB
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
glat
62 32bit
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, 203 views)
Mar 10 '17 #9
PhilOfWalton
1,430 Expert 1GB
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
glat
62 32bit
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, 208 views)
Mar 13 '17 #11
PhilOfWalton
1,430 Expert 1GB
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
glat
62 32bit
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
1,430 Expert 1GB
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
glat
62 32bit
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
1,430 Expert 1GB
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
glat
62 32bit
Phil, are you saying I should not use the drop down boxes I'm using?
Mar 14 '17 #17
PhilOfWalton
1,430 Expert 1GB
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
glat
62 32bit
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
1,430 Expert 1GB
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

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

Similar topics

7
by: Garyrek | last post by:
Hi I have a requirement where I need to make a report for each auto dealer how many hits they got on specified date range, so for each car detail page I increment the counter and total it while...
5
by: Michelle | last post by:
Hi all I have a report which displays staff absences. An absence record has a start and end date and the type of absence, ie sickness, annual leave. I have created a form which allows the...
2
by: Sara | last post by:
I have followed instructions on the http://allenbrowne.com/tips.html for limiting a report to a date range. At the bottom there is a note that says You will end up using this form for all sorts...
9
by: ckpoll2 | last post by:
Hello, I have a report that presents data based on a query that is built into the report. It asks for a start date and end date. When I type in 5/1/07 and 5/20/07, for example, there are no...
19
by: ali3n8 | last post by:
Hello I have attempted to create a date range report from a query called qrycustomerinformation. The field that contains the value of my date is called Followup. When i run a report on this it is...
7
by: dozingquinn | last post by:
Hello, Is there any way to auto populate the user defined date range into a report? I currently have the criteria "Between And " for a date range field. This prompts the user to enter a date...
12
smithj14
by: smithj14 | last post by:
I have a form to enter start and end dates then select a worker name to filter a report. This all works fine and when the report is open in preview mode it shows the date range in the txtboxes on the...
3
tuxalot
by: tuxalot | last post by:
I have a listbox showing reports, and when a report is selected, criteria (dates, employee name, etc.) necessary to print that report become visible. For a few reports, I would like to have the...
3
by: anuragrathor | last post by:
I have one data parameter as Date, when a user enters a date, I need the result for the last 3 months. For example, if a user enters the date as March 3, 2010. Then I only need to display the data...
0
by: LBinGA | last post by:
Hello! I am using Access 2010 and I have an Unbound Form (FrmFilter) that filters a report by (up to) 7 criteria using the code below, which works beautifully. I would like to add 2 additional...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.