get SQL to load result of SELECT into a variable

[FeS2]

I am having one heck of a time to get the result of a sql query to load it's results into a string variable. I have tried the follow but get a Compile Error that says Expected Function or Variable, then shows my code with .runsql highlighted in blue. The strSQL will only ever result in a 7 digit phone number, w/ no dashes.

strSQL = "SELECT [AM Completions].[phone] WHERE [AM Completions].[phone] = Forms!Faxination1!Phone;"
strTest = (DoCmd.RunSQL(strSQL))

 

[PHV]

Have a look to the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

The RunSQL method is for action or data-definition queries, i.e. INSERT INTO, CREATE TABLE, DELETE, etc., not SELECT queries that will return a recordset. You *can* do it by opening a recordset, but PHV's suggestion is on target, DLookUp is perfect for this sort of thing.

Ken S.

 

[TheAceMan1]

How are ya FeS2 . . . . .

In parallel with [blue]PHV[/blue], try this:

[blue]   Dim strTest As String, Criteria As String
   
   Criteria = "[phone] = '" & Forms!Faxination1!Phone & "'"
   strTest = DLookup("[phone]", "[AM Completions]", Criteria)[/blue]

See Ya! . . . . . .

 

[FeS2]

DLookup works great. But I am running into trouble now in an if statement. I started a question about if I should use an If or IIF. thread705-1066072 I'll ask the rest of the question there since it involves more of the If than DLookup. Thanks for everyones help.