Solved

Strange query results...

Posted on 2014-03-20
6
188 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 32

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 33

Expert Comment

by:paulmacd
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 32

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now