Basic: Executing simple sql query to extract a value in vba

[msp4422]

Hi I am trying to execute a sql to fetch a value which will be source to a text box. Could you help me on how to write and execute sql's in vba?? tnx in advance.

I tried below but it is giving 3163: the field is too small to accept the value..........

Dim SS As String

DoCmd.SS = "SELECT qryAgreement_Data.[Unique ID] FROM qryAgreement_Data WHERE qryAgreement_Data.[Unique ID]=311;"


Me.Agreement_ID.Value = SS

thank you.

 

[pr2-eugin]

Hello msp4422, Welcome to AWF..

Well SQL statements return a Recordset.. Why DoCmd? What you need is just a simple DLookUp..

Dim SS As String

SS = Nz(DLookUp("[Unique ID]","qryAgreement_Data","[Unique ID]=311"),0)

 

[pbaldy]

As answered elsewhere

I think you made up the SS command there. You would have to open a recordset on that SQL to get the value. You may find DLookup() simpler:

http://access.mvps.org/access/general/gen0018.htm