Validating A Surname

Apr 2, 2008

I need to validate a surname field which obviously can only consist of letters and occasionally spaces and/or hyphens. (i.e. van Driel or Johnson-Crooks)

I managed to create a validation rule which would allow me to have space in the surname field:

Is Null Or Not Like "*[!((a-z) or ( ))]*"

but when I tried to allow hyphens into the field - I used these codes - but none of them worked:


Is Null Or Not Like "*[!((a-z) or ( ) or (-))]*"

Is Null Or Not Like "*[!(a-z)]*" Or Not Like (" ") Or Not Like ("-")

Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[!( )]*" Or Not Like "*[!(-)]*"

Is Null Or Not Like "*[!(a-z)]*" Or Like "*[!( )]*" Or Like "*[!(-)]*"

Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[( )]*" Or Not Like "*[(-)]*"

Is Null or Not Like "*[!(a-z)]*" or (in ("-"," "))

Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*"))

Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*")) And (In ("-"," "))

Is Null Or Not Like "*[!(a-z)]*" Or Like ( ) Or (-)


Please can smeone help me.

James

View Replies


ADVERTISEMENT

Surname Name

Aug 22, 2006

I have a query with the following sql:
SELECT QY_ALL.TB_STAFF_STAFF_ID, QY_ALL.LNAME, QY_ALL.FNAME, QY_ALL.JOB_TITLE, QY_ALL.BASE, QY_ALL.DEPT_WARD, QY_ALL.STATUS, QY_ALL.COURSES_DONE_ID, QY_ALL.COURSE, QY_ALL.START_DATE, QY_ALL.END_DATE
FROM QY_ALL
WHERE (((QY_ALL.DEPT_WARD)=[Enter Dept/Ward]) AND ((QY_ALL.STATUS)="CURRENT") AND ((QY_ALL.COURSES_DONE_ID)=[SELECT COURSE ID]) AND ((QY_ALL.START_DATE) Between [Enter start date] And [Enter end date]))
ORDER BY QY_ALL.LNAME, QY_ALL.FNAME;When I run a report off it it returns correct data except it treats same surnames as one. E.g. if I have Jon, Jo and Fred Smith I get Jon listed but Jo and Fred's records attributed to him.

Is the sql wrong or do I fix it in the report?

Thanks.

View 1 Replies View Related

Extracting First Name And Surname

Jan 12, 2006

Hi,

I have been sent a file of customer contacts. They are held in a single field and have the format firstname + surname. So that I can load this data into a enterprise database system, I need to be able to extract surname and first name into to separate fields.

Any ideas on how I achieve this through an Access query?

Many thanks

Clay

View 2 Replies View Related

New Idea Duplicate Surname And DOB

Jul 5, 2005

hi ,

see below for the current code to find a duplicate surname (this works Fine)


now that ive properly thought it through what i need to do is insert a surename and if (by Chance) when i insert the Date of Birth if this is the same highlight it as a duplicate record. However if these people are twins then i need the option to insert the duplicate record.

any ideas on how to go about this, see code below.

Private Sub Text24_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.Surename.Value
stLinkCriteria = "[Surename]=" & "'" & SID & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("Surename", "tblData", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Duplicate Name " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

View 1 Replies View Related

Surname Search Problem

May 26, 2005

I have a button that when clicked is used to switch to a form displaying a record details based on a search by surname using a combo box. The record chosen by using the combo box is also displayed lower down on the initial form.
My problem is with multiple surname entries, it keeps switching to the first surname record; even when another surname record is selected.

Can I combine the use of surname and first name in my criteria? or Is there a better way to switch to the correct record once it has been selected. I am a bit of a novice with coding. My code is below:

Private Sub Open_Form_Click()
On Error GoTo Err_Open_Form_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Patient Details/Visit History"

stLinkCriteria = "[Surname]=" & "'" & Me![Surname] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Open_Form_Click:
Exit Sub

Err_Open_Form_Click:
MsgBox Err.Description
Resume Exit_Open_Form_Click
End Sub

View 2 Replies View Related

Input Mask For Surname With '

Aug 24, 2005

Is it possible to set up an input mask for a form for a surname field so that names will show first letter of name as a capital (easy), as in Smith - but for names like O'Connor, show the first and second letters as capitals? (and, of course, allow the ' to be entered).
Thanks.

View 1 Replies View Related

Sample Data With FirstName And Surname

May 3, 2005

Hi everyone,

Can someone please help me out here?

I am finishing a project at my client, and they said that I could take a copy of the database I created home (i.e. so that I will support them ;) )

I have a table tblPeople, however, the only condition is that I have to change the First- and LastName columns in this table, since it contains personal information. I need to change the names for about 600 people (400 men, 200 women)

