Access Table How to add a hard space to end of record 1

[keusch]

This is probably very simple but I can'f figure it out.
Problem
Table A -with a field "shortname" is used as a pass through query with Table B to create a new table.
I have shortname entries like
Table A
GV
GVW

To make the pass through work properly, I need a hard space at the end of GV and GVW but not all shortname entiers need a hard space.

The pass though command uses the data like this

IIf mid(TableB!longname),1,LEN(TableA!shortname)) = TableA!shortname, TableA!Fieldname,null)
I need Entries GV to have a space so that Entries matching GVW don't also match GV.

Any ideas?

 

[Remou]

I cannot see how this can work with a space. This is the way I see the information you have supplied:

TableB!longname = GVDept, for example
TableA!shortname = GV & Space
LEN(TableA!shortname) = 3
mid(TableB!longname),1,3) = GVD
Which is not equal to GV & Space

What point am I missing?

 

[keusch]

Remou,
Thank you for your response. I should have been clearer. However, after reading your response, I came up with a couple quick solutions. So thanks!
Here is the problem
Table A -with a field "shortname" is used in a query with Table B to create a new table.

I have Table A shortname entries like:
GR
GV
GVW

Table B has long names
GREAT PLAINS 307
GR 101
GV 324
GVW 786
However, only certain specific criteria needed. I wanted a new table to capture GR 101 NOT GREAT PLAINS, to capture GV put not GVT.
What was needed in most cases was a space after shortname. However, not all records in Table A shortname needed a space. I was trying to figure out how to had a hardcoded space in Table A field shortname but only where applicable.

There are two solutions both required me to edit Table A to include a more extensive shortname listing. The solution matches up to the 1st space on Table A shortname entries- IIf mid(TableB!longname),1,LEN(TableA!shortname)+1) = TableA!shortname, TableA!Fieldname,null)
By increasing the lenght by 1 it basically matched the space at the end of the character. The other method was to use Instr using the 1st space as reference.
Again, thank you for taking the time to respond.

 

[PHV]

Something like this (SQL snippet)?
WHERE [Table B]..[long name] Like [Table A].shortname & [tt]' *'[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[keusch]

Thanks PHV that was far too easy! It proves that I have spent far to much time in the gooey interface and should spend some quality time with SQL.
However, I would ask if there is a way to hard code a space at the end of a record? As originally stated (before I altered the shortnames), some of the fields did not need a space and others did.
????? Any suggestions?