Syntax error (missing operator) in query expression (1 Viewer)

[SachAccess]

Hi,

I am very much new to MS Access, kindly pardon me if am asking silly questions.

Am getting below error on one of the FORM.
Run-time error ‘3075’
Syntax error (missing operator) in query expression
‘Tbl_Cluster_Head. Cluster_Head_ID=’

Can anyone please help me with this, what should I check in the code to resolve the issue. Thanks.

Option Compare Database
Private Sub btn_Options_Click()
    Dim DB As Database
    Dim strSQL As String
    Dim rs As Recordset
    Dim lNum As Long
    
    'CurrentDb = Current Databse
    'Tbl_Cluster_Head = Table name
    Set DB = CurrentDb
    'Cluster_Head_ID = Column name from 'Tbl_Cluster_Head' table
    strSQL = "Select * from Tbl_Cluster_Head where Tbl_Cluster_Head.Cluster_Head_ID=" & Trim(Me.txt_Cluster_Head_ID.Value) & ";"
    
    Set rs = DB.OpenRecordset(strSQL, dbOpenDynaset)
    
    DoCmd.OpenForm ("Frm_ClusterHeadNew")
    
    'txt_Cluster_Head_ID = TextBox
    'txt_Cluster_Head_Name = TextBox
    Form_Frm_ClusterHeadNew.txt_ClusterHeadID.Value = Me.txt_Cluster_Head_ID.Value
    Form_Frm_ClusterHeadNew.txt_ClusterHeadName.Value = Me.txt_Cluster_Head_Name.Value
End Sub

 

[CJ_London]

use of trim implies that the value is text in which case you need to use the text identify (a single quote in this context)

Where Tbl_Cluster_Head.Cluster_Head_ID='" & Trim(Me.txt_Cluster_Head_ID) & "'"

Note that neither the .value is required (it is the default property of the control) nor the final ;

 

[SachAccess]

Hi @CJ_London sir, thanks a lot for the help. Am going through it again and will revert with details. Have a nice day ahead.

 

[SachAccess]

Hi @CJ_London sir, got it, it was my mistake, I need to add one condition here.
If the record is present for 'txt_Cluster_Head_ID' then code works without any error.

But if value is not present and button is clicked then macro gives bug.
I need to amend code here to handle this scenario as well. Thanks.

 

[arnelgp]

use Nz() function:

strSQL = "Select * from Tbl_Cluster_Head where Tbl_Cluster_Head.Cluster_Head_ID=" & Nz(Me.txt_Cluster_Head_ID.Value, 0) & ";"

 

[SachAccess]

Hi @arnelgp sir, you are great, thanks a lot, other day only I read about NZ but did not realize that I can use it here.
I was thinking about what would be equivalent of 'On Error Resume Next', blame it on my Excel backgroud.

Have a nice day ahead.

 

[arnelgp]

Nz() can be used in Text/Numeric fields, to supply a "default" value when the field is Null.

 

[SachAccess]

Thanks @arnelgp sir.