Another recordset problem!

Hello All

I had some help with a recordset problem recently (many thanks Stefan and
Daryl!), but now have another. I just get "Error 3135 Syntax error in JOIN
operation" with the following:

    Set rsNIC = CurrentDb.OpenRecordset( _
        "SELECT staffs.*, months.*, [NI tables 2010].*" & _
        " FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months" & _
        " ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
        " ON [NI tables 2010].niSet = months.nicset)" & _
        " WHERE (((months.[month name])='" & [Forms]![frm x main]![month
name] & "'));")

If anyone can help with this I'd be really grateful - as ever!!

Many thanks
Leslie Isacas



0
PayeDoc
3/4/2010 1:22:24 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
671 Views

Similar Articles

[PageSpeed] 53

The part:

([NI tables 2010] RIGHT JOIN months" & _
        " ON [NI tables 2010].[NI letter] = staffs.[NI code])"

sounds strange: you define a join on two tables... but ON another table, I 
mean, why it is not:


([NI tables 2010] RIGHT JOIN months" & _
 " ON [NI tables 2010].niSet = months.nicset)"


and then, introduces table  staffs in another join:

....
        " FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months" & _
        " ON [NI tables 2010].niSet = months.nicset)"  & _
        " ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
....

Such join is ambiguous, though, and cannot be used as it is, with Jet. Make 
a first query with:
SELECT * FROM [NI tables 2010] RIGHT JOIN months  ON [NI tables 2010].niSet 
= months.nicset

and use that saved query:


.....
    " FROM staffs RIGHT JOIN savedQuery ON savedQuery.[NI letter] = 
staffs.[NI code] "  & _
.....


if that is really what you want.




Vanderghast, Access MVP



"PayeDoc" <enquiries@gppayroll.org.uk> wrote in message 
news:e8c%23c25uKHA.3536@TK2MSFTNGP06.phx.gbl...
> Hello All
>
> I had some help with a recordset problem recently (many thanks Stefan and
> Daryl!), but now have another. I just get "Error 3135 Syntax error in JOIN
> operation" with the following:
>
>    Set rsNIC = CurrentDb.OpenRecordset( _
>        "SELECT staffs.*, months.*, [NI tables 2010].*" & _
>        " FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months" & _
>        " ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
>        " ON [NI tables 2010].niSet = months.nicset)" & _
>        " WHERE (((months.[month name])='" & [Forms]![frm x main]![month
> name] & "'));")
>
> If anyone can help with this I'd be really grateful - as ever!!
>
> Many thanks
> Leslie Isacas
>
>
> 

0
vanderghast
3/4/2010 1:53:31 PM
Hello Vanderghast

Many thanks for your reply.
Perhaps I should provide a little more info.

I had this, which works fine:
    Set rs = CurrentDb.OpenRecordset( _
        "SELECT staffs.*, [tax tables].*, [NI tables 2009].*, [SSP
tables].*, [SMP tables].* " & _
        "FROM (([tax tables] RIGHT JOIN ([NI tables 2009] RIGHT JOIN staffs
" & _
        "ON [NI tables 2009].[NI letter] = staffs.[NI code]) " & _
        "ON [tax tables].set = staffs.taxset) LEFT JOIN [SSP tables] " & _
        "ON staffs.[ssp set] = [SSP tables].[SSP set]) LEFT JOIN [SMP
tables] " & _
        "ON staffs.[smp set] = [SMP tables].[SMP set];")

The problem is that I now need to pick up the data in [NI tables 2009] via a
join to the table [months] i.e. [NI tables 2010].niSet = months.nicset. This
is in addition to the join [NI tables 2009].[NI letter] = staffs.[NI code],
which is in the original recordset above. I still need the other fields from
[SSP tables], [SMP tables] and [tax tables] from the recordset above, but
thought it best to define a new recordset (rsNIC) for the [NI tables 2009]
values that are needed, and it's this new recordset that I'm now struggling
with. Would it be feasible/recommended instead to amend the original
recordset above, so that it picks up the the data in [NI tables 2009] using
the (new) link to the table [months]?

Hope this all makes sense!
Thanks again for your help.
Les



