[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5255
  • Last Modified:

Putting SQL statement into control source of MS Access unbound text box

Hi experts,

I am trying to place an SQL into an unbound field on my form.  The SQL is intended to return the maximum date in a field where moduleID = 1.   My SQL is as follows:

=(SELECT Max([DCTargetCompletionDate]) AS Expr1 FROM KControl WHERE ModuleID=1;)

I am getting #Name as a result and do not know how to get round this.

Can anybody help?

Thank you.
Terry
0
TerenceHewett
Asked:
TerenceHewett
  • 2
2 Solutions
 
pmctrekCommented:
Why dont you add the SELECT to the datasource for the form and bind the test box to that new field?
0
 
pmctrekCommented:
The problem is that bound objects like text boxes can only access the forms recordset.  If the data you want is outside the recordset then you need to look it up some other way.  The attached code is a simple way to set the vaule of a field as the form is being rendered.
Private Sub Form_Current()
    Dim intI As Integer
    Dim fld As Field
 
    Set rst = New ADODB.Recordset
    rst.Open "SELECT Max([DCTargetCompletionDate]) AS CompDate FROM KControl WHERE ModuleID=1;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Text0.Value = rst.Fields("CompDate")
End Sub

Open in new window

0
 
JimFiveCommented:
Try =DMax("[DCTargetCompletionDate]", "KControl","ModuleID=1")
--
JimFive

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now