Can't get Date Range to work from Form to Report

Hi,
I've followed Allen Browne's directions very closely for creating a form for 
a date range to limit results to that date range on a report and can not get 
it to work for some reason. I've read many threads on this board which all 
say the same thing and it seems to work for everyone else. :(

Here's what I have:
A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. 
The form is called "frmDateRange" and the report is called "Sales Activity 
Report". The two unbound text boxes are set to "Short Date" format which the 
user inputs a date into each to get the Date Range. 

I have a Cancel command button with the following code:

Private Sub Cancel_Click()
DoCmd.Close acForm, Me.Name
End Sub

I have an OK command button with the following on-click code:

Private Sub OK_Click()
Dim strReport As String     'Name of report to open.
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"

    strReport = "Sales Activity Report"
    strField = "SaleDate"

    If IsNull(Me.txtDateBegin) Then
        If Not IsNull(Me.txtDateEnd) Then   'End date, but no start.
            strWhere = strField & " <= " & Format(Me.txtDateEnd, 
conDateFormat)
        End If
    Else
        If IsNull(Me.txtDateEnd) Then       'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.txtDateBegin, 
conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.txtDateBegin, 
conDateFormat) _
                & " And " & Format(Me.txtDateEnd, conDateFormat)
        End If
    End If
End Sub

In my query (and placed on my report) there is the field called "SaleDate".

I have also placed text boxes with the controls to display the 
[txtDateBegin] and [txtDateEnd] in the report header.

When I click the OK button - nothing happens!  

ALSO ****** I have a subreport. How do I pass along the same info to the 
subreport??  What am I missing?????? I'm using ver 2003 on XP.

Thanks for your help.
0
Utf
11/7/2007 2:35:02 PM
access.reports 4434 articles. 0 followers. Follow

1 Replies
646 Views

Similar Articles

[PageSpeed] 37

OK, I've figured out my problem was that the txt boxes and command buttons 
were in the Form Detail section. They should be in the Form Header!!

"laknight" wrote:

