Issues with Query/SQL String

[Kevin_S]

Hi everyone,

I am having an interesting problem with a query and SQL string that hopefully some of you good people can help me with. I'm working on an app that has an MS Access 2002 front end connected to SQL Server 2000 back end. I have a simple query that has 3 fields: Submission ID (text - Primary Key), SectionPositionTitle (Text), ManagerDecision (Integer). In the query I am trying to filter the results by adding criteria to each field like as follows: SubmissionID = SUB_ID on main form, SectionPositionTitle = "DistrictSupervisor", & ManagerDecision = 1. Its fairly simple and works perfectly fine as a stand alone query and returns the correct results. The SQL looks like this:

SELECT dbo_WIP_InternalReview.SubmissionID, dbo_WIP_InternalReview.SectionPostionTitle, dbo_WIP_InternalReview.SectionDecisionID FROM dbo_WIP_InternalReview WHERE (((dbo_WIP_InternalReview.SubmissionID)=[forms]![frmWIPMAIN]![SUB_ID]) AND ((dbo_WIP_InternalReview.SectionPostionTitle)="DistrictSupervisor") AND ((dbo_WIP_InternalReview.SectionDecisionID)=1));

Now, what I want to do is use this SQL in VBA to return a string value but when I try to run the code it errors and highlights this section: ="DistrictSupervisor") and tells me that there has been a compile error Expected: End of Statement.

Any idea why this will run in one situation but not in another?

Any/All help on this is greatly appreciated!
Thanks -Kevin

 

[The_Doc_Man]

You'll have to look up the "Limits" topic for queries. I have no idea why, and I don't have Access 2002 to test this, but I know that on AC97 and AC2K, there are TWO query string size limits. One of them is huge, maybe 64K. The other is tiny, maybe 256.

See if you can find the distinction in the "limits" help topic for queries.

 

[dcx693]

If this is the SQL code for your saved query and it runs fine, but not when run through code in VBA, it's most likely because of the way the query is referencing the form and string values. Usually, when called through VBA, the query syntax must be something like this:

Currentdb.Execute "SELECT dbo_WIP_InternalReview.SubmissionID, dbo_WIP_InternalReview.SectionPostionTitle, dbo_WIP_InternalReview.SectionDecisionID FROM dbo_WIP_InternalReview WHERE (((dbo_WIP_InternalReview.SubmissionID)=" & [forms]![frmWIPMAIN]![SUB_ID] &") AND ((dbo_WIP_InternalReview.SectionPostionTitle)='DistrictSupervisor') AND ((dbo_WIP_InternalReview.SectionDecisionID)=1));"

 

[Rich]

Change the double quotes on DistrictSupervisor to single

 

[Kevin_S]

Thanks for the replys - I really appreciate it!

Maybe I'm doing this whole thing wrong - let me explain what Im trying to accomplish: I have a subform with a many to one relationship to the main form through the Primary Key SubmissionID (Alpha-numeric key) Basically the subform (Intenal Review) is used by different supervisor stations to approve or deny a project. The supervisors sign off according to a hierarchy off control and one follows the other - if a lower level supervisor denies a project the rest do not have to sign off on it.

So what I have now is I capture the user's supervisory level during login and use that in a select case statement in the before update event on the ManagerDecision field to see if the supervisor before them has reviewed and signed off AND that the sign off is approved ( approved = 1). Here is what I have so far in the code for the before update:

Select Case strRole
Case Is = "DistrictPlanner"
Heres where I need to evaluate if the previous supervisor signed off and approved
'If the previous supervisor has not signed off OR has denied then
MsgBox "You cannot sign off on this WIP until it has been signed off by the District Supervisor or this project has been denied approval", vbCritical + vbOKOnly, "ACTION REQUEST DENIED"
Me.Undo
Else
Me.SectionPostionTitle = strRole
End If
Case Is = "Planning Chief"
etc.....
End Select
End Sub

This is what I was trying to accomplish in the SQL query... Is that the best method or the quickest? Would I be better served using a DLookUp?

All help is greatly appreciated!
Thanks in Advance,
Kevin

 

[Kevin_S]

dcx693 - I tried your suggestion but I think I didn't implement it correctly as it didn't work...

Rich - I tried the single qoutes and I didn't get an error so that was great! but I didn't get the result I was looking for either which leads me to believe that I am setting this up wrong from the beginning - could you possibly explain to me how I would go about setting this up correctly based on what I described above - or possible just the best possible method I should use to find a vaule in a subform datasheet?

Thanks for the help!
Kevin

 

[dcx693]

Argh! I made a bad suggestion. The Currentdb.Execute method is for executing action queries - not for opening "normal" queries. Sorry.

With the syntax I posted, you can use the CreateQueryDef method to create a saved query using the SQL text, then use the DoCmd.OpenQuery method to open it to view it.

 

[Kevin_S]

dcx693 - thanks for the continued help!

Based on what I am trying to do: Search through a select number of records and see if some conditions are met - is this the most effective approach (that is - opening a query in vba and looking at records) or is there a more effect method (DLookUp or other...?)

thanks,
Kevin

 

[dcx693]

