Question DoCmd.OpenForm, WhereCondition using Alphanumerics (1 Viewer)

I have a database I've been designing to track projects. I'm having trouble setting up a DoCmd to open a form to specific records where field values contain alphanumerics. I'll try to keep this simple.

I think this is a datatype mismatch issue. Any ideas how to setup a DoCmd to open a form to a record where the value in a field in a table is alphanumeric and matches the value a user types into a text box? Can anybody spot what I'm doing wrong? It works perfect with numbers, but not strings.

Some details are:

tblProjects
-Contains a field called ProjectNumber, which is not a primary key
-Project numbers are not determined by me, but are assigned based on the department I am working with
-These project numbers are Alphanumeric and therefore I set the data type for this field to be Long Text

frmProject
-This is the form tied to tblProjects where users enter project information into the fields

frmHome
-This is essentially the home screen, opens upon opening database
-This form has a text box called txtProjectNumber
-This form also has a command button called cmdProjectNumber
-User types the project number into txtProjectNumber and it is intended to open frmProject to a record were the field ProjectNumber from tblProjects matches the value in txtProjectNumber

When I try to do the same thing with other fields in the same table where the datatype is Number, the following code works PERFECT. The command button opens frmProject to records where value in a field in tblProjects is equal to the value held in the text box where the user is typing the value in to search. For example, with quote numbers, because they are a number datatype, this works perfect.

DoCmd.OpenForm "frmProject", acNormal, "", "[Quote Number] =" & [Forms]![frmHome]![txtQuote], acEdit, acNormal

When I try the same thing with a Long Text field, the DoCmd cannot find a matching record, I suspect because of a mismatch in datatypes. So, I tried converting both the value in the field and the value being searched into strings. This works beautifully only if values in Project Number field are numbers (even though data type is set to long text).

DoCmd.OpenForm "frmProject", acNormal, "", "Str([Project Number]) =" & Str([Forms]![frmHome]![txtProjectNumber]), acEdit, acNormal

For example, project number 123 is held in the field Project Number. User types 123 into the text box and searches, frmProject opens perfectly to this record.

If i change 123 to 123ABC in the table, and the user searches 123ABC, no records are found. In fact, i now find that as soon as a single alphanumeric value appears in any cell of this field in this table, the DoCmd cannot find ANY records, even ones that only have numbers in other cells of this field.

If the variable is a alphanumeric it needs to be surrounded with single quotes.
What you have there will work fine for numeric values

For a string try

One way to check your syntax is to create the Where criteria in a variable and then Debug.Print the variable.


HTH

Thank you for the advice! I really appreciate it. Unfortunately, this didn't entirely work. But I believe we are on the right path.

I tried what you suggested and the value I type into txtProject shows as <div>VALUETYPED</div>.

I set the where condition like you suggested as a string and put breakpoints in to watch the value of strWhere. In the immediate window of the debugger, it StrWhere shows as:

[Project Number] ='<div>ABCDEF</div>'

Any idea why <div> and </div> is appearing? There are no records where the value in field [Project Number] match <div>ABCDEF</div>. Not sure what the /div means. Thoughts?

Ok so thank you very much, this works now. Turns out I had the text box control set to rich text and the field in the table set to plain text, so it was turning out as <div>VALUE I AM TYPING</div>. By setting both to plain text, the <div> </div> was removed and now works. Basically just a syntax issue, had to wrap the value of the text box in single quotes inside of the double quotes like below.

Thanks again for helping me work through this.

StrWhere = "[Project Number] ='" & txtProject & "'"
DoCmd.OpenForm "frmProject", acNormal, "", StrWhere, acEdit, acNormal

The benefit of that syntax is that you can hover over the variable to see it's contents of Debug.Print it.

Glad you got it sorted.