Re: Join Query Expression

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks to the both of you, Jerry and John, with your help the query is now
working.

"John Spencer" wrote:

INVALID Procedure call could be cause by there being no comma in the
name field or the name field being blank.

You might experiment with either the Comments.Name field or the
Sample.Debtor field in a separate query and see if you can get the name
field in one or the other to match the structure of its equivalent field.

See if this query gives you an error or if it works.
SELECT Comments.Name,
Mid(Comments.Name, Instr(1,Comments.Name & ",",",")+1) & " " as FNMI
FROM Comments

Then Try this one to see if it works or if it errors.
SELECT Comments.Name
, Left(Comments.Name,Instr(1,Comments.Name & ",",",")-1) as LN
FROM Comments

If they work then you can build a query along the lines of Jerry
Whittle's suggestion. If they fail, you can play with them until the
expression(s) work and then build a query like Jerry Whittle's.

Otherwise you might consider building a VBA function to allows you to
pull out the parts of the names as separate entities. Take a look at
the split function.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


GMD wrote:
Anbody have any other ideas?

"GMD" wrote:

I get an invalid procedure call

"Jerry Whittle" wrote:

Try this:

SELECT [COMMENTS].[name],
[SAMPLE].[DEBTOR]
FROM [COMMENTS], [SAMPLE]
WHERE [SAMPLE].[DEBTOR]
=Mid([name],(InStr([name],",")+1)) & " "
& Left([name],(InStr([name],",")-1)) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



"GMD" wrote:

Jerry, thanks for the response. I am having trouble with the syntax though.
the first table is COMMENTS, the field Name is in the order of Last,FirstMI.
The second table is SAMPLE, the field DEBTOR is in the order of FirstMILast.

"Jerry Whittle" wrote:

SELECT EmpNameLastFirstMI.txtEmpNameLastFirstMI,
EmpNameFirstMiLast.txtEmpNameFirstMiLast
FROM EmpNameLastFirstMI, EmpNameFirstMiLast
WHERE EmpNameFirstMiLast.txtEmpNameFirstMiLast
=Mid([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")+1)) & " "
& Left([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")-1));

Basically you need to rearrange the name in one table to match the other.
However if the layout of the name in either table is different, such as a
period after the middle initial or a space after the comma, the above won't
work. You may need to use code to get as many matches as possible then
manually fix the problem records. Then there's the problem of having more
than one Jim J Jones in either table....

If you are getting this data from another system, consider talking to them
to see if they can reformat their export to you so that both tables match.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



"GMD" wrote:

Trying to join employee data from two tables with the employee names in
different order. Table 1 has employee name in this order: Brown,John A;
Table 2 has employee name in this order: John A Brown. Any help would be
greatly appreciated.

.

Relevant Pages

  • Re: Corstabquery or normal query
    ... This is my SQL for Query: ... "John Spencer" wrote: ... IF you have problems with the crosstab query, ... 2007 Deacon Bob 1 ...
    (microsoft.public.access.reports)
  • Re: Corstabquery or normal query
    ... "John Spencer" wrote: ... Try to run the query. ... IF you have problems with the crosstab query, ... 2007 Deacon Bob 1 ...
    (microsoft.public.access.reports)
  • Re: Removing Formatting Symbols from Phone numbers
    ... "John Spencer" wrote: ... Dim strOut As Variant ... For intCount = 1 To Len ... When I attempt to do the Update Query where I copy and past your code into ...
    (microsoft.public.access.queries)
  • Re: Using If expressions with an Or expression, and a Count questi
    ... The addition of the commas inside the was to ensure exact matches to Bill and Steve and preclude a record with STE being returned. ... "John Spencer" wrote: ... You did not give us any table or field names so what I wrote was a generic example of a query. ... To do this in design view, you would put the IIF statement into a field "box" and the Like into a criteria box under the IIF. ...
    (microsoft.public.access.queries)
  • Re: Removing Formatting Symbols from Phone numbers
    ... "John Spencer" wrote: ... Sometimes the Design View (query grid) will insert quotes in the update to ... Does Access add quote marks around the table and field names or around ... Dim strOut As Variant ...
    (microsoft.public.access.queries)