Solved

SQL PROBLEMS

Posted on 2009-05-09
10
177 Views
Last Modified: 2012-05-06
This SQL code below collects all my clocked in/out users and then opens a crystal report and shows my collected data.

The problem is if I put in a range of 01/04/2009 - 30/04/2009 it will give me dates for all the months in my database not just for the date range I want.
sql = "SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], " & _

                  "(SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog " & _

                  "INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code " & _

                  "WHERE EventType = 1 AND EventDate BETWEEN '" & Format(DateFrom.Value, "dd/MM/yyyy") & "' AND '" & Format(DateTo.Value, "dd/MM/yyyy") & "' ORDER BY EventDate"

Open in new window

0
Comment
Question by:richard_gar
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24345822
Hello richard_gar,

That suggests that your EventDate column is using a [n][var]char data type.  Is this true?  If so, can you
switch to datetime or smalldatetime?

Regards,

Patrick
0
 

Author Comment

by:richard_gar
ID: 24345859
well the thing is the table I am getting the data from has the clock in/out times in one column and I split that into two colums for my application to read it correctly

Example: 20/03/2009 13:12:00

So I have created a query in my database that the SQL code calls and in the query I split the data in the table I am getting the data from into two columns by doing the following in my query in MS ACCESS

SELECT CashierLog.CashierCode, CashierLog.CashierName, CashierLog.EventType, CDate(Format([EventDateTime],'dd/mm/yyyy')) AS EventDate, Format([EventDateTime],'Short Time') AS EventTime
FROM CashierLog;
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24345898
Hi,
If the type of the data columns in database is DateTime then try the following query.
sql = "SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], " & _

                  "(SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog " & _

                  "INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code " & _

                  "WHERE EventType = 1 AND EventDate BETWEEN Convert(DateTime, '" & DateFrom.Value & "', 103) AND Convert(DateTime, '" & DateTo.Value & "', 103) ORDER BY EventDate"

Open in new window

0
 

Author Comment

by:richard_gar
ID: 24345950
getting erro: Undefined function 'Convert' in expression.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24346029
I think you are using SQL Server 2000. If so then try this
sql = "SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], " & _

                  "(SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog " & _

                  "INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code " & _

                  "WHERE EventType = 1 AND EventDate BETWEEN CDate'" & DateFrom.Value & "') AND CDate('" & DateTo.Value & "') ORDER BY EventDate"

Open in new window

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 75

Expert Comment

by:Anthony Perkins
ID: 24346046
>>getting erro: Undefined function 'Convert' in expression.<<
I would have to guess you are not using MS SQL Server, but isntead are using MS Access.
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24346121
If it is SQL Server 2000 (or above), our experience with date matching shows that you we have to customize the date values as follows:

Start Date
Format(DateFrom.Value, "yyyy-MM-dd 00:00:00")

End Date
Format(DateTo.Value, "yyyy-MM-dd 23:59:59")

Replace your original query with the above and see the result. Hope it helps.
0
 

Author Comment

by:richard_gar
ID: 24347838
I am using access as the database
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24348163
Use the cdate function then.
0
 

Accepted Solution

by:
richard_gar earned 0 total points
ID: 24349610
I fixed the problem:
sql = "SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], " & _

                  "(SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], 1440+DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog " & _

                  "INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code " & _

                  "WHERE EventType = 1 AND EventDate >= #{0}# AND EventDate <= #{1}# ORDER BY EventDate"

            sql = String.Format(sql, Format(DateFrom.Value, "MM/dd/yyyy"), Format(DateTo.Value, "MM/dd/yyyy"))

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

867 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

21 Experts available now in Live!

Get 1:1 Help Now