Check for Duplicate values in Access

Hi, I'm creating a database that needs to check a value in a text field =
=
called JobCreate in a form called frmJobCreated against a table called =
tblJobCreated which has a whole list of job numbers in a field called =
JobNo, then come back with a message telling the user if there is a =
duplicate value...any help would be greatly appreciated,,thanks, FraserPlease, post DDL, sample data, and specify expected results.
Are you trying to report duplicate values, prevent them from being inserted
or what?
ML|||Hi, sorry for my ignorance but I'm quite new at databases what is DDL?
I'm trying to tell the user that a job number is already created if they
enter a duplicate number in the form that is already held in another
table, so it will display a message box telling them this, thanks
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
First of all, This is not the right forum because there is a dedicated forum
for access.
well. Use primary key (SOME THING LIKE SELECT COLUMN WHILE DESIGININGG AND
RIGHT CLICK SELECT PRIMARY KEY) in access on the column so that it will
prevent and throws an error. you can customise your error using VBA.
You have to use VBA because you are not using SQL SERVER 2000
Regards
R.D
--Knowledge gets doubled when shared
"Fraser Hetherington" wrote:

> Hi, sorry for my ignorance but I'm quite new at databases what is DDL?
> I'm trying to tell the user that a job number is already created if they
> enter a duplicate number in the form that is already held in another
> table, so it will display a message box telling them this, thanks
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Why are you making your users think of unique values? Why would any user car
e
what number his job is given?
Use a column constraint and design a function that will automatically
generate unique values for the job column. You sound like a crappy ISP -
designed to serve millions of users, yet each of them must create his/her ow
n
*globally unique* username in order to create a valid account. A total waste
of time.
ML|||"Fraser" <fraser68@.tiscali.co.uk> wrote in message
news:op.sy2fmed6s8gdz1@.acer64...
Hi, I'm creating a database that needs to check a value in a text
field
called JobCreate in a form called frmJobCreated against a table called
tblJobCreated which has a whole list of job numbers in a field called
JobNo, then come back with a message telling the user if there is a
duplicate value...any help would be greatly appreciated,,thanks,
Fraser
Fraser,
Pleaes try reposting your above message in *one* of the following
newsgroups:
comp.databases.ms-access
microsoft.public.access.forms
microsoft.public.access.formscoding
Sincerely,
Chris O.

char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value =
'50' and when I do a select on it with single quotes surrounding the value it
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
Thanks in advance,
John Scott.
John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.
|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.
|||Thanks for the help Russell!!
Thanks,
John Scott.
"Russell Fields" wrote:

> Of course, that falls apart if somebody typed leading blanks. Such as would
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>
>

char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value =
'50' and when I do a select on it with single quotes surrounding the value i
t
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
Thanks in advance,
John Scott.John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Thanks for the help Russell!!
--
Thanks,
John Scott.
"Russell Fields" wrote:

> Of course, that falls apart if somebody typed leading blanks. Such as wou
ld
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>
>

char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value = '50' and when I do a select on it with single quotes surrounding the value it
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
--
Thanks in advance,
John Scott.John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> => '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> => '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Thanks for the help Russell!!
--
Thanks,
John Scott.
"Russell Fields" wrote:
> Of course, that falls apart if somebody typed leading blanks. Such as would
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
> >I noticed that when creating a char(5) field in SQL 2005 it places any
> > padding on the right of the value in there...for example I put in a value
> > => > '50' and when I do a select on it with single quotes surrounding the value
> > it
> > returns '50 ' . I was just wondering if that is a setting in SQL that
> > can
> > be changed to have the padding at the front of the value instead at the
> > end?
> > Or, what is a good way of getting that padding at to the front?
> >
> >
> > --
> > Thanks in advance,
> >
> > John Scott.
>
>|||John,
Although there are ways to do this, you have to ask yourself if it is a
good idea. IMO, it is a bad idea. You would need such code whereever you
are handling the column, or you might get incorrect results. For
example, image a search query like this:
SELECT ...
FROM my_table
WHERE my_char5_column = @.val
If you simply use the standard behavior (right padding of spaces), this
will all work as expected, but if you change the formula, then it is all
up to get to get everything right (including how to handle strings that
are too long).
If you need left padding and no right padding, then you should do this
in the front-end application. If you must do it in the database, then I
suggest you create a computed column for it, just for display purposes.
For example:
ALTER TABLE my_table
ADD my_leftpadded_char5_column AS
RIGHT(SPACE(5)+RTRIM(my_char5_column),5)
HTH,
Gert-Jan
John Scott wrote:
> I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value => '50' and when I do a select on it with single quotes surrounding the value it
> returns '50 ' . I was just wondering if that is a setting in SQL that can
> be changed to have the padding at the front of the value instead at the end?
> Or, what is a good way of getting that padding at to the front?
> --
> Thanks in advance,
> John Scott.