DCount arguments substituted with variables returns wrong answer

[catluvr]

Hi,

I am trying to make a public sub that checks to see if an item is already in a table

I have three separate tables that I want to check and each table has different names for the field I want to check. They all follow the same name structure, so I want to use text variables in a DCount function.

I cannot seem to wrap my head around where to use quotation marks and where not to.

I like to use chr(34) if the quotation mark will be part of a string. I have a debug.print after the DCount function and it's not returning the correct number - it should return a 0 if the text isn't located and a 1 if it is.

Here is what I have so far: (I know this is a not a public sub, but I'm trying to work out the correct way to write the DCount with the variables.

Private Sub Command7_Click()
 
Dim GroupFieldName As String
Dim GroupTableName As String
Dim NewData As String
Dim Tbl2Find As String
Dim Field2Check As String
Dim intCount As Integer
Dim Criteria As String
 
GroupFieldName = Me.Field_Name     'this will ultimately be a passed argument, right now it's a text box on a sample form
GroupTableName = Me.Table_Name   'this will ultimately be a passed argument, right now it's a text box on a sample form
NewData = Me.Data2Check               'this will ultimately be a passed argument, right now it's a text box on a sample form


Tbl2Find = "tlu_" & GroupTableName           ' given GroupTableName = Categories, this should return exactly -->  tlu_Categories
Field2Check = GroupFieldName & "_Name"          ' given GroupFieldName = Cat, this should return exactly -->   Cat_Name
Criteria = Field2Check & "=" & NewData         'given NewData = bird, this should return exactly -->   Cat_Name=bird  
 
intCount = DCount(Field2Check, Tbl2Find, chr(34) & Criteria & chr(34))
Debug.Print intCount
 
If intCount <> 0 Then
    MsgBox ("already exists!")
Else
    MsgBox ("not in table!")
End If
 
End Sub

In the Locals window, the variables are listed as:

: Tbl2Find : "tlu_Categories" : String
: Field2Check : "Cat_Name" : String
: Criteria : "Cat_Name=bird" : String


The intCount always returns 6, which is the number of records in the tlu_Categories table. It should have been 1 because "bird" is in the table. If the NewData is "Desk" then, intCount is still 6. Because "desk" isn't in the table, the value should have been 0.

I'm quite sure it's a quotation mark issue, but I've tried a bunch of different permutations and nothing is working.

To test the placement of the chr(34), I tried:

intCount = DCount(Field2Check, Tbl2Find, "'" & Criteria & "'")
intCount = DCount(Field2Check, Tbl2Find, """ & Criteria & """)
intCount = DCount Field2Check, Tbl2Find, "”"" & Criteria & "”"")


Each one always returns 6, no matter what is entered.

So, I left the Dcount as (Field2Check, Tbl2Find, Criteria) and worked on the line that builds the Criteria string

I tried:

Criteria = Chr(34) & Field2Check & "=" & NewData & Chr(34)

and it always returned 6. The locals table showed : Criteria : ""Cat_Name=bird"" : String

I tried:


Criteria = “’” & Field2Check & "=" & NewData & “’”

and it always returned 6. The locals table showed : Criteria : "'Cat_Name=bird'" : String


Then I tried: Field2Check & "=" & NewData

This didn't work either, but instead of the incorrect intCount value, I got the following error:

The expression you entered as a query parameter produced this error: 'bird'



When I use:

intCount = DCount(Field2Check, Tbl2Find, "Cat_Name=" & Chr(34) & NewData & Chr(34))

I get the correct intCount and correct messages.


What syntax will let me substitute the field name in the criteria with a string?

[June7]

Must be: Cat_Name='bird' or Cat_Name="bird"

So

Criteria = Field2Check & "=" & Chr(34) & NewData & Chr(34)

or

Criteria = Field2Check & "='" & NewData & "'"

then

intCount = DCount(Field2Check, Tbl2Find, Criteria)

or

intCount = DCount("*", Tbl2Find, Criteria)


Works for me.

[Welshgasman]

Start learning to use Debug.Print. That will show you your errors.

[moke123]

Sub TestIt()
    Debug.Print tblCount("tblCategories", "CatName", "Fish")
End Sub


