VBA query parameters not working

[WillC]

Been scouring the internet for ages to understand why this isn't working whenever I run it I get this error message 'Syntax error (missing operator in query expression '((([Asset Group].[Asset Number]) = AN00001) AND ((Group.[Group Type]) = Camera Unit))'. I've copied this code straight out from what access query created for me.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [Asset Group].[Asset Number], Group.[Group Type]FROM [Group] INNER JOIN [Asset Group] ON Group.[Group Number] = [Asset Group].[Group Number] WHERE ((([Asset Group].[Asset Number])= " & [AddText1] & " ) AND ((Group.[Group Type])= " & [AddText2] & "))")
rst.MoveLast
MsgBox rst.RecordCount
rst.Close
End Sub

any help greatly appreciated

 

[vbaInet]

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [Asset Group].[Asset Number], [Group].[Group Type] " & _
                                  "FROM [Group] INNER JOIN [Asset Group] ON [Group].[Group Number] = [Asset Group].[Group Number] " & _
                                  "WHERE [Asset Group].[Asset Number] = " & [AddText1] & " AND [Group].[Group Type] = " & [AddText2])
rst.MoveLast
MsgBox rst.RecordCount
rst.Close
[COLOR=Red]set rst = nothing[/COLOR]
End Sub

Compare it with yours to see where some syntax problems existed.

Also, what are the data types of Asset Number and Group Type?

You should avoid spaces when naming objects and fields.

 

[WillC]

Asset Number and Group Type are both text data types. And I have added in line of code thanks still getting used to using recordsets. And know its bad practice to have spaces....
thanks for help

 

[vbaInet]

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [Asset Group].[Asset Number], [Group].[Group Type] " & _
                                  "FROM [Group] INNER JOIN [Asset Group] ON [Group].[Group Number] = [Asset Group].[Group Number] " & _
                                  "WHERE [Asset Group].[Asset Number] = [COLOR=Red]'[/COLOR]" & [AddText1] & "[COLOR=Red]'[/COLOR] AND [Group].[Group Type] = [COLOR=Red]'[/COLOR]" & [AddText2] & "[COLOR=Red]'[/COLOR]", dbOpenSnapshot)
rst.MoveLast
MsgBox rst.RecordCount
rst.Close
set rst = nothing
End Sub

Those single quotes in red is how you handle text data types. Copy the whole code.

 

[WillC]

Yes, it works thanks so much. So just for reference if was number data type would it be just like I had it before? Thanks again

 

[vbaInet]

That's right Will. For dates you use hash (#).

Happy developing!