Query to show items before a nominated Date

[David Ball]

Hi,

I have a database table of Forecast Delivery Dates.
I want to set up a Query to show items that have a forecast delivery date before a certain date. I want to be able to nominate that "certain date" on the fly.

I have tried to set up a "Date" field where I can enter a date as a parameter, then have a calculated field to subtract the Forecast dates from the date entered.

This returns an error. I think it is because the date returned by my new field doesn't seem to be in date format. I can't find any way to set this to date type.

How can I make my new date field return results as dates?

Is there a better, more direct way to do this?

I tried:

InvalidDates: [ForecastDate]<[Enter Date]

where "ForecastDate" is the field for my delivery date forecasts and "Enter Date" generates a parameter box where I enter a date. This didn't work either (maybe the date I'm entering into the parameter box is not showing as a date?).

Thanks very much for any help with this.

Dave

 

[John W. Vinson]

Hi,

I have a database table of Forecast Delivery Dates.
I want to set up a Query to show items that have a forecast delivery date before a certain date. I want to be able to nominate that "certain date" on the fly.

I have tried to set up a "Date" field where I can enter a date as a parameter, then have a calculated field to subtract the Forecast dates from the date entered.

This returns an error. I think it is because the date returned by my new field doesn't seem to be in date format. I can't find any way to set this to date type.

How can I make my new date field return results as dates?

Is there a better, more direct way to do this?

I tried:

InvalidDates: [ForecastDate]<[Enter Date]

where "ForecastDate" is the field for my delivery date forecasts and "Enter Date" generates a parameter box where I enter a date. This didn't work either (maybe the date I'm entering into the parameter box is not showing as a date?).

Thanks very much for any help with this.

Dave

You can use a Form with a textbox, frmCrit and txtDate let's say, and use a
Query with a criterion

< [Forms]![frmCrit]![txtDate]

It's often helpful to define this in the query's Parameters collection:
rightclick the background of the tables window in query design; select
Parameters; copy and paste [Forms]![frmCrit]![txtDate] in the left column of
the grid and specify Date/Time in the right. You certainly should not be
creating a new table field to do this.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com