str handling

[bpaquette]

i have 3 cbo's on a form, which all hold data in the format similar to this:



CBO1: A -- operational Warfare Instructor
CBO2: 45V -- Nurse
CBO3: C -- Pediatrics


What i would like is a txt to be populated with certain characters from all cbo values, so the txt should read


A45VC

so i need to extract the first char from strings 1 and 3 and the first 3 chars from cbo2, is there any standardized syntax for that? i know the cpp way but not vba


anyone in a helpful mood this morning?

 

[namliam]

anyone in a helpful mood this morning?

Me as usual

If its standard and allways:
cbo1: First
cbo2: First 3
cbo3: First

Easy: Left(cbo1,1) & left(cbo2,3) & left(cbo3,1)
( May need to change , to ; )

If it can change but allways up to the first space:

use Instr(cbo1," ") to locate the space eg replace the part behind the comma.

Regards

P.S. unless you have a verry specific need its not advisable to store a compacted string like this in your table...

 

[bpaquette]

hah at this rate i'll have to credit you as technical support on this database nam

 

[namliam]

Hmz, or you could start paying me

I am in need of some $$ or a "real" job

Regards

The Mailman

 

[bpaquette]

hah, i work for the government, i can't even pay myslef

 

[bpaquette]

well i think i'm getting better at this vba stuff..


Private Sub cmdGeneratePAFSC_Click()
txtOfficerPAFSCFull = Left(cboOfficerPAFSCPrefix, 1) & Left(cboOfficerPAFSC, 3) & _
cboOfficerPAFSCSkill & Left(cboOfficerPAFSCSuffix, 1)

End Sub



except it doesn't work, needless to say


i've tried adding me. to each string, to no avail. the commas have to be commas, according to the autocomplete structure when typing it in.

can anyone see something wrong?>

 

[Fizzio]

Prefix all the cbo with me.

ie

txtOfficerPAFSCFull = Left(me.cboOfficerPAFSCPrefix, 1)

(From your first post, this assumes that the data is stored in one field and that you do not have the data in 3 columns and that the data to interrogate is in the first column)

 

[bpaquette]

the data is in three columns though.


essentially column 1 is a prefix, column two is an afsc or job code, column 3 is a skill level, column 4 is a skill level, and column 5 is the result of an extraction of specific characters from all 4 previous columns.

col 1 = 1st char
col 2 = chars 1, 2, 3
col 3 = whole field
col 4 = 1st char

i've tried prefixing each cbo w/ me. but it still yielded negative results, i'll throw it in and give it another shot. is there anywhere else it could be going wrong?

 

[bpaquette]

adding me. yields an invalid use of . or ! operator, btw

 

[namliam]

Hmz, your syntax seems correct

Your doing it from a button right?

Are you getting your data from the BOUND column of the combo's ?

If all else fails, strip it and post a sample. I am sure i (or maybe someone else) can get it working from your sample....

Regards

P.S. You are not only taking the the characters from the combos as you first posted but also the full value of cboOfficerPAFSCSkill

 

[Fizzio]

OK

I'll assume that you are wanting to concatenate data from the first (bound) column of all combos AND the button you are using to do this is on the same form as the combos

Private Sub cmdGeneratePAFSC_Click() 'Button MUST be on same form as controls
txtOfficerPAFSCFull = cboOfficerPAFSCPrefix & cboOfficerPAFSC & _ 
cboOfficerPAFSCSkill & cboOfficerPAFSCSuffix 
End Sub

If this is not the case, explain how not or post your cut down version as namliam suggests

 

[bpaquette]

er i tweaked a bit and still got nothing. here's a bare bones sample. the pafsc prefix box doesn't seem to want to work in this new stripped versoin, but that's not really relevant to the issue.


any help is as always appreciated

 

[namliam]

No file, make sure you ZIP it and its not to big....

 

[bpaquette]

that's pretty much it, fizzio, except i need the first few chars of each field, except the third one wherein i need the whole thing.

 

[namliam]

If fizzio's sample seems to work:

Private Sub cmdGeneratePAFSC_Click() 'Button MUST be on same form as controls
txtOfficerPAFSCFull = cboOfficerPAFSCPrefix & cboOfficerPAFSC & _ 
cboOfficerPAFSCSkill & cboOfficerPAFSCSuffix 
End Sub

Then mine (posted earlier by you as well) should do exactly what you want

Private Sub cmdGeneratePAFSC_Click() 
txtOfficerPAFSCFull = Left(cboOfficerPAFSCPrefix, 1) & Left(cboOfficerPAFSC, 3) & _ 
cboOfficerPAFSCSkill & Left(cboOfficerPAFSCSuffix, 1) 
End Sub

Regards

 

[bpaquette]

d'oh....notice i haven't eaten today, a little out of it...

it wont let me post it. its a 170k file , but its saying its too big.



this may take a minute or two....bleh

 

[bpaquette]

this won't work..would anyone be willing to accept an email?

 

[pono1]

b,

I would bet you can debug this yourself. Take your time and break the thing down and try first to get a simple statement to work. From there move forward methodically.

For example...

This (from your sample post) doesn't work?

	me. txtOfficerPAFSCFull = _
	Left(me.cboOfficerPAFSCPrefix, 1) & _
	Left(me.cboOfficerPAFSC, 3) & _ 
	me.cboOfficerPAFSCSkill & _
	Left(me.cboOfficerPAFSCSuffix, 1)

If not, does this work?

	me. txtOfficerPAFSCFull = _
	Left(me.cboOfficerPAFSCPrefix, 1)

If so, does this work?

	me. txtOfficerPAFSCFull = _
	Left(me.cboOfficerPAFSCPrefix, 1) & _
	Left(me.cboOfficerPAFSC, 3)

etc.

Regards,
Tim

 

[bpaquette]

ok, for some reason i just now understood namlians previous post about unbound boxes, so i unbound the txt box that received the values, and it appears to work fine. now why doesn't it work with a bound box? how can i store this data in my table?

 

[namliam]

It should work as well with bound text boxes....

But as a general rule DO NOT store calculated values!