Well I have tried the follwoing code now in a new window
is there anything else that I could possibly do?

Here is the code:

SELECT Forname, Surname, Location, Department, Username,
Password, [Type of Dialin]
FROM [SQT Live CISCO Accounts]
UNION ALL
SELECT Left([Name], Instr([Name], " ") -1) As Forname,
Mid([Name], Instr([Name], " ") + 1) As Surname, NULL,
Base, Username, Password, [Type of Dialin]
FROM [live CISCO Accounts]

Please could you assist?

Many Thanks

James

What is wrong with the current SQL? Undesired results or an error message?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
"James" <(E-Mail Removed)> wrote in message
news:04fb01c39c66$08797280$(E-Mail Removed)...
> Well I have tried the follwoing code now in a new window
> is there anything else that I could possibly do?
>
> Here is the code:
>
> SELECT Forname, Surname, Location, Department, Username,
> Password, [Type of Dialin]
> FROM [SQT Live CISCO Accounts]
> UNION ALL
> SELECT Left([Name], Instr([Name], " ") -1) As Forname,
> Mid([Name], Instr([Name], " ") + 1) As Surname, NULL,
> Base, Username, Password, [Type of Dialin]
> FROM [live CISCO Accounts]
>
> Please could you assist?
>
> Many Thanks
>
> James

Well I get an error messich which tells me that its
an "Invalid Procedure Call".

I have also found that it is in the bit of:

UNION ALL
SELECT Left([Name], Instr([Name], " ") -1) As Forname,
Mid([Name], Instr([Name], " ") + 1) As Surname, NULL,
Base, Username, Password, [Type of Dialin]
FROM [live CISCO Accounts]

If I run it without the UNION ALL command it runs quite
happily but when I add it in I get this Invalid Procedure
Call.

Does this help any?

Many Thanks

James

>-----Original Message-----
>What is wrong with the current SQL? Undesired results or
an error message?
>
>--
>HTH,
>
>Steve Clark, Access MVP
>FMS, Inc.
>Professional Solutions Group
>http://www.FMSInc.com
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>Is your Access database too slow?
>Are you ready to upgrade to SQL Server?
>Contact us for optimization and/or upsizing!
>http://www.FMSInc.com/consulting
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>"James" <(E-Mail Removed)> wrote in
message
>news:04fb01c39c66$08797280$(E-Mail Removed)...
>> Well I have tried the follwoing code now in a new window
>> is there anything else that I could possibly do?
>>
>> Here is the code:
>>
>> SELECT Forname, Surname, Location, Department, Username,
>> Password, [Type of Dialin]
>> FROM [SQT Live CISCO Accounts]
>> UNION ALL
>> SELECT Left([Name], Instr([Name], " ") -1) As Forname,
>> Mid([Name], Instr([Name], " ") + 1) As Surname, NULL,
>> Base, Username, Password, [Type of Dialin]
>> FROM [live CISCO Accounts]
>>
>> Please could you assist?
>>
>> Many Thanks
>>
>> James
>
>
>.
>

Hi,
The error means that something is wrong with either your Left
or Mid statement. I would guess that the value returned by InStr
within your Left function is probably returning 0, thereby giving you
a value of -1 for the length. That would definitely generate an
'invalid procedure call' error.

--
HTH
Dan Artuso, MVP

"A problem well stated is a problem half solved"


