SQL statement in VBA

[skwilliams]

I have an unbound form 'frmWeeklyReprints" with an unbound date field "Text150". I also have a query "qryOCParts1" with sum totals based on another query "qryOCParts" to get totals for each day. I'd like to select a date from Text150 and through VBA query qryOCParts1 to pull the matching date. Here's my SQL statement.

strSQL = "SELECT Sum(qryOCParts1.Count) AS SumOfCount " & vbCrLf & _
"FROM qryOCParts1 " & vbCrLf & _
"GROUP BY qryOCParts1.EndOfWeek " & vbCrLf & _
"HAVING (((qryOCParts1.EndOfWeek)=[Text150]));"

strSQL comes up null.

Any ideas??

 

[pbaldy]

The variable is Null or the result of using it is Null? For starters, you have to concatenate the form reference into the string, with appropriate delimiters. For a date that would look like:

"HAVING qryOCParts1.EndOfWeek = #" & [Text150] & "#"

By the way, you don't need vbCrLf in the code. Access will ignore them.

 

[skwilliams]

I forgot about the date delimiters.

When I debug and run the procedure, then mouse over strSQL it says
strSQL=""

 

[pbaldy]

At what line? If the focus is on that line, it hasn't run yet. Make sure you're checking after that line has executed. This may also help:

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

 

[skwilliams]

Here's a sample of the data in qryOCParts1
SumOfCount EndOfWeek
2566 3/31/2012
6960 3/24/2012
7488 3/17/2012

If I selected 3/24/2012 from Text150 then Text16 prefills with 6960.

I'm getting this error.
Too few parameters. Expected 1.

The immediate window says:

SELECT Sum(qryOCParts1.Count) AS SumOfCount FROM qryOCParts1 GROUP BY qryOCParts1.EndOfWeek HAVING qryOCParts1.EndOfWeek = #3/24/2012#

 

[pbaldy]

Your description says the field name is "SumOfCount" but you have "Count" in the SQL. If it's really SumOfCount, that would cause that error.

 

[skwilliams]

When I run the code now, here's what I get.

Run-time error '3075':
Syntax error in date in query expression
'qryOCParts1.EndOfWeek=#[Text150]'.

Here's what shows up in the Immediate window.

SELECT Sum(qryOCParts1.Count) AS SumOfCount 
FROM qryOCParts 
GROUP BY qryOCParts1.EndOfWeek 
HAVING qryOCParts1.EndOfWeek=#[Text150]#;

 

[pbaldy]

What does your code look like? It looks like you aren't concatenating the form reference. You were in the last post.

 

[skwilliams]

I was using SumOfCount as the name for the counts sum. Its called count in qryOCParts but called SumOfCount in qryOCParts1 because I'm getting a daily total. Does that make sense or am I missing something? (Which is verrry possible)

 

[pbaldy]

You're querying qryOCParts1, so you have to use whatever fieldname qryOCParts1 returns; SumOfCount.

 

[skwilliams]

qryOCParts1 uses the field name SumOfCount.

 

[pbaldy]

So use that name in your SQL.

 

[skwilliams]

I've just attached a stripped down version of the database with all relevant tables, queries, forms, and VBA.

 

[pbaldy]

You couldn't change the field name? You can't do what you're doing anyway; the control source of a textbox can't be SQL. You can open a recordset on the SQL or use a DLookup():

http://www.mvps.org/access/general/gen0018.htm