Change recordsourse

[Vulcan1500]

I have a form with a chart and want via a txtbox look at a specific selection of the records. I get constantly an error when the after update event runs and I'm unable to find the problem. The error arises in the line printed bold. Maybe one of you can have a look at this:

Private Sub txtArea_AfterUpdate()

Dim strHaving As String
Dim strSQL As String

strHaving = "[Area]=" & Me.txtArea

strSQL = "SELECT qryTEST.YearSurvey, "
strSQL = strSQL & "qryTEST.YearExecution, "
strSQL = strSQL & "qryTEST.Area, "
strSQL = strSQL & "Sum(([Small]*[SmallDWTAverage])/1000) AS SmallPUD, "
strSQL = strSQL & "Sum(([Medium]*[MediumDWTAverage])/1000) AS MediumPUD, "
strSQL = strSQL & "Sum(([Large]*[LargeDWTAverage])/1000) AS LargePUD, "
strSQL = strSQL & "Sum(([Jumbo] * [JumboDWTAverage]) / 1000) As JumboPUD "
strSQL = strSQL & "GROUP BY qryTEST.YearSurvey, qryTEST.YearExecution, qryTEST.Area "
strSQL = strSQL & "FROM qryTEST HAVING "

Me.RecordSource = strSQL & strHaving
Me.Requery

End Sub

 

[boblarson]

Two things -

1. Have you set a breakpoint so that you can see your final SQL string in the IMMEDIATE WINDOW? That way you can see what is getting passed and what you might have missed.

2. If, by chance, [Area] is text you would need single quotes:
strHaving = "[Area]='" & Me.txtArea & "'"

 

[Vulcan1500]

Thanks for your swift reply Bob. The string that gets passed is empty. Area is indeed text, but I do not understand the single quotes. Is the format as follows: "[Area]='" & Me.txt.Area & "'", so a single and a double after = and double, single, double after Me.txtArea. Is this what you mean and what is the purpose of the single quotes?

Two things -

1. Have you set a breakpoint so that you can see your final SQL string in the IMMEDIATE WINDOW? That way you can see what is getting passed and what you might have missed.

2. If, by chance, [Area] is text you would need single quotes:
strHaving = "[Area]='" & Me.txtArea & "'"

 

[boblarson]

Thanks for your swift reply Bob. The string that gets passed is empty. Area is indeed text, but I do not understand the single quotes. Is the format as follows: "[Area]='" & Me.txt.Area & "'", so a single and a double after = and double, single, double after Me.txtArea. Is this what you mean and what is the purpose of the single quotes?

The purpose of the single quotes is that is how you tell Access that the value it is looking for is text. You can also use double quotes but that gets unwieldy when you have to encapsulate double quotes within double quotes because the string you are building has to be encapsulated within double quotes for the whole string, so it would need extra double quotes to make the string work. It is much simpler to use the single quotes with the concatenation and it is actually how a SQL Statement on SQL Server delimits text; with single quotes.

Does that help?

 

[Vulcan1500]

Understood. strSQL is displayed in the immediate window as prepared in the code. strArea is now displaying [Area]='NEE' when NEE is typed in the txtbox. This seems to be ok now, but the Me.Recordsource still results in an empty string and access stops with an error message 3141 and states that "the SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect". The debugger highlights the bold statement. Hope this gives you directions.

The purpose of the single quotes is that is how you tell Access that the value it is looking for is text. You can also use double quotes but that gets unwieldy when you have to encapsulate double quotes within double quotes because the string you are building has to be encapsulated within double quotes for the whole string, so it would need extra double quotes to make the string work. It is much simpler to use the single quotes with the concatenation and it is actually how a SQL Statement on SQL Server delimits text; with single quotes.

Does that help?

 

[pbaldy]

Your FROM and GROUP BY clauses are reversed. You can try to print the whole thing:

Debug.Print strSQL & strHaving