"James" <(E-Mail Removed)> wrote in message
news:076501c39ca5$5c0b57d0$(E-Mail Removed)...
> Well I get an error messich which tells me that its
> an "Invalid Procedure Call".
>
> I have also found that it is in the bit of:
>
> UNION ALL
> SELECT Left([Name], Instr([Name], " ") -1) As Forname,
> Mid([Name], Instr([Name], " ") + 1) As Surname, NULL,
> Base, Username, Password, [Type of Dialin]
> FROM [live CISCO Accounts]
>
> If I run it without the UNION ALL command it runs quite
> happily but when I add it in I get this Invalid Procedure
> Call.
>
> Does this help any?
>
> Many Thanks
>
> James
>
> >-----Original Message-----
> >What is wrong with the current SQL? Undesired results or
> an error message?
> >
> >--
> >HTH,
> >
> >Steve Clark, Access MVP
> >FMS, Inc.
> >Professional Solutions Group
> >http://www.FMSInc.com
> >-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> >Is your Access database too slow?
> >Are you ready to upgrade to SQL Server?
> >Contact us for optimization and/or upsizing!
> >http://www.FMSInc.com/consulting
> >-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> >"James" <(E-Mail Removed)> wrote in
> message
> >news:04fb01c39c66$08797280$(E-Mail Removed)...
> >> Well I have tried the follwoing code now in a new window
> >> is there anything else that I could possibly do?
> >>
> >> Here is the code:
> >>
> >> SELECT Forname, Surname, Location, Department, Username,
> >> Password, [Type of Dialin]
> >> FROM [SQT Live CISCO Accounts]
> >> UNION ALL
> >> SELECT Left([Name], Instr([Name], " ") -1) As Forname,
> >> Mid([Name], Instr([Name], " ") + 1) As Surname, NULL,
> >> Base, Username, Password, [Type of Dialin]
> >> FROM [live CISCO Accounts]
> >>
> >> Please could you assist?
> >>
> >> Many Thanks
> >>
> >> James
> >
> >
> >.
> >

So how do I rectify it? do I have to put +1 or something?

Many Thanks

James
>-----Original Message-----
>Hi,
>The error means that something is wrong with either your
Left
>or Mid statement. I would guess that the value returned
by InStr
>within your Left function is probably returning 0,
thereby giving you
>a value of -1 for the length. That would definitely
generate an
>'invalid procedure call' error.
>
>--
>HTH
>Dan Artuso, MVP
>
>"A problem well stated is a problem half solved"
>
>
>"James" <(E-Mail Removed)> wrote in
message
>news:076501c39ca5$5c0b57d0$(E-Mail Removed)...
>> Well I get an error messich which tells me that its
>> an "Invalid Procedure Call".
>>
>> I have also found that it is in the bit of:
>>
>> UNION ALL
>> SELECT Left([Name], Instr([Name], " ") -1) As Forname,
>> Mid([Name], Instr([Name], " ") + 1) As Surname, NULL,
>> Base, Username, Password, [Type of Dialin]
>> FROM [live CISCO Accounts]
>>
>> If I run it without the UNION ALL command it runs quite
>> happily but when I add it in I get this Invalid
Procedure
>> Call.
>>
>> Does this help any?
>>
>> Many Thanks
>>
>> James
>>
>> >-----Original Message-----
>> >What is wrong with the current SQL? Undesired results
or
>> an error message?
>> >
>> >--
>> >HTH,
>> >
>> >Steve Clark, Access MVP
>> >FMS, Inc.
>> >Professional Solutions Group
>> >http://www.FMSInc.com
>> >-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>> >Is your Access database too slow?
>> >Are you ready to upgrade to SQL Server?
>> >Contact us for optimization and/or upsizing!
>> >http://www.FMSInc.com/consulting
>> >-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>> >"James" <(E-Mail Removed)> wrote in
>> message
>> >news:04fb01c39c66$08797280$(E-Mail Removed)...
>> >> Well I have tried the follwoing code now in a new
window
>> >> is there anything else that I could possibly do?
>> >>
>> >> Here is the code:
>> >>
>> >> SELECT Forname, Surname, Location, Department,
Username,
>> >> Password, [Type of Dialin]
>> >> FROM [SQT Live CISCO Accounts]
>> >> UNION ALL
>> >> SELECT Left([Name], Instr([Name], " ") -1) As
Forname,
>> >> Mid([Name], Instr([Name], " ") + 1) As Surname, NULL,
>> >> Base, Username, Password, [Type of Dialin]
>> >> FROM [live CISCO Accounts]
>> >>
>> >> Please could you assist?
>> >>
>> >> Many Thanks
>> >>
>> >> James
>> >
>> >
>> >.
>> >
>
>
>.
>

"James" <(E-Mail Removed)> wrote
> So how do I rectify it? do I have to put +1 or something?
>
> >-----Original Message-----
> >Hi,
> >The error means that something is wrong with either your Left
> >or Mid statement. I would guess that the value returned by InStr
> >within your Left function is probably returning 0,
> thereby giving you
> >a value of -1 for the length. That would definitely generate an
> >'invalid procedure call' error.
> >
> >--
> >HTH
> >Dan Artuso, MVP
> >
Hi James,

