Search tool in front end Excel Userform

[cken21]

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

 

[PHV]

Which database ?
what are the type and value of DateFrom and DateTo ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cken21]

The DB and DateFrom/To values are declared here


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=\\c900000fsl0001\public$\dbSwitchingFeedback1.mdb;"
' open a recordset for the correct table


DateFrom = Format$(tbDateFrom.Value, JetDateFmt)
DateTo = Format$(tbDateTo.Value, JetDateFmt)

 

[PHV]

Well, what is JetDateFmt ?

Anyway, I'd use this:

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cken21]

Hi,

Thanks for the reply, i tried this and 0 values are still returned, im not sure but i dont think the date format is what is causing the problem, i thought it might be something in this statement

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

 

[cken21]

JetFmt is a global constant in excel, i have it setting the date to a UK date as excel and access have US date format as their default setting

 

[SkipVought]

Heed PHV's advice and use the UNAMBIGUOUS yyyy-mm-dd format.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

It may help you to add this BLUE line of code:

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

and see your actual SQL in Immediate Window. You can run it in your DB to see if/why you get/don't get records.

Have fun.

---- Andy

 

[cken21]

Hi,

I have made the above changes and the txt boxes are still returning 0 values any suggestions?

 

[Andrzejek]

Then again, what do you get in the [tt]Debug.Print[/tt]?

Have fun.

---- Andy

 

[cken21]

Nothing happens when i add Debug.Print into the code

 

[cken21]

Sorry, by nothing happens i mean the sql statement returns the same value

 

[Andrzejek]

Would it be too much to ask: "Could you show us your SQL from Debug.Print line"?

Have fun.

---- Andy

 

[SkipVought]

cken21,

You are exasperatingly stingy with helpful information.

You could help YOURSELF out a lot, if you were to think a little, like the guys who are attempting to figure out what is going on in your private world. They can neither see into your head, nor into your application, except for the meager data you have been providing.

They are bravely attempting to help you. You do not seem to care about helping YOURSELF! This as been painful to observe.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

So sorry not able to post the code as the file is saved on my work system, good news is I did manage to resolve the issue before I left for the day. The error was on the excel sheet that the SQL statement copies the data. Thanks for the time and effort you put into answering my question.



Skipvought- I have taken your comments on board and will provide as much useful data as possible in the future.

Thanks again

 

[SkipVought]

Yes, please provide clear, concise and complete information AND please be diligent to discover you own errors before asking for time from others.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}