Left Function to Extract First Four Characters (1 Viewer)

SactoSweetie · Nov 28, 2006

I have seen variations of this question and have tried to modify for my purpose, but have been unsuccessful. I need to extract the first four digits of the Social Security Number. We no longer view the whole number, but a variation of the first four of social and first four of first name. I have tried many variations of the Left Function to extract the data from the field:

Expr1: SSN(Left([SSN],4))
Expr1: Left([SSN],4)

Etc.
I get various errors, mostly involving the comma. I am at a loss since I usually search for the code and find that it works. In this case, it doesn't work. I am also trying to extract the first four of the first name and will later combine the two although I broke it down since I couldn't get the basic "left" function to work.

Please help if possible.

pbaldy · Nov 28, 2006

Just tested this and it worked fine:

Expr1: Left([p_ssn],4)

What does your data look like?

SactoSweetie · Nov 28, 2006

Hello pbaldy,

I put in exactly what you have (w/my field name of course) and this is the error I have been getting:

"Syntax error (comma) in query expression 'TIMEK_PERSONS.[Left([SSN],4)]"

I am running Access 2003 w/Win XP Pro (Ver. 2002) operating system. My field consists of a social security number (#########) with no dashes.

Does anything jump out at you?? I have been working on this for a long time and it is such a simple issue.

gemma-the-husky · Nov 28, 2006

its probably the period

you need to left the entire variable

hence in this case

left([TIMEK_PERSONS].[SSN],4)

(assuming ssn is a string and not a numeric)

SactoSweetie · Nov 28, 2006

Good Morning... back at it again

gemma-the-husky said:
(assuming ssn is a string and not a numeric)

I questioned this myself. I am very new in Access and find that most of these terms don't really strike me as common sense. I couldn't find a good description of a string, although I did find that it is used a lot. Basically, the SSN field represents exactly what I put in my first post, 9 numbers. To me, this didn't strike me as a "string," but I found no alternative way to extract the segment of numbers I need from each record.

Ok... Here is the format I am working with. I have several tables that I pulled from an Oracle database and used to create a query. Within that query, I have two fields (SSN & FIRST_NAME) from which I need to extract the first four characters and then combine them in this format "####-LL??" (i.e. 5555-ABCD). In the original table, these fields are text fields.

gemma-the-husky said:
left([TIMEK_PERSONS].[SSN],4)

I tried this suggestion and got the same error

Edit: BTW... "TIMEK_PERSONS" is the table name. In the error I posted, I didn't actually input this into the experssion. It went in automatically when I selected the table. Not sure if that helps

boblarson · Nov 28, 2006

Also, check your references for any MISSING references.

Here's a quote from a previous post I wrote on this:

If it truly is a reference problem, the usual offender for date, left$, mid$ and right$ functions is MSCOMCTL.ocx. That one will occur and not show as MISSING. To fix (if that is the problem), click browse on the references dialog and browse to C:\Windows\System32\MSCOMCTL.ocx.

pbaldy · Nov 28, 2006

Can you post the entire SQL of the query?

cherosoullis · Nov 29, 2006

Try this one

Part from Table: Mid([Table name],1,4)
Number 1 is the starting place and number 4 is the counting from starting place. In this one is 4 digits from the first charachter. This expression must be entered on the field of the query.

neileg · Nov 29, 2006

Where are you entering the expression? Is this a new calculated field?

As pbaldy says, post the SQL of your query and we should be able to help.

SactoSweetie · Dec 1, 2006

Well darn, I had to scratch this whole project and start over. It seems that there were more problems than solutions! I have the disadvantage of not knowing a whole lot about Access. I don't know how to post the SQL of the query... I was using experssion builder in the query design.

Thank you all for the time you took to respond. Maybe it will end up helping out someone who is having a similar problem

pbaldy · Dec 1, 2006

Bummer. FYI, with the query in design view, you can click on View/SQL View to see the actual SQL. You can copy/paste to show it here.

Are you in Sacto, Ca? I was raised there, now living in Reno. Just spent Thanksgiving there with my mom.

SactoSweetie · Dec 1, 2006

I sure am! Lived here for about 5 years now

You know, I clicked on view SQL and it really didn't look right so I thought I was in the wrong spot. I will remember that the next time I have a questions and include it in my initial post. Sounds like it works out better if you can view the code.

Thanks again

Left Function to Extract First Four Characters (1 Viewer)

SactoSweetie

New member

pbaldy

Wino Moderator

SactoSweetie

New member

gemma-the-husky

Super Moderator

SactoSweetie

New member

boblarson

Smeghead

pbaldy

Wino Moderator

cherosoullis

Registered User.

neileg

AWF VIP

SactoSweetie

New member

pbaldy

Wino Moderator

SactoSweetie

New member

Similar threads

Users who are viewing this thread