Date Queries within Travel Database

[John Buckingham]

Hi ....calling all access experts!

I am responsible for maintaining a UK based travel database which retains
comprehensive information about overseas journeys carried out by academics
and students. Each traveller submits details of their journey via an online
travel notification form on the University's intranet site, and this data is
input into the database.

I have a very basic understanding of search criteria within database
queries, but need to know how to obtain a snapshot of those journeys in
progress by searching on two input box parameters "date" and "country".

There are various reasons for needing to do this, but obvious examples; to
identify a persons whereabouts at the time when new political unrest occurs
in a particular country; or to see who might need to rearrange their
transport due to a forthcoming air traffic control or ferry/port dispute.

The date search box is a "must have" input, but the destination country
search box could be left blank so the query generates results based on a
worldwide search of all journeys.

The key fields to be searched in this query are "departure date", "return
date" and "destination country". Search results should find journeys where
the selected date falls within the two date periods so is equal to or greater
than the "departure date", and equal to or less than the "return date".

Hope this all makes sense, but please ask questions if not! I should be most
grateful for any suggested ideas on useable criteria expressions and against
which field names these expressions need to be entered.

Many thanks from a novice user!

John

 

[John Spencer]

You could use the following where clause if the country field always has a
value and if the two date fields always have values.

WHERE [Date Input] Between [Departure Date] and [Return Date]
AND Country like Nz([Enter Country],"*")

That may not be the most efficient since any indexes might not be used by the
date criteria, so the following may work better.

WHERE [Departure Date] <= [Date Input] and [Return Date] >= [Date Input] AND
Country like Nz([Enter Country],"*")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Beetle]

Your best option here may be to create a separate search form
(i.e. frmTravelSearch). You could have an unbound text box (txtDateSearch)
where the users would enter a date to search for. As far as the Country, that
depends on the structure of your tables. In a normalized application, the
table that stores information about the travel details would not store the
country name but rather the key value from a Countries table (ie. CountryID).
In this case you would need an unbound combo box (cboCountrySearch)
with properties like;

Row Source - Select CountryID, CountryName From tblCountries
Order By CountryName
Bound Column - 1
Column Count - 2
Column Widths - 0cm, 8cm (or whatever works best for the width)

The above combo box would hold the CountryID but display the
CountryName. On the other hand, if your travel details table does
store the actual country name, then you can just use a text box
(txtCountrySearch).

You would then create a query of the table fields you need returned in
your results. The query would use criteria like;

criteria for DepartureDate -
<= Forms!frmTravelSearch!txtDateSearch.

criteria for ReturnDate -

= Forms!frmTravelSearch!txtDateSearch

criteria for CountryID -
Forms!frmTravelSearch!cboCountrySearch
(modify the above if the travel table stoes the country name)

You could also, for example, place a continuous or datasheet subform
within your search form. The subform would use the query as a
record source and would display the results. You would need a command
button that requeries the subform after the criteria is entered in
txtDateSearch and cboCountryID

This assumes that the ReturnDate field will not be Null. If so, those records
would not be returned by a query like this. In that case the criteria for
the ReturnDate field would need to be modified like;

= Forms!frmTravelSearch!txtDateSearch OR [ReturnDate] Is Null

 

[John Buckingham]

Hi Sean

Thanks very much for this. I will give it a try and let you know how I get on.
John

Your best option here may be to create a separate search form
(i.e. frmTravelSearch). You could have an unbound text box (txtDateSearch)
where the users would enter a date to search for. As far as the Country, that
depends on the structure of your tables. In a normalized application, the
table that stores information about the travel details would not store the
country name but rather the key value from a Countries table (ie. CountryID).
In this case you would need an unbound combo box (cboCountrySearch)
with properties like;

Row Source - Select CountryID, CountryName From tblCountries
Order By CountryName
Bound Column - 1
Column Count - 2
Column Widths - 0cm, 8cm (or whatever works best for the width)

The above combo box would hold the CountryID but display the
CountryName. On the other hand, if your travel details table does
store the actual country name, then you can just use a text box
(txtCountrySearch).

You would then create a query of the table fields you need returned in
your results. The query would use criteria like;

criteria for DepartureDate -
<= Forms!frmTravelSearch!txtDateSearch.

criteria for ReturnDate -

= Forms!frmTravelSearch!txtDateSearch

criteria for CountryID -
Forms!frmTravelSearch!cboCountrySearch
(modify the above if the travel table stoes the country name)

You could also, for example, place a continuous or datasheet subform
within your search form. The subform would use the query as a
record source and would display the results. You would need a command
button that requeries the subform after the criteria is entered in
txtDateSearch and cboCountryID

This assumes that the ReturnDate field will not be Null. If so, those records
would not be returned by a query like this. In that case the criteria for
the ReturnDate field would need to be modified like;

= Forms!frmTravelSearch!txtDateSearch OR [ReturnDate] Is Null

--
_________

Sean Bailey

Hi ....calling all access experts!

I am responsible for maintaining a UK based travel database which retains
comprehensive information about overseas journeys carried out by academics
and students. Each traveller submits details of their journey via an online
travel notification form on the University's intranet site, and this data is
input into the database.

I have a very basic understanding of search criteria within database
queries, but need to know how to obtain a snapshot of those journeys in
progress by searching on two input box parameters "date" and "country".

There are various reasons for needing to do this, but obvious examples; to
identify a persons whereabouts at the time when new political unrest occurs
in a particular country; or to see who might need to rearrange their
transport due to a forthcoming air traffic control or ferry/port dispute.

The date search box is a "must have" input, but the destination country
search box could be left blank so the query generates results based on a
worldwide search of all journeys.

The key fields to be searched in this query are "departure date", "return
date" and "destination country". Search results should find journeys where
the selected date falls within the two date periods so is equal to or greater
than the "departure date", and equal to or less than the "return date".

Hope this all makes sense, but please ask questions if not! I should be most
grateful for any suggested ideas on useable criteria expressions and against
which field names these expressions need to be entered.

Many thanks from a novice user!

John