Database application query problem

Hello everyone,

This is my first post and i am new to this forum and i have a little problem in the application i was making.
My application stores the data provided to it in the database without any problems, but this application is date dependent. It stores the date properly in the database, but i have to create a report that prints the sum of the time (ie the total time in the db) for the current month, last month, last seven days.

How can this be done?? How can time be added and according to the current month?? any ideas please help
I added an extra entry in the db that stores time in this fashion: 102259 ie 10:22:59 which can be easy for adding but again adding directly would give wrong answers.

I dont remember the sql query for time and the different operators, this is what i recollect:
select sum(time) from cmdb where date between first and second;
i know the above query has errors, but please help me with the query.
It would be really nice if somebody can help with a code.

Thanks

Welcome to VBForums :wave:

Thread moved to Database Development forum - which is where SQL questions belong

You've given a fairly good explanation of what you want, but unfortunately haven't given enough info for us to be able to help yet. The kind of things we'll need to know are:

• Which database system are you using? (eg: MS Access 2003, SQL Server 2000, SQL Server 2005 Express, MSDE 2000, MySQL 4.1, Oracle 9, etc)
• What are the names and data types of the fields?
• Please also give a few example records of the data in your table and an example of the data you want the query to produce based on those records.

Thanks for the reply and sorry for the incomplete information.
I am using VB 6 and Ms access is the Database(Ms access- version prior to MS access 2000).

The database fields are:
module (text)
probem_desc (text)
attended_by (text)
call_in (date/time)
start_time (date/time)
end_time (date/time)
total_time (text) --->end time-call_in time
desc (text)
parts_replaced (text)
tot_time (number) --------- the total time i mentioned earlier is same as total_time but in such format: 102259

query would be based on total_time or tot_time for adding the total time based on date(current month,last 30 days , last 7 days)

Can u please help on how to do the connectivity using VB6 for the above query.

Thanks

Welcome to the forums. :wave:

Quote:

---

Originally Posted by cool11

Can u please help on how to do the connectivity using VB6 for the above query.

---

Before you can run a query from your VB6 program, you have to be able to connect to the database.

Have you gotten that far yet?

yes that is done, but for retrieving information and the above thing i had difficulties with it.

The format you use to store the data makes this very complex.. what I would recommend is either storing it as a Date/Time, or as a number of seconds (so 10:22:59 would be 10*(60*60)+22*60+59=37379).

However, due to your note (total_time (text) --->end_time-call_in time) I would recommend not storing it at all - as you can calculate it easily when needed.

If you do that, the query can be like this:

Code:

---

SELECT Sum(end_time-call_in time)
FROM cmdb
WHERE call_in Between #07/01/2008# and #07/31/2008#;

---

The issue you get with this tho (or storing the total as a Date/Time) is that the display will be odd when the total is a day or more - as it will show a particular date (I think 1 day is something like 1 Jan 1890 !), which you will need to format appropriately in your program.

You will get that kind of issue whatever route you take, as the number of seconds would also need to be formatted appropriately.

Thanks for the query,
the code that i made for the retrieval looks like this:
Dim ds1 As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
ds1.Provider = "microsoft.jet.oledb.4.0"
ds1.Open "C:\sup\db2.mdb"

rs1.Open "CL1", ds1, adOpenDynamic, adLockOptimistic


que = "SELECT Sum(end_time-call_in)"
que = que + "From CL1 WHERE call_in Between #07/01/2008# and #07/31/2008#;"
Set rs1 = ds1.Execute(que)
temp = (Val(rs1("end_time-call_in"))) ----> error
Label1.Caption = temp

End Sub


The error i get there is "The item cannot be found in the collection corresponding to the requested name or ordinal"

however i was having difficulties in the same line, on how to retrieve the data and display it.

I have another query is that will the query be able to subtract correctly? as the data that is stored is date as well as time
and call_in contains data in terms of date/time so would it be able to filter the date in 'where' clause? or do i have to use a seperate date field that stores only the date?

