SQL into a string

[soupisgood84]

I am looking for some guidance. I wrote a SQL statement that returns only one result every time for a table. I need to get the data from that result into a string so I can modify it before I put it into another table. What should my next step be or should I use a recordset?

CODE:
<code>
selectSQL = "SELECT Table1.F1" & " "
fromSQL = "FROM Table1" & " "
whereSQL = "WHERE (((Table1.F1) Like 'Free space *'))"

SQLstatement = selectSQL & fromSQL & whereSQL & ";"

CurrentDb.QueryDefs("qryImport(tmpTable1)").SQL = SQLstatement
</code>

 

[TheAceMan1]

How are ya soupisgood84 . . .

Since your info is a little vague, here's the logical sequence I see:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT [F1] " & _
         "FROM Table1 " & _
         "WHERE ([F1] Like 'Free Space *');"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.EOF Then
      rst!FieldToModify = [green]'Your Modified rst!FieldToModify[/green]
      [green]'Append Query here with modified rst!FieldToModify[/green]
   Else
      MsgBox "No records!"
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and gives insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[soupisgood84]

That seem to be more or less what I am looking for, although I don't know the way to read in the current record into a string.

Example:
(Once the recordset has been opened, there should only be one record in that record set {at least for the case that I am using it for})

Dim tmpStr AS String
tmpStr = current_record

 

[TheAceMan1]

soupisgood84 . . .

Again, vagarie is not allowing resolution!

If [blue]tmpStr = current_record[/blue] then whats the purpose of the recordset!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[soupisgood84]

Ok, let me see if I can describe my problem a different way.

I am importing a text document that contains hard drive information for multiple servers. The text document imports the exact same way for each server, (all data into one column and each line of the document is one row). I want to extract a single line from the table into a string so I can manipulate the data before I move the information into a permanent table, because the format isn't what I need.

Dlookup isn't really an option because I wont know what the data is before I look for it. Although I know what part of the line will be, that is how I am looking it up through SQL definitions.

Let me know if this helps.

Like always, thank you very much for any information.

 

[Remou]

You can use either dlookup or a recordset, I think.

'Requires Microsoft DAO 3.x Object Library
Dim rs As DAO.Recordset
selectSQL = "SELECT Table1.F1" & " "
fromSQL = "FROM Table1" & " "
whereSQL = "WHERE (((Table1.F1) Like 'Free space *'))"

SQLstatement = selectSQL & fromSQL & whereSQL

Set rs=CurrentDB.OpenRecordset(SQLstatement)

strString=rs!F1

DlookUp:

strString =DlookUp("F1","Table1","F1 Like 'Free space *'")

 

[soupisgood84]

Many thanks for the guidance. It is working just as I'd hoped now. Thank you again!