Question which function can I use?

[movaheditabrt]

Hi there!
I'm really new to access. I'm using access 2007. I've studied about table,query and form and know nothing about macro and vb. thus, please help me regarding my knowledge of access.I have three tables: agentstbl : [agent-ID] , [name] , [sale-id] , [distance] , [town] , [impassable] , etc
pricetbl : [from date] , [to date] , [saleID] , [city] , [difficult-pass] , [from distance] , [to distance] , [price]
billtbl : [billno] , [date-f] , [agent-ID] , [name] , [t-price] , etc.
values of [town] , [impassable] , [city] , [difficult-pass] are 0 and 1 and 2.
in order to [t-price] be filled automatically in a form , these criteria must take place in query :
[sale-id]=[saleID] , [town]=[city] , [impassable]=[difficult-pass] , [date-f] between [from date] and [to date] , [distance] between [from distance] and [to distance].
how can I do so? which function and what is it like(the expression)?
thank you

 

[Galaxiom]

Unfortunately I don't really understand your question but I would advise you change your naming system. Don't include spaces or special characters. With these you must always use the square brackets around the name. Use CamelCase names instead.

I would also recommend DateTo and DateFrom, DistanceTo and DistanceFrom. This groups the names better in Intellisense when you start writing VBA.

Avoid Reserved Words for names. "Name" is a particularly bad one to choose for a field name.

 

[movaheditabrt]

Thanks for your reply. I changed field names. Let you know that:​

We have many agents that distribute our product. The price of product is variant according to Sale-ids of agents, cities of agents, the condition of the road (difficult or easy to pass), distances of agents from our company, and the date of sale. I design a form based on a query. When our operators type the name of an agent and the date in the form, the desirable price must automatically come in its place in the form.​

So, I made this form based on a query. In the query, I added these tables and drag necessary fields to the grid and inserted a new column that the expression is located, Which must look for the price? My problem is: which function can I use and what its expression is look like?​

Thanks again.​

 

[Galaxiom]

Still not clear but perhaps you would need to use a DLookUp function. These are used to look up a field value in a table or query under a given set of conditions for fields in that table or query.

 

[movaheditabrt]

I wrote in the query this expression:
tPrice: IIf([SaleID]= 6001 Or 6003 Or 6004; DLookUp(IIf([Datefrom] <= [datef] <= [Dateto];DLookUp(DLookUp(IIf([distancefrom] <= [distancef]<=[distanceto];DLookUp([price];"pricetbl";[distancefrom] <= [distancef]<=[distanceto]);1);"pricetbl";[sale-id]=[saleID]);"pricetbl";[Datefrom] <= [datef] <= [Dateto]);1);"pricetbl";[city]=[town]);0)
it works , but it takes too much time to run the query. what is the solution?