Solved

Strange query results...

Posted on 2014-03-20
6
192 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
If condition on Html with Asp 11 29
MYSQL responding very slow 3 27
Comparison query - 4 columns 9 28
Clear input text 15 9
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

820 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