Date Range Report

[ali3n8]

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 fine. But I have decided I would like to pull this report for a specific date range. Ive attempted to use the method on allen brownes page http://allenbrowne.com/casu-08.html. I would like to also mention the followup field also uses this code to generate a date three months from the date the record is entered =DateAdd("m",3,[Date]). When enter a date range for the report it accepts the range but opens the report with no information on it. When I open the report without the date range method it show me all my records just fine. Im thinking that for some reason that the date range form is not pulling from the field followup. I hope you can understand. What can I do to fix this or is there another method that I can go about creating this date range report?

[Scotter]

Hi, I think I just had the same issue you are having now. Look at this and tell me if its similar to what your trying to do.
http://www.thescripts.com/forum/showthread.php?p=2771747#post2771747

Scotter

[ali3n8]

I am trying to do a similiar thing, but when I pull my reports they come up blank.

[Scotter]

Ok, so your report is based on a query correct? And are you putting the date into a form and then runing the report, or no?

If you are using a form, are you doing the same kinda thing where your building your SQL within the form? and what is your querries SQL?

Scotter

[ali3n8]

Ok, so your report is based on a query correct? And are you putting the date into a form and then runing the report, or no?

If you are using a form, are you doing the same kinda thing where your building your SQL within the form? and what is your querries SQL?

Scotter

Yes my report is based on a query. Is this what your asking for

SELECT tblcustomerinformation.CustomerID, tblcustomerinformation.[File Status], tblcustomerinformation.[Contact Number], tblcustomerinformation.First, tblcustomerinformation.Last, tblcustomerinformation.Street, tblcustomerinformation.State, tblcustomerinformation.City, tblcustomerinformation.[Zip Code], tblcustomerinformation.Verification, tblcustomerinformation.Consultant, tblcustomerinformation.Closer, tblcustomerinformation.Opener, tblcustomerinformation.Date, tblcustomerinformation.Followup
FROM tblcustomerinformation;

[Scotter]

Yes my report is based on a query. Is this what your asking for

SELECT tblcustomerinformation.CustomerID, tblcustomerinformation.[File Status], tblcustomerinformation.[Contact Number], tblcustomerinformation.First, tblcustomerinformation.Last, tblcustomerinformation.Street, tblcustomerinformation.State, tblcustomerinformation.City, tblcustomerinformation.[Zip Code], tblcustomerinformation.Verification, tblcustomerinformation.Consultant, tblcustomerinformation.Closer, tblcustomerinformation.Opener, tblcustomerinformation.Date, tblcustomerinformation.Followup
FROM tblcustomerinformation;

Ok, try adding WHERE ((([tblcustomerinformation].[Followup])>mydate)); so the last line will look like

FROM tblcustomerinformation WHERE ((([tblcustomerinformation].[Followup])>mydate)); 
 

Now when you run the report a box should come up asking what mydate is, put in a date that comes before a certian date of one of the records you are looking for and you should get some results. Try that and well see what happens, I'm pretty new to this myself so lets see if we can struggle through :)

[ali3n8]

Ok, try adding WHERE ((([tblcustomerinformation].[Followup])>mydate)); so the last line will look like

FROM tblcustomerinformation WHERE ((([tblcustomerinformation].[Followup])>mydate)); 
 

Now when you run the report a box should come up asking what mydate is, put in a date that comes before a certian date of one of the records you are looking for and you should get some results. Try that and well see what happens, I'm pretty new to this myself so lets see if we can struggle through :)

I did that and it did not prompt for any date?

[Scotter]

you put it into the SQL view of the Query the report is based on?

[ali3n8]

Yes I did insert it into the sql view.

Thank You

[mlcampeau]

Yes I did insert it into the sql view.

Thank You

Please indicate the exact results you got after placing the code that Scotter suggested. Did the query run? Did you get an error message?

[ali3n8]

Please indicate the exact results you got after placing the code that Scotter suggested. Did the query run? Did you get an error message?

Ok it does prompt me for mydate. But this is causing issue with other functions in my db. I have a search form and now when I open that it prompts me for my date first before I can begin to use a search form. I am assuming that I need to create seperate query for the report rather than running the report from the query I use for my search form. Am I right by saying that?

[Scotter]

Ok it does prompt me for mydate. But this is causing issue with other functions in my db. I have a search form and now when I open that it prompts me for my date first before I can begin to use a search form. I am assuming that I need to create seperate query for the report rather than running the report from the query I use for my search form. Am I right by saying that?

I would, but I'm no expert

[mlcampeau]

I would, but I'm no expert

In this case, I would create a separate query, one for your form and one for your report. You did not indicate whether the query worked as you expected when you entered a date in the prompt.

[ali3n8]

I have gotten date range to work on several of my reports just one specific one is giving me a problem. Im running the report from a query called followup. When I run this report it comes up blank in order to get data to appear on it I have to switch the report to design view and enter an expression which I will get to. The Fields that I have on this report are:

Followup - Date/Time which has a default value set as =DateAdd("m",3,[Order Entered])

Contact Number - Number
First - Text
Last - Text
Consultant - Text

Now in order to get all fields to show the data on the report I have to switch the report to design view and in the field that says followup on the report I have to enter =DateAdd("m",3,[Order Entered]). Once I add this expression to it the Date Range no longer functions, I dont understand why I would have to do this on the report if the default value for the [Order Entered] Field has already been set to =DateAdd("m",3,[Order Entered]). Is there something that I am not doing properly?

[mlcampeau]

I have gotten date range to work on several of my reports just one specific one is giving me a problem. Im running the report from a query called followup. When I run this report it comes up blank in order to get data to appear on it I have to switch the report to design view and enter an expression which I will get to. The Fields that I have on this report are:

Followup - Date/Time which has a default value set as =DateAdd("m",3,[Order Entered])

Contact Number - Number
First - Text
Last - Text
Consultant - Text

Now in order to get all fields to show the data on the report I have to switch the report to design view and in the field that says followup on the report I have to enter =DateAdd("m",3,[Order Entered]). Once I add this expression to it the Date Range no longer functions, I dont understand why I would have to do this on the report if the default value for the [Order Entered] Field has already been set to =DateAdd("m",3,[Order Entered]). Is there something that I am not doing properly?

I'm not too familiar with Date functions, but have you tried putting your DateAdd expression in your query instead? Once you do that, then add that field to your report and see what happens. This is getting a bit out of my area but let me know if that works.

[ali3n8]

I have tried entering the formula in the criteria field for the query also, is this what you were speaking of?

[mlcampeau]

I have tried entering the formula in the criteria field for the query also, is this what you were speaking of?

No, what I meant was, in design view of your query, in the Field row of an empty column, type something like:

FollowupDate: DateAdd("m",3,[OrderEntered])

Then on your report, in the field list, you should see FollowupDate and then add that field on your report instead.

[ali3n8]

Thank you I tried your method and it does work to the extent that it shows the follow up date on my report Im just still not able to pull a report via date range Ive entered this into the critera row for FollowupDate to >=[forms]![frmReport]![txtDateFromfollowupdate] And <=[forms]![frmReport]![txtDateTofollowupdate] I am using a form to enter my date ranges. Unfourtunately I have still been unsucessful in achieve a date range for this paticular report.

[ali3n8]

Actually it did work im just a dee dee and had mispelled somethings in my code. Thank you much I appreciate it.

[mlcampeau]

Actually it did work im just a dee dee and had mispelled somethings in my code. Thank you much I appreciate it.

I'm glad you got it to work! Good luck with the rest of your project!