waiting for your reply on your suggestion and what correction is required for the code

Thanks

Quote:

---

Originally Posted by cool11

The error i get there is "The item cannot be found in the collection corresponding to the requested name or ordinal"

---

That means you specified a field that is not in the recordset.. unfortunately as it is, you cannot be sure what the field will be called (it will be automatically given a unique name).

The way I would recommend to deal with it is to explicitly give it a name in the query, like this:

Code:

---

que = "SELECT Sum(end_time-call_in) as TotalTime"

---

..and then use that name to read the field:

Code:

---

        Label1.Caption = rs1("TotalTime").Value

---

Quote:

---

I have another query is that will the query be able to subtract correctly? as the data that is stored is date as well as time
and call_in contains data in terms of date/time so would it be able to filter the date in 'where' clause? or do i have to use a seperate date field that stores only the date?

---

Date/Time fields always store date and time, and it is perfectly fine to add/subtract them.

It is fine to query on just the date, but not quite how I did it... if you don't put in a time, it gets treated as if you had specified midnight (which means what I posted will only find items for 07/31 that have no time, or a time of midnight). To deal with that, the easiest way is to specify the maximum possible time for the later date, ie:

Code:

---

que = que + "From CL1 WHERE call_in Between #07/01/2008# and #07/31/2008 23:59:59#;"

---

The query looks good but i am getting an error, maybe because of some minor mistake, the error that i get is:
"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

where could the mistake? i tried to figure out but was not so successful.

Thanks

Unfortunately I can't see what the issue is based on the posts in this thread, so show us your current code.

This is the code:

Dim ds1 As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
ds1.Provider = "microsoft.jet.oledb.4.0"
ds1.Open "C:\sup\db2.mdb"

rs1.Open "CL1", ds1, adOpenDynamic, adLockOptimistic
que = "SELECT Sum(end_time-call_in) as TotalTime"
que = que + "From CL1 WHERE call_in Between #07/01/2008# and #07/31/2008#"
Set rs1 = ds1.Execute(que)
Label1.Caption = rs1("TotalTime").Value

End Sub

Ah.. I made a typo, there needs to be a space between the field alias and the From, ie:

Code:

---

que = "SELECT Sum(end_time-call_in) as TotalTime "

---

I get a runtime error '94' at this point:
Label1.Caption = rs1("TotalTime").Value

The error says "Invalid use of NULL"
dunno whats happening

Thanks

Open up the database and take a look in the table.. check if there are any blanks (or technically, Nulls) in the end_time field, and do the same for the call_in column.

How you stop the error from happening depends on which of those two columns have got Nulls in them, and how you want to act when they do have Nulls.

Assuming that end_time is the only one (I guess it can be set a while after the record has been created), and that when it is you simply want to ignore that record, you can do that by adding the following to the end of your SQL statement:

Code:

---

que = que & " AND end_time Is Not Null"

---

Just to add to what Si's already said, there is another way you could get a null returned in that field. If you do a sum across an empty recordset you'll get a null returned when you probably actually want a zero. So if there are no records whose call_in falls between your two dates you'll get a null returned.

To get around that probably the best thing to do would be to use Si's technique to eliminate records with a null end_time and then, if you still have a null total, use the nz funtcion to translate it to a zero:-

Code:

---

SELECT nz(Sum(end_time-call_in) ,0)as TotalTime
From CL1 WHERE call_in Between #07/01/2008# and #07/31/2008#
 AND end_time Is Not Null

---

I was still getting the "Invalid use of NULL" error but i used vbNullString, the error was removed but there was no output. the current code is:

Code:

