Excel Userform search tool

Printable View

Oct 24th, 2011, 06:18 AM
cken21

Excel Userform search tool

Hi,

Can anybody help me with the following code, this is an excel userform acting as the front end of a database. What this tool does is searches the database and return the number of entries present where a specified condition is met, in this case it refers to escalated "Yes" or "No". I not receiving any run time error but the 3 fields to be filled always return a value of 0 which is not accurate. i have posted the code below.

Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM CompD WHERE [Date] >= " & DateFrom & " And [Date] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalFeedback = rs.RecordCount
tbCom.Value = totalFeedback
Set rs = Nothing

Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM CompD WHERE [Escalated] = '" & "Yes" & "' AND [Date] >= " & DateFrom & " And [Date] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalEsc = rs.RecordCount
tbEsc.Value = totalEsc
Set rs = Nothing

Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM CompD WHERE [Escalated] = '" & "No" & "' AND [Date] >= " & DateFrom & " And [Date] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalNonEsc = rs.RecordCount
tbNonEsc.Value = totalNonEsc
Set rs = Nothing
Oct 24th, 2011, 07:49 AM
si_the_geek

Re: Excel Userform search tool

Quote:

Originally Posted by cken21

this is an excel userform

In that case the VB6 forum is the wrong place to post - generally you should be posting in the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB.

In this case however, the issue is best in our 'Database Development' forum, so I have moved the thread there.

Quote:

I not receiving any run time error but the 3 fields to be filled always return a value of 0 which is not accurate. i have posted the code below.

Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM CompD WHERE [Date] >= " & DateFrom & " And [Date] <= " & DateTo

Based on an educated guess of what DateFrom and DateTo are and contain, that is not surprising... because you are not specifying any date values - you are specifying calculations that return small numbers (such as 24/11/2011 = 0.001085).

For an explanation and examples of delimiters to use around values within SQL statements, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)
Oct 25th, 2011, 04:38 AM
cken21

Re: Excel Userform search tool

Hi,

I have changed the code to match your recommendation but it is still returning 0

Private Sub ReportMI()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, rsoverall As ADODB.Recordset

' connect to the database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=dbSwitchingFeedback1.mdb;"
' open a recordset for the correct table

DateFrom = "#" & Format$(tbDateFrom.Value, "dd/mm/yyyy") & "#"
DateTo = "#" & Format$(tbDateTo.Value, "dd/mm/yyyy") & "#"

Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM CompD WHERE [Date] >= " & DateFrom & " And [Date] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalCom = rs.RecordCount
tbCom.Value = totalCom
Set rs = Nothing

Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM CompD WHERE [Escalated] = '" & "Yes" & "' AND [Date] >= " & DateFrom & " And [Date] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalEsc = rs.RecordCount
tbEsc.Value = totalEsc
Set rs = Nothing

Set rs = New ADODB.Recordset
searchstring = "SELECT * FROM CompD WHERE [Escalated] = '" & "No" & "' AND [Date] >= " & DateFrom & " And [Date] <= " & DateTo
rs.Open searchstring, cn, adOpenStatic
totalNonEsc = rs.RecordCount
tbNonEsc.Value = totalNonEsc
Set rs = Nothing

Response = MsgBox("MI Collated, Many Thanks", vbOKOnly, "Switching Department")
Oct 25th, 2011, 07:13 AM
si_the_geek

Re: Excel Userform search tool

When you post code please put it inside code tags so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]

You have made at least one mistake in implementing my suggestion, because a format string of "dd/mm/yyyy" is totally wrong - no matter how much you think it is right. The FAQ article explains why.

Another potential issue is what kind of objects tbDateFrom and tbDateTo are, but that is less likely to be a problem (ideally they should be date based objects [such as a DateTimePicker or Calendar], rather than text based [such as a Textbox]).
Oct 25th, 2011, 08:35 AM
cken21

Re: Excel Userform search tool

Since your last post i have reverted back to my original JetDateFm function as this has worked in similar forms i have created, the reason being any change to the input method of the date has not changed the outcome, ie dd/mm/yyyy or yyyy/mm/dd as you suggest. Below is the module where i declare the jet date format

Code:

Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l" Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l" Global Const JetTimeFmt = "\#hh\:nn\:ss\#;;;\N\u\l\l"

It is clear the error lies elsewhere in the code
Oct 25th, 2011, 11:56 AM
si_the_geek

Re: Excel Userform search tool

Quote:

Originally Posted by cken21

ie dd/mm/yyyy or yyyy/mm/dd as you suggest.

I certainly did not suggest the first of those (and have explicitly stated that it is always wrong), and didn't suggest the second either (it can have problems too).

The text about formats (which is one of the few things in bold) in the FAQ article is:

Quote:

US format (M/D/YYYY) or ISO format (YYYY-MM-DD).

Obviously I don't know what your JetDateFm function does (or how you call it, etc, etc), but at least the format strings you posted are the correct style - so I will assume that it is giving the correct strings.

Quote:

Originally Posted by cken21

It is clear the error lies elsewhere in the code

What data type is the field?

Can you show us one the SQL statements you have created (use the Watch window, or Debug.Print), along with some values from the table that you expect to be returned by it?

Have you tried (just as a test) removing the date conditions from the Where clauses to see if that returns anything?

By the way, rather than returning all of the data from the table and then counting all of the records, it is quicker and better for a variety of other reasons (such as locking, network speed, memory usage) to simply ask the database system to do the counting, eg:

Code:

Set rs = New ADODB.Recordset searchstring = "SELECT Count(*) FROM CompD ... rs.Open searchstring, cn totalCom = rs.Fields(0)