trim in rowsource with Right(), forcing a String return value with $,Access 2007

[rawdstorage]

Hi!

In a form I am trying to trim a string value in a row source that gets
its data from a query. The row source for my filtering combobox is:
SELECT [Query_MyQuery].[FieldID], [Query_MyQuery].[FieldName] FROM
[Query_MyQuery];

Since I am hiding the first FieldID column in my combobox (by setting
its width = 0), when opening my form I just see the FieldName column
in the combobox. Now, for some reason when they populated the table
behind it all, they found it to be a good idea to enter both the ID
and the Name in the FieldName table column. The table looks something
like

FieldID: 1 FieldName: 01_Name1
FieldID: 2 FieldName: 02_Name2
....

I want the combobox to accept the user to start typing in the value
they want to filter on, without having to know which name is preceded
by which nr as a prefix- i.e. not requiring them to know which ID each
name belongs to. To do this I am trying to trim my rowsource value for
the second FieldName column by removing the first three string
characters in order to just see the remaining FieldName:

Right(FieldName, 3)

returning a value of "Name1" for FieldID = 1

New row source attempt:
="SELECT [Query_MyQuery].[FieldID], " & Right( & "[Query_MyQuery].
[FieldName]" & ,3) & " FROM [Query_MyQuery];"

This approach is not really working.

I have also read that Right() returns a Variant Long subtype, but that
you can force it to work with strings, forcing a String data type by
using $ somehow. How is this done? Is it even necessary for my display
of the selectable values in my combobox to convert this Variant Long
into a String type so that it shows up properly in the combobox when
viewing the form in form view?

 

[Stefan Hoffmann]

hi,

Now, for some reason when they populated the table
behind it all, they found it to be a good idea to enter both the ID
and the Name in the FieldName table column.

Aha. I assume it was the boss. If they need the ID, then show it as the
second column (change your row source).

Right(FieldName, 3)

Right() returns the right most characters. You need Mid(yourField, 4).


mfG
--> stefan <--

 

[rawdstorage]

Got some help from a friend and the solution is
SELECT [Query_MyQuery].[FieldID], Mid([Query_MyQuery].[FieldName], 4,
Len([Query_MyQuery].[FieldName])) AS Expression1 FROM [Query_MyQuery]
ORDER BY Mid([Query_MyQuery].[FieldName],4 , Len([Query_MyQuery].
[FieldName]));

I did not need to worry about the return type either. =)

The ORDER BY is there to order the values shown in the combobox by
their internal alphanumerical order, instead of the previous FieldID
order, which doesn't make much sense any longer since the FieldIDs are
no longer visible.

 

[Stefan Hoffmann]

hi,

Got some help from a friend and the solution is
SELECT [Query_MyQuery].[FieldID], Mid([Query_MyQuery].[FieldName], 4,
Len([Query_MyQuery].[FieldName])) AS Expression1 FROM [Query_MyQuery]
ORDER BY Mid([Query_MyQuery].[FieldName],4 , Len([Query_MyQuery].
[FieldName]));

You don't need to calculate the length, if the length parameter is
omitted, it returns the rest of the string starting at your specified
position.


mfG
--> stefan <--

 

[rawdstorage]

Hi,

Thanks for that! =)

hi,

Got some help from a friend and the solution is
SELECT [Query_MyQuery].[FieldID], Mid([Query_MyQuery].[FieldName], 4,
Len([Query_MyQuery].[FieldName])) AS Expression1 FROM [Query_MyQuery]
ORDER BY Mid([Query_MyQuery].[FieldName],4 , Len([Query_MyQuery].
[FieldName]));

You don't need to calculate the length, if the length parameter is
omitted, it returns the rest of the string starting at your specified
position.

mfG
--> stefan <--