Any Microsoft Access Experts here?

[Mr Incredible]

I usually use Excel and am quite familiar with Excel type functions. But I have a need to create an Access database in which I need to calculate a text field from another text field.

This Excel function works a treat:

=LEFT(G3,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},G3&"0123456789"))-1)

Where G3 is the cell in which a full UK post code is present. The above expression strips out the left most characters up to the point where a number character is found. This then becomes my postal region.

Can I create a similar expression in Access using the Left and Instr function? I can't work it out!

Thanks.

 

[jaipal2004]

Yes you can although can't remember how from the top of my head(on avf app).

On Access 2003 I remember you could get a function wizard or something similar?

 

[mark1080p]

Instead of FIND I think it's INSTR function in ms access and the syntax is the other way round to excel.

As in find("1",cell ref) in excel is instr([fieldname],"1") in ms access

INSTR being short for instring..

Try
=LEFT([postcode],MIN(INSTR([postcode],{"0","1","2","3","4","5","6","7","8","9"},[postcode]&"0123456789"))-1)

Apologies I am on the AVF app, so you might need to play around a bit with it.

Hope this helps

 

[Mr Incredible]

Thanks. I'm afraid the error I get doesn't help me too much in fault finding.

The "{" "}" curly brackets appear to give a "expression contains a malformed GUID constant" because when I put rounded brackets I then get another error, suggesting the syntax is invalid and I've missed something out! Needle and haystack come to mind considering my limited logical abilities!

 

[GarethP]

Custom Functions are your friend here...

Create a code module

Paste in this function

Public Function GetPostalRegion(strPostCode)

Dim intPosition As Integer
Dim StrCharToCheck As String
Dim intPositionCount As Integer
Dim intLength As Integer
Dim strTwoChars As String

intPositionCount = 1
strInitialChars = "XX"
intLength = Len(strPostCode)

Do Until intPositionCount > intLength

StrCharToCheck = Mid(strPostCode, intPositionCount, 1)

Select Case StrCharToCheck

Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0

strInitialChars = Left(strPostCode, intPositionCount - 1)
Exit Do

Case Else

intPositionCount = intPositionCount + 1

End Select

Loop

GetPostalRegion = strInitialChars

End Function​

The to use it just put;

GetPostalRegion(postcode)

see how that goes...

(sorry, it appears to have lost my indentation!)

 

[Mr Incredible]

Thanks. I'll try it later in the week, am away for a few days now.

 

[tvbox]

Thanks. I'll try it later in the week, am away for a few days now.

Wait, Mr Incredible needed help with IT?!!!

 

[Mr Incredible]

Custom Functions are your friend here...

Create a code module

Paste in this function

Public Function GetPostalRegion(strPostCode)

Dim intPosition As Integer
Dim StrCharToCheck As String
Dim intPositionCount As Integer
Dim intLength As Integer
Dim strTwoChars As String

intPositionCount = 1
strInitialChars = "XX"
intLength = Len(strPostCode)

Do Until intPositionCount > intLength

StrCharToCheck = Mid(strPostCode, intPositionCount, 1)

Select Case StrCharToCheck

Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0

strInitialChars = Left(strPostCode, intPositionCount - 1)
Exit Do

Case Else

intPositionCount = intPositionCount + 1

End Select

Loop

GetPostalRegion = strInitialChars

End Function​

The to use it just put;

GetPostalRegion(postcode)

see how that goes...

(sorry, it appears to have lost my indentation!)

I must be doing something wrong! I created a "Module" and pasted the above with the exception of changing PostCode to Postcode. I then attempted to create a calculated (text) field, entered the expression GetPostalRegion(postcode) and got this message:

Any pointers warmly welcome!!

Thanks.

 

[mark1080p]

I will be on my PC shortly, so will send you the formula without the need of vb coding. Before I endeavour, if you had a table with the following.

AB1 2CD
AB23 3EF
B2 4FG
B11 5HJ
CF23 6JK

You want the query to return with
AB1
AB23
B2
B11
CF23

Also does your data have the space in between or is it like CF236JK?

 

[mark1080p]

if it is the above...
Try these
Left([Post Code],(InStr([Post Code]," ")-1)) the -1 manually removes the space
or
Trim(Left([Post Code],(InStr([Post Code]," ")))) to remove space after first part of post code

When I run my query i get:

Query1
Post Code | Formula
AB1 2CD | AB1
AB12 3EF | AB12
B1 4GH | B1
B22 5JK | B22
CF1 6AB | CF1
CF22 7CD | CF22

 

[Mr Incredible]

I will be on my PC shortly, so will send you the formula without the need of vb coding. Before I endeavour, if you had a table with the following.

AB1 2CD
AB23 3EF
B2 4FG
B11 5HJ
CF23 6JK

You want the query to return with
AB1
AB23
B2
B11
CF23

Also does your data have the space in between or is it like CF236JK?

Hi,

Not quite.

If I have
AB1 2CD
AB23 3EF
B2 4FG
B11 5HJ
CF23 6JK

I want
AB
AB
B
B
CF

Something is weird. I tried a simple calculated field to just get the left most 2 characters in the Postcode field.

When I enter the expression for the calculation, and enter a "P" it returns the "Postcode" field OK., but when I enter it it goes away as if to calculate the results, but it then returns this error:

