cannot get value from SELECT string

[cubadew]

I am relatively new to using SQL but I have gotten several insert and update queries working quite well. I cannot however seem to figure out how to use the SELECT SQL to get a value from a table.

Here is my code that is under the "Click" action of a button:

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT Count([username])" & _
"AS [Number]" & _
"FROM Userlog" & _
"WHERE (((IsNull([exit]))=True));"

Me.fltext = strSQL 'this is my text box

I have tried using a msgbox and a text box to see the value but all I see is the SQL statement. This works if I use the query builder and test the statement.

I am using this to determine the amount of users currently logged into my db. It is querying my table userlog which has login/logout date/time/user recorded.

My end goal is to use this to determine if there is more than one user in the db so I can do a backup and therefor the value will be used as part of a control.

so my question...
Why does the query builder give me a number of users... yet when I try to set the value of a msgbox of text box to strSQL all I see is the SQL statement? I need to pull the number generated by the count() function.

 

[PHV]

What about this ?
Me.fltext = DCount("username", "Userlog", "[exit] Is Null")

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

 

[cubadew]

PHV

That does work... so thank you for helping me find an alternative... but I have yet to get a SELECT sql to work. There are other places I really need/would like to use them.

How do I pass a value from a select statement to a msgbox or text box using a string? Am I missing something? From what I've read I dont need a DoCmd.runsql or anything...

I tried this to see if it was just the count() function that was messing me up. No dice.

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb

strSQL = "SELECT About.AvSupOName FROM About;"

Me.fltext = strSQL

It still just passes the sql on to the txt box. What am I missing?

 

[lespaul]

I stole this code from another thread (thanks to Randy700) to show you what you are missing. The bold section of code below actually runs your query and assigns the RESULTS to a variable that you can work with. The red bold section shows how to get information from that result set and assign it to something else. In this case it's assigning the value of the rsB field to the rsA field, but you can do the same thing with any other variable.

Those are the two pieces that you need to add to YOUR code to get the information you want from a query.

Dim db as Database
Dim rsA as Recordset, rsB as Recordset
Dim strSQL1 as String, strSQL2 as String
strSQL1 = "SELECT * FROM tbl1 WHERE Trim(Len(casenum & ticketnum & dob & lastname)) = 0"
strSQL2 = "SELECT * FROM tbl2"
Set db = CurrentDb()
Set rsA = db.OpenRecordset(strSQL1)
[b]Set rsB = db.OpenRecordset(strSQL2)[/b]
rsA.MoveFirst
rsB.MoveFirst
With rsA
   Do Until .EOF
      If not rsB.EOF
         .Edit
         [COLOR=red][b]casenum = rsB!cust_casenum[/b][/color]
         ticketnum = rsB!cust_ticketnum
         dob = rsB!cust_dob
         lastname = rsB!cust_lastname
         .Update
         rsB.MoveNext
      End If
      .MoveNext
   Loop
End With

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[cubadew]

Awesome! Thank you!

This was the code I used for a select then update sql and it works great.

Dim db As Database
Dim strSQL As String
Dim rsA As Recordset
Dim strShuffle
strShuffle = "SELECT CRBlog.CRBdate FROM CRBlog WHERE (([ID]=4));"
Set db = CurrentDb()
Set rsA = db.OpenRecordset(strShuffle)
backupnum = rsA!CRBdate
DoCmd.SetWarnings False

strSQL = "UPDATE CRBlog " & _
"SET [CRBdate]=#" & backupnum & "# " & _
"WHERE [ID] = 5"

DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

Thanks for the tip!

 

[cubadew]

I have been having problems getting any sort of sql that isnt a straight forward lookup to work... what should I be putting in the rsA!.... I tried datemodified but it threw an error. I even tried adding "AS Future" then doing rsA!Future, but no joy.

any tips? Here is my code.

Dim db As Database
Dim strSQL5 As String
Dim rsA As Recordset
Dim strTest5

strTest5 = "SELECT Max(DateAdd('d',1,[datemodified]))FROM modified;"
Set db = CurrentDb()
Set rsA = db.OpenRecordset(strTest5)
strTest = rsA!datemodified
Msg = "" & strTest
MsgBox Msg

 

[dhookom]

I'm not sure why "future" would work without defining a field named future

Dim db As DAO.Database
Dim strSQL5 As String
Dim rsA As DAO.Recordset
Dim strTest5 as String
Dim Msg as String
strTest5 = "SELECT Max([datemodified])+1 As NextDate FROM modified"
Set db = CurrentDb()
Set rsA = db.OpenRecordset(strTest5)
strTest = rsA!NextDate
Msg = "Here it is: " & strTest
MsgBox Msg

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[traingamer]

The select statement returns a recordset (an array, if you will) which you have to loop through to get the results. Your select max(... still returns a recordset (even though it should only have one row). It does not return a simple string or a date.

Leslie's example is one way of looping through the recordset. Does that make more sense?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[dhookom]

A fairly flexible method for returning a value is to use the generic concatenate function faq701-4233. Your control source or expression in code might be

=Concatenate("SELECT Max([datemodified])+1 FROM modified")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]