Counting matching substrings

[Splinters]

I have tblWood, with a field txtWoodIDcode with entry's that identify a piece of wood.

For example:
txtWoodIDcode
Ash07:001
Ash07:002
Ash07:003
Ash07:004
Elm06:001
Elm06:002
Maple05:001
Maple05:002
Maple05:003

& so forth...

I have a form with a combo box that I select the first part of the string (the species of wood), the second part (the 2 digit year) is then concatenated onto this from a date function, and then a ":" is added.

So, I now have the substring "Maple07:" At this point it works fine. The problem is when I do a count for all of one species - in this case Maple. I need this to get the next number in the sequence for that species to append to the string for the full ID code. I am trying to do this using a DCount.

For some reason, this does not seem to find matches for the substring - but when I do the same search for the number "07", I get a match for all the records in the field.

The code is:
Private Sub cboSpeciesSelect_Click()
Dim species As String
Dim seq As Integer
species = Me.cboSpeciesSelect.Value & Right(DatePart("yyyy", Date), 2) & ":"
MsgBox (species)
seq = DCount("[txtWoodIDcode]", "tblWood", species)
species = species & seq
Me.SpeciesSelected.Value = species
End Sub

Am I missing something here? Everything I see indicates that DCount will work with strings - but apparently it may not work with substrings...

 

[missinglinq]

I think that what you're missing is in the criteria portion of the DCount function!

DCount("[txtWoodIDcode]", "tblWood", species)

Criteria is, as Access Help says, like the Where clause in a SQL statement. You have to compare something to something else, in this case species to something else, and you haven't done that! I'm not really sure exactly what is allowed in this criteria field, but basically it needs to say something like this

species = left(txtWoodIDcode,instr(txtWoodIDcode,":"))

 

[Splinters]

I think that what you're missing is in the criteria portion of the DCount function!

DCount("[txtWoodIDcode]", "tblWood", species)

Criteria is, as Access Help says, like the Where clause in a SQL statement. You have to compare something to something else, in this case species to something else, and you haven't done that! I'm not really sure exactly what is allowed in this criteria field, but basically it needs to say something like this

species = left(txtWoodIDcode,instr(txtWoodIDcode,":"))

Nope - that gets a runtime error 2465 (Access cannot find the field "|" referred to in your expression).

What I don't understand is that, in the examples of DCount I've seen, the criteria is shown as "string of text" for searching for a matching string in the table. And I am beginning to suspect that it needs to be a full match (Maple07:005), not just a partial match (Maple07: does not match Maple07:005). I have found that I can do this in a manual query using the "Like" argument - so now I have to figure out how to do that in VBA.

Thanks for the suggestion.

Stephen

 

[missinglinq]

I didn't really expect that to run as written, it was meant to give you a general idea of what had to be done. The "string of text" has to compare the search term with a field (or part of a field) in the table. Using Like might very well be the way to do this comparison. Simply giving the search term, i.e. species, without doing the comparison will never work.

 

[Splinters]

I didn't really expect that to run as written, it was meant to give you a general idea of what had to be done. The "string of text" has to compare the search term with a field (or part of a field) in the table. Using Like might very well be the way to do this comparison. Simply giving the search term, i.e. species, without doing the comparison will never work.

OK, now I am unsure of what you told me before.

When you offered the code:
species = left(txtWoodIDcode,instr(txtWoodIDcode,":")), I thought that was the same as I get already from the combobox when I select the species and then append on the 2 digit year, plus a ":".

That is what I have in my code as the value of "species":
species = Me.cboSpeciesSelect.Value & Right(DatePart("yyyy", Date), 2) & ":" - and this works.

Are you saying that I need to parse the value in the txtWoodIDcode field of each record, then compare that to what I now have as my search string?

I just tried that, and got a syntax error - with the usual helpful clues as what the actual error is...

Obviously I'm not communicating as well as I might - I'm new to Access and still get bent around by some of the jargon.

I'm attaching a zip of the DB, maybe that will help clear up what I need. As you can see, it's a combobox that selects a species, then the VBA code adds the year and the ":" - up to there it works just fine, the results age in the string species.

When I try to do the DCount to get the number of matches in the DB to species, it chokes. I understand now that the normal match requires a full match, and what I am trying to do is match a substring to a string - and this seems to be where the problem is. I had hoped that using the Like operator would do this - apparently I'm missing something in how to put it in a form that Access can understand. I did do this in the query tblWood Query using the criteria 'Like "Maple07:*"', and it worked - so I am pretty sure it is the way to go. Just don't have a good road map to get there...

See if you can make sense out of what I have - the object is to return the number of matches to the substring.

I appreciate your help on this, hopefully having the DB will make it clearer than I have.

Stephen