Dcount for multiple colunms 1

[9milla]

I've been searching the forums for something remotely helpful with regards to using Dcount for multiple colunms.

I'm hopeful that I will find a solution.

I have a form with 2 controls. i.e. an [/i]unbound textbox[/i] named txtNumber and a command button
named cmdSearch.

[highlight #FF99FF]Scenario:[/highlight]
The user is prompted to insert a contact number and hits the Search button. Another continuous form loads
listing the results of the inserted criteria. Problem is that the table in which the data lies have 3 seperate columns
for contact numbers i.e [HomeTelNo], [WorkTelno] and [CellNo] in tblClientBase

[highlight white]
[ul square]
[li]tblClientBase[/li]
[ul circle]
[li][HomeTelNo][/li]
[li][WorkTelno][/li]
[li][CellNo][/li]
[li] etc...[/li]
[/ul circle]
[/ul square]
[/highlight]

I want Dcount to check to see if the 'Contact Number' exists in all columns before it proceeds to the 'Results Form'.

I have tried various and sometimes downright absurd variations. I know that I can always just use a SELECT STATEMENT in SQL,
but am hoping that there is a solution to use DCount in this manner.

The code that follows works.

[navy]Private Sub[/navy] cmdSearch_Click()
[navy]On Error GoTo[/navy] Err_cmdSearch_Click

    [navy]Dim[/navy] stDocName [navy]As String[/navy]
    [navy]Dim[/navy] strCount [navy]As String[/navy]
    [navy]Dim[/navy] directDb [navy]As Connection[/navy]
    [navy]Dim[/navy] searchRs [navy]As ADODB.Recordset[/navy]
    
    [navy]Set[/navy] directDb = CurrentProject.Connection
    [navy]Set[/navy] searchRs = [navy]New[/navy] ADODB.Recordset
    
    strCount = [b]"SELECT HomeTelNo, WorkTelNo, CellNo, ARARefNo " & _
               "FROM dbo.tblClientBase " & _
               "WHERE (HomeTelNo = '" & Me.txtNumber & "') " & _
               "OR (CellNo = '" & Me.txtNumber & "') " & _
               "OR (WorkTelNo = '" & Me.txtNumber & "')"[/b]
               
    searchRs.Open strCount, directDb, adOpenKeyset, adLockOptimistic
    
    [navy]If[/navy] searchRs.RecordCount >= 1 [navy]Then[/navy]
        stDocName = "frmSearchResultScreen"
        
        stLinkCriteria = "[HomeTelNo] LIKE '" & Me.txtNumber & "' " & _
                        "OR [WorkTelNo] LIKE '" & Me.txtNumber & "' OR " & _
                        "[CellNo] LIKE '" & Me.txtNumber & "'"
                    
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    [navy]Else[/navy]
        MsgBox "No records found for this criteria", vbInformation + vbOKOnly, _
        "ConsultantQ Error"
    [navy]End If[/navy]
    
    searchRs.Close
    
Exit_cmdSearch_Click:
    [navy]Exit Sub[/navy]

Err_cmdSearch_Click:
    MsgBox Err.Description
    [navy]Resume[/navy] Exit_cmdSearch_Click
    
[navy]End Sub[/navy]

Though I need something like this......

[navy]Private Sub[/navy] cmdSearch_Click()
[navy]On Error GoTo[/navy] Err_cmdSearch_Click

    [navy]Dim[/navy] stDocName [navy]As String[/navy]
    [navy]Dim[/navy] intCount [navy]As Integer[/navy]

    stDocName = "frmSearchResultScreen"
    stLinkCriteria = "[HomeTelNo] LIKE '" & Me.txtNumber & "' OR " & _
					"[WorkTelNo] LIKE '" & Me.txtNumber & "' OR " & _
                    "[CellNo] LIKE '" & Me.txtNumber & "'"

    intCount = DCount("[HomeTelNo] LIKE '" & Me.txtNumber & "' OR " & _
					"[WorkTelNo] LIKE '" & Me.txtNumber & "' OR " & _
                    "[CellNo] LIKE '" & Me.txtNumber & "'", "tblClientBase", " & _
					"[HomeTelNo] OR [WorkTelNo] OR [CellNo] = '" & Me.txtNumber & "' OR " & _
					"[WorkTelNo] LIKE '" & Me.txtNumber & "'")
                    
                    
    [green] 'intCount = DCount("[HomeTelNo]", "tblClientBase", _
           '"[HomeTelNo] = '" & Me.txtNumber & "'")
           'ETC..
           'ETC..
           'ETC..
           'If something like this is possible,
           'any help will be appreciated.[/green]
    
    [navy]If[/navy] (intCount >= 1) [navy]Then[/navy]
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    [navy]Else[/navy]
        MsgBox "No records found for this criteria", vbInformation + vbOKOnly, & _
		"ConsultantQ Error"
    [navy]End If[/navy]

Exit_cmdSearch_Click:
    [navy]Exit Sub[/navy]

Err_cmdSearch_Click:
    MsgBox Err.Description
    [navy]Resume[/navy] Exit_cmdSearch_Click
    
[navy]End Sub[/navy]

Hope I've managed to get my question across.

[highlight #FF99FF]
Technologies in play:
MS Access 2003
SQL Server 2005
[/highlight]

Thanks
9milla

"And so it begins!"
LOTR (Battle for Helms Deep)

 

[PWise]

what about
dcount("*","tablename",stLinkCriteria )

 

[9milla]

Hi there pwise, thanks for the prompt response. Muchly appreciated.

Yes, your suggestion worked as simple as it looks, however, I was wondering that if by selecting "*" FROM "tblClientBase", what impact will that have on performance whereas if I only want to Dcount 3 columns out of 30+ in total.

dcount("*","tablename",stLinkCriteria )

Is this looking at the entire table?

[b]dcount([!]"*"[/!],"tablename",stLinkCriteria )[/b]

[tt]Theres about +-5 million records[/tt]

Thanks
9milla

"And so it begins!"
LOTR (Battle for Helms Deep)

 

[PWise]

1)for a table with 5 million I will never use a Dcount I will use a recordset

"Select count(*) from tblClientBase where " & stLinkCriteria

this is not looking at all the fields just at count of records

 

[9milla]

I see, thanks again for the reply and advise pwise.

1)for a table with 5 million I will never use a Dcount I will use a recordset

