SQL query to subform

[calvinle]

Hi,

I don't know where is the issue but I am having issue to display the result in my subform using the following query:

Can anyone help on it?

  Dim strWhere As String              
  Dim rsResult As Recordset
  
  strWhere = "SELECT * FROM tblWork WHERE client_id = " & Nz(Me.cboClient.Value)
  
  Set rsResult = CurrentDb.OpenRecordset(strWhere, dbOpenSnapshot)
  
  Me.frmWork.SourceObject = "sfWork"
  Set Me.frmWork.Form.Recordset = rsResult

The SQL string shows:

SELECT * FROM tblWork WHERE client_id = "1A1"

but the subform does not display any data.

Thanks

 

[bob fitz]

Perhaps:

Me.frmWork.Form.Recordset = "SELECT * FROM tblWork WHERE client_id = " & Nz(Me.cboClient.Value)

 

[isladogs]

Client_id is a string so you need text delimiters

  "SELECT * FROM tblWork WHERE client_id = '" & Nz(Me.cboClient.Value) & "'"

 

[calvinle]

Client_id is a string so you need text delimiters

  "SELECT * FROM tblWork WHERE client_id = '" & Nz(Me.cboClient.Value) & "'"

Finally it works!!

But I though both can work because it's string..? Either " or '

 

[isladogs]

What you had would have worked if the field was a number but not for text

 

[calvinle]

This is my main code that I use during that filtering:

strWhere = strWhere & "([client_id] Like ""*" & Me.cboClientId & "*"") AND "

How should I change this one so it works?

Thanks

 

[calvinle]

I have got it.. Thanks