ConcatRelated Error: "Error 3061: Too few parameters. Expected 3" (1 Viewer)

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
Hi. Congratulations! Glad to hear you got it working on your end. Good luck with your project.
Thanks!

Quick follow-up question: if I want to change how the results are concatenated to a new line in the record instead of a comma, do I modify this part of the SimpleCSV module?

Code:
Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = ",") As String
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
Thanks!

Quick follow-up question: if I want to change how the results are concatenated to a new line in the record instead of a comma, do I modify this part of the SimpleCSV module?

Public Function SimpleCSV(strSQL As String, _
Optional strDelim As String = ",") As String
No, you don't modify the module/function at all. You just pass the delimiter you want to use. The default is a comma. To use a new line, you can use Chr(13) & Chr(10). For example:

Code:
SimpleCSV("SELECT...", Chr(13) & Chr(10))
 

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
No, you don't modify the module/function at all. You just pass the delimiter you want to use. The default is a comma. To use a new line, you can use Chr(13) & Chr(10). For example:

Code:
SimpleCSV("SELECT...", Chr(13) & Chr(10))
Perfect thank you. Lastly, I want to include the combo box selections as part of the WHERE clause in the SimpleCSV() function. Currently it is concatenating all ASDs associated with the control, regardless of the filters.

Would I do this?
Code:
SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & "' & (([Pivoted Data].Place)=[Forms]![Pass Percentage Form]![Place_combo]) AND (([Pivoted Data].Unit)=[Forms]![Pass Percentage Form]![U_combo]) AND (([Pivoted Data].[The_Date])=[Forms]![Pass Percentage Form]![Date_combo])",Chr(13) & Chr(10)) AS ASDs
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
Perfect thank you. Lastly, I want to include the combo box selections as part of the WHERE clause in the SimpleCSV() function. Currently it is concatenating all ASDs associated with the control, regardless of the filters.

Would I do this?
Code:
SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & "' & (([Pivoted Data].Place)=[Forms]![Pass Percentage Form]![Place_combo]) AND (([Pivoted Data].Unit)=[Forms]![Pass Percentage Form]![U_combo]) AND (([Pivoted Data].[The_Date])=[Forms]![Pass Percentage Form]![Date_combo])",Chr(13) & Chr(10)) AS ASDs
Hi. What I would do is use the AfterUpdate event of each Combobox to update the Record Source of the subform. You would want to concatenate the value from the combo and not just include a parameter in the SQL statement (or you will go back to the original error that started this thread).
 

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
Hi. What I would do is use the AfterUpdate event of each Combobox to update the Record Source of the subform. You would want to concatenate the value from the combo and not just include a parameter in the SQL statement (or you will go back to the original error that started this thread).
Hey there. Currently, for each combo box AfterUpdate even I have a:

SearchForRecord command WHERE =="[combo box field] = " & "'" & [Screen].[ActiveControl] & "'"
and a
Requery command

Do I replace these with a Refresh command?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
Hey there. Currently, for each combo box AfterUpdate even I have a:

SearchForRecord command WHERE =="[combo box field] = " & "'" & [Screen].[ActiveControl] & "'"
and a
Requery command

Do I replace these with a Refresh command?
No, you would replace it with something like:

Code:
Dim strSQL As String

'need some logic stuff in this section for constructing the ASDs based on selected comboboxes

strSQL = "SELECT..." 'incorporating the result of the above logic test

Me.RecordSource = strSQL
 

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
No, you would replace it with something like:

Code:
Dim strSQL As String

'need some logic stuff in this section for constructing the ASDs based on selected comboboxes

strSQL = "SELECT..." 'incorporating the result of the above logic test

Me.RecordSource = strSQL
Hmmm, I'm sorry I guess I'm just a little lost on the logic I would use there.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
Hmmm, I'm sorry I guess I'm just a little lost on the logic I would use there.
Okay, based on the original file you sent me, here's my best guess as well. I only added the first combo. I can't tell if the result is correct though, since I am not familiar with your data. Hope it helps...
 

Attachments

  • ConcatTest (3).zip
    75.2 KB · Views: 155

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
Okay, based on the original file you sent me, here's my best guess as well. I only added the first combo. I can't tell if the result is correct though, since I am not familiar with your data. Hope it helps...
Hey. I looked at the results, I don't think anything changed from before. Probably because the ASDs mostly rely on the Unit combo box, so code for that box might have more of an effect on the results.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
Hey. I looked at the results, I don't think anything changed from before. Probably because the ASDs mostly rely on the Unit combo box, so code for that box might have more of an effect on the results.
Right. Don't look at the result; but rather, on the technique I tried to implement. Remember, your final SQL was different than mine because you're using a different set of data. For example, when I tried to copy the "final SQL" you posted earlier into the sample file I am using, nothing showed up. So, try to disregard what my data result is, but concentrate on how you can apply what I'm trying to do on this sample db into your db. As a test, you could try trimming down the data in the sample db to see if anything changes. For example, remove some records for a specific place for a specific Control ID. One thing I can tell in the last file I posted is the Pass% column is changing, so it is having "some" effect to the data behind the form. Cheers!
 

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
Right. Don't look at the result; but rather, on the technique I tried to implement. Remember, your final SQL was different than mine because you're using a different set of data. For example, when I tried to copy the "final SQL" you posted earlier into the sample file I am using, nothing showed up. So, try to disregard what my data result is, but concentrate on how you can apply what I'm trying to do on this sample db into your db. As a test, you could try trimming down the data in the sample db to see if anything changes. For example, remove some records for a specific place for a specific Control ID. One thing I can tell in the last file I posted is the Pass% column is changing, so it is having "some" effect to the data behind the form. Cheers!
Yes its definitely changing to filter the right things! Just not working correctly on the ASD column.

