• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Access 2003 (adp) application - Remove first charachters in a View

Experts,

Quick question... save me probaly another hour of googling...
I have an Access ADP project and from one field I need to get rid of the first 3 charachters.
but in my ADP view Left is not working...

Thx
0
ITBenelux
Asked:
ITBenelux
  • 5
  • 4
  • 3
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
Is MID or SUBSTR working?

Mid(YourField, 4)
Mid(YourField, 4, 999)
SubStr(YourField, 4)
SubStr(YourField, 4, 999)

You may or may not need the third parameter on these (if they are working for you).
0
 
Kevin3NFCommented:
You need an update statment that updates the field to the right (length -3) characters, not delete the first 3...

Aircode:

Update mytable
Set MyField = Right(Myfield,Len(myfield)-3)
Where somefield = somevalue

test before implementing
0
 
ITBeneluxAuthor Commented:
DSAcker,
Substr & mid are not working in an ADP view.

Kevin3NF,
Can I accomplish this in one view, or do I need to make a 'assist' view.

This is what I want to accomplish:
SELECT     LEFT(dbo.Software.STAT, 3) AS title from dbo.Software (...and some joints)
the field STAT starts always with the same 3 char., so they need to be removed, otherwise my join will not work.


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin3NFCommented:
Ahh....you are joining, not updating.  Ignore my previous comment

I'll test and get back to you
0
 
Kevin3NFCommented:
Wait..are you wanting to join on the field without the first the characters, or just display then that way?
0
 
ITBeneluxAuthor Commented:
Yes, I need to join on the field without the first 3 char.
0
 
dsackerContract ERP Admin/ConsultantCommented:
A few things to rule out.

*  Try the functions with and without the $ sign on the end.

*  Just to make you are not having reference problems with VBA, try

        VBA.Mid$
        VBA.Mid
        (i.e., prefix the functions with VBA.)

*  Check that ADP hasn't turned off your standard VBA references to:

        Visual Basic For Applications
        Microsoft Access 12.0 Object Library
        OLE Automation
        Microsoft DAO 3.6 Object Library

In the VBA Editor (Alt-F11), Tools, References, at a minimum these should be checked.

0
 
ITBeneluxAuthor Commented:
I checked the references, and everything looks fine.  Isn't this a problem because It's a view and not a query... so It's SQL functions that must be used. Or am I wrong ?
0
 
Kevin3NFCommented:
SELECT     dbo.Table1.table1Key, dbo.Table2.Table2Key
FROM         dbo.Table1 JOIN
                      dbo.Table2
      ON Right(dbo.Table1.table1Key,Len(dbo.Table1.table1Key)-3) = right(dbo.Table2.Table2Key,Len(dbo.Table2.Table2Key)-3)

I created some generic tables, stuck data in them and did a join on the identical field.

table1Key            Table2Key            
-------------------- --------------------
HILSmithT1           HILSmithT1
JONSmithT1           HILSmithT1
HILSmithT2           HILSMithT2

(3 row(s) affected)

Note that the second row's first 3 characters are being ignored as part of the join...but displaying just for reference.

Hope that is what you were looking for....a join on all but the first 3 characters
0
 
ITBeneluxAuthor Commented:
That's indeed what i'm looking for. I added the code, did some tests, and I got no errors, but the join is not working.  I will provide my query:

SELECT     dbo.Software.STAT, dbo.Inventory.Serial, dbo.Software.PROG
FROM         dbo.Software LEFT OUTER JOIN
                      dbo.Inventory ON RIGHT(dbo.Software.STAT, LEN(dbo.Software.STAT) - 3) = RTRIM(LTRIM(dbo.Inventory.Serial))

the Software.STAT field contains APXBF9W42J (The serial number + APX in front)
the Inventory.serial field contains BF9W42J (Only the serial)  
these 2 must be linked... to be able to display to Inventory information.
0
 
dsackerContract ERP Admin/ConsultantCommented:
If you're using SQL Server (dbo?) or Access, the MID function should work fine.

If you have just been able to use the RIGHT function, then it stands to reason you can use any other, whether RIGHT, MID, LEFT, etc. They all come out of the same collection.

However, SUBSTRING (not abbreviated) works well in SQL Server:

SELECT     dbo.Software.STAT, dbo.Inventory.Serial, dbo.Software.PROG
FROM         dbo.Software LEFT OUTER JOIN
                      dbo.Inventory ON SUBSTRING(dbo.Software.STAT, 4, 99) = RTRIM(LTRIM(dbo.Inventory.Serial))
0
 
ITBeneluxAuthor Commented:
Super !! It's working.
Thank you kindly for your help.

ITBenelux
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now