Query displaying only part of a record

  • Thread starter Thread starter Chuck
  • Start date Start date
C

Chuck

I have a table that looks like this:

Column1:
140b-b1
140b-b2
140b-b3
140b-aa1
140b-aa2
180c-a1
180c-a23

I want to put a drop-down list in a form that will contain a portion of the
string values found in Column1. Essentially the portion I want is
everything up to the last character.

e.g.:

140b-b
140b-aa
180c-a

Since the amount of characters after the dash will vary and the amount of
digits at the end will also vary, I am having problems in getting this to
work. I tried using the Right function along with either the InStr and
InStrRev functions but the latter don't appear to take wildcards.

Does anyone have any ideas?

Thanks
 
I have a table that looks like this:

Column1:
140b-b1
140b-b2
140b-b3
140b-aa1
140b-aa2
180c-a1
180c-a23
Click to expand...

Eeeuw. This is why relational database designers suggest that fields
should be "atomic" - i.e. have only ONE piece of information in each
field. This would be better stored as THREE fields - '140b', 'b', and
'1' for example. It's very easy to put these together, much harder to
take them apart!
I want to put a drop-down list in a form that will contain a portion of the
string values found in Column1. Essentially the portion I want is
everything up to the last character.

e.g.:

140b-b
140b-aa
180c-a
Click to expand...

Well... what about the last row? Should it be 180c-a2 or is it also
180c-a? If it is, your "last character" isn't quite right: you may
want "up through the last non-numeric character", complicating matters
markedly!
Since the amount of characters after the dash will vary and the amount of
digits at the end will also vary, I am having problems in getting this to
work. I tried using the Right function along with either the InStr and
InStrRev functions but the latter don't appear to take wildcards.
Click to expand...

If you do in fact want to just remove the last character, use

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

If you want to trim off any final numeric substring, of any length,
you'll need some custom VBA code.
 
I was able to do it by creating this function in the Modules section of
Access:

Function get_prefix(my_string)
string_length = Len(my_string)
For i = string_length To 1 Step -1
current_char = Mid(my_string, i, 1)
char_type = Val(current_char)
If char_type = 0 Then
Exit For
End If
Next i
get_prefix = Left(my_string, i)
End Function

I then specify this function in a query.
 
If you look about 6 posts up there is a guy wth the same
problem from the other end, the answer I just posted for
him would work with a little modification. Since it's on
there I'll just give you the gist.

If you know the maximum number of digits after the last
alpha you can use nested IIF statment to work back to the
last digit

I.e.
IIF (isNumeric(Right[Column1],1)),IIF(isnumeric(Right
([Column1],2)),Left([column1],Len([column1])-2),Left
([Column1],Len([Column1])-1),[Column1])

This will check eachright hand character in turn and take
the left hand side minus the number of charicters it's
checked.

If the maximum number of numerics is too much for this
kind of nested IIF statment you're going to have to write
a small routine in VBA to read through it e.g.

Function Truncated(Column1 as string)
Dim counter as integer
dim return as string
return = coulmn1
for counter = 1 to Len(column1)
if not isnumeric(Right(coulmn1,counter)) then
Return = left(column1,len(column1)-counter)
End if
Truncated = Return
End Sub

Create a module, stick that (or something like it) and
call it from the query as you would any other function
e.g. Truncated([Column1])

That should get you close.

Si
 
Back
Top