count result always 0?

[brad78]

Can someone please tell me why my NumResults variable, after running the below query, is always equal to 0? It shouldn't be and I can't figure it out.

strSqlSelpg = "Select count(page_file_name) AS NumResults from HTML_stats where page_file_name = '" & page_file_name & "'"

Thanks
Brad

 

[MSAccessRookie]

Can someone please tell me why my NumResults variable, after running the below query, is always equal to 0? It shouldn't be and I can't figure it out.

strSqlSelpg = "Select count(page_file_name) AS NumResults from HTML_stats where page_file_name = '" & page_file_name & "'"

Thanks
Brad

I assume that you mean that you found strSqlSelpg to be 0 after the result was returned. In that case:

• Does the query work in SQL Mode if you substitute a valid value for page_file_name?
• Your code snippet does not show the definition of page_file_name. Is it defined and what value is assigned to it?

 

[brad78]

page_file_name is a variable containing a URL.

In the query window, if I run

SELECT page_file_name AS PG_Name from HTML_stats where page_file_name = 'http://mydomain/etd/html/wr_micro/index.htm'

The result is one row with the column heading PG_Name and the one record of http://mydomain/etd/html/wr_micro/index.htm, which is correct. If I run this from the application, the PG_Name variable returns no value.

I need to be able to check if the record exists already and insert if it doesn't but no value is being returned to the PG_Name variable to check against.
Thanks

 

[brad78]

Here is my full code.

'Now query to add the page_file_name into HTML STATS TABLE
    Dim page_file_name As String
    page_file_name = Me.page_file_name
 
    Dim strSqlSelpg, strSqlApp2 As String
    Dim PG_Name As String
 
    strSqlSelpg = "SELECT page_file_name AS PG_Name from HTML_stats where page_file_name = '" & page_file_name & "'"
    MsgBox (strSqlSelpg)
 
    MsgBox (PG_Name)
 
    If (PG_Name = page_file_name) Then
        MsgBox ("record already in HTML_stats table")
    Else
        strSqlApp2 = "INSERT INTO HTML_stats (page_file_name) values ('" & page_file_name & "')"
        MsgBox (strSqlApp2)
        DoCmd.RunSQL (strSqlApp2)
        On Error GoTo ErrorHandler
    End If

 

[MSAccessRookie]

Here is my full code.

'Now query to add the page_file_name into HTML STATS TABLE
    Dim page_file_name As String
    page_file_name = Me.page_file_name
 
    Dim strSqlSelpg, strSqlApp2 As String
    Dim PG_Name As String
 
    strSqlSelpg = "SELECT page_file_name AS PG_Name from HTML_stats where page_file_name = '" & page_file_name & "'"
 
[COLOR=red][B]    DoCmd.RunSQL (strSqlSelpg)[/B][/COLOR]
 
    MsgBox (strSqlSelpg)
 
    MsgBox (PG_Name)
 
    If (PG_Name = page_file_name) Then
        MsgBox ("record already in HTML_stats table")
    Else
        strSqlApp2 = "INSERT INTO HTML_stats (page_file_name) values ('" & page_file_name & "')"
        MsgBox (strSqlApp2)
        DoCmd.RunSQL (strSqlApp2)
        On Error GoTo ErrorHandler
    End If

I notice that you never execute the SQL Statement (See the RED code above). As a result, PG_Name will never be defined. Add the line to execute the SQL code and see if that helps.