Enter date and find records

[NeilPattison]

I need to create a query so that I can run a report that will show records entered between certain dates. For instance lets say

tblTMS has fields IDNo, DateReceived, Name, Address

tblWeeks has fields StartDate, EndDate, WeekNo

(all dates are in the format dd/mm/yyyy).

I want the user to be able to enter a date (possibly by a popup txtbox when trying to run the report)and for it find what week number it relates to in tblWeeks and also what records from tblTMS has entries in the TblTMS.DateReceived between the tblWeeks.StartDate and tblWeeks.EndDate of that particular week.

So the output of the query so show records in the following order.

tblWeeks.WeekNo
tblTMS.IDNo
tblTMS.Name
tblTMS.Address

I've been trying to do this but for some reason can't figure it out. I have done this kind of thing before but now can't get my head around it.

Any help would be greatly appreciated

 

[alvechurchdata]

A simple way would be to use a parameter query. There are examples in Access Help.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[dhookom]

IMHO, parameter prompts are never good user interface. I always use controls on forms for entering criteria. Typically to run a report, I would have a form with text boxes:
Name: txtStartDate
Format: Short Date

Name: txtEndDate
Format: Short Date

A command button would open the report. The code for the command button would build a WHERE CONDITION to use in the DoCmd.OpenReport method.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]