I've got such a simple SQL query, but it won't output the friggin results of the query!

I put the query in a "Query" in access, run it... and it outputs me a integer value. I'm running the following VBA code in "Form Load"

Private Sub Form_Load()

Dim strSQL As String

strSQL = "SELECT Count(*) FROM ContactDetails;"

MsgBox strSQL

What it does is on form load instead of popping up a msgbox saying 41 or whatever the integer value of that count query should be.... it outputs the query itself:

SELECT Count(*) FROM ContactDetails

Any ideas on how I can get the SQL query to run as a query and output the value into the msgbox?

A message box is not built to accept the results of a query. Build your own form, bind the form to the SQL string, and put a single text box on it bound to the single field.

But, you could use a recordset to return the results to a message box IF you are only returning a very short amount of data. If one record and one field then you can use:

Dim rs As DAO.Recordset

Set rs = CurrentDb.Open("SELECT Count(*) FROM ContactDetails")

MsgBox rs(0)

rs.Close
Set rs=nothing

You may be able to adapt the code in this thread (http://www.access-programmers.co.uk/forums/showpost.php?p=549345&postcount=2)here:

well, I'd ultimately like to get the value output in a text box.

I just need to know if there are records in the table for SAID ID number. do a count and output the value to a txt box then use an IF statement from there.

IF me.txtbox > 0 then BLAH BLAH else BLEH BLEH
end if

and I want this SQL Query to populate me.txtbox

If you just want the text box output then use a DCount as the control source:

=DCount("YourKeyIDFieldFromYourTable","ContactDetails")

the Dcount proved to be the easiest and most successful for what I needed!

dcount doesn't mess around :D Thanks alot for all the suggestions and help guys!