Concatenate fields or lookup problem?

[MHPGuy]

I'm sure this is simple- but I'm just not as good at access as I'd like.

What I need is to enter a number in a form, and see if that number exists in a table. In the table, the first 3 charaters are in one field, and the remaining characters are in another. So, the source table looks like this:

ROW FIELD 1 FIELD 2
1 576 987654
2 719 37519
3 961 7583175


I have a form that someone needs to be able to put in the concatenated number and perforn a lookup. for example, lookup 71937519 and get a return on row 2.

I'm thinking I can do this on the form, without the need to concatenate field 1 and field 2 into a third field.

Does anyone have any ideas?

Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[jdegeorge]

Create an unbound field on your form.

Set it's default value to

= [Field1]+[Field2]

Or, just skip the unbound field and have your lookup use the formula:

DLookup("[DesiredField]","DesiredTable", "[TOTAL]=[Field1]+[Field2]&quot

If these fields are TEXT,

DLookup("[DesiredField]","DesiredTable", "[TOTAL] = '" &[Field1]+[Field2] & "'&quot

Hope this helps.

Jim DeGeorge