Data look up

[bob]

Hi



We are receiving data in a new format, where as before we would have one
registration number which was a unique reference. This registration number
now appears for each policy year. So we could have the same registration
number several times on the downloads we receive.



The registration Number is in the field txtRegNo



And the policy year is in the field txtPolicyTNo



How do I now look up the registration but also find the line of date with
the correct registration number and the highest transaction number in the
field txtPolicyTNo



The code I use at present is as follows:



Dim ExistingReg As Variant



ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers", "[txtRegNo]='" &
_ Me.Registration_Number & "'")



If Not IsNull(ExistingReg) Then

Me.Policy_Number = ExistingPolicyNo



How do I modify this data to lookup the txtPolicyTNo?



Thanks



Bob

 

[KARL DEWEY]

Try this --

WHERE (txtRegNo = [Enter registration number]) AND txtPolicyTNo = (SELECT
Max([XX].[txtPolicyTNo]) FROM YourTableName AS [XX] WHERE [XX].[txtRegNo] =
YourTableName.[txtRegNo])