Lookup and assign value based upon text within alpha range (2 Viewers)

[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.