I was thinking of adding the First- and Last Names of movie stars, to keep the flair in my DB :)
Does anyone maybe have a sample database with (movie star) names that I can use to populate my DB?

Thanks in advance,

Jean

View 3 Replies View Related

Letter + Auto Number From Surname

Oct 16, 2004

Hi i have to design a member database in access,
As the membership number i need to use the first letter of the surname,
then have an auto number.
e.g Smith, if the last member was S47 then Smith would need to be S48,
This will be used on a form for a video hire shop, so, the customer details
will be entered, is it possible for this auto number to work just by putting the name in
the surname field?

I only know basic Access so please help.

Thanks a lot

Nick

View 2 Replies View Related

Forms :: Prevent Duplicate First Name And Surname?

Apr 25, 2013

I just need a simple method to prevent a member being imput twice on a form.

I have two fields firstname and surname ( this is necessary).

I want to stop duplication of the same person being entered when staff use the form.

How do I stop this?

View 2 Replies View Related

General :: Only Last Word In Uppercase (Surname)

Sep 11, 2013

I have a textbox called "name", what i am trying to do is have lowercase on first/second names in the textbox except the last word (surname)this i want in uppercase.

View 1 Replies View Related

Combine Columns.. Forename Surname To Produce..

Nov 27, 2006

Hi

im trying to combine the

Forename Column and Surname column so i can produce another column which is initial surname eg

Matthew | Williamson | M Williams
Kevin | smith | K Smith


