dcount with wildcard - proper syntax

need another pair of eyes, not seeing the problem - type mismatch on I believe the ncityname.

    gCountryID = Nz([Forms]![frmAddLocation]![CountryRecID])
gStateID = Nz([Forms]![frmAddLocation]![StateRecID])
nCityName = Nz([Forms]![frmAddLocation]![cboCity])
 
  n = DCount("[CityRecID]", "tblCity", "((tblCity.CityName) Like   & " * " &  nCityName &  " * ", " &
            " AND [CountryRecID] = '" & gCountryID & "' " & _
            " AND [StateRecID] = '" & gStateID & "' ")
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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 & "' ")
0
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
Karen SchaeferBI ANALYSTAuthor Commented:
I also tried using the chr(39) , but unsure does it go before or after the *>
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Karen SchaeferBI ANALYSTAuthor Commented:
does not like the ("*", - wrong number of arguments.
0
PatHartmanCommented:
You would need to paste what you actually used to determine the syntax error.  I changed "CityRecID" to "*" because counting a field actually counts only not null values and since the ID is unlikely to ever be null, it is more efficient to use "*" as what you want to count.  So go back to counting the ID and see if the error changes.  Since you need to surround the city name with double quotes, I find it easiest to create a constant.  Put the constant declaration at the top of a standard module.

Public Const QUOTE = """"

n = DCount("*", "tblCity", "((tblCity.CityName) Like   & QUOTE & "*" &  nCityName &  "*" & QUOTE, " &
              " AND [CountryRecID] = '" & gCountryID & "' " & _
              " AND [StateRecID] = '" & gStateID & "' ")
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
What type is 'n', string or integer?

k
0
Karen SchaeferBI ANALYSTAuthor Commented:
still getting type mismatch when I changed to string.
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
PatHartmanCommented:
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)
0
Brendt HessSenior DBACommented:
Unless I am seriously mistaken, your problem is a missing quotation mark.

Re-examine your DCount:

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

Shouldn't that be:

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

The added quotation mark between "...CityName) Like" and "& QUOTE & " should fix your main issue.
0
Karen SchaeferBI ANALYSTAuthor Commented:
doesn't like the Const in:   Public Const QUOTE = """"

k
0
Gustav BrockCIOCommented:
This simplified should do:

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

/gustav
0
Karen SchaeferBI ANALYSTAuthor Commented:
what do you know about validation rules, and can they be used to check for duplication on new entries?
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

0
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
Gustav BrockCIOCommented:
No you shouldn't.
This will count "Ajax" from "AjaxD, On, Canada" in CityName:

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

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
ok now I need to include a list of records found where the name is similar using the wildcard.

K
0
Gustav BrockCIOCommented:
Then you will need a select query with the same criteria.
Use it to open a recordset - this will hold your list with (potential) dupes.

/gustav
0
Karen SchaeferBI ANALYSTAuthor Commented:
thanks of the great assist
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.