Function tblCount(tbl As String, fld As String, newD As String) As Integer
    tblCount = DCount(fld, tbl, fld & " = """ & newD & """")
End Function

[catluvr]

Criteria = Field2Check & "=" & Chr(34) & NewData & Chr(34)

THANK YOU! I knew it was a quotation issue!


Susie
Kansas

[ArviLaanemets]

It was the 1st issue from many you will have with this db structure!

Instead having 3 different tables for different types of whatever you have there, have a single table with a field indicating the type. And in addition of unique primary key for this table, set unique index for name field. As result:
1. User can't save a record, when the provided name exists in table;
2. When later you need to add a new type of whatever, you simply add a new type, and start to use it (assumed you design anything using the type field properly). With current design, you have to redesign your entire database (forms, reports, queries, UDF's, calculation formulas etc.).

[catluvr]

Here is the issue:

(the "bird" example above was me playing around with limited set of categories/devices/types)

I'm trying to put together an "approved equipment" database for my engineer husband. This is a list that combines vendors and their models or part numbers that are approved for use by the company. He has everything in an Excel sheet that he inherited from someone else. The Excel sheet is a mess and there is no consistency. I realize that he could set up some validation checks in Excel, but he'd rather go with a database. New models will be added to the database.

There are currently 12 categories of equipment. Some of them are: Pressure, Temperature, Flow, Security, Communications, Plant Automation & Networking. Categories might be added in the future.

There are 24 devices. Some of them are: Switch, Cellular Modem, Transmitter, Indicator, Hardware. Not every category has all 24 devices, but there might be a category in the list that could have that device, but doesn't currently.

There are 91 types. Some of them are: Static, Differential, Radio Tower, Router, Submersible. Not every device has every type and, again, there might be devices that could have that type, but doesn't currently.

Giving a unique ID to a type will not work. For one thing, some approved equipment doesn't have a type ... for this usage, for example, a Cellular Modem is a Cellular Modem, and there is no type ... or perhaps Pressure is the category, Switch is the device and anything from XYZ manufacturing is fine. On the other hand, in the Level category, switch devices can be adjustable float or fixed float so that category/device needs a type..

Another issue comes in the addition of new equipment and the new connections. An example of the Category/Device connections is: There is a kind of device -- Indicator. There are currently Flow Indicators (types: differential, gas rotameter, liquid rotameter) and Pressure Indicators (no type). Level Indicators exist, but there are none in this long list. However, there could be Level Indicators added to the list and I have no idea if those Level Indicators have Types.

So, each approved equipment record has a Category, Device, and usually a type but maybe not.

I would love to be able to say a type has this device and that category, but it won't work ... at least not without a heck of a lot more data analysis on my end.

Thank you all for the help!

I need additional help/ideas, but I'll make that a separate post, of course!

Susie
Kansas

[Welshgasman]

Sounds like you need to go back to basics and create a structure that works for your scenario.
Only then can you start to think of forms and reports.
Otherwise you will be making kludges continually to get around the failure of a correct structure of your dB?

[June7]

It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works" is a common adage in db design. https://dev.to/er_dward/the-trade-of...and%20evolves.

[Welshgasman]

Very true, but most times new posters have never even heard of normalisation, so from the very start it all it goes awry?
Examples are one table per company, one table for payments, another for deposits. :-(

[ArviLaanemets]

...There are currently 12 categories of equipment. Some of them are: Pressure, Temperature, Flow, Security, Communications, Plant Automation & Networking. Categories might be added in the future.

There are 24 devices. Some of them are: Switch, Cellular Modem, Transmitter, Indicator, Hardware. Not every category has all 24 devices, but there might be a category in the list that could have that device, but doesn't currently.

There are 91 types. Some of them are: Static, Differential, Radio Tower, Router, Submersible. Not every device has every type and, again, there might be devices that could have that type, but doesn't currently.

With types it is clear - a device has certain single type, or none. You need a TypeID field in Devices table. For cases the device doesn't have a type defined, you either leave it empty (value Null), or in case you don't want Null values, you define a dummy type for such cases (e.g. 'Standard'/'Not Defined'/whatever);
About categories I'm unclear. Can same device belong to several categories at once? Or can the device belong either to one category, or to another category, but never to both (what practically means you messed up with your Devices table, as it can't be the primary source of Devices anymore)? In case the device belong always to one category, you need to have the category field in Devices table. In case device can belong to several categories at same time, you need a junction table (like tblDeviceCategories: DeviceCategoryID, DeviceID, CategoryID). In case the current device can have several categories, but only one at same time, you have to split those devices to separate ones.