how do i do this ive completely gone blank!!!!!! :(((

View 3 Replies View Related

General :: Concentrate First Name And Surname Fields In Form?

Aug 8, 2013

I am currently using Access 2007, where I used to be able to concentrate First Name and Surname Fields by simply using:

= [First Name] &" "& [Surname]

However I have downloaded Access Run Time 2010 on the other employees computers so they will be able to access the Database, however since i have done this the First Name and Surname do not appear, it simply shows:

#Name?

I am guessing this is because it is a newer version, however even when I open the Database with Access 2007 it still does not work, even if I delete it and add it again.

View 2 Replies View Related

Reports :: Grouping Multiple Fields (Firstname / Surname)

Jan 21, 2015

I have a report with the fields

Firstname_
Surname_
[field1]
[field2]
etc etc (they're not called field1/2 but they're irreverent at present.

On the database people can be entered multiple times so I may have

Bob Smith
Jimmy Carter
Bob Smith
Jackson Smith
Tim Jones
Jackson Smith
Bob Holmes

Now I now with the group function I could group by the firstname_ field but the results would group like the following

Bob Smith
Bob Smith
Bob Holmes

Jackson Smith
Jackson Smith

Jimmy Carter

Tim Jones

And if I do it by surname, well I am sure you get the drift.

So how can I sort so I would get the following

Bob Smith
Bob Smith

Bob Holmes

etc etc.....

View 2 Replies View Related

Queries :: Possible To Extract First Letter Of A Surname In Access Query?

Jun 11, 2015

is it possible to extract the first letter of a surname in an Access Query? Below is my sample data, and below that is what I want to acheive.

Player Name
Hart, Joe
Cech, Petr
de Gea, David
Szczesny, Wojciech
Mignolet, Simon
Reina, Pepe
Lloris, Hugo
Howard, Tim
Begovic, Asmir

What I want the query to return:

Player Name
Hart, J
Cech, P
de Gea, D
Szczesny, W
Mignolet, S
Reina, P
Lloris, H
Howard, T
Begovic, A

View 6 Replies View Related

Forms :: Possible To Have Combo Box On Form With Fields Joined With Alphabetical Order On Surname

Aug 1, 2015

I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression

View 11 Replies View Related

Validating

May 3, 2006

I have a form that creates quotes for my company by inputting inventory items in a continuous subform. I need to be able to check and see if the item already exists in the inventory or if it is a new one that has been manually entered, and have this toggle a bound yes/no field that I will use to control whether or not some fields on the form are locked. Here is the code I have been using, but it doesn't seem to work at all.

Private Sub Combo14_AfterUpdate()
Dim itemcheck As Integer
itemcheck = DCount("[Item]", "tbItems", "[Item] = " & Chr(34) & Me![Item] & Chr(34))
If itemcheck = "0" Then
Me![Locked] = False
Else
Me![Locked] = True
End If
End Sub

I would appreciate anyone's feedback on this.

View 2 Replies View Related

Validating Records

Jul 19, 2007

All I really need some help.

I have two tables:

Table 1
Table 2

There is a one to many relationship here.

One in table 1 and many in table 2.

When I add in a record in table 1, I need a validation script/key that won't allow adding a record to table 1 without a corresponding record in table two.

Please help. I'm really stuck. I am doing this through a form. I tried messing around with the "requirements" but had no luck.

View 5 Replies View Related

Validating Against Another Table

Nov 15, 2006

Hello all.

I'm having a problem with a query. I am pulling information from a table that holds demographic info plus some company info. the data in question is the deptno of an employee. This employee also has a jobcode associated with it. I have another table with a list of a few jobcodes.

The data I will retrieve is fname, lname and deptno. If a person is in detno 210 then I want to check his jobcode in the jobcode table. If it is in the table then I want it to return 215 for a deptno. If not then it will return 210.

Does anyone know how this query would look like?

Thanks in advance.

View 1 Replies View Related

Validating Data

May 10, 2005

I have taken over a database that has be modified by a number of people over time. I have a forw with tab controls and a some of the fields are critical to be completed before you should be able to progress to the next tab. There are currently validation controls on the controls on the first tab, but if you only complete the first of these controls it is possible to move on to the second tab. The third tab is a subform and and if you try and select this tab, the validation text appears, but you can still switch to that tab which I want to prevent.

Finally, I have deleted one of the controls which was a critical field for completeion. I had assumed the validation was based on the validation on the control and would therefore be deleted but I still get a message saying complete this. I can't work out where there miight be code to make this happen? I'm not sure if anyone can help me with this without seeing the database, but I just wondered if anyone had any ideas what to even look for to help me understand this.

Sorry if all this is a bit vague, but would really grateful if anyone can help me at all.

View 1 Replies View Related

Validating A Dropdown

Oct 25, 2005

Question I am looking at some annoying Access database for a charity and got stuck on trying to make something work that they believe used to

Two Tables

Venues
pK Venue ID
Venue Name
Venue Capacity

Booking
pK
Venue
Name
Addr1
Addr2
Addr3
Ect

They have a query call capacity check which does the following
Count Occurrences of Venues then takes this away from venue capacity

Essentially they have a standard access form of the Booking table with a drop down box and want it to only validate if seats are still free at a venue eg if the current count for venue is less than the venue capacity.

Any ideas ?

View 2 Replies View Related

Validating A Form Using VBA

Mar 23, 2006

Hi all

Im currently trying to set validation rules for a form using VBA, does anyone know of a good method of doing this as i'm a bit stuck:confused:

View 1 Replies View Related

Validating New Records

May 4, 2006

I have a NEW INVOICE button, and the code behind says
DoCmd.GoToRecord , , acNewRec

Now if a user clicks on this buttons 3 times, three blank records get inserted in the table. THis is because I am generating teh invoice number myself, and the invoice_date is defaulted to current date (DATE())

The script written in the Sub Form_BeforeUpdate event to validate if the invoice is empty, if the total is ZERO etc. is all skipped, and I get blank records in the table. I even tried to place breakpoints in the before updates script, but it does not even come inside.

What is the right way to trap data errors, blank records in this situation?

Please help.

Thanks,
Vinai

View 3 Replies View Related

Validating An I.P. Entry

Jul 26, 2006

Hey,

I've been looking at this website (http://learning.north.londonmet.ac.uk/ib212/week7/validation.htm) and i'm trying to find an input mask that lets a user enter in an I.P. address like the IP settings in Network Connection > Properties > TCP/IP > Properties.

E.g. a user enters 19216802. I want this converted to 192.168.0.2 without the user having to enter any '.'

So far this is the closest i've gotten to validating the field
0##.0##.0##.0## whereas 0 means something has to be entered and the # are optional.

Thanks,

Barret

View 3 Replies View Related

Validating IP Entries

Jul 28, 2006

Hey,

In a form I am trying to complete, I want the user to be able to enter in an IP address like so

192168<user hits space bar>0<user hits space bar>1

Would convert to - 192.168.0.1

Is there an input mask which allows this to happen?

Thanks.

View 2 Replies View Related

Validating A Post Code

Nov 29, 2006

HiBeen trying To Find A Way Of Validating A Postcode.But Getting No Where Being They Can Be Like CT1 1QX, CT11 2DT, CT11 12DT Act.Best I've done is to make sure there's a space and it starts and ends with a Letter.Any Help Advice Would be much AppreciatedMick

View 2 Replies View Related

Validating Dates In Tables

Jun 20, 2007

Hi there, just a quick question really.
I have got a start date and an end date field in a table both in Date/Time data type. Can i validate the end date so that it cannot be longer than 5 days after the start date, that has been entered. Or is this impossible to do in a table?

Thanks for any help

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved