By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,510 Members | 1,821 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,510 IT Pros & Developers. It's quick & easy.

Running a query in expression builder

P: 10
I would like to take the values of two combo boxes in a form, pass them to a query, and if there is a result display that in a text box in the form, i would like to do this in expression builder if possible, and if it is if there is no result fromt he query can you display some text such as "not valid", here is how i thought it might work but didnt:
Expand|Select|Wrap|Line Numbers
  1. =( [Flights Query]![SourceID] = [Source ID], [Flights Query]![DestinationID] = [DestinationID] ) SELECT «Expr» [Flights Query]![FlightID] 
The flightID is what i would like to be the result if the SourceID and DestinationID exist as a flight.

Thanks

Nathan
Mar 14 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. =nz(DLookup("[FlightID]", "Flights Query", "[SourceID]=" & [SourceID] & " AND [DestinationID]=" & [DestinationID]),0)
This will return 0 if no FlightID is found. If FlightID is a number you can't have a text value like "not valid"

Mary
Mar 14 '07 #2

P: 10
Arghh i got a little ahead of myself i think im having trouble with this
Expand|Select|Wrap|Line Numbers
  1. SELECT flights.SourceID, AirportName FROM Airports ORDER BY Airports.AirportName; 
I am trying to make a Combo Box display the names of the Airports, Airports have an AirportID as Primary key and then within flights SourceID is a foreign key attached to AirportID, so i am trying to make the destination selected by name become SourceID

Thanks
Mar 14 '07 #3

P: 10
Corrected, it should be AirportID it finds and holds as the field value

Arghh i got a little ahead of myself i think im having trouble with this
Expand|Select|Wrap|Line Numbers
  1. SELECT Airports.AirportID, AirportName FROM Airports ORDER BY Airports.AirportName; 
I am trying to make a Combo Box display the names of the Airports, Airports have an AirportID as Primary key and then within flights SourceID is a foreign key attached to AirportID, so i am trying to make the destination selected by name become AirportID i think

Thanks
Mar 14 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Corrected, it should be AirportID it finds and holds as the field value
Sounds about right.
Mar 14 '07 #5

P: 10
i have control source as:
Expand|Select|Wrap|Line Numbers
  1. =[Flights]![DestinationID]
and Row Source as;
Expand|Select|Wrap|Line Numbers
  1. SELECT Flights.DestinationID, Airports.AirportName FROM Airports INNER JOIN Flights ON (Airports.AirportID=Flights.ViaID) AND (Airports.AirportID=Flights.DestinationID) AND (Airports.AirportID=Flights.SourceID) ORDER BY Airports.AirportName; 
The combobox shows all the airport as is wished but when u select an airport it says: control cannot be edited, it is bound to the expression [Flights]![DestinationID], am i just missing something that I shoudl be doing thats really obvious?
Mar 14 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
i have control source as:
Expand|Select|Wrap|Line Numbers
  1. =[Flights]![DestinationID]
and Row Source as;
Expand|Select|Wrap|Line Numbers
  1. SELECT Flights.DestinationID, Airports.AirportName FROM Airports INNER JOIN Flights ON (Airports.AirportID=Flights.ViaID) AND (Airports.AirportID=Flights.DestinationID) AND (Airports.AirportID=Flights.SourceID) ORDER BY Airports.AirportName; 
The combobox shows all the airport as is wished but when u select an airport it says: control cannot be edited, it is bound to the expression [Flights]![DestinationID], am i just missing something that I shoudl be doing thats really obvious?
You cannot set the control source to a reference like that

The control source should be just DestinationID and this should be available in the table or query that is bound in the Record Source of the form.

Mary
Mar 14 '07 #7

P: 10
Sorry have sorted that now:)

But i do have another thing i am struggling with instead,
I have this to find the flight ID, is it possible for me to make this into a combo box that runs the query and if there are multiple results you can choose from them with flightDate displayed next to the flight number?;

Expand|Select|Wrap|Line Numbers
  1. =nz(DLookUp("[FlightID]","Flights Query","[SourceID]=" & [SourceID] & " AND [DestinationID]=" & [DestinationID]),0)
Thanks
Nathan
Mar 14 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry have sorted that now:)

But i do have another thing i am struggling with instead,
I have this to find the flight ID, is it possible for me to make this into a combo box that runs the query and if there are multiple results you can choose from them with flightDate displayed next to the flight number?;

Expand|Select|Wrap|Line Numbers
  1. =nz(DLookUp("[FlightID]","Flights Query","[SourceID]=" & [SourceID] & " AND [DestinationID]=" & [DestinationID]),0)
Thanks
Nathan
Nathan

The DLookup won't work for multiple returns. Set up your combo box and set the Row Source to ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Me.Combobox.RowSource = "SELECT FlightID, FlightDate FROM [Flights Query] WHERE SourceID=" & Me.SourceID & " AND [DestinationID]=" & Me.DestinationID
This will have to be done using code in the AfterUpdate event of the SourceID or DestinationID textboxes. Have a look at this tutorial.

Cascading Combo/List Boxes

Mary
Mar 14 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.