Am i using wrong method?

[Petros]

Hi all..and thanks for all patiance

Currently performing the "basic" migration to SQL i ask my self if below code is correct..should i use the Dcount..and should i filter the form or should i "filter" in the tables..i am looking for all means to increase performance..

On Error GoTo Err_OpenCompanyFromMenu_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ViewallCompanies/Xcodes"

If DCount("*", "ViewCompaniesQueryQuery", "[Company] like '*" & Me.Search & "*'") > 0 Then
DoCmd.OpenForm stDocName
Forms![ViewallCompanies/Xcodes].Filter = "[Company] like '*" & Me.Search & "*'"
Forms![ViewallCompanies/Xcodes].FilterOn = True

Else

DoCmd.OpenForm "CompanySwitch"

End If

Exit_OpenCompanyFromMenu_Click:
Exit Sub
Err_OpenCompanyFromMenu_Click:
MsgBox Err.Description
Resume Exit_OpenCompanyFromMenu_Click

Thanks!

 

[vbaInet]

Hard to advise without knowing the big picture Petros.

With filters I would imagine that the database isn't requeried whereas if you were building the sql string it will always be querying the db.

It's not completely clear in what context you're using the DCount() function and using a LIKE clause in an aggregate function such as DCount() is not fully justifiable. Maybe you have your reasons?

 

[Petros]

This search function serves to finde Company names and related records, unfortunatly they are not always correct entered in the db by the user, not by format but by "spelling" Co in english S.R.O in Italian etc etc
the Like cures this and returns one ore more related results

Currently the db resides > 20K Company names and i realized that performing a wildcard search of such large number of records is perhaps not the best method..well.. perhaps the "pragmatic" migration and the SQL you mentioned will cure this..

Thanks!

 

[pbaldy]

I would use this to filter the form:

http://www.baldyweb.com/wherecondition.htm

which will filter it before/as it opens, instead of after the fact.

 

[Petros]

Thanks for your suggestion pbaldy..i will try this out. The search criteria often includes text and numbers in comination..i iwll see how this wokrs for a wildcard search

will try, thanks again both

 

[vbaInet]

Also, I think what you need is a search form that will display records of the closest match to the criteria. Then allow the user to select the one they want from the list. I think I gave you that link from pbaldy but you can always search through his site for that.

 

[Petros]

"You must spread some Reputation around before giving it to ..... again."..what is this..how does one say thank you in a simple way just by clicking on the balance?

 

[vbaInet]

I have no idea. Maybe Paul knows what that means.

 

[boblarson]

The search criteria often includes text and numbers in comination..

That makes no difference. It is the field data type which determines if you need quotes, octothorpes (#) or nothing around them.

You can expand his code to do something like this:

    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnTrim As Boolean
    
        If Not IsNull(Me.cboSelectName) Then
            stWhere = "[NamesID]=" & Me.cboSelectName & " And "
            blnTrim = True
        End If
 
        If Not IsNull(Me.cboSelectCity) Then
            stWhere = stWhere & "[CityID]=" & Me.cboSelectCity & " And "
            blnTrim = True
        End If
 
        If IsNull(Me.txtFrom) And Me.txtFrom = "" Then
            If Not IsNull(Me.txtTo) And Me.txtTo <> "" Then
                stWhere = stWhere & "[TravelDate]  <=" & Me.txtTo & "#"
                blnTrim = False
            End If
        Else
            If IsNull(Me.txtTo) And Me.txtTo = "" Then
                If Not IsNull(Me.txtFrom) And Me.txtFrom <> "" Then
                    stWhere = stWhere & "[TravelDate]>=" & Me.txtFrom
                    blnTrim = False
                End If
            Else
                If (Not IsNull(Me.txtFrom) And Me.txtFrom <> "") And (Not IsNull(Me.txtTo) Or Me.txtTo <> "") Then
                stWhere = stWhere & "[TravelDate] Between #" & Me.txtFrom & "# And #" & Me.txtTo & "#"
                blnTrim = False
                End If
            End If
        End If
 
        If blnTrim Then
            stWhere = Left(stWhere, Len(stWhere) - 5)
        End If

    stDocName = "frmTipsLog"
    DoCmd.OpenForm stDocName, acNormal, , stWhere

 

[boblarson]

"You must spread some Reputation around before giving it to ..... again."..what is this..how does one say thank you in a simple way just by clicking on the balance?

Just post a thank you to the thread. The reputation stuff is set so you can't give reputation for the same person multiple times in a row. You have to give others (somewhere between 10 to 15 people) reputation before you can give it to the same person again. There are some threads about why that is. You can search for them if you are interested.

 

[Petros]

Thank you for the code suggestions and further explanation of the "house" rules.. Much appreciated!

I will announce the outcome of the testing...