How to test if a match isn't found in searching for a record?

[Seth Schrock]

I have a textbox on my form that allows me to type in a loan number and it will go to that loan. My problem is that if the number that I type in doesn't exist, nothing happens. My initial thought was that an error would occur and I could trap for the error to perform the steps that I want to happen when a match isn't found. But no error occurs for me to trap. I have tried both DoCmd.SearchForRecord and Me.Recordset.FindFirst. The former does nothing and the later just goes to the first record. What can I use to see if no match was found?

[Rabbit]

You could use a DCount before doing the search.

[Seth Schrock]

Do you mean DCount the data source of the form using a criteria of the loan number?

[Seth Schrock]

Well, it does work that way even if that wasn't what you were meaning, although I think that it was. Thanks Rabbit.

[TheSmileyCoder]

If we work on the assumption that the loan number is unique, it could look like this:

Private sub cmdSearch_Click()
  If me.txtSearch & ""<>"" then
    Me.recordset.findfirst "LoanNumber=" & me.txtSearch
    If me.Recordset.NoMatch Then 
      Msgbox "Loan number not found"
    End If
  End If
End Sub

While the Dcount will work, its not necessary the best way to go, since it would require a double query of the table, which isn't really called for.

[NeoPa]

Why not use an unbound ComboBox instead, and let the operator search from a list of known valid values? The ComboBox can be populated with the valid data as part of its design.

[Seth Schrock]

That is what I was looking for! Thanks Smiley.

This is a little off subject but deals with the code you gave me, so I'll leave it to you (as moderator) to decide if it should be moved to a different thread.
Recently, I've been given several examples of code (including yours) that doesn't give an = in the If/Then statement. I would really like to understand why you are able to test

If Me.txtSearch & ""<>""

without giving an Me.txtSearch = ... or Not IsNull(Me.txtSearch). I really have no idea what to Google or anything like that so I can't search it on my own.

[NeoPa]

There is no requirement for an equals (=), or any other equality operator. What is required is anything that results in a Boolean value.

Rushing now, but will return later for a fuller explanation.

[TheSmileyCoder]

It probably should be moved to another thread, or better yet I could write a short insight about it, but for now:
I am checking to see if something has been entered into the control.

Now in many cases it should be sufficient to simply check the value for null, using IsNull(Me.txtSearch) which will work for comboboxes as well. One thing to note is that during most normal use of a database a text field will either contain Null or a string of length >0. If you clear out a control (delete all characters) access will not store a zero-length string in the field, it will store a null.

However, I had issues in which input being imported from excel would sometimes result in zero length strings being stored in the database. (It can also be stored through use of code)

In those cases the IsNull was suddenly useless. I therefore started to use the notation I use now:

If Me.txtSearch & ""<>"" then

In cases where txtSearch is null, the concatenation with the zero length string will yield a zero length string. In cases where it is a zero length string it will also yield a zero length string, and in all other cases it will yield a string different from a zero length string. This also works for comboboxes.

[NeoPa]

When using the If statement it's important to realise that it is not :
If <SomeValue> <EqualityOperator> <AnotherValue> Then

It is rather :
If <condition> Then

<condition> can be one of a great nuber of expressions or values. All that is necessary is for it to result in a value that can be considered as a Boolean. Specifically, it is not necessary to put comparisons of any kind in here, even if they are, by far, the most common conditions. When you already have a Boolean value, stored in a Boolean variable, there is absolutely not need to convert it into a Boolean value by comparing it either to True or False. So, the following is totally unnecessary (and technically doesn't even do exactly what is expected of it anyway - but we won't go there for now) :

Dim blnFlag As Boolean
 
...
If blnFlag = True Then ...

The most sensible way to write this is simply :

If blnFlag Then ...

In the specific case of checking form controls to see if they have any contents, it is only necessary to compare the control to an empty string. As Smiley says, it is possible for empty controls (depending on the design of your tables and fields - I always try to ensure empty strings are never allowed anywhere) to have Null or an empty string representing their .Value. Code to handle that is simply :

If Me.MyControl > "" Then ...

This will result in a True condition only when neither Null nor "" is contained therein. Essentially when it has an actual value of some sort (any sort). Many people prefer to use <>, as this is generally recognised as the inequality operator and that is a better fit for how they think of it, but technically > works just as well. Please understand that for those that think that way, <> is a more sensible option than >. How you understand what you write is important.

[Seth Schrock]

Okay, I understand how

If blnFlag Then...
 
'same as
 
If blnFlag = True Then...

I also understand the last code block that NeoPa posted as that has the equality operator that I would expect. However I still don't quite Smiley's. Me.txtSearch is not a boolean value, and I'm not sure what & does (is it the same as AND?) and why the double set of double quotation marks around the <>? I understand the difference between empty strings and nulls and the reason for not testing simply for a null value (something I'm guilty of far too often). I just don't understand how that line equates testing for that. One of the other instances where I have recently found an If/Then used without a equality operator is in my code to test if the BE file exists where it should be. The code is:

If .Attributes And dbAttachedTable Then
...

I have absolutely no idea what values it is testing for. When running the code, the values for .Attributes and dbAttachedTable that result in a True are
.Attributes = 1073741824
dbAttachedTable = 107371824
Where in that code does it specify that those are the values it is looking for?

[NeoPa]

Seth:
However I still don't quite Smiley's.