---

    Dim ds1 As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    ds1.Provider = "microsoft.jet.oledb.4.0"
    ds1.Open "C:\Machine\db2.mdb"
   
    rs1.Open "CL1", ds1, adOpenDynamic, adLockOptimistic
    que = "SELECT Sum(end_time-call_in) as TotalTime "
    que = que + "From CL1 WHERE call_in Between #07/01/2008# and #07/31/2008#"
    que = que & " AND end_time Is Not Null;"
    Set rs1 = ds1.Execute(que)
    Label1.Caption = rs1("TotalTime").value & vbNullString

---

I checked the database before and checked it again there are no null strings in both the fields, not even in one record.
I am still wondering what is going wrong.
I tried the query in Visual data manager, it returns the query in floating point. What would be the result of the query ? in hours or some decimal?

@FunkyDexter:
I tried the nz function but it says "Undefined function nz in expression"

Guys need your help

Thanks

I'm guessing you're using a very old version of access then. Try this instead:-

Code:

---

iif(isnull(Sum(end_time-call_in), 0, Sum(end_time-call_in))

---

It's the same principle but avoids the nz function. Is Sum(end_time-call_in) it will return a zero, otherwise it will return the sum.

Quote:

---

Originally Posted by cool11

there are no null strings in both the fields, not even in one record.

---

Are there any Nulls in either of the fields?

By the way, "null string" is not apt, as Null is not a value of any kind - it is the complete absence of value (or indicator of "this hasn't been set"), rather than being a string with no length (which would be a value, albeit an empty one). Unfortunately some of the VB constants (like vbNullString) can make that harder to understand, as they aren't actually Nulls but rather something else which has been given a technically inaccurate name.

As the fields are Date/Time, they cannot contain a String - only date and/or time, or Null.

Quote:

---

I tried the query in Visual data manager, it returns the query in floating point. What would be the result of the query ? in hours or some decimal?

---

It's something you don't actually need to worry about (but technically is days since a certain date, with the decimal part being hours etc), as VB understands what it means - all you need to do is tell it to convert that value to a Date, which you can do like this:

Code:

---

    Label1.Caption = CDate(rs1("TotalTime").value)

---

No there are no nulls in either of the field, because i by default enter the system date + time in the form during data entry.. just have to change the end time during data entry. I checked the data in the database and it is consistent and without any nulls(blanks)

Thanks

In that case I'm not sure what the problem is.. could you upload your mdb file so we can try it out?

(unfortunately you'll need to put it in a zip file before you can upload it here, which you can do by clicking "go advanced" then "manage attachments").

Thanks a lot, you rock man! :wave:
Well the current problem is solved, it was a really stupid mistake i did :P
the query date was "#07/01/2008# and #07/31/2008#" whereas it should have had been "#08/01/2008# and #08/31/2008#" for the data in the database.

The current working code is:

Code:

---

    Dim ds1 As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    ds1.Provider = "microsoft.jet.oledb.4.0"
    ds1.Open "C:\Machine\db2.mdb"
   
    rs1.Open "CL1", ds1, adOpenDynamic, adLockOptimistic
    que = "SELECT Sum(end_time-call_in)as TotalTime "
    que = que + "From CL1 WHERE call_in Between #08/01/2008# and #08/31/2008#"
    que = que & " AND end_time Is Not Null;"
    Set rs1 = ds1.Execute(que)
 
    Label1.Caption = Format(CDate(rs1("TotalTime").Value), "hh:mm:ss") & vbNullString

---

I have uploaded the mdb file for ur reference
Thanks a lot for all the help you have given.

The current code gives proper sum of time if the hrs is less than 24 hrs, but if the time exceeds 24 hrs then it chops of the multiple of 24, what should i do to make it proper hrs?
I also want to ask you something about reports tomorrow.

Thanks again :)

Quote:

---

Originally Posted by cool11

it was a really stupid mistake i did :P
the query date was "#07/01/2008# and #07/31/2008#" whereas it should have had been "#08/01/2008# and #08/31/2008#" for the data in the database.

---

Ah, we've all done that at least once! (I'm probably into the thousands by now :sick: )