So I'll be going then with my original code then.

[navy]Private Sub[/navy] cmdSearch_Click()
[navy]On Error GoTo[/navy] Err_cmdSearch_Click

    [navy]Dim[/navy] stDocName [navy]As String[/navy]
    [navy]Dim[/navy] strCount [navy]As String[/navy]
    [navy]Dim[/navy] directDb [navy]As Connection[/navy]
    [navy]Dim[/navy] searchRs [navy]As ADODB.Recordset[/navy]
    
    [navy]Set[/navy] directDb = CurrentProject.Connection
    [navy]Set[/navy] searchRs = [navy]New[/navy] ADODB.Recordset
    
    strCount = [b]"SELECT HomeTelNo, WorkTelNo, CellNo, ARARefNo " & _
               "FROM dbo.tblClientBase " & _
               "WHERE (HomeTelNo = '" & Me.txtNumber & "') " & _
               "OR (CellNo = '" & Me.txtNumber & "') " & _
               "OR (WorkTelNo = '" & Me.txtNumber & "')"[/b]
               
    searchRs.Open strCount, directDb, adOpenKeyset, adLockOptimistic
    
    [navy]If[/navy] searchRs.RecordCount >= 1 [navy]Then[/navy]
        stDocName = "frmSearchResultScreen"
        
        stLinkCriteria = "[HomeTelNo] LIKE '" & Me.txtNumber & "' " & _
                        "OR [WorkTelNo] LIKE '" & Me.txtNumber & "' OR " & _
                        "[CellNo] LIKE '" & Me.txtNumber & "'"
                    
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    [navy]Else[/navy]
        MsgBox "No records found for this criteria", vbInformation + vbOKOnly, _
        "ConsultantQ Error"
    [navy]End If[/navy]
    
    searchRs.Close
    
Exit_cmdSearch_Click:
    [navy]Exit Sub[/navy]

Err_cmdSearch_Click:
    MsgBox Err.Description
    [navy]Resume[/navy] Exit_cmdSearch_Click
    
[navy]End Sub[/navy]

Thanks
9milla

"And so it begins!"
LOTR (Battle for Helms Deep)