dcount with wildcard - proper syntax

City name must be enclosed in quotes.  Single will work if you have no city names with apostrophes in them.  Otherwise, you'll need double quotes.  BUT, if you are using a combo to select city, why are you using LIKE at all?

n = DCount("*", "tblCity", "((tblCity.CityName) Like   & " '* " &  nCityName &  " *' ", " &
             " AND [CountryRecID] = '" & gCountryID & "' " & _
             " AND [StateRecID] = '" & gStateID & "' ")

Not using a combo on the city name is a text.  I need to verify Name, State, Country combination does not currently exist prior to adding a new city. hence the ncount.  and yes I will need to be able to allow apostrophes within the city name, but strip out special characters.

Just tried it and it does not like the '* portion of your string.


K

I also tried using the chr(39) , but unsure does it go before or after the *>

does not like the ("*", - wrong number of arguments.

Here is my latest code, where you can see my many attempts to get this working.  Including reverting to an actual query with referencing the actual field names and not variables.  still getting type mismatch.

Also, note, I have change the datasource for the cityname - not cbocity but a text box named CItyName

Private Sub cmdAdd_Click()
Dim strSQL As String
Dim curDB As Database
Dim rs As Recordset
Dim nCountryName As String
Dim nStateName As String
Dim gCityName As String
'Dim n As Integer

gCountryID = Nz([Forms]![frmAddLocation]![CountryRecID])
gStateID = Nz([Forms]![frmAddLocation]![StateRecID])
gCityName = Nz([Forms]![frmAddLocation]![CityName])
'nCountryName = DLookup("Countryname", "tblCountry", "CountryRecID =" & gCountryID)
'nStateName = DLookup("StateName", "tblState", "stateRecID =" & gStateID)

    Set curDB = CurrentDb()
    
   ' n = DCount("*", "tblCity", "((tblCity.CityName) Like   & chr(39) & " * " &  nCityName &  " * " & chr(39) & ", "" & _
             " AND [CountryRecID] = '" & gCountryID & "' " & _
             " AND [StateRecID] = '" & gStateID & "' ")
   '  Debug.Print n
 '   strSQL = "SELECT tblCountry.CountryName, tblCity.CityName, tblState.StateName" & _
                " FROM (tblCity INNER JOIN tblCountry ON tblCity.CountryRecID = tblCountry.CountryRecID) INNER JOIN tblState ON tblCity.StateRecID = tblState.StateRecID" & _
                " WHERE tblCity.CountryRecID =" & gCountryID & "" & _
                        " And tblCity.stateRecID =" & gStateID & "" & _
                " GROUP BY tblCountry.CountryName, tblCity.CityName, tblState.StateName" & _
                " HAVING (((tblCity.CityName) Like " * " & [Forms]![frmAddLocation]![CityName] & " * "))"
strSQL = "Select * from qryAddCityValidation"
Debug.Print strSQL
        Set rs = curDB.OpenRecordset(strSQL)
        If rs.RecordCount = 0 Then
            DoCmd.RunSQL _
                ("INSERT INTO tblCity ( CountryRecID, StateRecID, CityName, TaxInclusive, Top5000, DateCreated, Modifiedby ) " _
                & "VALUES ( '" & gCountryID & "', '" & gStateID & "', '" & _
                gCityName & "', " & gTaxIncl & ", " & gTop & ", Date(), '" & _
                GetgUserID() & "' )")
            DoCmd.RunSQL ("Delete * from tblCityAddTemp")
        Else
            Select Case MsgBox("Records Found", vbYesNo Or vbExclamation Or vbDefaultButton1, "found")

                Case vbYes

                Case vbNo

            End Select
            
        End If

   On Error GoTo 0
   Exit Sub

End Sub

Select allOpen in new window

What type is 'n', string or integer?

k

still getting type mismatch when I changed to string.

Private Sub cmdAdd_Click()
Dim strSQL As String
Dim curDB As Database
Dim rs As Recordset
Dim nCountryName As String
Dim nStateName As String
Dim gCityName As String
Dim n As String

gCountryID = Nz([Forms]![frmAddLocation]![CountryRecID])
gStateID = Nz([Forms]![frmAddLocation]![StateRecID])
gCityName = Nz([Forms]![frmAddLocation]![CityName])
'nCountryName = DLookup("Countryname", "tblCountry", "CountryRecID =" & gCountryID)
'nStateName = DLookup("StateName", "tblState", "stateRecID =" & gStateID)

    Set curDB = CurrentDb()
    