After further thought, would it be better to add the = Unit to the SimpleCSV function?
Code:
SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & " AND [Unit] = '" & [Unit] & "'",Chr(13) & Chr(10)) AS ASDs
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
Yes its definitely changing to filter the right things! Just not working correctly on the ASD column.

After further thought, would it be better to add the = Unit to the SimpleCSV function?
Code:
SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & " AND [Unit] = '" & [Unit] & "'",Chr(13) & Chr(10)) AS ASDs
Hi. As much as possible, I would not advise modifying the function. You should be able to pass to it a valid/modified SQL statement and just let it do its work. Let me see if I can play with the data I have to see if the ASD part changes based on places.

But, if you want to try and modify the function, please feel free... (I only request you leave all applicable attribution)
 

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
Hi. As much as possible, I would not advise modifying the function. You should be able to pass to it a valid/modified SQL statement and just let it do its work. Let me see if I can play with the data I have to see if the ASD part changes based on places.

But, if you want to try and modify the function, please feel free... (I only request you leave all applicable attribution)
Alright thanks for the help - I'll default to your expertise.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
Okay, I only removed some records... See if this makes sense to you (it doesn't to me).
 

Attachments

  • ConcatTest (4).zip
    99.1 KB · Views: 141

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
What exactly did you change?
I removed some records to make a big difference in what could show up on the form, just to show that the code is doing "something." However, I guess I didn't remove enough. Try the attached file instead. When you first open the form, you get 14 rows and 5 ASDs for CA-001. If you select Place 1 from the Combobox, you get 11 rows and only 4 ASDs for CA-001.
 

Attachments

  • ConcatTest (5).zip
    95 KB · Views: 132

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
I removed some records to make a big difference in what could show up on the form, just to show that the code is doing "something." However, I guess I didn't remove enough. Try the attached file instead. When you first open the form, you get 14 rows and 5 ASDs for CA-001. If you select Place 1 from the Combobox, you get 11 rows and only 4 ASDs for CA-001.
Ah okay yes I see that now. So for more context, when you first open the form nothing should display. Only when you select the Place/Unit/Date boxes should it display the results for the controls tested at that Place, only for the selected unit and date.

All "Places" should have expect to have the same ASDs generally. But, depending on the Unit select this will change what ASDs will display. Essentially, the # of ASDs per control should not change depending on the Place, instead change depending on the Unit. Does that help clarify at all?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
21,357
Ah okay yes I see that now. So for more context, when you first open the form nothing should display. Only when you select the Place/Unit/Date boxes should it display the results for the controls tested at that Place, only for the selected unit and date.

All "Places" should have expect to have the same ASDs generally. But, depending on the Unit select this will change what ASDs will display. Essentially, the # of ASDs per control should not change depending on the Place, instead change depending on the Unit. Does that help clarify at all?
Hi. Understood. But, I was just trying to show you how you could apply your own "rules" to the code. So, you should be able to adapt what I did to the next combobox, right? Since I only did the first combo, which has nothing to do with Units, I had to mess with the data. Once you apply the same technique to the next combobox, you should be able to use the full set of data. Since I didn't know the actual requirements in the first place, I just decided to work on the first combobox. To display nothing on the form until after a selection is made in the combobox, just delete the Record Source for the form in Design View.
 
Last edited:

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
Hi. Understood. But, I was just trying to show you how you could apply your own "rules" to the code. So, you should be able to adapt what I did to the next combobox, right? Since I only did the first combo, which has nothing to do with Units, I had to mess with the data. Once you apply the same technique to the next combobox, you should be able to use the full set of data. Since I didn't know the actual requirements in the first place, I just decided to work on the first combobox. To display nothing on the form until after a selection is made in the combobox, just delete the Record Source for the form in Design View.
Ah yes I gotcha. I implemented the same code to the Unit combo box and it worked! The ASDs were displaying correctly, for the correct units, for the correct places. The only thing thats wonky now is the scores are totally over the place, but I will keep looking into this. I tried implementing the code for the date combo as well but that did not fix it.
 

newdbguy

Member
Local time
Today, 07:43
Joined
Mar 9, 2020
Messages
30
@theDBguy would we be able to remove the AvgPass calculation from the VBA update events? I was curious if we did this then the scores would default back to the recordsource of the form which originally displayed the correct scores.

I tried removing them but got a Run-time error '3141': SELECT statement includes a reserved word or misspelled word, or punctuation is incorrect --> and then this is highlighted in the VBA: Me.RecordSource = strSQL

Thoughts?
 

Users who are viewing this thread

Top Bottom