The ampersand (&) is a string concatenator character (See Using "&" and "+" in WHERE Clause for some interesting and related points). "ABC" & "DEF" resolves to "ABCDEF".

Form controls can be unpredictable. One way to make a control behave as if it is not a Null but an empty string, is to create an expression Me.MyControl & "". As the string is a string, and the control reference will return something, whatever, the expression will always resolve to a string. Even if the control is a Null.

If Me.MyControl & "" <> "" Then
This is forcing the left side of the expression to evaluate to a String expression, no matter whether it contains a value or not (Particularly Null). It isn't necessary, but it can be helpful to understand when you are dealing with variables (of any sort) that may contain Null.

AND
You really need to look this up and think about it. It isn't too straightforward for people who don't think excessively logically. I'll explain what I can though (Not a reason to avoid checking it out properly, but hopefully may get you started).

AND is a bitwise logic operator. Bitwise operators are ones that work on bits (Binary Digits) independently of each other. I should say at this point that it is much easier to understand and appreciate if you work and think either in Binary or, more probably, Hexadecimal, rather than Decimal (Bases 2, 16 and 10).

If you consider a Bytes variable has 8 bits and is generally treated as numeric. A decimal number 10 is written in Hex as 0A and Binary as 0000 1010. And is an operator that puts two values up against each other and returns a 1 value for each bit where both matching bits in the original values are 1. If either or both is a 0 then 0 is returned. Here are some binary examples for you :

0000 1010     0000 1111     0000 1010
0000 1010     1111 0000     0000 0000
0000 1010     1111 1111     0000 1010
1010 0000     0000 1111     0000 0000
1010 0000     1111 0000     1010 0000
1010 1010     0000 1111     0000 1010
1010 1010     1111 0000     1010 0000
1010 1010     0000 1010     0000 1010
1010 1010     0000 0101     0000 0000

If you print out .Attributes and dbAttachedTable in Hex (Use ?Hex(.Attributes),Hex(dbAttachedTable)) you'll see that only one bit is set.

40000000   40000000

This is because you can store a flag value in each of the available bits when using bitwise operations. .Attributes is such a field. When checking for this one flag, we're only interested in this flag. If we use AND, as illustrated above, we can ignore (turn off) all other bits simply by ANDing with a long integer which has binary 0s in each position we have no interest in. dbAttachedTable is exactly that. A value which turns off every binary flag except the one to indicate attached tables.

If you checked the If command earlier, as suggested, you will see that it treats any value that is not zero as True. If you have a complex flag field with various flags set, then any result of the AND that doesn't turn all off is considered to be True. Another reason why one should never use the code :

If blnFlags = True Then ...

The result of the bitwise AND may well be considered to be True by If, but it wouldn't be equal to the defined value True as that is explicitly -1. -1 because that is the number which is the result of all bits being 1 in a long integer. So, if this format is used you would get an unexpected failure instead of a success.

[Seth Schrock]

Okay, I understand Smiley's code now. I was thinking of the double quotes as if they were trying to have quotation marks added to a string. That part makes perfect sense now. In this particular case, there was an equality operator in the condition.

For the .Attributes and dbAttachedTable part, I think I understand, but let me make sure. The And compares the two values and returns a true when the values are equal (all the binary number match up). Is that correct?

[NeoPa]

No. A True is a confusing concept here. Each bit position returns a 1 when the 1 bits match up in the two values being ANDed together. The result is a pattern of 1s and/or 0s. Only if all bits are 1 can it be said to be equal to True (-1), but any value other than all 0s (False) will take the True route through the If command. This is why it can be confusing (and logically incorrect) to compare a value with True to test it.

[Seth Schrock]

Ah, I see.

If .Attributes = True AND dbAttachedTable = True
 
If (.Attributes AND dbAttachedTable) = True

are all false statements as there isn't any true/falseness about them. But because of the match (in the real or original code), the If/Then statement will follow the true path. That makes sense. Now would I be correct in understanding that the reason that .Attributes and dbAttachedTable have the bitwise comparison done between them is that there is no equality operator? So while the outcome is the same, the process is totally different between the following:

If .Attributes = 1073741824 AND dbAttachedTable = 1073741824 Then...
vs.
If .Attributes AND dbAttachedTable Then...

The first option would perform no comparison between the two other than matching that both equations are true or both are false. In the second option, because there is no equality operator, the condition becomes a comparison between the two and the If/Then statement will follow True if they match and False if they don't.

Can this be done between regular variables (strings, integers, boolean, etc.) or does it have to be something that returns a number (so string and boolean wouldn't work)? I can't think of a situation where it would be needed but it would be nice to know if I ever come up with a situation that I might be tempted to use it if it is something I can or can't do.

[NeoPa]

That seems to indicate good progress Seth, but I mentioned it wasn't too straightforward, no?

First important tip is to use Hex when dealing with bits. There is no overlap between binary and decimal, so it's very complicated to see where the bits are.

Bitwise logic is not a simple as you seem to appreciate as in 8 bits you can store 8 separate flags or pieces of binary information. If bit-0 (numbered from the right, or least significant digit) reflects A and bit-1 reflects B then I can check for either of them in my flag variable by saying :

If blnFlags And &H03 Then ...

Certainly, one could check one flag of the 8, but the facility is there to check more than one.

As such, I don't believe your understanding as expressed is quite accurate.