Any Microsoft Access Experts here?

Mr Incredible

Distinguished Member
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

Well-known Member
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

Well-known Member
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
 
Last edited:

Mr Incredible

Distinguished Member
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

Well-known Member
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

Distinguished Member
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:

upload_2014-4-11_19-26-50.png


Any pointers warmly welcome!!

Thanks.
 

mark1080p

Well-known Member
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

Well-known Member
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

Distinguished Member
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.
 
Last edited:

mark1080p

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

GarethP

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

Bubblin

Well-known Member
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.
 
Last edited:

Mr Incredible

Distinguished Member
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

Well-known Member
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

Distinguished Member
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

Distinguished Member
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

Well-known Member
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.
 
Last edited:

Mr Incredible

Distinguished Member
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.
 
Last edited:

Mr Incredible

Distinguished Member
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

Well-known Member
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

Distinguished Member
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))
 

The latest video from AVForums

Podcast: CES 2021 Special - Sony, LG, Panasonic, Samsung, TCL and Hisense TV news and more...

Latest News

iFi Audio launches iDSD Diablo DAC/Headphone amp
  • By Andy Bassett
  • Published
Samsung launches Galaxy Buds Pro
  • By Andy Bassett
  • Published
Samsung launches Galaxy S21, S21+ and S21 Ultra smart phones
  • By Andy Bassett
  • Published
Mola Mola debuts Kula integrated amplifier
  • By Andy Bassett
  • Published
TCL announces TS8132 soundbar
  • By Andy Bassett
  • Published
Top Bottom