Building a DCount/SQL statement in VBA via concatenation if test is true

[newbieX]

I have a data entry form (Access 2007) which is designed to find out if the captured animal already has an existing WHno. Unfortunately, the data is messy and these is not a single unique identifier so several tests must be performed to narrow the search.



The animal could have 1 to 10 different pieces of information which will help identify the animal’s existence in the database. (The script only tests for about half of them thus far) I was thinking the best way to do this would to be to “build” a DCount and/or SQL statement based on which fields the user selects. I hope test to see if a particular text field box (unbound) has been filled out, and if yes, concatenate that section of code to the DCount/SQL statement, then move on to the next text field box to test.

Once the statement has been completely built, I want to test to see how many records have been counted/selected. If one record has been selected, I want to display the results in FormA. If 2 or more records are found, I want to display the records in a multi-listing form (FormB) from which the user can select the correct animal based on additional information not tested but displayed in FormB. If zero records are found, I want to create a new record with the data entered into the form updated into the table.

The hurdle I am struggling with now is building the DCount statements. I keep getting syntax errors . I do not know how to put this together piecemeal when the function bombs out because the syntax is incomplete (which it will be until I finish “building” it.)

I know the data is a mess. The scene out in the field is chaotic, different people gather different kinds of information, and not all the data that should be entered on the paper forms get filled out completely - if at all. The data gathering procedures are unlikely to change anytime soon.

Ideas? A different but easier approach idea is also welcome. New to this and not sure of all my programming options.

Also, how long can this statement be before it bombs out?

Code so far:

Private Sub GenerateWHno_Click()
Dim rs As DAO.Recordset

If IsNull(Forms!F_HotelEntry!txtSpecies) Or (Forms!F_HotelEntry!txtSpecies) = "" Then
    MsgBox "Species is a required field. Please enter a species"
    Exit Sub

End If


MsgBox txtSpecies

' Each line of code below indicates a data entry field(s) that needs testing and appended to SpeciesCount if "true". The first line is unchanging and is declared upfront.

'SpeciesCount = DCount("[Species]", "AnimalInfo", "(nz([Status])= '' OR [Status] = 'Alive' OR [Status] = 'Unknown') AND ([Species]= '" & txtSpecies & "')" _
'    & "AND (((nz([L_ET_Color1])= '" & Nz(txtL_ET_Color1) & "' AND nz([L_ET_No1])= '" & nz(txtL_ET_No1) & "')" _
'    & "AND (((nz([R_ET_Color1])= '" & Nz(txtR_ET_Color1) & "' AND nz([R_ET_No1])= '" & nz(txtR_ET_No1) & "')" _
'    & "AND nz([L_ET_No2])= '" & nz(txtL_ET_No2) & "')" _
'    & "AND nz([R_ET_No2])= '" & nz(txtR_ET_No2) & "')" _
'    & "")


'If txtL_ET_Color Is Not Null Or txtL_ET_No Is Not Null Then
    'LET1 = & "AND (((nz([L_ET_Color1])= '" & Nz(txtL_ET_Color1) & "' AND nz([L_ET_No1])= '" & nz(txtL_ET_No1) & "')" _
    'Species Count = SpeciesCount & LET1
'End If

'If txtR_ET_Color Is Not Null Or txtR_ET_No Is Not Null Then
    'RET1 = & "AND (((nz([R_ET_Color1])= '" & Nz(txtR_ET_Color1) & "' AND nz([R_ET_No1])= '" & nz(txtR_ET_No1) & "')" _
    'Species Count = SpeciesCount & RET1
'End If

'If txtL_ET_No2 Is Not Null Then
    'LET2 = AND nz([L_ET_No2])= '" & nz(txtL_ET_No2) & "')" _
'Species Count = SpeciesCount & LET2

[June7]

Can be looooong.

Don't need all those parens around the AND terms.

Use IsNull instead of Is Null http://allenbrowne.com/casu-12.html

If Not IsNull(txtL_ET_Color) Or Not IsNull(txtL_ET_No) Then

SpeciesCount variable has a space in it in the 3 If Then structures.

Every code module should have the following lines in header:
Option Compare Database
Option Explicit

[newbieX]

Thank you for those very helpful pointers but my main question is how can I build the DCount sentence using the IF statement approach. I am running into problems because at each stage the DCount statement is incomplete until the final IF statement test. (If the tests are true, the values tested in the IF statements gets appended to the DCount statement.) The program bombs out telling me the DCount statement declared up front (which is by design incomplete) is missing something.

The code in the above example is more designed to show what I am attempting to do overall in case someone has a better approach in mind.

Can be looooong.

Don't need all those parens around the AND terms.

Use IsNull instead of Is Null http://allenbrowne.com/casu-12.html

If Not IsNull(txtL_ET_Color) Or Not IsNull(txtL_ET_No) Then

SpeciesCount variable has a space in it in the 3 If Then structures.

Every code module should have the following lines in header:
Option Compare Database
Option Explicit

[June7]

Are you saying you need to dynamically build the WHERE CONDITION argument of the DCount? Here is example of dynamically building a criteria string http://allenbrowne.com/ser-62code.html

So conditionally build the WHERE string first and use it in the DCount

SpeciesCount = DCount("[Species]", "AnimalInfo", strWhere)

[Dal Jeanis]

First, you can build the SQL in a string variable before assigning it, so nothing will be checked until you want it to.

I have posted this type of VBA on the site before. Let me see if I can find it. Okay, here's two of them.
https://www.accessforums.net/queries...ria-36143.html
https://www.accessforums.net/queries...orm-36100.html

Second, a max SQL length of 255 bytes rings a bell. However, you could make the SQL into a named query, so that the Dcount only has to refer to the named query rather than the whole string. Or you could open a recordset using the required SQL string, movelast, movefirst, then check the recordset's count.

If you find that your SQL typically gets too long, then you could break it down into a Query on certain fields and a filter on others, but that is probably overcomplicated for your current purposes.

[June7]

Review http://blogs.office.com/b/microsoft-...07-limits.aspx

Approx 64,000 characters in SQL statement for a query (32,750 for RecordSource/RowSource), 255 for a single parameter, 99 ANDs in criteria (doesn't mention ORs).

[Dal Jeanis]

Thanks, June. I thought 255 seemed small as a limit for an entire query.

[newbieX]

Yes. That's the ticket. Dynamically build. Perfect description of what I am trying to do. I will give this a try.

Thanks.

Are you saying you need to dynamically build the WHERE CONDITION argument of the DCount? Here is example of dynamically building a criteria string http://allenbrowne.com/ser-62code.html

So conditionally build the WHERE string first and use it in the DCount

SpeciesCount = DCount("[Species]", "AnimalInfo", strWhere)