I'm confused.

 

[mark1080p]

Ah... got ya... so you just want the letters. ok i will play about a bit more

 

[GarethP]

What version of Access are you using? and you want this calculated field in a table, not a query, right?

 

[Bubblin]

Hi,

Use

Instr([postcode],"*" ) replace star to count number if places to the character you want to find, you could use "*[1-9]" in this case to find the first number.


Then left([postcode], (instr([postcode],"*" )) to return what your after.

Your getting the error I think because its from another table not the one your in or have open in the query.

 

[Bubblin]

Oh and you may need to trim the instr by -1 (sorry)

 

[Mr Incredible]

What version of Access are you using? and you want this calculated field in a table, not a query, right?

2010 64 bit Office. And yes, I just want a calculated field in the same (single) table.

Hi,

Use

Instr([postcode],"*") replace star to count number if places to the character you want to find, you could use "*[1-9]" in this case to find the first number.


Then left([postcode], (instr([postcode],"*")) to return what your after.

Your getting the error I think because its from another table not the one your in or have open in the query.

I only have one table to work on. I imported an Excel spreadsheet with company names and addresses, but I segment the geography by postal region and want to have that available within the table. I attempted to just append a new calculated field at the end of the imported columns.

Here's the single table and you can see the Postcode field clearly!

 

[Bubblin]

On sheet1 open in design view and manually add a new column which will contain the new postcode geography items you want, formatted as text,

Then in the query you can link append that new data using the update function.

 

[Mr Incredible]

Oh and you may need to trim the instr by -1 (sorry)

And the parameter to start the Instr at!

I have now concluded there is a bug with Access 2010 64 bit.

I have a VM running Windows 8.1 and I installed Office 2010 32 bit. A simple calculated expression like
Left([Postcode],InStr(1,[Postcode],"6")) works in the 32 bit version but not 64 bit. 64 bit seems to think the field Postcode is not in the table.

So, given that I can get result:

How do I get to the desired result because replacing "6" with "*[1-9]" does not work - it just returns nothing because is is looking for the string [1-9] not any number in the range 1-9.

 

[Mr Incredible]

Custom Functions are your friend here...

Create a code module

Paste in this function

Public Function GetPostalRegion(strPostCode)

Dim intPosition As Integer
Dim StrCharToCheck As String
Dim intPositionCount As Integer
Dim intLength As Integer
Dim strTwoChars As String

intPositionCount = 1
strInitialChars = "XX"
intLength = Len(strPostCode)

Do Until intPositionCount > intLength

StrCharToCheck = Mid(strPostCode, intPositionCount, 1)

Select Case StrCharToCheck

Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0

strInitialChars = Left(strPostCode, intPositionCount - 1)
Exit Do

Case Else

intPositionCount = intPositionCount + 1

End Select

Loop

GetPostalRegion = strInitialChars

End Function​

The to use it just put;

GetPostalRegion(postcode)

see how that goes...

(sorry, it appears to have lost my indentation!)

Once I got around the 64bit problem, the Function returns this error:

 

[Bubblin]

In the 64bit version

Try

[Sheet1].[postcode] instead of just postcode.

Try 'like [1-9]' instead of *[1-9]

Won't near my PC till Mon so cant try this out until then.

 

[Mr Incredible]

Access 64 bit still doesn;t think the field is in the database table I have open!

And Like doesn't appear to work either.

Cheers!

UPDATE:

I created a new database starting from scratch in the 64 bit version, and this time it recognises the field. However, the functions as discussed above do not appear to work in a calculated field, although I can get a simple "Left" and "InStr" expression to work as in the 32 bit version.

 

[Mr Incredible]

Custom Functions are your friend here...

Create a code module

Paste in this function

Public Function GetPostalRegion(strPostCode)

Dim intPosition As Integer
Dim StrCharToCheck As String
Dim intPositionCount As Integer
Dim intLength As Integer
Dim strTwoChars As String

intPositionCount = 1
strInitialChars = "XX"
intLength = Len(strPostCode)

Do Until intPositionCount > intLength

StrCharToCheck = Mid(strPostCode, intPositionCount, 1)

Select Case StrCharToCheck

Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0

strInitialChars = Left(strPostCode, intPositionCount - 1)
Exit Do

Case Else

intPositionCount = intPositionCount + 1

End Select

Loop

GetPostalRegion = strInitialChars

End Function​

The to use it just put;

GetPostalRegion(postcode)

see how that goes...

(sorry, it appears to have lost my indentation!)

Well I approached it from a slightly different angle.....

I created a simple query and added the function to a new field in the Query. Result!

 

[GarethP]

Freaky. I only have Access 2007 at home and that apparently doesn't support calculated fields in tables at all!!! I'd tested the custom function in a query and it worked fine - so assumed that it'd work ok as a calculated field in a table - obviously a newly feature that doesn't work properly! Don't get me started on date issues that still exist going back from Access 95!

Glad you got it sorted...

 

[Mr Incredible]

Also.....

This worked as well once I decided to move the Expression into a query rather than the table itself:

PostalRegion: IIf(IsNumeric(Mid([PostCode],2,1)),Left([PostCode],1),Left([PostCode],2))