Solved

Strange query results...

Posted on 2014-03-20
6
191 Views
Last Modified: 2014-03-20
Hello all.  I am hoping to find someone who is willing to help me, since I am still using Classic ASP.  ;oP
 
I am running the following query, but am getting no results when there is actually 1 result in the database.  When I run the same query in Access, I get the '1' result.  If there are more than one result, then it runs fine, including the first result.  Can anyone explain this to me?

Here is the Query that I am running...
 
sQry = "SELECT dbEmpLogin, EmpName, EmpNumber, dbEmpLogin, EmpName, WorkDate, SCA_Code, SCA_Activity, SCA_Wage, Emp_Wage, JobDesc, Hours FROM tblEmpTime WHERE (((dbEmpLogin)='" & vEmpID & "') AND ((tblEmpTime.WorkDate) Between #" & vFrom & "# And #" & vTo & "#) AND ((tblEmpTime.SCA_Activity)='" & vActivity & "') AND ((tblEmpTime.dbCaseNo) Not Like 'All Projects%' And (tblEmpTime.dbCaseNo) Not Like 'Bereavement' And (tblEmpTime.dbCaseNo) Not Like 'Holiday' And (tblEmpTime.dbCaseNo) Not Like 'Jury Duty' And (tblEmpTime.dbCaseNo) Not Like 'Sick Leave' And (tblEmpTime.dbCaseNo) Not Like 'No Project and Task%' And (tblEmpTime.dbCaseNo) Not Like 'Vacation'))"

Set rsEmpSCA = oConn.Execute( sQry )
 
   While NOT rsEmpSCA.EOF
          vHours = vHours + rsEmpSCA("Hours")
   rsEmpSCA.MoveNext
   Wend

Open in new window


Thanks,

Mike
0
Comment
Question by:tmss_it_dept
6 Comments
 
LVL 33

Accepted Solution

by:
Big Monty earned 200 total points
ID: 39942427
to verify that your query is running properly, you can check to see if any records have been returned:

Set rsEmpSCA = oConn.Execute( sQry )
Response.Write rsEmpSCA.BOF and rsEmpSCA.EOF

if you get a value of true, then your recordset is empty.

you can also try changing your loop to a do while:
do While NOT rsEmpSCA.EOF
          vHours = vHours + rsEmpSCA("Hours")
          rsEmpSCA.MoveNext
   loop

one final thing to try is doing a response.write sQry of the record in questions and run that query directly in the database
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39942434
With one record, you're at EOF right from the start.

Try something like...

...
   IF NOT rsEmpSCA("Hours") = "" THEN
          vHours = vHours + rsEmpSCA("Hours")
   END IF

   While NOT rsEmpSCA.EOF
          vHours = vHours + rsEmpSCA("Hours")
   rsEmpSCA.MoveNext
   WEND
...
0
 
LVL 5

Expert Comment

by:indrajitmahajan
ID: 39942441
Hi..

It seems.. below part of query ,might be creating problem --
 ((tblEmpTime.WorkDate) Between #" & vFrom & "# And #" & vTo & "#)

Instead of above sql, you may write the same as:
 ((tblEmpTime.WorkDate)  >=  #" & vFrom & "# OR (tblEmpTime.WorkDate)  <=  #" & vTo & "#)

try above sql in your query and try
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39942449
you have 2 times EmpName in your SELECT, that can be an issue ...
0
 

Author Comment

by:tmss_it_dept
ID: 39942643
@ Big Monty

Thank you for your response.  I did as you suggested and looked for an empty record set, and it came up empty.  Hmmm, that didn't make sense to me.  Then I snapped that this record had a field with data that I didn't want in my results.  

See, I had a query giving me distinct codes, and then my second query used that list to get all records related to those codes.  Well, it turns out that my second query was missing the filtering that the first query had, so I was getting results that shouldn't have been there.  :)  Happens to all of us, right?

Thank you again for all your help...

Mike
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39942659
can't tell you how many times I've done something very similar :)

glad ya got it working tho!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question