Quote:

---

The current code gives proper sum of time if the hrs is less than 24 hrs, but if the time exceeds 24 hrs then it chops of the multiple of 24, what should i do to make it proper hrs?

---

The easiest way is to find the difference from the default date/time, which due to the way VB actually stores dates is equivalent to the number 0.

To find the difference between two dates/time (in the units you choose), you can use the DateDiff function, so the code could be like this:

Code:

---

Label1.Caption = DateDiff("h",0,rs1("TotalTime").Value) & Format(CDate(rs1("TotalTime").Value), ":mm:ss")

---

It works pretty fine for the hour format, but the mins is displayed wrong and is always displayed as 12. Seconds is displayed properly.
What can be the issue?

Thanks

Ah, I didn't check that part of your code (even tho I copied & pasted, then put a highlight right next to it!) :o

For the Format function, "mm" means months and "nn" means minutes. In some cases the function will automatically correct that mistake for you (like before, when you obviously intended a common time format), but it wont always (like now that I have changed it to an unusual time format!).

Sorry for the late reply i was not at home yesterday,
The solution works absolutely fine, Thanks a ton! .
I want to make a report with different records from the database in a tabular form and take print out of report.
Which would be the best component for storing data in tabular form and which would be the best component for printing reports(So that it would be easy for a novice like me to code it :p ).

Thanks

For either of those things, it will be much easier if you do this formatting within the SQL itself, so change the first line to this:

Code:

---

    que = "SELECT DateDiff('h',0,Sum(end_time-call_in)) &  Format(Sum(end_time-call_in), ':nn:ss') as TotalTime "

---

..and your code to use it back to this:

Code:

---

Label1.Caption = rs1("TotalTime").Value

---

It should be fine, but run it to check that it is.


The most common components for showing data in a tabular form are the FlexGrid and the ListView. To put the data into them from a recordset, see either the "FlexGrid: fill from recordset" link in my signature, or the FAQ article How can I fill a ListView with values from a database?.


In terms of printing, it is up for debate! I personally prefer to make use of Excel, which is quite easy to do and has lots of features, but does need Excel to be installed.

I think listview should be easy... i guess(am trying right now)
But suppose i have 10 cols and 20 rows, not all fields are to be filled from database but some have to be filled manually at runtime. Can i do this using listview?? I also want to do rowspan and colspan, can i do it?

And i have to print the entire report, can i do it using excel? or any standalone code?

Thanks

As you are filling controls using code (rather than binding them to a control) having the manual input is definitely possible, but the methods are debatable. A common option is to use a textbox and a button, putting the text in when the button is pressed (the code will be similar to part of the examples to fill from the database, so make sure you understand the code in them).

I'm not sure what the options are for rowspan and colspan, I don't think the ListView is good for that - the FlexGrid should be tho (check out the help for the Merge properties).


As to printing, you can do it using Excel (if it will be installed on the same computer as your program), see the Tutorial link in my signature. If you have further questions about that (or other methods for printing), it would be best to start a new thread about it in the VB6 forum.

Sorry again for the late reply,
Actually the earlier code is supposed to be used in here. I used a label just to check if it is working properly. I am using the code for one of the fields. Most of the fields have to be entered manually.

My current code looks like:

Code:

---

Private Sub Command1_Click()
    Dim ds1 As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    ds1.Provider = "microsoft.jet.oledb.4.0"
    ds1.Open "C:\sup\db2.mdb"
   
    rs1.Open "CL1", ds1, adOpenDynamic, adLockOptimistic
    que = "SELECT DateDiff('h',0,Sum(end_time-call_in)) &  Format(Sum(end_time-call_in), ':nn:ss') as TotalTime "
    que = que + "From CL1 WHERE call_in Between #" + Trim(Text1.Text) + "# and #" + Trim(Text2.Text) + "#"
    que = que & " AND end_time Is Not Null;"
    Set rs1 = ds1.Execute(que)
    MSFlexGrid1.TextMatrix(1, 5) = rs1("TotalTime").Value
       
   
