Lookup and assign value based upon text within alpha range

[GPGeorge]

Or ... SLIGHTLY less typing

Me.Assigned_To = DLookup("Analyst", "[tbl_company_analysts]", "'" & companyF & "' BETWEEN [StartRange] AND [EndRange]" )

With the understanding that this ONLY works correctly when the text ranges are all exactly the same length in text characters as well as being all uppercase; and companyF has to ALSO be uppercased as well as extracted: UCASE( LEFT( company-name, 3 )) - if you are using 3-character ranges.

Interestingly enough, I've been so conditioned not to use BETWEEN because of the problem of Dates with times. It is appropriate here and is simpler syntax.

 

[Gasman]

Just throw a DateValue() at them?

 

[The_Doc_Man]

Interestingly enough, I've been so conditioned not to use BETWEEN because of the problem of Dates with times. It is appropriate here and is simpler syntax.

Probably works for this case because the reference ranges are likely to not include times, in which case you don't care much if the tested date field or variable does or doesn't have time in it. If the reference points had times, then you get into nit-picking with BETWEEN. BUT it wouldn't hurt to trim time out of the tested date field just to be sure. That same caveat would have applied to the other syntax with <= and >= components.

 

[GPGeorge]

I was thinking it works here because the reference values are discrete alpha characters....

 

[Gasman]

My thoughts too.

 

[jco23]

as suggested by the others, you can create a new table (junction table) that will hold the companyID and assigned
employee to it (tblEmpCompanies).

see form CompanyEmployeeForm to see which employee is assigned to which customer.

open AssignmentForm form for your assignment and see the company combobox AfterUpdate event.

ideally, that would work, but users have gotten in the habit of using short names while others are using long names (e.g. IBM vs. I.B.M vs. International Business Machines vs. Int' Bus. Machines). we're in the process of cleaning some of that up, but this method works. this group deals with hundreds of thousands of transaction each month, so fixing everything could send us back too far.