Left Function to Extract First Four Characters

[SactoSweetie]

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]

Just tested this and it worked fine:

Expr1: Left([p_ssn],4)

What does your data look like?

 

[SactoSweetie]

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]

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]

Good Morning... back at it again

(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.

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]

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]

Can you post the entire SQL of the query?

 

[cherosoullis]

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]

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]

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]

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]

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