Prompt for date, then search two fields for results

[mabmabmab]

Hello, inexperienced Access user here.

I have a table with fields for dates which are StartDate and EndDate. How can I query this table to pull out records with dates that fall within this range. Example: a popup asking what date you are looking for, 01/15/03
record1 - StartDate 12/01/02 EndDate 12/31/02
record2 - StartDate 01/01/03 EndDate 01/31/03

How do I syntax the query criteria to give me record2 because it falls within that range???


Thanks...Mark

 

[bat17]

Welcome to the board

You can get a query to ask for criteria by entering a prompt in square brackets. In your case something like >=[Enter Date].
Unfortunately with this approach the user will be prompted for the date twice, once for the start and again for the end date.
A better way of doing it is to use unbound fields on a form and link the criteria of the query to these using something like.
>=[Forms]![MyForm]![MyField]
You can use the builder in the query to create these links to the form.
Normally you would create these fields in the form that you are using to launch the query.

HTH

Peter

 

[Mile-O-Phile]

Unfortunately with this approach the user will be prompted for the date twice, once for the start and again for the end date.

Not if you define the [Enter Date] parameter in the query editor.

I'd still go with referencing the form though.

 

[mabmabmab]

Thanks for the info, but not exactly what I am looking for:
Here is an example of my data in a table. How would i set up a query to ask for a date the user wants then display that record?
Example: User may want data from 3/2/2001 'the date entered'. Then have Access return record ID 117 because 3/2/2001 falls within that range.

ID Case MastersFromDate MastersThroughDate
115 123577 11/01/2000 12/31/2000
116 193923 01/01/2001 02/28/2001
117 193924 03/01/2001 04/30/2001
118 193925 05/01/2001 06/30/2001
119 098526 07/02/2001 07/13/2001

Thanks...
[/code]

 

[bat17]

I am not sure what you are asking for that we have not covered? If you create criteria for your start and end date fields, getting the date either from a form or by setting the parameter in the query itself, this will return what you want.

HTH

Peter

 

[notoca]

I think what he is saying is that he only wants the user to enter a date once and then have the query search two different fields and see if the date falls within the range of two fields.

I'd like to do something similar where a user enters a keyword and then the query searches 2 fields to find any occurrences.

Thanks

 

[Dr_Worm]

In your query put;
>=[Forms]![MyForm]![MyField] for the start date, and <=[Forms]![MyForm]![MyField] for the end date.

 

[Mile-O-Phile]

In your query put;
>=[Forms]![MyForm]![MyField] for the start date, and <=[Forms]![MyForm]![MyField] for the end date.

Between [Forms]![MyForm]![StartDate] And [Forms]![MyForm]![EndDate]

fills that function. Why overcomplicate matters?

Anyway, by using a defined parameter in the query it will only ask once for a date.