Creating Date Sections on a form that link to a query

[accessnewbie7]

Hi there,

I'm moving steadily through the creation of my first database but have become stuck (once again!)

I have created a 'Main Page' form. On this main page i have a command button which is linked to a report, which is linked to a query. When i press the button it uploads the report, not before my start date and end date parameters (which are in my query) pop up. This is what i want, so that the data can be filtered before the report is produced however i wondered if there is a way for the user to input the start date and end date on the main page prior to clicking the command button. this would then cut out the pop up boxes. i would also like the little calendars to pop up on the main page so that the date is formatted correctly.

I hope i have explained this well!!

Thank you

 

[Trevor G]

On your main form, add two new textbox and name each one through there properties txtStartDate and txtEndDate change the format property to dd/mmm/yyyy which will add a calendar when you change the view and click in one of the textbox.

Then go to the query and change the between[Start Date]And[End Date] to look for the form and then the textboxes, so something like this

Between Forms![frmSearchDates]![txtStartDate] And Forms![frmSearchDates]![txtEndDate]

Then save the query then when you add the dates you can use the command button to open your report

 

[accessnewbie7]

Trevor,

Thank you for the quick response. i have carried out the instructions as below but i still get a Parameter Value pop up box when i click on the report button. In the query design view i have cleared the parameters so am confused once again!!

 

[DCrake]

You may have removed them from the grid but have you actually removed the paramters?

Right click on the top gray pane in design view of your query and from the schortcut menu select parameters. Is there any in there? if so, delete them.

 

[accessnewbie7]

David,

Yes i have removed them both from there but the Parameter Value pop-ups keep appearing.

Thanks

Sy

 

[accessnewbie7]

This is what i have pasted into the criteria section of my Date Column in the query.

Between [Forms]![frmFront Page]![txtStartDate] And [Forms]![frmFront Page]![txtEndDate]

 

[DCrake]

Is the form open when you run the report? It must be open so that the report/query can refer to the contols on the form for the filter condition.

 

[Trevor G]

Please check the Report Data Source so that it is using the query you have altered.

 

[accessnewbie7]

David - Yes the form is open as the button to generate the report is after the two date text boxes have been filled in.

Trevor - I must be completely behind the curve with access, how do i check the report data source because when i go to properties the option is not there?

 

[accessnewbie7]

Trevor - i figured out how to see the source, it is the right query yet the issue is still ongoing. maybe i should delete it and start again?

 

[Trevor G]

Why not upload a sample of the database then and someone will take a look. It must be less than 2 meg big so zip a copy up.

 

[accessnewbie7]

Trevor - i am unable to upload a copy of the database in its current form, apologies for being rude but the data within the database is sensitive as are a number of the tables created. If its not an easy fix i will have to leave it with the pop-up boxes, i was just trying to get it looknig more proffesional and i dont think the pop-up boxes look best ever. Thank you though for your assistance though which i hope i can further lean on if i have any other queries.

 

[Trevor G]

I understand about sensitivity and hope the query/report can be sorted.

In the query window have you checked the Parameter Feature to ensure you haven't left anything in there related to dates.

Can you do a screen shot of the query window in design view (no data to be shown that way) and then upload a picture of the query window.