Retrieve value based on multiple criteria

[Sanasta]

I have 2 tables: "Letter_to_Insurer" and "Policy_OP" and query based on the "Letter_to_Insurer". I need to have one field in this query, which to find the policy number of concrete truck in the concrete time interval. I tried DLookup (in query and in the form:
PolicyNoLookUp("[Policy]";"Policy_OP";"[Truck1]=[Truck]" And "[Event_Date]>=[From]" And "[Event_Date]<=[To]")
Where fields [Policy], [From], [To] and [Truck] belong to "Policy_OP" but [Truck1] and [Event_Date] - ro "Letter_to_Insurer".
The result is not correct. Because I am not qualified enough I can't cope with this task. Please help me!
Sanasta

 

[HarleyQuinn]

Could you show us your table structure, how they relate and an example of your expected output please to aid us in helping you?

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Sanasta]

Sorry I have not been clear.
Table: Letter_to_Insurer
Key No Number
Date Date/Time
Insurer Text
Title Text
Name Text
Event Date Date/Time
Policy No Text
OurRef Text
Sum Number
Truck1 Text
Table: Policy_OP
Key ID_OP AutoNumber
Policy Text Insurance policy
Truck Text
From Date/Time
To Date/Time
Fields [From] and [To] determine the validity period of insurance policy. I have to receive Policy for the concrete truck, and the event_date for this truck to be between [From] and [To}

 

[HarleyQuinn]

Thanks for posting that, would you also be able to post a sample of your desired out put from this query please?

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

This is a guess aty what you want from what you've said (and I've had to guess the join but how about something like

SELECT l.[Policy No]
FROM Letter_to_Insurer AS l INNER JOIN Policy_OP AS p ON l.Truck1 = p.Truck
WHERE ((([l.Event Date]) Between [p].[From] And [p].[To]));

If I'm well out for what you want, sorry about that.

Hope this helps


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Sanasta]

I have 2 tables as have specified before. I would like to write a letter to Insurer (report) based on a query (I have a form based on this query as well). When I select the truck and write the Event_date, Policy No automatically to be returned (now I write this number /over 16 characters/ notwithstanding I have it in Policy_OP table. I have created in the form unbound text box and have written in it formula
=DLookUp("[Policy]";"Policy_OP";"[Avtovoz1]=[Avtovoz]" And "[Event_Date]>=[From]" And "[Event_Date]<=[To]"). The result is not correct.
You shall find bellow part of table Policy_OP content: Trucks are more than 25.

ID Policy Truck From To
1 3440060250R00001 C 0847 MB 01.04.06 31.03.07
8 3440060250R00001 C 1503 HX 01.04.06 31.03.07
11 3440060250R00004 C 2579 XC 09.05.06 08.05.07
20 3440060250R00006 C 6250 XC 21.05.06 20.05.07
32 3440070250R00001 C 0847 MB 01.04.07 31.03.08
39 3440070250R00001 C 1503 HX 01.04.07 31.03.08
42 3440070250R00001/1 C 2579 XC 09.05.07 31.03.08
51 3440070250R00001/2 C 6250 XC 21.05.07 31.03.08
63 3440080250R00001 C 0847 MB 01.01.08 31.12.08
70 3440080250R00001 C 1503 HX 01.01.08 31.12.08
72 3440080250R00001 C 2579 XC 01.01.08 31.12.08
77 3440080250R00001 C 6250 XC 01.01.08 31.12.08

 

[PHV]

What are [Avtovoz1], [Avtovoz] and [Event_Date] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

PHV,

Table: Letter_to_Insurer ...
Event Date Date/Time

As for [Avtovoz] I'd be guessing [truck]

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Sanasta]

[Avtovoz1], [Avtovoz] and [Event_Date]

Avtovoz is Truck on my nativ language
Event_Date is the date when a damage has been found

 

[HarleyQuinn]

How about:

[green][i]'Cheers Remou[/i][/green]
=DLookUp("[Policy]";"Policy_OP";"[Avtovoz1]='" & [Avtovoz] & "' And #" & Format([Event_Date],"yyyy/mm/dd") & "# Between [From] And [Event_Date]<=[To]")

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Sanasta]

It don't accept it. And I don't understand why because I'm not qualified enough

 

[HarleyQuinn]

Does it error?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Try taking the = off the start, that stopped it being accepting on my test. If that doesn't work try this

DLookUp("[Policy]";"Policy_OP";"[Avtovoz1]=[Avtovoz]"  And #" & Format([Event_Date],"yyyy/mm/dd") & "# Between [From] And [To]")

Textbox didn't seem to like the = at the start for some reason.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Sanasta]

Thank you Harleyq
Tomorrow I shall try it. Hope it works.
Sanasta

 

[Sanasta]

It gives me message: The expression you entered has an invalid date value". I have tried to change it but without success.