Query Future Dates Only

[Sharky II]

Help with combobox query to only show trips > Date()

Hi guys

In a combo box in my db you can select a trip from it, but i want it to only show trips where date of departure is greater than todays date... but also it needs to only be filled ONLY IF the fhe field DateOfDeparture is NOT NULL - otherwise when you're viewing old trips (before todays date), there will be nothing in the date of departure box....

here's my sql WITHOUT my attempt to do this (remember it's for a combo box):

SELECT tblTrips.TripID, tblTrips.TripDestination, tblTrips.TripReferenceNumber, tblTrips.DateOfDeparture, tblTrips.DateOfReturn
FROM tblTrips
WHERE (((tblTrips.TripType)=[Forms]![frmTripDetail4]![TripType]))
ORDER BY tblTrips.TripDestination, tblTrips.DateOfDeparture;

But when i enter "IF NOT NULL > Date()" or "IF [tblTrips].[DateOfDeparture] NOT NULL > Date()" or other variations it doesn't work. Here's what happens in one case:

SELECT tblTrips.TripID, tblTrips.TripDestination, tblTrips.TripReferenceNumber, tblTrips.DateOfDeparture, tblTrips.DateOfReturn
FROM tblTrips
WHERE (("IF NOT NULL">Date()) AND ((tblTrips.TripType)=[Forms]![tblTripDetail4]![TripType]))
ORDER BY tblTrips.TripDestination, tblTrips.DateOfDeparture;

The problem is i don't have enough knowledge of the SQL syntax to work this on out - can anyone lend a hand? Cheers

Cheers again

 

[FoFa]

You have to break them apart as seperate criteria, SO it would be
[DateOfDeparture] IS NOT NULL AND [DateOfDeparture] > Date() AND tblTrips.TripType=[Forms]![tblTripDetail4]![TripType]

Parens removed for readability

 

[Sharky II]

cheers man... i can't use the criteria box etc as it only does OR's....

i can't get the brackets right, can anyone tell em what i'm doing wrong...:

SELECT tblTrips.TripID, tblTrips.TripDestination, tblTrips.TripReferenceNumber, tblTrips.DateOfDeparture, tblTrips.DateOfReturn
FROM tblTrips
WHERE ((([tbl.trips].[DateOfDeparture] IS NOT NULL) AND ([tbl.trips].[DateOfDeparture] > Date())) AND ((tblTrips.TripType)=[Forms]![tblTripDetail4]![TripType]))
ORDER BY tblTrips.TripDestination, tblTrips.DateOfDeparture;

Thanks... i'm playing with it now to try to get it working

Thanks again

Edd

 

[Sharky II]

When i try

SELECT tblTrips.TripID, tblTrips.TripDestination, tblTrips.TripReferenceNumber, tblTrips.DateOfDeparture, tblTrips.DateOfReturn
FROM tblTrips
WHERE (((tblTrips.DateOfDeparture)="IS NOT NULL") AND (((tblTrips.DateOfDeparture)>Date())) AND ((tblTrips.TripType)=[Forms]![tblTripDetail4]![TripType]))
ORDER BY tblTrips.TripDestination, tblTrips.DateOfDeparture;

it says

"the expression is typed incorrectly, or is too complex to be evaluated. for example, a numeric expression may contain too many complicated elements. Try simplyfying the expression by assigning parts of the expression to variables"

 

[Jon K]

Change ="IS NOT NULL" to Is Not Null, i.e. remove the = sign and the quotes.


In fact, when you specify DateOfDeparture > Date(), the Null dates will be automatically excluded. And since only one table is involved in the query, there is no need to type the table name in front of the field names.

So this simplified one should also work:-

SELECT TripID, TripDestination, TripReferenceNumber, DateOfDeparture, DateOfReturn
FROM tblTrips
WHERE DateOfDeparture > Date() And
TripType = [Forms]![tblTripDetail4]![TripType]
ORDER BY TripDestination, DateOfDeparture;

 

[Sharky II]

Hi

No mate, when i do this, the old dates fields are still not filled in for the old trips! I should mention that i'm using cascading combo boxes.

There is a box, trip type, you select a trip type in the combo box i've been working on above..... then you select the trip from there (the box comes up with date of dept, date of return, reference number etc) and then the dates are filled in according which trip was selected. now i only wanna show the trips in the future (not every trip ever)... like i say, when i try the code you (kindly) gave, and then try to view the trips with dates eariler than todays, the dates and trip destination are blank....

can't fix this annoying problem!!!!

any help would be loved, i'm really stuck, and i think the nightmare of this database is nearly over... apart from this damn problem!

cheers

 

[Sharky II]

damn... no one able to crack it?

 

[Sharky II]

I want the old trips to show on the form, but not to be shown when the combo box is expanded. combo box should only show future trips.

 

[Sharky II]

i think the problem lies a bit deeper and is a bit to complicated for me to explain here (no one will read it), you have to see it to get it.

thanks again

 

[Sharky II]

but i WANT the combo to look empty for those rows?

i want the date and text fields which get filled in upon selection of hte combo box (afterupdate) to stay.

 

[Sharky II]

don't worry i've sorted it

many thanks