n = DCount("*", "tblCity", "((tblCity.CityName) Like   & QUOTE & " * " &  nCityName &  " * " & QUOTE, " & _
              " AND [CountryRecID] = '" & gCountryID & "' " & _
              " AND [StateRecID] = '" & gStateID & "' ")
     Debug.Print n
 '   strSQL = "SELECT tblCountry.CountryName, tblCity.CityName, tblState.StateName" & _
                " FROM (tblCity INNER JOIN tblCountry ON tblCity.CountryRecID = tblCountry.CountryRecID) INNER JOIN tblState ON tblCity.StateRecID = tblState.StateRecID" & _
                " WHERE tblCity.CountryRecID =" & gCountryID & "" & _
                        " And tblCity.stateRecID =" & gStateID & "" & _
                " GROUP BY tblCountry.CountryName, tblCity.CityName, tblState.StateName" & _
                " HAVING (((tblCity.CityName) Like " * " & [Forms]![frmAddLocation]![CityName] & " * "))"
'strSQL = "Select * from qryAddCityValidation"
Debug.Print strSQL
       ' Set rs = curDB.OpenRecordset(strSQL)
       ' If rs.RecordCount = 0 Then
        If n = 0 Then
            DoCmd.RunSQL _
                ("INSERT INTO tblCity ( CountryRecID, StateRecID, CityName, TaxInclusive, Top5000, DateCreated, Modifiedby ) " _
                & "VALUES ( '" & gCountryID & "', '" & gStateID & "', '" & _
                gCityName & "', " & gTaxIncl & ", " & gTop & ", Date(), '" & _
                GetgUserID() & "' )")
            DoCmd.RunSQL ("Delete * from tblCityAddTemp")
        Else
            Select Case MsgBox("Records Found", vbYesNo Or vbExclamation Or vbDefaultButton1, "found")

                Case vbYes

                Case vbNo

            End Select
            
        End If

   On Error GoTo 0
   Exit Sub

End Sub

Select allOpen in new window

Why are you including spaces around the *'s???
Why are you embedding ID fields in single quotes?  If they are numeric they should not be delimited.

When you are getting syntax errors in an SQL string, the simplest solution is to build the string into a variable which you can then print to the immediate window.

Dim strWhere as String
strWhere = "((tblCity.CityName) Like   & QUOTE & "*" &  nCityName &  "*" & QUOTE, " & _
              " AND [CountryRecID] = " & gCountryID  & _
              " AND [StateRecID] = " & gStateID
n = DCount("*", "tblCity", strWhere)

Unless I am seriously mistaken, your problem is a missing quotation mark.

Re-examine your DCount:


Shouldn't that be:


The added quotation mark between "...CityName) Like" and "& QUOTE & " should fix your main issue.

doesn't like the Const in:   Public Const QUOTE = """"

k

This simplified should do:

n = DCount("*", "tblCity", "[CityName] Like '*" & nCityName & "*' " & _
    "AND [CountryRecID] = '" & gCountryID & "' " & _
    "AND [StateRecID] = '" & gStateID & "'")

/gustav

what do you know about validation rules, and can they be used to check for duplication on new entries?

still getting type mismatch - what declaration type should n be???

I currently have
dim n as long

Option Explicit
Public gCountryID As Integer
Public gStateID As Integer
Public gCityID As Integer
Public gCityName As String

Private Sub cmdAdd_Click()

Dim n As Long
   On Error GoTo cmdAdd_Click_Error

gCountryID = Nz([Forms]![frmAddLocation]![CountryRecID])
gStateID = Nz([Forms]![frmAddLocation]![StateRecID])
gCityName = Nz([Forms]![frmAddLocation]![CityName])
    
    n = DCount("*", "tblCity", "[CityName] Like '*" & gCityName & "*' " & _
        "AND [CountryRecID] = '" & gCountryID & "' " & _
        "AND [StateRecID] = '" & gStateID & "'")
    Debug.Print n

        If n = 0 Then
            DoCmd.RunSQL _
                ("INSERT INTO tblCity ( CountryRecID, StateRecID, CityName, TaxInclusive, Top5000, DateCreated, Modifiedby ) " _
                & "VALUES ( '" & gCountryID & "', '" & gStateID & "', '" & _
                gCityName & "', " & gTaxIncl & ", " & gTop & ", Date(), '" & _
                GetgUserID() & "' )")
        
        Else
            Select Case MsgBox("Records Found", vbYesNo Or vbExclamation Or vbDefaultButton1, "found")

                Case vbYes

                Case vbNo

            End Select

        End If

   On Error GoTo 0
   Exit Sub

cmdAdd_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAdd_Click of VBA Document Form_frmAddLocation"

End Sub

Select allOpen in new window

ok Got the typemismatch issue corrected by removing the single quotes around the gcountryid & gstateID.

However, another problem arises,  I need to look for all version of what is typed in the citytext field.  so if the user enters
AjaxD, On, Canada.

The results will return all options where the AJAX is found for the city name for the same country.  to prevent typos.  any ideas how to handle this, I thought having the wildcard on both sides of the citytext would cover this.  Do I need to loop through each letter entered in citytext to compare against current list of cities for ON, Canada?

K

ok now I need to include a list of records found where the name is similar using the wildcard.

K

thanks of the great assist

You are welcome!

/gustav