"vanderghast" <vanderghast@com> wrote in message
news:OXrsWI6uKHA.4636@TK2MSFTNGP06.phx.gbl...
> The part:
>
> ([NI tables 2010] RIGHT JOIN months" & _
>         " ON [NI tables 2010].[NI letter] = staffs.[NI code])"
>
> sounds strange: you define a join on two tables... but ON another table, I
> mean, why it is not:
>
>
> ([NI tables 2010] RIGHT JOIN months" & _
>  " ON [NI tables 2010].niSet = months.nicset)"
>
>
> and then, introduces table  staffs in another join:
>
> ...
>         " FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months" &
_
>         " ON [NI tables 2010].niSet = months.nicset)"  & _
>         " ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
> ...
>
> Such join is ambiguous, though, and cannot be used as it is, with Jet.
Make
> a first query with:
> SELECT * FROM [NI tables 2010] RIGHT JOIN months  ON [NI tables
2010].niSet
> = months.nicset
>
> and use that saved query:
>
>
> ....
>     " FROM staffs RIGHT JOIN savedQuery ON savedQuery.[NI letter] =
> staffs.[NI code] "  & _
> ....
>
>
> if that is really what you want.
>
>
>
>
> Vanderghast, Access MVP
>
>
>
> "PayeDoc" <enquiries@gppayroll.org.uk> wrote in message
> news:e8c%23c25uKHA.3536@TK2MSFTNGP06.phx.gbl...
> > Hello All
> >
> > I had some help with a recordset problem recently (many thanks Stefan
and
> > Daryl!), but now have another. I just get "Error 3135 Syntax error in
JOIN
> > operation" with the following:
> >
> >    Set rsNIC = CurrentDb.OpenRecordset( _
> >        "SELECT staffs.*, months.*, [NI tables 2010].*" & _
> >        " FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months" &
_
> >        " ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
> >        " ON [NI tables 2010].niSet = months.nicset)" & _
> >        " WHERE (((months.[month name])='" & [Forms]![frm x main]![month
> > name] & "'));")
> >
> > If anyone can help with this I'd be really grateful - as ever!!
> >
> > Many thanks
> > Leslie Isacas
> >
> >
> >
>


0
PayeDoc
3/4/2010 2:33:30 PM
Hi PayeDoc,
try in this way
Set rsNIC = CurrentDb.OpenRecordset("SELECT staffs.*, months.*, [NI tables 
2010].* FROM (staffs RIGHT JOIN [NI tables 2010] ON staffs.[NI code] = [NI 
tables 2010].[NI letter]) RIGHT JOIN months ON [NI tables 2010].niSet = 
months.nicset WHERE months.[month name]='" & [Forms]![frm x main]![month
 name] & "'")

All on the same line. 

HTH Paolo

"PayeDoc" wrote:

> Hello All
> 
> I had some help with a recordset problem recently (many thanks Stefan and
> Daryl!), but now have another. I just get "Error 3135 Syntax error in JOIN
> operation" with the following:
> 
>     Set rsNIC = CurrentDb.OpenRecordset( _
>         "SELECT staffs.*, months.*, [NI tables 2010].*" & _
>         " FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months" & _
>         " ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
>         " ON [NI tables 2010].niSet = months.nicset)" & _
>         " WHERE (((months.[month name])='" & [Forms]![frm x main]![month
> name] & "'));")
> 
> If anyone can help with this I'd be really grateful - as ever!!
> 
> Many thanks
> Leslie Isacas
> 
> 
> 
> .
> 
0
Utf
3/4/2010 2:46:01 PM
Hello Paolo

That's it - no errors now!
Very many thanks for this.

Les



"Paolo" <Paolo@discussions.microsoft.com> wrote in message
news:B56C5FE8-99CB-4EE6-BB5E-5353E8001C8F@microsoft.com...
> Hi PayeDoc,
> try in this way
> Set rsNIC = CurrentDb.OpenRecordset("SELECT staffs.*, months.*, [NI tables
> 2010].* FROM (staffs RIGHT JOIN [NI tables 2010] ON staffs.[NI code] = [NI
> tables 2010].[NI letter]) RIGHT JOIN months ON [NI tables 2010].niSet =
> months.nicset WHERE months.[month name]='" & [Forms]![frm x main]![month
>  name] & "'")
>
> All on the same line.
>
> HTH Paolo
>
> "PayeDoc" wrote:
>
> > Hello All
> >
> > I had some help with a recordset problem recently (many thanks Stefan
and
> > Daryl!), but now have another. I just get "Error 3135 Syntax error in
JOIN
> > operation" with the following:
> >
> >     Set rsNIC = CurrentDb.OpenRecordset( _
> >         "SELECT staffs.*, months.*, [NI tables 2010].*" & _
> >         " FROM ((staffs RIGHT JOIN ([NI tables 2010] RIGHT JOIN months"
& _
> >         " ON [NI tables 2010].[NI letter] = staffs.[NI code])" & _
> >         " ON [NI tables 2010].niSet = months.nicset)" & _
> >         " WHERE (((months.[month name])='" & [Forms]![frm x main]![month
> > name] & "'));")
> >
> > If anyone can help with this I'd be really grateful - as ever!!
> >
> > Many thanks
> > Leslie Isacas
> >
> >
> >
> > .
> >


