Solved

SQL PROBLEMS

Posted on 2009-05-09
10
176 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

706 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