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

Date Range Issue

P: 5
Hello,
This is my first time posting on here so I hope I don't ramble too much. I have a complex database that a co-worker and I created 2 years ago and that I have been adding to since then. The database allows us to keep track of projects that we are quoting and all of the important information related to those projects. Each project can be quoted to several customers on completely different dates.

My problem comes with the most recent report that I was asked to make. The report needs to show all of the customers that are on a given project regardless of if they fall into the date range. I am using the below code in my query in the Date field to the restrict the data.

>=[Forms]![popupDateRangeDateCheck]![txtStart]

The problem is that some jobs might have a date older than the date enterd in [txtStart] but we still need it to show up in the report and I don't know how to write the code. They only need to be included if one customer is within the date range, then we need to find all customers who we have quoted that job regardless of date.

This probably doesn't make any sense and I'm sure I will have to explain what I mean again but I feel like if I keep going I'm going to confuse it more so I'm going to submit it.

Hopefully someone can help decifer.

Thanks,
Dan
Aug 15 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
Hi. You are applying a condition to your report which is not the one you need in the circumstances; from what you say you need to show all jobs for customers where at least one of those jobs is of a date greater than or equal to a specific start date. You need to find the highest job date (the maximum date for each customer) for each customer for this purpose. You will not be able to do so in the same query as the one you are reporting on, as it is at a different level of grouping.

Create a query in design view in which you include the customer reference and the job date. Set View, Totals on to make it a Group By query, and change the Group By of the job date to Maximum instead. Give this newly-computed field a new name such as Max Date. Save the query under a suitable name such as qryMaxJobDate.

Then, join this query back onto your report's query with the join being where the customer reference fields are equal. You now have a maximum job date for each customer, and it is this field you can use as >= your specific date instead of the job date itself.

Unfortunately you haven't posted the SQL of your report query (so I don't know what the relevant fields are). Anyway, exemplar SQL follows but it is for you to apply it in your own circumstances. If you use the Access query editor you may find it easier that way to add the max query to your current report query, make the join, and change your comparison field (instead of using the SQL view to do it).

qryMaxJobdate: minimum skeleton
Expand|Select|Wrap|Line Numbers
  1. SELECT [customer ref], max([job date]) AS [Max Date]
  2. FROM [your job table]
  3. GROUP BY [Customer ref];
Report query skeleton
Expand|Select|Wrap|Line Numbers
  1. SELECT [whatever fields you currently have], [Max Date]
  2. FROM (([whatever tables you already have joined in some way]) 
  3. INNER JOIN qryMaxJobDate
  4. ON [Max date] >= CDate(Forms![your form]![your date])
  5. GROUP BY [whatever was there before];
The CDate function is there to ensure you are comparing an actual date to your max job date - not a text string that happens to look like a date (date/time values are stored as numeric values in the database, not text strings). Trying to compare dates to values in text boxes can lead to failures which are not obvious - after all, the text in the control looks like a date...

-Stewart
Aug 15 '08 #2

P: 5
Stewart,
Thank you for the detailed reply. I will give that a try and see what I can come up with.

Thanks,
Dan
Aug 15 '08 #3

P: 5
Ok so I tried doing things a little differently and have gotten further but I am having trouble getting the 2 queries to link together. Here is the code for the two queries:

Query 1

qryDateRange5
Expand|Select|Wrap|Line Numbers
  1. SELECT tblContractorOnProject.Date, tblContractorOnProject.Project_ID
  2. FROM tblContractorOnProject
  3. WHERE (((tblContractorOnProject.Date)>=[Forms]![popupDateRangeDateCheck5]![txtStart]));
  4.  
Query 2

qryDateRange6
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProject.ID, tblProject.Name AS tblProject_Name, tblContractorOnProject.Date, tblContractorOnProject.Contractor_ID, tblContractor.Name AS tblContractor_Name
  2. FROM tblProject INNER JOIN (tblContractor INNER JOIN tblContractorOnProject ON tblContractor.ID = tblContractorOnProject.Contractor_ID) ON tblProject.ID = tblContractorOnProject.Project_ID
  3. WHERE (((tblProject.ID)=[qryDateRange5]![tblContractorOnProject].[Project_ID]));
  4.  
Every time I try and run it I get error messages regarding the final line of the 2nd query. I've tried formatting it with brackets and parenthesis and using every different thing I can think of but I can't get the code to work. Any ideas?

Thanks,
Dan
Aug 18 '08 #4

Expert Mod 2.5K+
P: 2,545
Hi. To refer to querydaterange5 from query daterange6 you need to include it in the FROM part of your SQL, and join it to the other tables on the appropriate fields. If you can't see how you should join it you can instead look up the field value using DLookup, but this is not the best solution to adopt in general.

You were trying to refer to a query that is not part of the SQL FROM by using a three-part reference to the item. You can refer to a control on a form that way, as the syntax for this is forms!formname!controlname - but this is not the same as joining onto a field in a query or table.

-Stewart
Aug 18 '08 #5

P: 5
I will give it a try.

Thanks Stewart.
Aug 18 '08 #6

P: 5
Stewart,
I was able to fix the query as you suggested and now the report works perfectly. Thank you very much for the timely suggestion. I was about to scrap it and start the report a whole different way.

Thanks for your help,
Dan
Aug 18 '08 #7

Post your reply

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