0
PayeDoc
3/4/2010 3:55:19 PM
Reply:

Similar Artilces:

matching a column of numbers to another in another spreadsheet
I have a spreadsheet of shortpayment amounts and their invoice numbers on one spreadsheet with a column of credit amounts with their invoice numbers on another spreadsheet. I need to match the credits written with the shortpayments. kinda like this spreadsheet 1 spreadsheet 2 inv # amount date Credit # Amount Date 122334 15.00 11-25-09 675555 15.00 12-10-09 223345 22.00 10-20-09 754444 22.00 111-25-09 naturally...

Can I call movenext on a recordset with a single new row in it?
I open a recordset into a table that may or may not return rows. If it does not return a row, I .AddNew one. This repeats as required. For instance, if the recordset had two rows and I'm adding four, it should re-use the first two and then .AddNew two new ones. Is it OK to .MoveNext on a newly created row? It seems to work OK, but I just want to make sure there's no edge cases? Maury As long as rs.EOF isn't true, you should be able to use .MoveNext without issue. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Maury Markowitz"...

Creating column content based on another column's keyword(s) #2
Max, That scriptlet was very useful, and easily tweakable to work in myria applications. Thx! -K -- KHashmi31 ----------------------------------------------------------------------- KHashmi316's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1043 View this thread: http://www.excelforum.com/showthread.php?threadid=27348 Glad to hear that ! Thanks for posting back -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "KHashmi316" <KHashmi316.1evevz@excelforum-nospam.com> wrote in message news:KHashmi316.1e...

install 2003 upgrade problem
I recently bought a new computer with Win 7 OS. I have Office 2003 installed on my old XP computer. The problem is I have an upgrade version and can't find my original Office 97 cd. I had to have it when Office 2003 was installed on the XP computer after the hard drive crashed in 2006. I don't need a new version for my home use so would like to install my upgrade Office 2003 version on the Win 7 computer. Is there any way to get the upgrade to install when I don't have the old cd to put in when it asks for it. I still have the C of A with the product ID for Offi...

Grabbing a number from another cell on another worksheet
Hello, I have a spreadsheet with a number of different worksheets. All I want to do is if I enter a number into a cell on the 2nd worksheet for example, I want it to automatically show in a specific cell on the first worksheet. So a simple copy and paste but for excel to automatically do it for me! it seems like something so easy that I just cant figure out how to do. Try this... Do this once. Select the cell on the 2nd sheet Right click>Copy Navigate to the other sheet and select the cell where you want the value to appear. Right click>Paste Special>Paste Lin...

