Solved

Strange query results...

Posted on 2014-03-20
6
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 143

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with MySQL query - graph 3 46
GitHub 1 40
Estimating my database size 7 51
Error creating summetric key on SQL 2012 (Amazon) 7 46
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

738 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