Selecting a record on values in two fields 1

[tamus121]

Hi
I am lookoing up a record in a table with
s = DLookup("[ShareNumbersTo]", "CertificateDetails", "[ShareNumbersTo]=(Select Max([ShareNumbersTo]) from [CertificateDetails])")

and this works ok to a point but I want the largest share number for only the selected type of share. So If I have ShareType = "O", I want the largest share number for shares of [Tpye] O. I can't get this work when I try to inculde "[Type] = ShareType" in the above statement. Any sugestions would be great.

 

[randy700]

Perhaps (not tested)

Select Max([ShareNumbersTo]) from [CertificateDetails] [b][COLOR=red]WHERE ShareType = 'O'[/color][/b])

Randy

 

[tamus121]

[Type] is the name of the field in CertificateDetails

So I have tried

ShareType = "O"

or other depending on option selected by user

s = DLookup("[ShareNumbersTo]", "CertificateDetails", "[ShareNumbersTo]=(Select Max([ShareNumbersTo]) from [CertificateDetails] WHERE [Type] = 'ShareType')")

It gives me the largest share number without WHERE but this may not be the correct one but "s" gives me nothing when i have the WHERE clause

 

[PHV]

s = DLookup("ShareNumbersTo", "CertificateDetails", "ShareNumbersTo=(Select Max(ShareNumbersTo) from CertificateDetails WHERE Type='" & ShareType & "')")

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

 

[tamus121]

Thanks PH

Works as I wanted