It appears to me that you have 2 choices
if your problem is coming from

SELECT Left([Name], Instr([Name], " ") -1) As Forname,
Mid([Name], Instr([Name], " ") + 1) As Surname,

1) use immediate If

UNION ALL
SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
.......

The "Trim" handles people entering a space
before or after a name....it happens! (argh)

2) create functions in a module to get the first and last names

for example,

Public Function fLastName(pName As Variant) As Variant
On Error GoTo Err_fLastName
'Input: full name, i.e, "John T. Smith"
'Returns only last segment (last name), if it exists.
Dim arrSegments As Variant, intUBound As Integer

If Len(Trim(pName & "")) > 0 Then
arrSegments = Split(pName, " ", -1, vbBinaryCompare)
intUBound = UBound(arrSegments)
Select Case arrSegments(intUBound)
Case "Jr.", "Sr.", "MD", "I", "II", "III", "IV"
If intUBound > 1 Then
fLastName = arrSegments(intUBound - 1)
If Right(fLastName, 1) = "," Then
fLastName = Left(fLastName, Len(fLastName) - 1)
End If
Else
fLastName = arrSegments(intUBound)
End If
Case Else
fLastName = arrSegments(intUBound)
End Select
Else
fLastName = pName
End If
Exit_fLastName:
Exit Function

Err_fLastName:
MsgBox Err.Description
Resume Exit_fLastName

End Function

SELECT fLastName([Name]) AS Surname,
........

Please respond back if I have misunderstood.

Good luck,

Gary Walter

YAY!! It works...

Many Thanks to everyone who has assisted me and all the
time and effort put in.

James
>-----Original Message-----
>
>"James" <(E-Mail Removed)> wrote
>> So how do I rectify it? do I have to put +1 or
something?
>>
>> >-----Original Message-----
>> >Hi,
>> >The error means that something is wrong with either
your Left
>> >or Mid statement. I would guess that the value
returned by InStr
>> >within your Left function is probably returning 0,
>> thereby giving you
>> >a value of -1 for the length. That would definitely
generate an
>> >'invalid procedure call' error.
>> >
>> >--
>> >HTH
>> >Dan Artuso, MVP
>> >
>Hi James,
>
>It appears to me that you have 2 choices
>if your problem is coming from
>
>SELECT Left([Name], Instr([Name], " ") -1) As Forname,
>Mid([Name], Instr([Name], " ") + 1) As Surname,
>
>1) use immediate If
>
>UNION ALL
>SELECT
>IIF(Instr(Trim([Name] & "")," ") >0,
>Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
>IIF(Instr(Trim([Name] & "")," ") >0,
>Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
>.......
>
>The "Trim" handles people entering a space
>before or after a name....it happens! (argh)
>
>2) create functions in a module to get the first and last
names
>
>for example,
>
>Public Function fLastName(pName As Variant) As Variant
>On Error GoTo Err_fLastName
> 'Input: full name, i.e, "John T. Smith"
> 'Returns only last segment (last name), if it exists.
> Dim arrSegments As Variant, intUBound As Integer
>
> If Len(Trim(pName & "")) > 0 Then
> arrSegments = Split(pName, " ", -1,
vbBinaryCompare)
> intUBound = UBound(arrSegments)
> Select Case arrSegments(intUBound)
>
Case "Jr.", "Sr.", "MD", "I", "II", "III", "IV"
> If intUBound > 1 Then
> fLastName = arrSegments(intUBound - 1)
> If Right(fLastName, 1) = "," Then
> fLastName = Left(fLastName, Len
(fLastName) - 1)
> End If
> Else
> fLastName = arrSegments(intUBound)
> End If
> Case Else
> fLastName = arrSegments(intUBound)
> End Select
> Else
> fLastName = pName
> End If
>Exit_fLastName:
> Exit Function
>
>Err_fLastName:
> MsgBox Err.Description
> Resume Exit_fLastName
>
>End Function
>
>SELECT fLastName([Name]) AS Surname,
>........
>
>Please respond back if I have misunderstood.
>
>Good luck,
>
>Gary Walter
>
>
>.
>