|
security
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Inner Joinscan you apply a Order By clause to a Inner Join? When I do this I get an
invaild Argument error. Yes you can. Would you post your SQL?
Show quoteHide quote "Cameron" <Came***@discussions.microsoft.com> wrote in message news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... > can you apply a Order By clause to a Inner Join? When I do this I get an > invaild Argument error. SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors Master
Table].ConNum, CompanyName FROM [contractors Master Table] INNER JOIN [Company Master Table] ON [contractors Master Table].ConNum = [Company Master Table].ConNum WHERE ([contractors Master Table].ConNum=[Forms]![main menu]![CompanyID] OR [Forms]![main menu]![CompanyID] Is Null) And ([contractors Master Table].Active=Yes) ORDER BY [Last] & ", " & [First]; Show quoteHide quote "Lynn Trapp" wrote: > Yes you can. Would you post your SQL? > > -- > > Lynn Trapp > Microsoft MVP (Access) > www.ltcomputerdesigns.com > > > "Cameron" <Came***@discussions.microsoft.com> wrote in message > news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... > > can you apply a Order By clause to a Inner Join? When I do this I get an > > invaild Argument error. > > > Try changing the Order By clause to simply:
ORDER BY [Last], [First] Show quoteHide quote "Cameron" <Came***@discussions.microsoft.com> wrote in message news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... > SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors Master > Table].ConNum, CompanyName > FROM [contractors Master Table] INNER JOIN [Company Master Table] ON > [contractors Master Table].ConNum = [Company Master Table].ConNum > WHERE ([contractors Master Table].ConNum=[Forms]![main menu]![CompanyID] > OR > [Forms]![main menu]![CompanyID] Is Null) And > ([contractors Master Table].Active=Yes) > ORDER BY [Last] & ", " & [First]; > > > > "Lynn Trapp" wrote: > >> Yes you can. Would you post your SQL? >> >> -- >> >> Lynn Trapp >> Microsoft MVP (Access) >> www.ltcomputerdesigns.com >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... >> > can you apply a Order By clause to a Inner Join? When I do this I get >> > an >> > invaild Argument error. >> >> >> I tried that and I still get an invalid argument error.
Show quoteHide quote "Lynn Trapp" wrote: > Try changing the Order By clause to simply: > > ORDER BY [Last], [First] > > -- > > Lynn Trapp > Microsoft MVP (Access) > www.ltcomputerdesigns.com > > > "Cameron" <Came***@discussions.microsoft.com> wrote in message > news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... > > SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors Master > > Table].ConNum, CompanyName > > FROM [contractors Master Table] INNER JOIN [Company Master Table] ON > > [contractors Master Table].ConNum = [Company Master Table].ConNum > > WHERE ([contractors Master Table].ConNum=[Forms]![main menu]![CompanyID] > > OR > > [Forms]![main menu]![CompanyID] Is Null) And > > ([contractors Master Table].Active=Yes) > > ORDER BY [Last] & ", " & [First]; > > > > > > > > "Lynn Trapp" wrote: > > > >> Yes you can. Would you post your SQL? > >> > >> -- > >> > >> Lynn Trapp > >> Microsoft MVP (Access) > >> www.ltcomputerdesigns.com > >> > >> > >> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... > >> > can you apply a Order By clause to a Inner Join? When I do this I get > >> > an > >> > invaild Argument error. > >> > >> > >> > > > So are [Last] and [First] fields in [contractors Master Table]?
Show quoteHide quote "Cameron" <Came***@discussions.microsoft.com> wrote in message news:BE7BE1C1-6B32-4C50-ADFD-DCA838F9EB81@microsoft.com... >I tried that and I still get an invalid argument error. > > "Lynn Trapp" wrote: > >> Try changing the Order By clause to simply: >> >> ORDER BY [Last], [First] >> >> -- >> >> Lynn Trapp >> Microsoft MVP (Access) >> www.ltcomputerdesigns.com >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... >> > SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors >> > Master >> > Table].ConNum, CompanyName >> > FROM [contractors Master Table] INNER JOIN [Company Master Table] ON >> > [contractors Master Table].ConNum = [Company Master Table].ConNum >> > WHERE ([contractors Master Table].ConNum=[Forms]![main >> > menu]![CompanyID] >> > OR >> > [Forms]![main menu]![CompanyID] Is Null) And >> > ([contractors Master Table].Active=Yes) >> > ORDER BY [Last] & ", " & [First]; >> > >> > >> > >> > "Lynn Trapp" wrote: >> > >> >> Yes you can. Would you post your SQL? >> >> >> >> -- >> >> >> >> Lynn Trapp >> >> Microsoft MVP (Access) >> >> www.ltcomputerdesigns.com >> >> >> >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> >> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... >> >> > can you apply a Order By clause to a Inner Join? When I do this I >> >> > get >> >> > an >> >> > invaild Argument error. >> >> >> >> >> >> >> >> >> oops... i think I see it. Change your WHERE clause:
WHERE ([contractors Master Table].ConNum=[Forms]![main menu]![CompanyID] OR IsNull([Forms]![main menu]![CompanyID])) Show quoteHide quote "Cameron" <Came***@discussions.microsoft.com> wrote in message news:BE7BE1C1-6B32-4C50-ADFD-DCA838F9EB81@microsoft.com... >I tried that and I still get an invalid argument error. > > "Lynn Trapp" wrote: > >> Try changing the Order By clause to simply: >> >> ORDER BY [Last], [First] >> >> -- >> >> Lynn Trapp >> Microsoft MVP (Access) >> www.ltcomputerdesigns.com >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... >> > SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors >> > Master >> > Table].ConNum, CompanyName >> > FROM [contractors Master Table] INNER JOIN [Company Master Table] ON >> > [contractors Master Table].ConNum = [Company Master Table].ConNum >> > WHERE ([contractors Master Table].ConNum=[Forms]![main >> > menu]![CompanyID] >> > OR >> > [Forms]![main menu]![CompanyID] Is Null) And >> > ([contractors Master Table].Active=Yes) >> > ORDER BY [Last] & ", " & [First]; >> > >> > >> > >> > "Lynn Trapp" wrote: >> > >> >> Yes you can. Would you post your SQL? >> >> >> >> -- >> >> >> >> Lynn Trapp >> >> Microsoft MVP (Access) >> >> www.ltcomputerdesigns.com >> >> >> >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> >> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... >> >> > can you apply a Order By clause to a Inner Join? When I do this I >> >> > get >> >> > an >> >> > invaild Argument error. >> >> >> >> >> >> >> >> >> Ok, I tried this one too. And it came up as an invalid argument error.
Show quoteHide quote "Lynn Trapp" wrote: > oops... i think I see it. Change your WHERE clause: > > WHERE ([contractors Master Table].ConNum=[Forms]![main menu]![CompanyID] > OR IsNull([Forms]![main menu]![CompanyID])) > > -- > > Lynn Trapp > Microsoft MVP (Access) > www.ltcomputerdesigns.com > > > "Cameron" <Came***@discussions.microsoft.com> wrote in message > news:BE7BE1C1-6B32-4C50-ADFD-DCA838F9EB81@microsoft.com... > >I tried that and I still get an invalid argument error. > > > > "Lynn Trapp" wrote: > > > >> Try changing the Order By clause to simply: > >> > >> ORDER BY [Last], [First] > >> > >> -- > >> > >> Lynn Trapp > >> Microsoft MVP (Access) > >> www.ltcomputerdesigns.com > >> > >> > >> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... > >> > SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors > >> > Master > >> > Table].ConNum, CompanyName > >> > FROM [contractors Master Table] INNER JOIN [Company Master Table] ON > >> > [contractors Master Table].ConNum = [Company Master Table].ConNum > >> > WHERE ([contractors Master Table].ConNum=[Forms]![main > >> > menu]![CompanyID] > >> > OR > >> > [Forms]![main menu]![CompanyID] Is Null) And > >> > ([contractors Master Table].Active=Yes) > >> > ORDER BY [Last] & ", " & [First]; > >> > > >> > > >> > > >> > "Lynn Trapp" wrote: > >> > > >> >> Yes you can. Would you post your SQL? > >> >> > >> >> -- > >> >> > >> >> Lynn Trapp > >> >> Microsoft MVP (Access) > >> >> www.ltcomputerdesigns.com > >> >> > >> >> > >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> >> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... > >> >> > can you apply a Order By clause to a Inner Join? When I do this I > >> >> > get > >> >> > an > >> >> > invaild Argument error. > >> >> > >> >> > >> >> > >> > >> > >> > > > Are you building this SQL statement in code, or is this the SQL of a saved
query? Try providing the table name for each field... SELECT [Company Master Table].EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors Master Table].ConNum, [Company Master Table].[Company Name] FROM [contractors Master Table] INNER JOIN [Company Master Table] ON [contractors Master Table].ConNum = [Company Master Table].ConNum Also you may need to make clear your WHERE statement with some more brackets WHERE ((([contractors Master Table].ConNum=[Forms]![main menu]![CompanyID]) OR ([Forms]![main menu]![CompanyID] Is Null)) And ([contractors Master Table].Active=Yes)) ORDER BY [Last] & ", " & [First]; You may also be having the problem because you are using Last and First as field names, both of which are reserved words in Access. -- Show quoteHide quoteJoan Wild Microsoft Access MVP Cameron wrote: > I tried that and I still get an invalid argument error. > > "Lynn Trapp" wrote: > >> Try changing the Order By clause to simply: >> >> ORDER BY [Last], [First] >> >> -- >> >> Lynn Trapp >> Microsoft MVP (Access) >> www.ltcomputerdesigns.com >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... >>> SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors >>> Master Table].ConNum, CompanyName >>> FROM [contractors Master Table] INNER JOIN [Company Master Table] ON >>> [contractors Master Table].ConNum = [Company Master Table].ConNum >>> WHERE ([contractors Master Table].ConNum=[Forms]![main >>> menu]![CompanyID] OR >>> [Forms]![main menu]![CompanyID] Is Null) And >>> ([contractors Master Table].Active=Yes) >>> ORDER BY [Last] & ", " & [First]; >>> >>> >>> >>> "Lynn Trapp" wrote: >>> >>>> Yes you can. Would you post your SQL? >>>> >>>> -- >>>> >>>> Lynn Trapp >>>> Microsoft MVP (Access) >>>> www.ltcomputerdesigns.com >>>> >>>> >>>> "Cameron" <Came***@discussions.microsoft.com> wrote in message >>>> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... >>>>> can you apply a Order By clause to a Inner Join? When I do this I >>>>> get an >>>>> invaild Argument error. It is in a saved query. And I have tried to put in the table names on the
respective fields. Still got the error Show quoteHide quote "Joan Wild" wrote: > Are you building this SQL statement in code, or is this the SQL of a saved > query? > > Try providing the table name for each field... > SELECT [Company Master Table].EmpNum, [Last] & ", " & [First] AS LastFirst, > [contractors Master Table].ConNum, [Company Master Table].[Company Name] > FROM [contractors Master Table] INNER JOIN [Company Master Table] ON > [contractors Master Table].ConNum = [Company Master Table].ConNum > > Also you may need to make clear your WHERE statement with some more brackets > WHERE ((([contractors Master Table].ConNum=[Forms]![main menu]![CompanyID]) > OR > ([Forms]![main menu]![CompanyID] Is Null)) And ([contractors Master > Table].Active=Yes)) > ORDER BY [Last] & ", " & [First]; > > You may also be having the problem because you are using Last and First as > field names, both of which are reserved words in Access. > -- > Joan Wild > Microsoft Access MVP > > Cameron wrote: > > I tried that and I still get an invalid argument error. > > > > "Lynn Trapp" wrote: > > > >> Try changing the Order By clause to simply: > >> > >> ORDER BY [Last], [First] > >> > >> -- > >> > >> Lynn Trapp > >> Microsoft MVP (Access) > >> www.ltcomputerdesigns.com > >> > >> > >> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... > >>> SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors > >>> Master Table].ConNum, CompanyName > >>> FROM [contractors Master Table] INNER JOIN [Company Master Table] ON > >>> [contractors Master Table].ConNum = [Company Master Table].ConNum > >>> WHERE ([contractors Master Table].ConNum=[Forms]![main > >>> menu]![CompanyID] OR > >>> [Forms]![main menu]![CompanyID] Is Null) And > >>> ([contractors Master Table].Active=Yes) > >>> ORDER BY [Last] & ", " & [First]; > >>> > >>> > >>> > >>> "Lynn Trapp" wrote: > >>> > >>>> Yes you can. Would you post your SQL? > >>>> > >>>> -- > >>>> > >>>> Lynn Trapp > >>>> Microsoft MVP (Access) > >>>> www.ltcomputerdesigns.com > >>>> > >>>> > >>>> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >>>> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... > >>>>> can you apply a Order By clause to a Inner Join? When I do this I > >>>>> get an > >>>>> invaild Argument error. > > > Cameron,
Joan may be on to something here. Are [Last] and [First] field names in your table? Show quoteHide quote "Cameron" <Came***@discussions.microsoft.com> wrote in message news:C9038F10-A77B-4412-B266-FFEBECA3EBC0@microsoft.com... > It is in a saved query. And I have tried to put in the table names on the > respective fields. Still got the error > > "Joan Wild" wrote: > >> Are you building this SQL statement in code, or is this the SQL of a >> saved >> query? >> >> Try providing the table name for each field... >> SELECT [Company Master Table].EmpNum, [Last] & ", " & [First] AS >> LastFirst, >> [contractors Master Table].ConNum, [Company Master Table].[Company Name] >> FROM [contractors Master Table] INNER JOIN [Company Master Table] ON >> [contractors Master Table].ConNum = [Company Master Table].ConNum >> >> Also you may need to make clear your WHERE statement with some more >> brackets >> WHERE ((([contractors Master Table].ConNum=[Forms]![main >> menu]![CompanyID]) >> OR >> ([Forms]![main menu]![CompanyID] Is Null)) And ([contractors Master >> Table].Active=Yes)) >> ORDER BY [Last] & ", " & [First]; >> >> You may also be having the problem because you are using Last and First >> as >> field names, both of which are reserved words in Access. >> -- >> Joan Wild >> Microsoft Access MVP >> >> Cameron wrote: >> > I tried that and I still get an invalid argument error. >> > >> > "Lynn Trapp" wrote: >> > >> >> Try changing the Order By clause to simply: >> >> >> >> ORDER BY [Last], [First] >> >> >> >> -- >> >> >> >> Lynn Trapp >> >> Microsoft MVP (Access) >> >> www.ltcomputerdesigns.com >> >> >> >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... >> >>> SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors >> >>> Master Table].ConNum, CompanyName >> >>> FROM [contractors Master Table] INNER JOIN [Company Master Table] ON >> >>> [contractors Master Table].ConNum = [Company Master Table].ConNum >> >>> WHERE ([contractors Master Table].ConNum=[Forms]![main >> >>> menu]![CompanyID] OR >> >>> [Forms]![main menu]![CompanyID] Is Null) And >> >>> ([contractors Master Table].Active=Yes) >> >>> ORDER BY [Last] & ", " & [First]; >> >>> >> >>> >> >>> >> >>> "Lynn Trapp" wrote: >> >>> >> >>>> Yes you can. Would you post your SQL? >> >>>> >> >>>> -- >> >>>> >> >>>> Lynn Trapp >> >>>> Microsoft MVP (Access) >> >>>> www.ltcomputerdesigns.com >> >>>> >> >>>> >> >>>> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> >>>> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... >> >>>>> can you apply a Order By clause to a Inner Join? When I do this I >> >>>>> get an >> >>>>> invaild Argument error. >> >> >> Yes, they are.
Show quoteHide quote "Lynn Trapp" wrote: > Cameron, > Joan may be on to something here. Are [Last] and [First] field names in your > table? > > -- > > Lynn Trapp > Microsoft MVP (Access) > www.ltcomputerdesigns.com > > > "Cameron" <Came***@discussions.microsoft.com> wrote in message > news:C9038F10-A77B-4412-B266-FFEBECA3EBC0@microsoft.com... > > It is in a saved query. And I have tried to put in the table names on the > > respective fields. Still got the error > > > > "Joan Wild" wrote: > > > >> Are you building this SQL statement in code, or is this the SQL of a > >> saved > >> query? > >> > >> Try providing the table name for each field... > >> SELECT [Company Master Table].EmpNum, [Last] & ", " & [First] AS > >> LastFirst, > >> [contractors Master Table].ConNum, [Company Master Table].[Company Name] > >> FROM [contractors Master Table] INNER JOIN [Company Master Table] ON > >> [contractors Master Table].ConNum = [Company Master Table].ConNum > >> > >> Also you may need to make clear your WHERE statement with some more > >> brackets > >> WHERE ((([contractors Master Table].ConNum=[Forms]![main > >> menu]![CompanyID]) > >> OR > >> ([Forms]![main menu]![CompanyID] Is Null)) And ([contractors Master > >> Table].Active=Yes)) > >> ORDER BY [Last] & ", " & [First]; > >> > >> You may also be having the problem because you are using Last and First > >> as > >> field names, both of which are reserved words in Access. > >> -- > >> Joan Wild > >> Microsoft Access MVP > >> > >> Cameron wrote: > >> > I tried that and I still get an invalid argument error. > >> > > >> > "Lynn Trapp" wrote: > >> > > >> >> Try changing the Order By clause to simply: > >> >> > >> >> ORDER BY [Last], [First] > >> >> > >> >> -- > >> >> > >> >> Lynn Trapp > >> >> Microsoft MVP (Access) > >> >> www.ltcomputerdesigns.com > >> >> > >> >> > >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... > >> >>> SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors > >> >>> Master Table].ConNum, CompanyName > >> >>> FROM [contractors Master Table] INNER JOIN [Company Master Table] ON > >> >>> [contractors Master Table].ConNum = [Company Master Table].ConNum > >> >>> WHERE ([contractors Master Table].ConNum=[Forms]![main > >> >>> menu]![CompanyID] OR > >> >>> [Forms]![main menu]![CompanyID] Is Null) And > >> >>> ([contractors Master Table].Active=Yes) > >> >>> ORDER BY [Last] & ", " & [First]; > >> >>> > >> >>> > >> >>> > >> >>> "Lynn Trapp" wrote: > >> >>> > >> >>>> Yes you can. Would you post your SQL? > >> >>>> > >> >>>> -- > >> >>>> > >> >>>> Lynn Trapp > >> >>>> Microsoft MVP (Access) > >> >>>> www.ltcomputerdesigns.com > >> >>>> > >> >>>> > >> >>>> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> >>>> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... > >> >>>>> can you apply a Order By clause to a Inner Join? When I do this I > >> >>>>> get an > >> >>>>> invaild Argument error. > >> > >> > >> > > > That could be your problem because, as Joan pointed out, Last and First are
reserved words in Access. Try renaming your table fields to some thing LastName, FirstName. Show quoteHide quote "Cameron" <Came***@discussions.microsoft.com> wrote in message news:1BC35022-F65C-4512-8B9C-4CD06563DF04@microsoft.com... > Yes, they are. > > "Lynn Trapp" wrote: > >> Cameron, >> Joan may be on to something here. Are [Last] and [First] field names in >> your >> table? >> >> -- >> >> Lynn Trapp >> Microsoft MVP (Access) >> www.ltcomputerdesigns.com >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> news:C9038F10-A77B-4412-B266-FFEBECA3EBC0@microsoft.com... >> > It is in a saved query. And I have tried to put in the table names on >> > the >> > respective fields. Still got the error >> > >> > "Joan Wild" wrote: >> > >> >> Are you building this SQL statement in code, or is this the SQL of a >> >> saved >> >> query? >> >> >> >> Try providing the table name for each field... >> >> SELECT [Company Master Table].EmpNum, [Last] & ", " & [First] AS >> >> LastFirst, >> >> [contractors Master Table].ConNum, [Company Master Table].[Company >> >> Name] >> >> FROM [contractors Master Table] INNER JOIN [Company Master Table] ON >> >> [contractors Master Table].ConNum = [Company Master Table].ConNum >> >> >> >> Also you may need to make clear your WHERE statement with some more >> >> brackets >> >> WHERE ((([contractors Master Table].ConNum=[Forms]![main >> >> menu]![CompanyID]) >> >> OR >> >> ([Forms]![main menu]![CompanyID] Is Null)) And ([contractors Master >> >> Table].Active=Yes)) >> >> ORDER BY [Last] & ", " & [First]; >> >> >> >> You may also be having the problem because you are using Last and >> >> First >> >> as >> >> field names, both of which are reserved words in Access. >> >> -- >> >> Joan Wild >> >> Microsoft Access MVP >> >> >> >> Cameron wrote: >> >> > I tried that and I still get an invalid argument error. >> >> > >> >> > "Lynn Trapp" wrote: >> >> > >> >> >> Try changing the Order By clause to simply: >> >> >> >> >> >> ORDER BY [Last], [First] >> >> >> >> >> >> -- >> >> >> >> >> >> Lynn Trapp >> >> >> Microsoft MVP (Access) >> >> >> www.ltcomputerdesigns.com >> >> >> >> >> >> >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> >> >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... >> >> >>> SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors >> >> >>> Master Table].ConNum, CompanyName >> >> >>> FROM [contractors Master Table] INNER JOIN [Company Master Table] >> >> >>> ON >> >> >>> [contractors Master Table].ConNum = [Company Master Table].ConNum >> >> >>> WHERE ([contractors Master Table].ConNum=[Forms]![main >> >> >>> menu]![CompanyID] OR >> >> >>> [Forms]![main menu]![CompanyID] Is Null) And >> >> >>> ([contractors Master Table].Active=Yes) >> >> >>> ORDER BY [Last] & ", " & [First]; >> >> >>> >> >> >>> >> >> >>> >> >> >>> "Lynn Trapp" wrote: >> >> >>> >> >> >>>> Yes you can. Would you post your SQL? >> >> >>>> >> >> >>>> -- >> >> >>>> >> >> >>>> Lynn Trapp >> >> >>>> Microsoft MVP (Access) >> >> >>>> www.ltcomputerdesigns.com >> >> >>>> >> >> >>>> >> >> >>>> "Cameron" <Came***@discussions.microsoft.com> wrote in message >> >> >>>> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... >> >> >>>>> can you apply a Order By clause to a Inner Join? When I do this >> >> >>>>> I >> >> >>>>> get an >> >> >>>>> invaild Argument error. >> >> >> >> >> >> >> >> >> The only thing that puts a wrench into that idea is that the Developer who
originally wrote this program and that SQL script said that that function works fine on her machine. The only difference is that she was contracted to build this application and isn't physically connected to the work server. Show quoteHide quote "Lynn Trapp" wrote: > That could be your problem because, as Joan pointed out, Last and First are > reserved words in Access. Try renaming your table fields to some thing > LastName, FirstName. > > -- > > Lynn Trapp > Microsoft MVP (Access) > www.ltcomputerdesigns.com > > > "Cameron" <Came***@discussions.microsoft.com> wrote in message > news:1BC35022-F65C-4512-8B9C-4CD06563DF04@microsoft.com... > > Yes, they are. > > > > "Lynn Trapp" wrote: > > > >> Cameron, > >> Joan may be on to something here. Are [Last] and [First] field names in > >> your > >> table? > >> > >> -- > >> > >> Lynn Trapp > >> Microsoft MVP (Access) > >> www.ltcomputerdesigns.com > >> > >> > >> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> news:C9038F10-A77B-4412-B266-FFEBECA3EBC0@microsoft.com... > >> > It is in a saved query. And I have tried to put in the table names on > >> > the > >> > respective fields. Still got the error > >> > > >> > "Joan Wild" wrote: > >> > > >> >> Are you building this SQL statement in code, or is this the SQL of a > >> >> saved > >> >> query? > >> >> > >> >> Try providing the table name for each field... > >> >> SELECT [Company Master Table].EmpNum, [Last] & ", " & [First] AS > >> >> LastFirst, > >> >> [contractors Master Table].ConNum, [Company Master Table].[Company > >> >> Name] > >> >> FROM [contractors Master Table] INNER JOIN [Company Master Table] ON > >> >> [contractors Master Table].ConNum = [Company Master Table].ConNum > >> >> > >> >> Also you may need to make clear your WHERE statement with some more > >> >> brackets > >> >> WHERE ((([contractors Master Table].ConNum=[Forms]![main > >> >> menu]![CompanyID]) > >> >> OR > >> >> ([Forms]![main menu]![CompanyID] Is Null)) And ([contractors Master > >> >> Table].Active=Yes)) > >> >> ORDER BY [Last] & ", " & [First]; > >> >> > >> >> You may also be having the problem because you are using Last and > >> >> First > >> >> as > >> >> field names, both of which are reserved words in Access. > >> >> -- > >> >> Joan Wild > >> >> Microsoft Access MVP > >> >> > >> >> Cameron wrote: > >> >> > I tried that and I still get an invalid argument error. > >> >> > > >> >> > "Lynn Trapp" wrote: > >> >> > > >> >> >> Try changing the Order By clause to simply: > >> >> >> > >> >> >> ORDER BY [Last], [First] > >> >> >> > >> >> >> -- > >> >> >> > >> >> >> Lynn Trapp > >> >> >> Microsoft MVP (Access) > >> >> >> www.ltcomputerdesigns.com > >> >> >> > >> >> >> > >> >> >> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> >> >> news:3EAEABA5-7F77-4CD5-898C-462888EC8619@microsoft.com... > >> >> >>> SELECT EmpNum, [Last] & ", " & [First] AS LastFirst, [contractors > >> >> >>> Master Table].ConNum, CompanyName > >> >> >>> FROM [contractors Master Table] INNER JOIN [Company Master Table] > >> >> >>> ON > >> >> >>> [contractors Master Table].ConNum = [Company Master Table].ConNum > >> >> >>> WHERE ([contractors Master Table].ConNum=[Forms]![main > >> >> >>> menu]![CompanyID] OR > >> >> >>> [Forms]![main menu]![CompanyID] Is Null) And > >> >> >>> ([contractors Master Table].Active=Yes) > >> >> >>> ORDER BY [Last] & ", " & [First]; > >> >> >>> > >> >> >>> > >> >> >>> > >> >> >>> "Lynn Trapp" wrote: > >> >> >>> > >> >> >>>> Yes you can. Would you post your SQL? > >> >> >>>> > >> >> >>>> -- > >> >> >>>> > >> >> >>>> Lynn Trapp > >> >> >>>> Microsoft MVP (Access) > >> >> >>>> www.ltcomputerdesigns.com > >> >> >>>> > >> >> >>>> > >> >> >>>> "Cameron" <Came***@discussions.microsoft.com> wrote in message > >> >> >>>> news:D2E89D96-4736-4064-90A6-253CC3643DB1@microsoft.com... > >> >> >>>>> can you apply a Order By clause to a Inner Join? When I do this > >> >> >>>>> I > >> >> >>>>> get an > >> >> >>>>> invaild Argument error. > >> >> > >> >> > >> >> > >> > >> > >> > > > 
Other interesting topics
User level security-- do not have necessary permissions
restrict combo box selection depending on user Warehouse personnel can't get the needed form - just the backgroun Opening a previously secured database Problem switching between Workgroups Access security question how to change a name in "msysaccounts"? Programically redirecting to different mdw files Problem opening an Access file from my computer Protect Access database in a Network |
|||||||||||||||||||||||