If you wanted to display the search results in a subform datasheet, that would certainly be possible. When you've chosen your criteria from the mainform (which I assume is called frmWIPMAIN based on your posts above), you need to place some code either onto the Click event of a command button that activates the search or in the AfterUpdate event of one of those fields. In that code, you can set the recordsource of the subform using SQL code. I'll assume your subform is called MySubForm. Have a statement that says something like this:
Me!MySubForm.Form.RecordSource= strSQL
where strSQL is that long SQL string I posted above with the quotations.

 

[Kevin_S]

Sorry - I think I need to clarify what I'm trying to do:

I dont need to populate a subform recordsource - I have a subform that a user enters records into as a means to sign off (approve or deny) on a project. There is a hierarchy which must be followed in the business operation so what I want to do is before a user can approve or deny a project - check and make sure that the supervisor before them has signed off AND approved to project.

If this condition is met (previous supervisor HAS signed off AND their decision was to approve) then allow the next entry by the next supervisor in line to be made.

Where I am having trouble is that I need to check this condition and I am not sure weither I need to use a DLookUp or to create a recordsource and loop through that or some other method...? Unfortunately I'm a little shaky with both situations...

Thanks,
kevin

 

[dcx693]

OK, sorry I misuderstood. I went and reread the other postings. When you just need to do a count of how many records meet your criteria, a DCount will work just fine.

The syntax of the function is:
DCount("[name of field you want to count]","name of table or query that has that field","the conditions you want to check")

The last parameter is often the tricky one. You need specify it as if it were the "WHERE" part of a SQL string, but without the word WHERE.

So you'd use a statement like this:
DCount(" [SubmissionID]","dbo_WIP_InternalReview","(((dbo_WIP_InternalReview.SubmissionID)=" & [forms]![frmWIPMAIN]![SUB_ID]) & " AND ((dbo_WIP_InternalReview.SectionPostionTitle)='DistrictSupervisor') AND ((dbo_WIP_InternalReview.SectionDecisionID)=1))"

 

[Kevin_S]

dcx693 - I tried to implement the DCount as you posted it (I copied it over into the code and then used a simple if/then statement to check and see if a record was found but I got this error when I tried to run it:

Run Time Error '3075'

Missing ),], or Item in query expression '(((dbo_WIP_InternalReview.SubmissionID)=ALU02002'


I know were close!!!!

Thanks for all your help I really appreciate it!
Kevin

 

[dcx693]

Is [forms]![frmWIPMAIN]![SUB_ID] a numeric or text field?

 

[Kevin_S]

SUB_ID = text
SubmissionID = text
SectionPositionTitle = text
SectionDecisionID = number

 

[dcx693]

Aha, change the last part of that parameter to:
(((dbo_WIP_InternalReview.SubmissionID)='" & [forms]![frmWIPMAIN]![SUB_ID]) & "' AND ((dbo_WIP_InternalReview.SectionPostionTitle)='DistrictSupervisor') AND ((dbo_WIP_InternalReview.SectionDecisionID)=1))

Text parameters need to be enclosed within quotation characters, unlike numbers.

 

[Kevin_S]

I made the changes and now I'm getting this error:

Run Time Error '3075'

Syntax error in string in query expression '(((dbo_WIP_InternalReview.SubmissionID)=ALU02002'

different then the last error but still the same section of code. Here is the total code I have right now:

Private Sub SectionDecisionID_BeforeUpdate(Cancel As Integer)
Dim strRole As String
Dim strCheck As Integer

strRole = Forms!frmWIPMAIN!txtRole

Select Case strRole
Case Is = "DistrictPlanner"
strCheck = DCount("[SubmissionID]", "dbo_WIP_InternalReview", "(((dbo_WIP_InternalReview.SubmissionID)='" & [Forms]![frmWIPMAIN]![SUB_ID]) & "' AND ((dbo_WIP_InternalReview.SectionPostionTitle)='DistrictSupervisor') AND ((dbo_WIP_InternalReview.SectionDecisionID)=1))"
If IsNull(strCheck) Then
'DO something here
Else
'DO something else
End If
End Select
End Sub

thanks,
Kevin

 

[dcx693]

Are you sure you got the same error message? You posted the same error number and text. You should've gotten a different one after the last code change.

 

[Kevin_S]

Yep, same error number and message but different error parts:

The first error message was:

Run Time Error '3075'

Missing ),], or Item in query expression '(((dbo_WIP_InternalReview.SubmissionID)=ALU02002'

The newest one is:

Run Time Error '3075'

Syntax error in string in query expression '(((dbo_WIP_InternalReview.SubmissionID)=ALU02002'

same error but different trigger...

 

[mission2java_78]

Can I jump in?

This doesnt look right at all:

strCheck = DCount("[SubmissionID]", "dbo_WIP_InternalReview", "(((dbo_WIP_InternalReview.SubmissionID)='" & [Forms]![frmWIPMAIN]![SUB_ID]) & "' AND ((dbo_WIP_InternalReview.SectionPostionTitle)='DistrictSupervisor') AND ((dbo_WIP_InternalReview.SectionDecisionID)=1))"

Jon

 

[dcx693]

Ugh. Crap. I keep forgetting syntax. You need to enclose field names in brackets when using DCount, and you do not specify the table names again, since you've already done that in the second parameter.

Try:
"([SubmissionID]='" & [Forms]![frmWIPMAIN]![SUB_ID]) & "') AND ([SectionPostionTitle]='DistrictSupervisor') AND ([SectionDecisionID]=1)"