Acessing function with a DLL which has be loaded from another dll
I have A.dll and B.dll. Assume that A.dll has following functions: A1() A2() Assume that B.dll has following functions: B1() B2() I am loading B.dll from function A1() in A.dll.After Loading B.dll i make a call to B1().After executing the call the function returns to beginning of A2.dll. why is this so? Define "begining". The word tends to make no sense in this context. What you seem to be saying is that you have void A1() { HANDLE h = ::LoadLibrary(_T("B.DLL")); ...test for h==NULL, deal with error typedef void (*B1)() B1Proc; B1Proc B1; ...

split problem
Hi all, I am interesting to break a string with spaces into an array with substrings and to have it in the same cell. for example: dim MyString as string MyString = range("A1").value ' range("A1").value = "Good morning" range("A1").value = split(MyString, " "). The problem is that instead of: Good morning as range("A1").value I get only: morning. Please help me to fix it! Thanks Eli 'Once you split it returns an array; so that you could try Dim MyString As String MyString = Range("A1&q...

Loop through recordset and create excel spreadsheets
Hi all I have a list of customers in a query called "qry RBUs". I have a continuous form which filters on an option group for different activities (Audits and VVs). I want to create individual spreadsheets for EACH RBU for EACH Activity. So far I have started with the code below, firstly is it best to do it this way? Or use recordsetclone? Secondly, what is the best way to filter the recordset per RBU (only if any data exists for that RBU) and create an excel spreadsheet from this, named by Activity & RBU. Thirdly, is it possible to apply conditional format...

FRx problem
I had installed Frx desktop 6.5 with Great plains 8.0, but when i go to Company->Information and changed accounting system from Frx demo general ledger to Great Plains - SQL Server\MSDE it hangs. why? Regards, Syed Fahad Anwar. Syed, FRx 6.5 is not supported/tested for Great Plains version 8.0. I would suggest that you upgrade to Version 6.7 and see if that doesn't resolve the problems. Regards. Dont forget to run the FRX SQL scripts (as part of the installation). "GP Consultant" <Hockey2356@aol.com> wrote in message news:1110896948.678056.116200@z14g2000c...

Populate data in cell by looking at another cells data
Hi Everyone, Hope I find you well. I'm not even sure how to go about this, so I hope that someone can shed some light. I have a serial number in one cell eg '80199DD270238' where 'DD' is code for another value, in this case 'DD' = 'BLUE'. How can I automatically populate a cell with the value 'BLUE' by looking at the serial number. Many thanks for any help you can provide. Best Regards Gazza Hi if the characters are alsways at the same place use =IF(MID(A1,6,2)="DD","Blue","other color") if they could be at ...

Sum dependant on '1' value in another column
Hi, Let's say I have a '1' or nothing in column 'L' and a time in seconds in column 'B'. The '1' means the time is a legitimate one. Can I do a sum of all the 'legitimate' times in column 'B' based on whether there is a corresponding '1' in column 'L'? Is there a formula that will allow me to do that? Sorry for the ignorance of the question but it is late and I am struggling to make this spreadsheet work for me. Thanks in advance, Dave Hi DL, Look at the SUMIF function in Excel help. Try: =SUMIF(L1:L10...

Problem on junk mail list for Outlook 2003
Dear All My outlook 2003 is connected to MS exchange 2003 server, In past, I will add some email address to junk e-mail list using web browser, but now I cannot add in junk e-mail list use web browser now, it show "Unable to add the list, The maximum size for the list is 1024 entries" or " A error occured procured your Junk mail lists, You are over the size limited on the server". But If I add the email address to junk e-mail list using MS outlook 2003, the list can can update to web-base now, How can I solve this problem Rgds Alan OWA supports fewer than Outloo...

is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? #2
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? ...

Problem with #name
Dear All , I am having a problem with the excel. In column 2, I have a set of data which consists of two data 1) "- Cardburry Taste Good (C)" 2) "- Cardburry Taste Bad (M)" 3) "- Cardburry Taste Moderate (K)" When I open the csv files in excel , the data 2) and 3) converted to "#name?" but data 1) is ok. I have made some research if i replace data 2) and 3) with (C) to replace with (M) and (K) ,Everthing is fine .. My question is what is (C) ? Is it a excel formula ? Looking forward for your kindly answer Thanks Ujoon Hi the problem is the minus si...

Automatic Link to another workbook
I have a workbook created that, every time I open it, I get the question: Do you want to update the automatic link to data in another workbook? I didn't consciously set up a link and I don't want to get this message. How do I find this link and break it so I will not continue to get this message? Any help is appreciated. Close any other Excel workbooks you have open and do a "Find" for :\ (colon backslash). If you have several worksheets in your workbook, ask Excel to search the entire workbook by selecting find within "Workbook" instead of "Sheet&quo...

Language and Keyboard Problems
Hi, I do a lot of typing in Greek and so sometimes I "Set Language" in Word to Greek. I use a different keyboard layout to the standard Greek one (it allows for accents etc.) and I've set this to be the default keyboard for the Greek language on my system. However, when I change the language in Word to Greek it uses the standard keyboard setting, which is not the one I want. I end up constantly having to change it in the Windows language bar. So what I'm asking is: Is there a way to change the default keyboard for a specific language in Microsoft Word 2007...

Problem Sending Attachments
When sending attachments via IMAP the recipient is confronted with a renamed file named winmail.dat that is unopenable due to the "dat" extension. Anyone know why this is and how to stop it? In other words how to acheive WISIWTG (what I sent is what they get). Normally WinMail.DAT attachments are the result of sending mail in Rich Text Format. Is that the format you're using? "Blogman" <@> wrote in message news:eoa4YE7wFHA.3556@TK2MSFTNGP12.phx.gbl... > When sending attachments via IMAP the recipient is confronted with a > renamed file named winmai...