End Sub

Private Sub Form_Load()
Text1.Text = "08/01/2008"
Text2.Text = "08/31/2008"
MSFlexGrid1.RowHeight(0) = 600
MSFlexGrid1.ColWidth(0) = 1350
MSFlexGrid1.ColWidth(1) = 1150
MSFlexGrid1.ColWidth(2) = 1750
MSFlexGrid1.ColWidth(3) = 850
MSFlexGrid1.ColWidth(4) = 1350
MSFlexGrid1.ColWidth(5) = 1350
MSFlexGrid1.ColWidth(6) = 1350
MSFlexGrid1.ColWidth(7) = 1750
MSFlexGrid1.ColWidth(8) = 850
MSFlexGrid1.ColWidth(9) = 1750

'FlexGrid_AutoSizeColumns MSFlexGrid1, Me
MSFlexGrid1.TextMatrix(0, 0) = "Production Line"
MSFlexGrid1.TextMatrix(0, 1) = "Machine ID"
MSFlexGrid1.TextMatrix(0, 2) = "Fault"
MSFlexGrid1.TextMatrix(0, 3) = "No. of Days"
MSFlexGrid1.TextMatrix(0, 4) = "Estimated Hours"
MSFlexGrid1.TextMatrix(0, 5) = "Total Breakdown"
MSFlexGrid1.TextMatrix(0, 6) = "Maintenance Ratio"
MSFlexGrid1.TextMatrix(0, 7) = "Action Taken"
MSFlexGrid1.TextMatrix(0, 8) = "In Operation (Yes/NO)"
MSFlexGrid1.TextMatrix(0, 9) = "Remarks"
End Sub

---

Isn't there a simple way for managing the size of the cols as Text size changes? Since i have to put a button for manually entering the values and theres no space on the current form, so do i have to put a button and load another form for entering the values?
I am also having difficulties with the merge property.
Pls help me.

For printing i will make another topic

Thanks

Your method of inputting dates will only work properly if the textboxes contain a value formatted as mm/dd/yyyy. If you are certain of that then there is no need to worry, but I would recommend using one of the date based controls (also look nicer & easier to use), and using the Format function when building the SQL.

Quote:

---

Isn't there a simple way for managing the size of the cols as Text size changes?

---

See the "FlexGrid: AutoSize columns" link in my signature.

Quote:

---

so do i have to put a button and load another form for entering the values?
I am also having difficulties with the merge property.

---

You may get useful ideas from these threads:

http://www.vbforums.com/showthread.php?t=231160 (allow editing directly in the grid itself)
http://www.vbforums.com/showthread.php?t=360210 (lots of useful tips & tricks for the FlexGrid)

I had already tried the "FlexGrid: AutoSize columns" link from your signature(The example usage is already there in my code- i commented it). All the cols are shrinked after applying the example usage(FlexGrid_AutoSizeColumns MSFlexGrid1, Me), Am i doing it right or is it wrong somewhere?

Thanks

So it is, I hadn't scrolled down. :blush:

You weren't doing it correctly, as you were calling it before putting text into the grid - it only works when you call it (it doesn't keep on working after that), based on the text that is in the grid at that time.

The code works absolutely fine, Thanks
But i want to print the page so if the text entered is more, then would it print properly or we will have to format the text so that it automatically increases the width of the col and the text goes to next line (using word wrap).

Thanks

The width of columns (and wrapping) in the grid is for the screen, and is not relevant to printing unless you are planning to print a picture of the grid - I would recommend adding the same data to Excel instead (it will just take one or two lines of code to do that, and a few more for formatting/sizing), as it is unlikely that the screen will be the same size as the paper.

Hi again,

