Form based parameter query

Question

I have created an unbound form (frmInvParameters) with controls txtDepart_FROM, txtDepart_TO (with Data Type of Short Date) and txtWhich_Venue and a button to run the query qryInvs.

In qryInvs I have put Between [Forms]![frmInvParameters]![txtDepart_FROM] And [Forms]![frmInvParameters]![txtDepart_TO] in criteria of DepartDate field and Like "*" & [Forms]![frmInvParameters]![txtWhich_Venue] & "*" in the Venue field's criteria. Show check boxes have a tick in each.

But on entering control values on the form and running the query I am being asked to "Enter Parameter Value" for txtDepart_From, txtDepart_TO and txtWhich_Venue.  If I simply click OK on these dialog boxes without entering anything the query runs and correct results are produced.  If I enter values in these boxes same as or different to those on the frmInvParameters they are ignored and query produces results per the settings in the form.

 Is there some way to stop those dialogs appearing as they are not necessary?

Answer 1

I don't understand what you mean by fully qualified.  This is what I have in the Criteria and seems to be in line with what you are suggesting:

Between [Forms]![frmInvParameters]![txtDepart_FROM] And [Forms]![frmInvParameters]![txtDepart_TO]

Is this not how it should be?

Answer 2

Unless you really wish to do substring matching on the VenueCode column, rather than simply making the venue parameter optional, I would also recommend that you change:

((Venues.VenueCode) Like "*" & [Forms]![frmInvParameters]![txtWhich_Venue] & "*")

to:

(Venues.VenueCode =  [Forms]![frmInvParameters]![txtWhich_Venue]

OR [Forms]![frmInvParameters]![txtWhich_Venue] IS NULL)

The LIKE operator does not allow for Nulls at the Venue column position, and any such rows would not be returned.  Also, the above syntax allows the optimizer to make use of the index, which will benefit performance.

If you do the above you should also omit the [Forms]![frmInvParameters]![txtWhich_Venue] parameter from the PARAMETERS clause.  A parameter declared as TEXT can never evaluate to NULL.  Using a combo box rather than the txtWhich_Venue control in the form would make for greater accuracy, as the user would then be constrained to entering or selecting a value which exists in the database.

Answer 3

I would chang e the PARAMETERS to be fully qualified with [Forms]![frmInvParameters]!

Answer 4

This is the SQL, I have removed some of the fields which are not relevant:

PARAMETERS [txtDepart_FROM:] DateTime, [txtDepart_TO:] DateTime, [txtWhich_Venue] Text ( 255 );

SELECT DISTINCTROW Bookings.VenueID, Bookings.ArrivalDate, CVDate(DateAdd("d",Nz([Nights],0),Nz([ArrivalDate],0))) AS DepartDate, Venues.VenueCode, Bookings.ReservataionID, Bookings.VenueRef, Bookings.Nights, Bookings.CustomerID, Bookings.Guest, Venues.VenueName, [TAXrate].[stdTAXrate],0) AS TAXr, Bookings.DateCancelled

FROM TAXrate, Venues INNER JOIN Bookings ON Venues.VenueID = Bookings.VenueID

WHERE (((CVDate(DateAdd("d",Nz([Nights],0),Nz([ArrivalDate],0)))) Between [Forms]![frmInvParameters]![txtDepart_FROM] And [Forms]![frmInvParameters]![txtDepart_TO]) AND ((Venues.VenueCode) Like "*" & [Forms]![frmInvParameters]![txtWhich_Venue] & "*") AND ((Bookings.DateCancelled) Is Null)

ORDER BY Bookings.VenueID, Bookings.ArrivalDate, Bookings.ReservationID;

Answer 5

Please show the entire SQL from that query. Sometimes that helps spotting unanticipated problems that might otherwise be overlooked.