Issues with Query/SQL String

[mission2java_78]

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)"

+


That's incorrect also.

Your parenthesis cannot be enclosed around the actual call to DCount...
Namely,

DCount(")" is incorrect.

Jon

 

[Kevin_S]

Hey Jon,

Any ideas on how I would fix this? Basically, Im trying to find is there is a match (count works fine for this) and then fire off some conde depending on the results.

I also thought about trying to loop through a recordset but I think this is the more direct approach as there will only ever be 1 record that matches these three criteria so, with the DCount I'll get a return of 1 or 0....

This is driving me crazy and is the last part of my db (of course!)

Kev

 

[mission2java_78]

DCOunt is just as slow as looping...I'd do this if I were you

Use an SQL Statement..with a condition..if the condition flags BOF then you know it does not exist...

for instance lets say we have a table of employees and we watn to find all of the employees with Age=20.

So I can tell easily using a recordset.

Dim db as Database
Dim rs as Recordset
Dim strSQL As string

strSQL = "SELECT * FROM Employees WHERE Age=20"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)

if rs.bof then
  'no one is 20 years old do something
else
  'at least one record exists...do something
  rs.MoveLast
  rs.MoveFirst
end if

Set rs =nothing
Set db = nothing

Jon

 

[Rich]

Try adding an unbound text box to the subform footer and use a Sum(Iif etc as its control source

 

[Kevin_S]

GOT IT!!!!

Here is the syntax:

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

Everything works Great now!!! Whew! That was fun! Thanks to everyone and dcx693 for your patience and for perseverance!

Take Care,
kev

 

[dcx693]

So,
DCount("[SubmissionID]", "dbo_WIP_InternalReview","([SubmissionID]='" & [Forms]![frmWIPMAIN]![SUB_ID]) & "') AND ([SectionPostionTitle]='DistrictSupervisor') AND ([SectionDecisionID]=1)")
will not work?

Jon, I'm not sure what you mean by

DCount(")" is incorrect.

I don't see that (or a form of it) in the expression.

 

[mission2java_78]

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))"
--------------------------------No------------------------------------------

strSQL = "SELECT * FROM yourTable WHERE SubmissionID= " & 
Forms!frmMain!YourField & " AND SectionPostionTitle='DistrictSupervisor' AND SectionDecisionID=1"

Set db = Currentdb
Set rs=db.openrecordset(strSQL)

if rs.bof
 'no records
else
 'records
end if

Set rs = Nothing
Set db = Nothing

Jon

 

[mission2java_78]

GOT IT!!!!

Here is the syntax:

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

Everything works Great now!!! Whew! That was fun! Thanks to everyone and dcx693 for your patience and for perseverance!

Take Care,
kev

Right it didnt work the first time cause look at 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))"

You're ending DCount with "

Jon

 

[dcx693]

Cool. I'm sorry about throwing you off with the syntax and all. Jon is right about D functions being very slow. I spend most of my time trying to avoid them! But in some cases, they work.

 

[dcx693]

Jon, when I wrote

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

I meant for the user to replace the last parameter in the Dcount expression. I think he understood because I think he got it to work with the final expression I sent him.

 

[Kevin_S]

Hey Jon,

Which do you think would be faster DCount or Your Looping Structure considering these tables are in SQL Server 2000?

I want this to be the most effecient means possible?

Thanks,
Kevin

 

[mission2java_78]

Hey Jon,

Which do you think would be faster DCount or Your Looping Structure considering these tables are in SQL Server 2000?

I want this to be the most effecient means possible?

Thanks,
Kevin

Kevin you're not looping though...check my code..where do you see a loop?

Jon

 

[mission2java_78]

Jon, when I wrote I meant for the user to replace the last parameter in the Dcount expression. I think he understood because I think he got it to work with the final expression I sent him.

Apparently not the first time:

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


__________________
Whats A Database?

 

[dcx693]

Dude, I wrote the final expression. I never wrote the first .

 

[Kevin_S]

OK - I didn't look close enough at the code

Is the creation of a recordset using SQL more effecient then the DCount?

 

[mission2java_78]

Dude, I wrote the final expression. I never wrote the first .

Dude no one said you did.

Jon

 

[mission2java_78]

OK - I didn't look close enough at the code

Is the creation of a recordset using SQL more effecient then the DCount?

Yes,

Because youre not doing anything in there but generating an SQL String first. Your DCount is performing an operation.
Since you're not looping and just checking rs.bof or not than I think this is sufficient. If it works with DCount and you dont want to modify it than just leave it. I tend to stay away from DCount etc..because 2 years from now you're going to be wondering what the hell you were doing. Its very hard to read.

Jon

 

[Kevin_S]

Ok Jon - I tried the code and implemented as follows:

Dim strRole As String
Dim strCheck As Integer
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

strRole = Forms!frmWIPMAIN!txtRole

Select Case strRole
Case Is = "DistrictPlanner"
strSQL = "SELECT * FROM dbo_WIP_InternalReview WHERE SubmissionID= " & Forms!frmWIPMAIN!SUB_ID & " AND SectionPostionTitle='DistrictSupervisor' AND SectionDecisionID=1"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.BOF Then
MsgBox "No Records!"
Else
MsgBox "RECORDS!"
End If
Set rs = Nothing
Set db = Nothing
End Select
End Sub

And it generated this error message:

Run Time Error '3622'

You must use the dbSeeChanges option with open recordset when accessing a SQL Server table that has an IDENTITY column

I've never seen error this before...? any ideas on how I can get ride of it?

 

[mission2java_78]

Dude, I wrote the final expression. I never wrote the first .

Please dont send me personal messages...if you had a problem with the solution or anything on this thread lay it out in public.
No one is making accusations..I saw the wrong usage of DCount and I pointed it out. So stone me...on with life...get over it.



Jon

 

[mission2java_78]

Sure,

I didnt even realize this was SQL Server.

You just need to add a parameter to the OpenRecordset method

Jon