Problem Pinning a Website to the IE8 icon on the Win7 Taskbar
I posted this message a couple of weeks ago, but the problem persists I have two brand new Win7 home premium 64 computers, each with IE8 installed and the icon on the taskbar. I want to pin some frequently accessed web links to the IE8 icon on the taskbar. The process I use is as follows: 1. Enter the desired website=92s address in the IE8 address bar, and access the site. 2. Left click the address in the IE8 address bar and drag the address down to atop the Taskbar=92s IE8 icon. That=92s it. But when I use this identical procedure on the two computers, I succeed on one a...

Copying from one ListControl to Another...
Hello everyone, I have been studying how List Controls work, and now I have two List Controls, A and B. Both of the list controls are in Report_View and they both have the same # of columns. What I would like to do is copy each entry of A into B. I use CListCtrl::GetItem(..) to get the item, and I use CListCtrl::InsertItem(..) to insert the item into B. However, this is not working. It is only inserting the first subitem. Is it possible to copy the entire item i.e, subitems 0-2 from CListCtrl A into CListCtrl B? Thanks ...

Sending Problems #2
I am having trouble with my outgoing mail, and my ISP is not accepting my login to the SMTP server. I have raised a problem, with them but while I await them fixing it, I have configured outlook 2003 to use a different account to send. My problem is that the faulty account is attempting to send some read receipts (I think) and is constantly failing. How do I flush the sending on this account? (There is nothing in my outbox) Paul <nothanks@btopenworld.com> wrote: > My problem is that the faulty account is attempting to send some read > receipts (I think) and is constantly...

if I can solve some problems in my excel program by using office .
There is several days that I can not open any of my files of excel infact I can not open the program itself.Whenever I try to do that a message appears that asks me a Microsoft office XP professional with front page disk which I have not .I want to know that if I can solve the problem with any online updates for offices or windows or not ,in short is it any way that I can solve the matte through microsoft online or not? Hi sounds like re-installing could be a good idea :-) -- Regards Frank Kabel Frankfurt, Germany "Maryam" <Maryam @discussions.microsoft.com> schrieb im...

Problem logging on to RWW
We have RWW set up and is working for everyone except one user who I've just built a new Toshiba Sattelitte laptop for with XP SP3 and IE8 installed. He's in the RWW group in AD. I can log on to his PC without problems. When he logs on to RWW he can see the list of PCs. He clicks on his PC and clicks Connect and then the page goes blank. I've enabled Active X addons. Any ideas? Verify that the pc in the LAN is set to allow remote connections and from this user. Start - rclick my computer - properties - remote -Please post the resolution to your issue so...

Another question about IF(COUNTIF) checks in Excel
<Tiff1618@discussions.microsoft.com> wrote in message > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com... > > Hey again, > > > > Is there a formula I can use to figure out if there is one specific phrase > > in a selection? > > > > I'm updating the attendance prgram at the school that I work at. Every > > student has their own attendance sheet in Excel, and each sheet sort of > > looks > > like this: > > > > |Monday| > > Period 1:| A | (A=Absent; S=Seat time) > > Per...

Another If/Ten
HI all. Trying to use IF/THEN to look at Column A on one sheet, if any cell in that column equals today() would like cells from that row to fill in another sheet. Is this possible? Any ideas on how to make that happen?? Thanks. Flue. Maybe you can use format|Conditional formatting. Select the range to color (I used A2:X999). With A2 the activecell, I did: format|conditional formatting formula is: =($a2=today()) and give it a nice format Brian Bennett wrote: > > HI all. > > Trying to use IF/THEN to look at Column A on one sheet, if any cell in that > column equal...

Navigation problem
I've got an XML doccument that has the structure: <Data> <inc_date value="28 Feb 2009" /> <inc_id1 value="2604" /> <inc_id2 value="0" /> <KF_LiveDbMode value="no" display="Training" /> <errorDescription value="" /> <errorUserAdvice value="" /> <KF_ReturnNumber value="0" /> <FRSIncidentNumber value="20090228-2604.0" /> <OriginOfCallDesc value="POL PW - CURRENTLY TAKING OUR CALLS" /> <FRSId value="JK&qu...