Need to split a field in a combo box

[lance.schaeffer]

Hello,

I currently have a combo box (unbound) which derives its values from a
list of criteria, of which two important ones are "OrgId" and "Dup"
[Dup being Duplicate]. OrgID Pulls the name of the organization from a
table, and displays it in the combo box. This combo box is a "search"
box for a form, in which it then loads whatever record meets the
criteria.

The issue I am running into with my original setup (OrgID was the bound
source) was that I couldn't select the second entry where OrgID was the
same, but Dup was different. This obviously being because my
controlsource was just OrgID. So my solution for this came from the
thread "MS Access: Multiple columns bound in Combo Box:. I combined my
two keys into "one" momentarily, creating a new controlsource "OrgDup",
and now am stuck trying to parse the data back out into something
meaningful, so that the combobox can control two lookupfields.

in all cases, Dup will be a 1 character field, and OrgID will be any
value from 1 to the thousands. I am able to derive the Dup field from
the combined field by just using a query that pulls the rightmost
character, but I can't figure out how to pull "all but the rightmost
character" for the orgID number. Any help on how to do that, or if
there is a better way to set up my controlsource, would be appreciated.
Thanks in advance!

 

[Guest]

So I guess you are doing something like this to get the Dup:
strDup = Right(Me.cboOrgDup, 1)
If so, then you can get the Org with:
strOrg = Left(Me.cboOrgDup, Len(Me.cboOrgUpd) -1)

 

[lance.schaeffer]

Well,

Yes and No.

I've actually built most of this within the form itself.

Thus, I have a form that pulls its data from a query that refreshes
itself based upon the combo box selection.

So within access I have a query built, where OrgID criteria is
right([forms]![frmName]![cmbOrgDup],1) - I tried your method, but I
can't get the "Len(" function to work...any ideas? I'd rather not scrap
my setup to redo the query in VBA if possible,.

 

[lance.schaeffer]

Just a follow up: I was able to get it to work

Apparently in the access query criteria for Left, everything past the
comma is the length, so I did Len(cmbOrgDup) - 1, as opposed to
Len(cmbOrgDup -1)

the final string was

Left([forms]![frmInternshipDetails]![OrgDup],Len([forms]![frmInternshipDetails]![OrgDup])-1)

Thanks for your advice, helped me wade my way through this disaster.

This is what happens when you have a non-database guy work on your
database

Lance

 

[John Vinson]

in all cases, Dup will be a 1 character field, and OrgID will be any
value from 1 to the thousands. I am able to derive the Dup field from
the combined field by just using a query that pulls the rightmost
character, but I can't figure out how to pull "all but the rightmost
character" for the orgID number. Any help on how to do that, or if
there is a better way to set up my controlsource, would be appreciated.
Thanks in advance!

Left([combobox], Len([combobox]) - 1)

should do it...

John W. Vinson[MVP]

 

[Guest]

I guess the Len function doesn't work in queries. One option might be to
write a simple function that would return that value like I posted before,
and make that function the criteria

 

[lance.schaeffer]

The issue I am running into now (I guess I lied saying it worked) is
that when I try to open the form initially, since the combobox has no
value, I get an error. (I tried setting a value in the combobox on form
load, but it still gets grumpy). The query works fine if there is a
value, but if there is no value, I get an error message telling me my
formula is "too complex" or "has an error". Any idea how to counter
this issue?

 

[John Vinson]

The issue I am running into now (I guess I lied saying it worked) is
that when I try to open the form initially, since the combobox has no
value, I get an error. (I tried setting a value in the combobox on form
load, but it still gets grumpy). The query works fine if there is a
value, but if there is no value, I get an error message telling me my
formula is "too complex" or "has an error". Any idea how to counter
this issue?

You'll have to use IIF(IsNull([combobox]), <one expression>, <other
expression>) to test first for an empty combo.

John W. Vinson[MVP]