• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Strange query results...

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
tmss_it_dept
Asked:
tmss_it_dept
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
Paul MacDonaldDirector, Information SystemsCommented:
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
 
indrajitmahajanCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have 2 times EmpName in your SELECT, that can be an issue ...
0
 
tmss_it_deptAuthor Commented:
@ 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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can't tell you how many times I've done something very similar :)

glad ya got it working tho!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now