Recordset problem (1 Viewer)

[benkingery]

I posted on Friday a very similar issue with a recordset. I've used most of the recordset portion of that sample code, so I don't think there's a problem with that. I think there is some problem with how the SQL statement interacts with the recordset, but I don't know recordsets well enough to know what is going on.

Does anyone have anything they can see here?

Private Sub AZBuildTitle_Click()
Dim strSQL  As String
Dim db      As DAO.Database
Dim rs      As DAO.Recordset

Set db = CurrentDb()
  
strSQL = "SELECT [LiquidationTies].[Brand] + ' Mens' + IIf([AttributeData_Ties].[AZ_pattern_Style] Is Null,'',' ' + [AttributeData_Ties].[AZ_Pattern_Style]) + IIf([AttributeData_Ties].[AZ_theme] Is Null,'',' ' + [AttributeData_Ties].[AZ_theme]) + IIf([AttributeData_Ties].[EBOS_Material] Is Null,'',' ' + [AttributeData_Ties].[EBOS_Material]) + IIf([AttributeData_Ties].[EB_Style] Is Null,'',' ' + [AttributeData_Ties].[EB_Style]) AS AZ_TitleSQL, LiquidationTies.Parent_SKU FROM AttributeData_Ties INNER JOIN LiquidationTies ON AttributeData_Ties.Parent_SKU = LiquidationTies.Parent_SKU WHERE (((LiquidationTies.Parent_SKU)=[Forms]![Liquidation_Tie_DataCollection]![Parent_SKU]));"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Me.AZ_Title = rs!AZ_TitleSQL
  
Set Rs1 = Nothing
Set Db1 = Nothing
End Sub

I ran the SQL statement within a query builder and it definitely gives me the desired results....

 

[pbaldy]

For starters, I'd use & instead of +. & is a concatenation operator, + is a math operator. Sometimes you can get away with +, in fact sometimes it gives you a neat trick (null propagation), but generally you want &. See if this technique helps solve the problem:

http://www.baldyweb.com/ImmediateWindow.htm

If not, post the resulting SQL here.

 

[pbaldy]

By the way, you're setting different variables to Nothing than you actually used.

 

[benkingery]

Thanks pbaldy. Always so helpful

I did the debug.print strSQL. Then i took that string and put it into a query designer. it worked exactly as I needed.

The error I'm still getting is:

Run-tim error '3061':

Too few parameters. Expected 1.

I also fixed my variables I was setting to "nothing" so it was consistent with what had been dimmed out.

Any other thoughts?

 

[benkingery]

Sorry, forgot to post the sql

SELECT [LiquidationTies].[Brand] + ' Mens' & IIf([AttributeData_Ties].[AZ_pattern_Style] Is Null,'',' ' & [AttributeData_Ties].[AZ_Pattern_Style]) & IIf([AttributeData_Ties].[AZ_theme] Is Null,'',' ' & [AttributeData_Ties].[AZ_theme]) & IIf([AttributeData_Ties].[EBOS_Material] Is Null,'',' ' & [AttributeData_Ties].[EBOS_Material]) & IIf([AttributeData_Ties].[EB_Style] Is Null,'',' ' & [AttributeData_Ties].[EB_Style]) AS AZ_TitleSQL, LiquidationTies.Parent_SKU FROM AttributeData_Ties INNER JOIN LiquidationTies ON AttributeData_Ties.Parent_SKU = LiquidationTies.Parent_SKU WHERE (((LiquidationTies.Parent_SKU)=[Forms]![Liquidation_Tie_DataCollection]![Parent_SKU]));

 

[pbaldy]

The problem is that the query designer can resolve the form reference, the recordset can not. You need to concatenate the form reference into the string so the SQL in Debug shows the value from the form, not the form reference. For a numeric data type it would look like (without the stupid parentheses that Access likes to add):

"...WHERE LiquidationTies.Parent_SKU = " & [Forms]![Liquidation_Tie_DataCollection]![Parent_SKU] & ";"

By the way, the semi-colon at the end is optional.

 

[benkingery]

pbaldy is the man!

Thank you! That was the problem.

 

[pbaldy]

LOL! Happy to help.