DoCmd.RunSQL "SELECT... 2

[kjv1611]

I am a beginner at building Access Databases. I am attempting to build one currently at my job. My problem involves the DoCmd.RunSQL statement. I am attempting to run a SQL statement instead of an actual query in order to count records in a table that meet certain criteria. Here is the code I am using:
DoCmd.RunSQL "SELECT Count(*) AS [Count] FROM tblStatusAudit WHERE (((tblStatusAudit.Month)=[Forms]![frmStatusAudit]![cmbMonth]) AND ((tblStatusAudit.[Team Leader])=[Forms]![frmStatusAudit]![cmbTeamLeader]) AND ((tblStatusAudit.Status)=[Forms]![frmStatusAudit]![cmbStatus]));"

- I left it all on one line, so did not need the line breakers & _

I've been trying other methods, but was told this would be the simplest, b/c I just want to return a count to a text box on a form based on certain criteria. I've seen on some forums statements that you cannot do the statment with DoCmd.RunSQL "SELECT... because it you can only use the RunSQL command with action queries. Any ideas out there as to how to get around this problem?

 

[kjv1611]

One other note I forgot to include: When I run this line of code, I get the Runtime Error 2342: A RunSQL action requires an argument consisting of an SQL statement.

 

[mgolla]

Use the DCount method instead.

DCount("[AnyField]","tblStatusAudit","[Month]=[Forms]![frmStatusAudit]![cmbMonth] AND [Team Leader]=[Forms]![frmStatusAudit]![cmbTeamLeader] AND [Status]=[Forms]![frmStatusAudit]![cmbStatus]")

HTH
Mike

 

[MakeItSo]

The way I see it, you need a completely different approach:
Yes, RunSQL performs a query, but you cannot load the results into a variable with it. So it is for action queries.

You might be fine with the DCount function:

textbox=DCount("[Team Leader]", "tblStatusAudit","[Month]='" & cmbMonth & "' AND [Team Leader]='" & cmbTeamLeader & "' AND Status='" & cmbStatus)

Mark: First argument of Dcount is Field, second is Domain (table/query), third is where-criteria

Hope this helps,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[MakeItSo]

Hmpff - I'm getting slow....Yawn

 

[kjv1611]

Thanks to the both of you for your extremely quick responses. By the way, how did you put in the exact table/form names I listed? Do I know one of you?

 

[kjv1611]

Nevermind my last question - I forgot I posted it exactly as I have in my VB code - long day...

 

[mgolla]

MakeItSo

I wouldn't say your getting slow...it is hard to keep up with all the posts when there are so many good people on here helping out



HTH
Mike

 

[kjv1611]

When using the code from MakeItSo, I'm getting Syntax Error 3075:
It states:
Syntax error in string in query expression '{Month]='January' AND [Team Leader]='Smith, John' AND Status='ACT'.

The original code is:
Me.txtAuditCount = DCount("[Team Leader]", "tblStatusAudit", "[Month]='" & cmbMonth & "' AND [Team Leader]='" & cmbTeamLeader & "' AND Status='" & cmbStatus)

Any help here?

 

[mgolla]

Looks like you put a "{" where you should have put "["

{Month]='January' AND [Team Leader]='Smith, John' AND Status='ACT'

Change to

[Month]='January' AND [Team Leader]='Smith, John' AND Status='ACT'

HTH
Mike

 

[kjv1611]

Well, that was actually a typo in the entry I placed here. The part that says "the original code is:" is the code I actually entered. The part above that says "It States" is what Microsoft gave me (except it did not make the typo i made with the { instead of [. Any help on the Original Code?

Thanks for your patience..

 

[mgolla]

Ok, Change

Me.txtAuditCount = DCount("[Team Leader]", "tblStatusAudit", "[Month]='" & cmbMonth & "' AND [Team Leader]='" & cmbTeamLeader & "' AND Status='" & cmbStatus)


To:
Me.txtAuditCount = DCount("[Team Leader]", "tblStatusAudit", "[Month]='" & cmbMonth & "' AND [Team Leader]='" & cmbTeamLeader & "' AND Status='" & cmbStatus & "'&quot


HTH
Mike

 

[kjv1611]

THANKYOU! THANKYOU! THANKYOU!
Once the syntax was corrected, that statement worked perfectly. The total count is accurately showing up in the textbox of my form. :-Þ

 

[MakeItSo]

Thanks for specting the missing end quote, Mike!