Error on date parameters

[bibbyd01]

Hi

I've got a query that lists leased items with a start and end date. I'm trying to produce a result where I can type in the start date and end date to find out when the lease is up, but I'm having a few errors coming up.

I'm trying to use the field 'Expected Termination' and search for a range of dates, so I'm using the criteria >=[StartDate] or <=[EndDate] (and I've tried 'Between [Startdate] and [Enddate]) where you enter the parameters when the query loads. I'm using the short date format in the table, but when I run this query and enter the dates I get the message;

This expression is typed incorrectly, or it is too comples to evaluate etc.

What am I doing wrong here?

 

[Galaxiom]

Post the sql view of your query.

 

[bibbyd01]

PARAMETERS StartDate Text ( 255 ), EndDate Text ( 255 );
SELECT Vehicles.Registration, Vehicles.Type, Vehicles.Make, Vehicles.Descripton, Vehicles.Depot, Vehicles.Leased, Vehicles.Termination, Vehicles.Service, Vehicles.Total, Vehicles.Lease, Vehicles.CC, Vehicles.Term, Vehicles.[Personal Use], Date() AS Today, Vehicles.ExpectedTermination
FROM Vehicles
WHERE (((Date()) Between [leased] And [expectedtermination]) AND ((Vehicles.ExpectedTermination)>=[StartDate])) OR (((Vehicles.ExpectedTermination)<=[EndDate]));

 

[Galaxiom]

If the StartDate and EndDate parameters are set up as text then the comparison with a date formatted field will fail.

Dates are held as a numbers not text. A date is the number of days since the end of the 19th century. Times are fractional parts of days. They only appear as dates and times through the formatting of the display.

Incidentally if you do have to compare a date in sql with a text field holding a date you must define the text as a date in the format #MM/DD/YYYY# regardless of the system settings.