> Hi,
> I've followed Allen Browne's directions very closely for creating a form for 
> a date range to limit results to that date range on a report and can not get 
> it to work for some reason. I've read many threads on this board which all 
> say the same thing and it seems to work for everyone else. :(
> 
> Here's what I have:
> A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. 
> The form is called "frmDateRange" and the report is called "Sales Activity 
> Report". The two unbound text boxes are set to "Short Date" format which the 
> user inputs a date into each to get the Date Range. 
> 
> I have a Cancel command button with the following code:
> 
> Private Sub Cancel_Click()
> DoCmd.Close acForm, Me.Name
> End Sub
> 
> I have an OK command button with the following on-click code:
> 
> Private Sub OK_Click()
> Dim strReport As String     'Name of report to open.
>     Dim strField As String      'Name of your date field.
>     Dim strWhere As String      'Where condition for OpenReport.
>     Const conDateFormat = "\#mm\/dd\/yyyy\#"
> 
>     strReport = "Sales Activity Report"
>     strField = "SaleDate"
> 
>     If IsNull(Me.txtDateBegin) Then
>         If Not IsNull(Me.txtDateEnd) Then   'End date, but no start.
>             strWhere = strField & " <= " & Format(Me.txtDateEnd, 
> conDateFormat)
>         End If
>     Else
>         If IsNull(Me.txtDateEnd) Then       'Start date, but no End.
>             strWhere = strField & " >= " & Format(Me.txtDateBegin, 
> conDateFormat)
>         Else                                'Both start and end dates.
>             strWhere = strField & " Between " & Format(Me.txtDateBegin, 
> conDateFormat) _
>                 & " And " & Format(Me.txtDateEnd, conDateFormat)
>         End If
>     End If
> End Sub
> 
> In my query (and placed on my report) there is the field called "SaleDate".
> 
> I have also placed text boxes with the controls to display the 
> [txtDateBegin] and [txtDateEnd] in the report header.
> 
> When I click the OK button - nothing happens!  
> 
> ALSO ****** I have a subreport. How do I pass along the same info to the 
> subreport??  What am I missing?????? I'm using ver 2003 on XP.
> 
> Thanks for your help.
0
Utf
11/7/2007 4:51:02 PM
Reply:

Similar Artilces:

Custom Form Error, publih to server
Hi All I'm trying to publish a new form I have created on the Administrator account of the exchange server, in outlook. When I go to publish in the Org. Forms Library, I receive the following error, "An error has occured when trying to access this folder." As this is an admin account, I am trying to find out what the error is and why it's happening, but to no avail. Anyone have any ideas or suggestions Much Appreciated Jason ...

get paid now!!!
when you subscribe you'll be rewarded 10$$$ Check it now,don't wait!!!!!!! http://e-mailpaysu.com/members/index.cgi?andrusko ...

Using a Total from a Sub Report
I have a report for Orders, and a subreport for each Order for Invoices. The subreport shows the total amount for Invoices against each Order. The Orders are grouped by Area. On the Area header/footer I have an Order total. I now want to show the total of ALL Invoices by the Order total. Please can someone tell me as simply as possible how to do this? For ease I will call my main report ORDERS and my subreport INVOICES. Any help is greatly appreciated - I have searched through relevant questions but have not found an expression that works! I would create a totals query t...

print report according to value field
I am trying to print a report depending on a the field "OrderQty". i did created the code with the print command and the criteria and it looks like this: DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip DoCmd.PrintOut , , , , [OrderQty] however, it give me an error saying, that "|" field is not found. i looked through the code and tested the code, by putting 1 or 2 in place of OrderQty and it prints fine, that means to me that it's not the code and i looked to through the Query that the source of that R...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Need help mutiple worksheets to create a report worksheet
I hope someone can help me I have just started with a reasonably large homeless organisation where they collect large amounts of statistical data using excell. There are 25 induvidual services hence 25 induvidual data sets. Each set essentially contains the 1. a referral worksheet - name date etc (Monthly Tally of referals) 2. an induvidual stats sheet a variety of data (for each referal that becomes a client) 3. an occupancy stats sheet (Like a tally sheet 1 counts for each day + a Bfwd and Cfwd column) 4. a monthly summary report (combines data from all) Whilst a Database comes to mind ...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

"#"##, or "#"@
Oh boy, iif there's a problem, it might take years but eventually, I find it! <g> I ran into the problem on Friday where I had a custom cell format that, for once, didn't always work because this sheet has number + letter combinations. "#"## results in: #1 #2 #3 1a 2a 3a, 3b Where "#"@ gives me: 1 2 3 #1a #2a #3a, 3b Is there any way to get it consistently, like so: #1 #2 #3 #1a #2a #3a, 3b with the user just entering the number or number + letter alone? Thank you! :oD I'm assuming you don't have negative values (if you do, show us where you ...

Problems with Access 2000 reports in Access 2003
Hi, we have a sent out a 2000 format access database to a client running Access 2003. When trying to open any of the reports they get an error - "the openreport action was cancelled" - Not sure if they get this by running the report from the database window or if it's only when done through a form (buttons that run docmd.openreport when pressed). Is anyone aware of any issues that may be causing this? I couldn't find anything elsewhere on the MS site. Thanks - Angie P.S. the reports work fine in 2000 and XP. ...

Junk Mail Filter
Environment: WinXP, Outlook 2003, both SP2/fully patched. Symptom: Some messages are incorrectly identified as Junk, even after both the Sender and the Sender's Domain have been added to the Safe Sender's list. This is verified by reviewing the Safe Senders list. The entries are not on the Blocked Senders list. One is an English language message from Germany. The ..DE domain is permitted, and confirmed on the International tab of Junk E-mail options. The protection level option is set to, Low. One of the messages is a Microsoft newsletter... Questions: 1. Why? 2. What...

Report Options
Hi Everyone, Using Excel 2003. I usually use excel to make forms (glorified word processing) I have recently started to paste data from an Access database tables, and queries into excel. I would like to know how I should set up my spreadsheet to show my data by month, quarter and then yearly. I want my report to show an average percent for the month of April, May, etc then other cells that show the percent per quarter. I don't necessarily need you to tell me how to do it step by step because I think it is going to be alot of steps. For right now, I have 2 sheets and ...

Arranged by date
Is it possible to set up extra ranges for the arrange by groups in Outlook 2003. For example I would like to extend "arrange by date" to group emails by 2 months ago, 3 months ago, ... last year, 2 years ago...... Currently everything that is older than 1 month gets grouped as "Older" Any ideas. Regards Alex Weatherall ...

System date
The system date is correct for todays date. Maybe last October it off by a year? Hank ...

How do I add a detail section to the form?
I have found instructions on how to add a header/footer but can't find how to add a detail section. A form always has a detail section. It can have only one detail section. -- Dave Hargis, Microsoft Access MVP "kjv77" wrote: > I have found instructions on how to add a header/footer but can't find how to > add a detail section. ...

Noob Question For Selecting Multiple Fields On A Form
Hello... In versions earlier than 2007 I would be able to go to the Menu Bar and click Edit -> Select All to select all fields on the form. Now, my question is... where in 2007 did the put that functionality? If Microsoft removed it from there... where did they recreate it? Thanks! Squirrel "SQLSQUIRREL" <SQLSQUIRREL@discussions.microsoft.com> wrote in message news:333547A1-9C6A-422B-9CD5-97D79D6037DF@microsoft.com... > Hello... > > In versions earlier than 2007 I would be able to go to the Menu Bar and > click Edit -> Select All to se...

How to goto cell containing specific date
Thought I asked this before, but can't find the thread w/ my question or any replies... I have a worksheet wih a full year's dates in the cells running down a colum, with other data for each date in the the adjacent columns; Instead of scrolling up & down to a cell with a specific date I'm looking for, is there another way to goto a cell containing a specific date? (e.g., today(), or another specific date) In case this is pertinent: the date series begins with the entry of one date (e.g., 01/01/2010 in cell A1), with the dates in subsequent rows arrived at ...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

Can I get my Dialog's background color already? :) Just can't figure it out
I'm trying to get the background color for the basic Dialog created using the Wizard GetSysColor(COLOR_WINDOW) isn't working (returns some other color) GetBkColor() for the DC isn't working (returns white) Where can I trap that? The dc->GetBkColor in OnPaint returns a white color I finally did GetSysColor(COLOR_3DFACE) because I noticed they are the same, but that's not really what I want Thank you. "Yasoo" <anonymous@discussions.microsoft.com> wrote in message news:D9FF1E6B-C815-41B0-92FA-846EDC82FBAE@microsoft.com... > I'm trying to get the ba...

how do i get font style back on toolbar?
new to this...when i started my document the font style was displayed on toolbar...now it is not...what do i do and thanks to those who help!!!!!!1 Melissa, try view, toolbars, and check formatting -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "melissa" <melissa@discussions.microsoft.com> wrote in message news:852099EC-03A0-4B0F-91AD-0B9F61CF8C4B@microsoft.com... > new to this...when i started my document the fon...

Get Mallika Sherawath Bathing Videos in all Angles
Get Mallika Sherawath Bathing Videos At http://blogcreationandhosting.blogspot.com/2009/11/1-per-click-in-adsense-programs.html < Due to some issues,I have hidden these videos inside an image.In that website , click on the big vertical Image on right side of website & get the videos, Enjoy it > ...

OFFSET problems in dynamic range
Hi, I'm having the strangest problem with the OFFSET function. I have 5 dynamic ranges in my worksheet. Aimline =OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1) Sessions =OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1) WCPM =OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1) Date =OFFSET ('Weekly ORF'!Aimline,0,-1) Date2 =OFFSET ('Weekly ORF'!WCPM,0,-1) The problem is that the two names for Date and Date2 keep reverting to =OFFSET ('Excel Template.xls'!Aimline,0,-1) and =OFFSET (&#...

Designing a report based on a crosstabe
Hello! Hope someone can help cause this is getting into my nerves... I have a crosstabe that I run with a parameter for "date of sales". Date of Sale is my column and the results are showed per month. So I run the query and set the dates from 01/jan/07 to 31/july/07 and the query returns the 7 months I want (mm/yy). How could I design a report that that would run with various parameters? From what I understood so far, the field in the report must necessarily have the name of the result achieved (ex. 01/07, 02/07, 03/07...). Hope I made myself understandable... Cheers everyon...

Report Manager #4
Hi I have created a cusomised toolbar but when I customise the toolbar I cannot find "Report Manager" anywhere to add to it. The odd thing is that it is on the main menu but does not appear in the customisable dialogue box. Does anyone know where to find it, so I can put it on my toolbar. Cheers -- All help much appreciated. Thanks I'm guessing you created this customized toolbar manually (not by code). If that's the case and you have "report manager" under the existing tools menu already (or is it under Data???). Tools|Customize (just to see that dialog ...

mySQL datetime field reports as Long Integer with ODBC
I'm using Access 2003 as a reporting tool against a mySQL database, connecting with the mySQL ODBC driver 3.51.17. The ODBC driver reports every datetime field as a Long Integer in Access. This makes date operations problematic. I notice an interesting post in this forum from 2005 that would work fine ("Convert UNIX time to windows general date"), but using a function with every row seems like an unnecessary performance hit. Also, it seems counter-intuitive that the driver doesn't recognize the correct field type. The problem looks suspiciously like the mySQL bug rep...

Connecting Crystal Reports
I've created my ODBC connections and I can see in Crystal Reports my "Great Plains" connection. I know exactly which tables I want to pull my reports from but all I see are these ZDP_Tables. I believe these are stored procedure tables. For example I would like to use the CPY10100 (EE card table) but I see a few ZDP_CPY10100N_1;1, ZDP_CPY10100N_2;1, ZDP_CPY10100N_3;1 and so on. These tables have some of the columes I need but not all. Why would I know be able to see the table doesn't anyone have any idea? If you look at the screen in which you configure your data sour...