Thanks for the links, they were helpful. I found one post by hack to directly insert text into msflexgrid.
The code works fine but i forgot the most important thing:
I actually want to omit the sum(end_time-call_in) during weekends ie saturday and sunday (ie maybe subtract the weekend sum values from the answer of the previous query). Or if we use a calender for selecting the dates we want to omit. Can we do that??

and how can we align the text of Msflexgrid to center?

and can we use a combo box in msflexgrid for the "Yes/No" selection?

Thanks

Quote:

---

Originally Posted by cool11

I actually want to omit the sum(end_time-call_in) during weekends ie saturday and sunday (ie maybe subtract the weekend sum values from the answer of the previous query).

---

You can find out the weekday of a date by using the Format function (with "w" as the second parameter), so you can exclude all weekend values by adding this to your query:

Code:

---

    que = que & " AND format(call_in,'w') NOT IN(1,7) "
    que = que & " AND end_time Is Not Null;"

---

(the values returned by Format will be 1 for Sunday, 2-5 for Mon-Fri, and 6 for Saturday).

Quote:

---

Or if we use a calender for selecting the dates we want to omit. Can we do that??

---

You could do, in this case the additional line could be like this:

Code:

---

    que = que & " AND Int(call_in) NOT IN(" & Int(DateValue1) & "," & Int(DateValue2) & ") "

---

(due to the way VB and Access store dates, using Int at both ends is a valid way to compare just the dates and ignore the times)

You can add more dates by repeating the red section, or check just one date by removing it.

Quote:

---

and how can we align the text of Msflexgrid to center?

and can we use a combo box in msflexgrid for the "Yes/No" selection?

---

As those questions are not related to your original question, they should be in a new thread (in the VB6 forum).

Many people who could help you won't even see your question here, as not only is it hidden among other things, but also not many people look at threads that are as long as this one.

Thanks for your reply,
I will make a thread for those questions, sorry for combining things together.

Quote:

---

que = que & " AND format(call_in,'w') NOT IN(1,7) "
(the values returned by Format will be 1 for Sunday, 2-5 for Mon-Fri, and 6 for Saturday).

---

Not in(1,7) --> is it 1-Sunday and 7-Saturday?
then it shouldn't it be 2-6 for mon-fri and 7 for sat?

Thanks again

oops, you are right - a bit of a typo there! :blush:

I tried the code for weekends, it works fine. But for the dates i am using a listbox for the dates. It gives a "Type mismatch" error at runtime. The code is:

Code:

---

    rs1.Open "CL1", ds1, adOpenDynamic, adLockOptimistic
    que = "SELECT DateDiff('h',0,Sum(end_time-call_in)) &  Format(Sum(end_time-call_in), ':nn:ss') as TotalTime "
    que = que + "From CL1 WHERE call_in Between #" + Trim(Text1.Text) + "# and #" + Trim(Text2.Text) + "#"
    que = que & " AND Format(call_in,'w') NOT IN(6,7) "
    que = que & " AND Int(call_in) NOT IN "
    For iloop = 0 To List1.ListCount
    que = que & " (" & Int("+List1.list(iloop)+") & ") "    ----->error
    Next iloop           
    que = que & " AND end_time Is Not Null;"
    Set rs1 = ds1.Execute(que)
    MSFlexGrid1.TextMatrix(1, 5) = rs1("TotalTime").Value

---

Can u check where am i doing it wrong?

Thanks

The first reason to get that error is that you have put in extra characters that weren't in my example ( "+ and +" ). By doing that you weren't using the listbox at all, but simply the fixed text +List1.list(iloop)+


When you correct that the error will actually still be there - because a ListBox stores Strings, not Dates.

For information on the difference etc, see the article Why are my dates not working properly? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

At the end of that article is the safe way of getting a Date value from a String - but you need to be sure which format the String contains (ie: what format was used when putting the values into the list). Note that while several people suggest CDate, that is not safe - as explained in the article.