Displaying Query Result in a textbox

[amc1902]

Hi all

I have a query which works very well and displays the single-value result in a datafield display. What I am trying to do is to display this result in a textbox instead, located on the form.

The SQL that I have is:
SELECT Count(Table1.[Date Received]) AS [Count]
FROM Table1
WHERE (((Table1.[Date Received])>=[Select DateFrom] And (Table1.[Date Received])<=[Select Date to]));

I am trying to display the result in textbox80, located on Form2.

Can anybody amend the above to allow this? I've also tried using the DLookup in the textbox80 Control Source, however, I keep getting an error result.



Any suggestions would be appreciated

Many thanks!!

[ranman256]

Create a query with this sql, THEN if you need to fill the result into a text box (or msgbox)

txtBox = Dlookup("[Count]","qsMyTotalQry")

[amc1902]

Create a query with this sql, THEN if you need to fill the result into a text box (or msgbox)

txtBox = Dlookup("[Count]","qsMyTotalQry")

Hi, thanks for your response - unfortunatly all I get is the #Error message in the textbox

[ranman256]

Then dont use the word count (its reserved)
try connecting a form to the count query... connect a text box to the field TOTAL (rename the count to total)
NOW it will show.

[amc1902]

Hi, thanks for this. Pretty new to the world of Access, so this is getting a bit beyond me

Just to recap, I have a query (qrycountBF) which is working fine but is displaying the result in a datasheet view. I would like to display it in a textbox (textbox80), located in Form2 if possible.
The sql for the query is:

SELECT Count(Table1.[Date Received]) AS [Count]
FROM Table1
WHERE (((Table1.[Date Received])>=[Select DateFrom] And (Table1.[Date Received])<=[Select Date to]));

Based upon the above (and what you suggested), could you give me an idea as to completing the DLookUp statement?

Thanks!!!

[hansendl]

I just tested ranman256's suggestion in a test database and it works fine on my system. The ControlSource property of textbox80 should read: =Dlookup("[Count]", "qrycountBF")

If that doesn't work, need to look at your SQL statement. Where do the values for the [Select DateFrom] and [Select Date to] fields come from? Are these fields in Table1, or are they parameters entered when you run the query?

[amc1902]

Hi hansendl

They are parameters entered when the query is run - I'll give your suggestion a try, thanks

[amc1902]

Hi hansendl

My apologies, I quoted the wrong query earlier. The query should read 'qrydaterec'. With this in mind, I've followed your suggestion and in the ControlSource property box of textbox80 I have added =DLookUp("[Count]","qrydaterec").

It hasn't worked, however, interesting enough, the #Error now shows #Name? in textbox80

What could that indicate?

[hansendl]

Not sure where the #Name? error is coming from, but when I added a parameter to my saved query, it produces an error in the DLookUp function (i.e., "#Error" shows up in the textbox).

I recommend you use VBA code. From a user interface perspective, I also recommend you add textboxes to your form for the user to specify the start and end dates for the query. If you name these textboxes "txtDateFrom" and "txtDateTo", your code would look something like this:

Private Function GetCount() As Long
    Dim lngReturn As Long
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
    
    strSQL = "SELECT Count(Table1.[Date Received]) AS [Count] " _
        & "FROM Table1 " _
        & "WHERE (((Table1.[Date Received])>=#" & Me.txtDateFrom _
        & "# AND (Table1.[Date Received])<=#" & Me.txtDateTo & "#));"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rst.BOF Then
        GetCount = rst![Count]
    Else
        GetCount = 0
    End If
    
End Function

Then just set the ControlSource property of your textbox to: =GetCount()