One-to-many problem, multiple row per record

Below is what I tried, but it seems to part run then gives the error "Syntax error in string in query question '((((qryPriorityPartnerships.txtInstitutionName)..............'

I have tried a couple of basic variations (at a guess..) but no joy. Not that I know what most of the other lines mean, but out of interest could you tell me what the line "strSQL = Left(strSQL, (Len(strSQL) - 5))" means?


Private Sub cmdDeptFilter_Click()
On Error GoTo Err_cmdDeptFilter_Click

Dim strSQL As String
Dim stDocName As String
stDocName = "View Priority Partnerships"
Dim strWHERE As String


strSQL = "(((qryPriorityPartnerships.txtInstitutionName)='A' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='B' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='C' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='D' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='E' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='F' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='G' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='H' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='I' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='J' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='K' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='L' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='M' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='N' Or"
strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='O')) AND "

If Nz(Me.cboDept, "") <> "" Then
strSQL = strSQL & "Discipline='" & Me.cboDept & "' AND "
End If


strSQL = Left(strSQL, (Len(strSQL) - 5))

'Strip Last " And "
If strSQL <> "" Then

strSQL = Left(strSQL, (Len(strSQL) - 5))
Debug.Print strSQL
'Set the Filter property
DoCmd.OpenReport stDocName, acPreview
Reports![View Priority Partnerships].Filter = strSQL
Reports![View Priority Partnerships].FilterOn = True
RunCommand acCmdZoom75
blnNotOpenMenu = True
Else
MsgBox "No criteria specified, opening the report unfiltered"
DoCmd.OpenReport stDocName, acPreview
RunCommand acCmdZoom75
End If
Exit_cmdDeptFilter_Click:
Exit Sub

Err_cmdDeptFilter_Click:
MsgBox Err.Description
Resume Exit_cmdDeptFilter_Click



End Sub
 
...could you tell me what the line "strSQL = Left(strSQL, (Len(strSQL) - 5))" means?
Click to expand...

Both sections of strSQL end with a space followed by the word AND and then another space. In other words 5 characters

strSQL = strSQL & " (qryPriorityPartnerships.txtInstitutionName)='O')) AND "
strSQL = strSQL & "Discipline='" & Me.cboDept & "' AND "

What the strSQL = Left(strSQL, (Len(strSQL) - 5)) does is first finds the length (Len()) of strSQL subtracts 5--we'll call this the new length. The Left() function takes the strSQL starting from the left (i.e. the first character of the string) for a length equal to the new length and assigns that back to strSQL. This, in effect, strips of the 2 spaces and the AND at the end of strSQL.

In the code there is this statement: Debug.Print strSQL

This will write the strSQL text to the VBA immediate window when you run the code. You can review that text to look for any errors. You can also copy the text from the immediate window and post it here for us to review. Or if it is easier, can you zip and post your database with any sensitive data removed?

Also there might be 1 too many left parenthesis in this statement

strSQL = "(((qryPriorityPartnerships.txtInstitutionName)='A ' Or"
 
I've had a good look through but not managed to get anywhere with it. I have done my best to remove any sensitive data without breaking anything. Let me know if you have any problems with it as is and I will make any necessary changes.

I have copied the code from the immediate window below, although it seems to be cut off at the end (although this is all the code displayed). As you'll see this code includes names which I abbreviated out earlier to make it clearer, so it looks somewhat longer.

Report is "Priority Partnership Report", Code is behind cmdFilter on frmDepartmentFilter3

((qryPriorityPartnerships.txtInstitutionName)='Tsinghua University' Or (qryPriorityPartnerships.txtInstitutionName)='China University of Political Science and Law' Or (qryPriorityPartnerships.txtInstitutionName)='East China Normal University' Or (qryPriorityPartnerships.txtInstitutionName)='National Tsing Hua University' Or (qryPriorityPartnerships.txtInstitutionName)='Fudan University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University of Science and Technology' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Management Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Science Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Technology Delhi (IIT-D)' Or (qryPriorityPartnerships.txtInstitutionName)='National Institute of Advanced Studies Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='National Taiwan University NTU' Or (qryPrio
rityPartnerships.txtInstitutionName)='College of William and Mary' Or (qryPriorityPartnerships.txtInstitutionName)='Brown University' Or (qryPriorityPartnerships.txtInstitutionName)='University of South Flori
((qryPriorityPartnerships.txtInstitutionName)='Tsinghua University' Or (qryPriorityPartnerships.txtInstitutionName)='China University of Political Science and Law' Or (qryPriorityPartnerships.txtInstitutionName)='East China Normal University' Or (qryPriorityPartnerships.txtInstitutionName)='National Tsing Hua University' Or (qryPriorityPartnerships.txtInstitutionName)='Fudan University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University of Science and Technology' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Management Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Science Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Technology Delhi (IIT-D)' Or (qryPriorityPartnerships.txtInstitutionName)='National Institute of Advanced Studies Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='National Taiwan University NTU' Or (qryPrio
rityPartnerships.txtInstitutionName)='College of William and Mary' Or (qryPriorityPartnerships.txtInstitutionName)='Brown University' Or (qryPriorityPartnerships.txtInstitutionName)='University of South Flori
((qryPriorityPartnerships.txtInstitutionName)='Tsinghua University' Or (qryPriorityPartnerships.txtInstitutionName)='China University of Political Science and Law' Or (qryPriorityPartnerships.txtInstitutionName)='East China Normal University' Or (qryPriorityPartnerships.txtInstitutionName)='National Tsing Hua University' Or (qryPriorityPartnerships.txtInstitutionName)='Fudan University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University of Science and Technology' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Management Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Science Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='Indian Institute of Technology Delhi (IIT-D)' Or (qryPriorityPartnerships.txtInstitutionName)='National Institute of Advanced Studies Bangalore' Or (qryPriorityPartnerships.txtInstitutionName)='National Taiwan University NTU' Or (qryPrio
rityPartnerships.txtInstitutionName)='College of William and Mary' Or (qryPriorityPartnerships.txtInstitutionName)='Brown University' Or (qryPriorityPartnerships.txtInstitutionName)='University of South Florida')) AND Discipline='Bioscie
 

Attachments

For some reason I am getting an "unrecognized database format" error & cannot open the file. In what Access version was the database created?

In terms of your filter, it would be a lot cleaner if you used the corresponding key field of the institution rather than the actual name of the institution. Also, is there some way to segregate/categorize the institutions with respect to those you that you will always want the report versus those you do not?
 
Looking more closely at the immediate window text and reformatting a little, I did find a couple potential issues. I just don't know if the items are an anomaly of the immediate window or an error in your filter. I've highlighted the problem areas in red. I don't see the full name of the university but also the OR is missing. And of course the AND clause is truncated as you mentioned. By the way, it looks like there are 2 sets for the series of "OR" statements. Did you run the code twice?

((qryPriorityPartnerships.txtInstitutionName)='Tsi nghua University' Or (qryPriorityPartnerships.txtInstitutionName)='Chin a University of Political Science and Law' Or
(qryPriorityPartnerships.txtInstitutionName)='East China Normal University' Or (qryPriorityPartnerships.txtInstitutionName)='Nati onal Tsing Hua University' Or (qryPriorityPartnerships.txtInstitutionName)='Fuda n University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University of Science and Technology' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Management Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Science Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Technology Delhi (IIT-D)' Or
(qryPriorityPartnerships.txtInstitutionName)='Nati onal Institute of Advanced Studies Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Nati onal Taiwan University NTU' Or (qryPriorityPartnerships.txtInstitutionName)='College of William and Mary' Or (qryPriorityPartnerships.txtInstitutionName)='Brow n University' Or (qryPriorityPartnerships.txtInstitutionName)='Univ ersity of South Flori
((qryPriorityPartnerships.txtInstitutionName)='Tsi nghua University' Or (qryPriorityPartnerships.txtInstitutionName)='Chin a University of Political Science and Law' Or
(qryPriorityPartnerships.txtInstitutionName)='East China Normal University' Or (qryPriorityPartnerships.txtInstitutionName)='Nati onal Tsing Hua University' Or (qryPriorityPartnerships.txtInstitutionName)='Fuda n University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University of Science and Technology' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Management Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Science Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Technology Delhi (IIT-D)' Or
(qryPriorityPartnerships.txtInstitutionName)='Nati onal Institute of Advanced Studies Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Nati onal Taiwan University NTU' Or (qryPriorityPartnerships.txtInstitutionName)='College of William and Mary' Or (qryPriorityPartnerships.txtInstitutionName)='Brow n University' Or (qryPriorityPartnerships.txtInstitutionName)='Univ ersity of South Flori
((qryPriorityPartnerships.txtInstitutionName)='Tsi nghua University' Or (qryPriorityPartnerships.txtInstitutionName)='Chin a University of Political Science and Law' Or
(qryPriorityPartnerships.txtInstitutionName)='East China Normal University' Or (qryPriorityPartnerships.txtInstitutionName)='Nati onal Tsing Hua University' Or (qryPriorityPartnerships.txtInstitutionName)='Fuda n University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University' Or (qryPriorityPartnerships.txtInstitutionName)='Hong Kong University of Science and Technology' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Management Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Science Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Indi an Institute of Technology Delhi (IIT-D)' Or
(qryPriorityPartnerships.txtInstitutionName)='Nati onal Institute of Advanced Studies Bangalore' Or
(qryPriorityPartnerships.txtInstitutionName)='Nati onal Taiwan University NTU' Or (qryPriorityPartnerships.txtInstitutionName)='College of William and Mary' Or (qryPriorityPartnerships.txtInstitutionName)='Brow n University' Or (qryPriorityPartnerships.txtInstitutionName)='Univ ersity of South Florida')) AND Discipline='Bioscie
 
I'm using access2007, maybe I did not zip it properly, different version attached this time. I have changed to using the pkID which is a lot clearer.
is there some way to segregate/categorize the institutions with respect to those you that you will always want the report versus those you do not?
Click to expand...
What if I build a new table with pkPriorityPartnerID and fkInstitutionID? Then use strSQL = strSQL & " (tblPriorityPartners.fkInstitutionID)='70' Or".....

- That way I at least have a separate list of the institutions I need, although I am not entirely sure how to go about completing this.

Since changing to using primary keys instead of names my immediate window code is now as below, but it is still cutting off some of my second filter name, the "Discipline='Psycho" bit

Code: 
((qryPriorityPartnerships.pkInstitutionID)='54' Or (qryPriorityPartnerships.pkInstitutionID)='70' Or (qryPriorityPartnerships.pkInstitutionID)='71' Or (qryPriorityPartnerships.pkInstitutionID)='72' Or (qryPriorityPartnerships.pkInstitutionID)='74' Or (qryPriorityPartnerships.pkInstitutionID)='90' Or (qryPriorityPartnerships.pkInstitutionID)='91' Or (qryPriorityPartnerships.pkInstitutionID)='92' Or (qryPriorityPartnerships.pkInstitutionID)='93' Or (qryPriorityPartnerships.pkInstitutionID)='94' Or (qryPriorityPartnerships.pkInstitutionID)='95' Or (qryPriorityPartnerships.pkInstitutionID)='96' Or (qryPriorityPartnerships.pkInstitutionID)='97' Or (qryPriorityPartnerships.pkInstitutionID)='99' Or (qryPriorityPartnerships.pkInstitutionID)='101')) AND Discipline='Psycho
 

Attachments

If you are using numbers, they should not be enclosed in the single quotes, but if you have a table that holds all of the applicable fkInstitutionID's then SQL text will be different since you will be able to use a nested query approach.

tblPriorityPartners
-pkPriorPartnersID primary key, autonumber
-fkInstitutionID foreign key to your institution table

strSQL= "qryPriorityPartnerships.fkInstitutionID in (SELECT fkInstitutionID FROM tblPriorityPartners) AND "

In terms of the database you attached, it has an mdb extension which is for versions 2003 and earlier. If it is indeed a 2007 version, I won't be able to open it here at work since I only have Access 2003; I'll have to take a look at it tonight from home.
 
Thanks, but unfortunately now I cannot open it! I am getting "unrecognized database format"

Would you be able to just post the code? / Is there another way to do this?

Bit confused as posting db on here has worked before, I am definitely using access 2007
 
Obviously I was able to open it at home using Access 2010. I did not change the file type or the file name.

The changes I made went beyond just the code; I had to modify the query to the report as well as the combo box on your popup form. I think some of the problem is in the file name you gave to the database. Access sees .mdb as an Access 2000-2003 version, so you may want to rename the database after you unzip it & then try to open it. If you are unsuccessful, I will see what I can do on my end from home this evening.
 
Strange, looking at my backup copies along the way it changes from accdb to mdb about 3 weeks ago, but I hide file type extensions so that I cannot change them accidentally if I make any filename alterations.

I have now made another copy of my database, changed the extension to accdb, and purposely saved it as a 2007 database. Should come up as .accdb. I have zipped and attached it again, would you mind seeing if if appears any different to you? I hope this will solve any problems....

Really appreciate your help on this.
 

Attachments

I see it as an .accdb file, so it appears to be OK. Were you able to open the database once you cleaned up the filename?
 
Access is still telling me that the copy you posted is in an unrecognised database format, even though I changed the filename extension to accdb. Not sure what else to try with it?

I believe I changed it correctly - I changed the filename ext by displaying it in my Folder Options, and edited it from there.
 
Just double checked - when I download and extract a new copy that you posted it is still unzipping as an mdb, maybe still something to do with me uploading a weird / mdb copy?
 
I created a new 2007 file and imported everything into it; I hope you are able to open it.
 

Attachments

This is frustrating, I am still getting 'unrecognised database format', not sure what to try now?

I have tried opening it on another computer, just in case mine is doing something funny, but it still wont have it.

Still struggling to find a way to make this filter work..
 
I have Access 2010 at home but created the file in an Access 2007 format. I wouldn't think that would cause any issues.

I can try to create the file in an Access 2003 format and see if that works, but it will have to wait until tonight.
 
OK, here is a 2003 version. I did get some errors in going back to the earlier version. I'm not sure what exactly was the source of the errors, but at least you should be able to see what I did and translate that into your current DB
 

Attachments

Nearly there... on this last bit I have a combo box to filter records by last name, but for those people who share the same last name, my db is combining the data into one report.

I have now put 2 columns in my combo box to display last and first name, but cant quite work out how to get it to filter using the first name as well as last. Code from the Onclick event of cmdFilter is below

Code: 
Private Sub cmdFindByLastName_Click()
On Error GoTo Err_cmdFindByLastName_Click

Dim strSQL As String
Dim stDocName As String
stDocName = "View Full Staff Record"
    
    
If Nz(Me.cboLastName, "") <> "" Then
 strSQL = strSQL & "txtLastName='" & Me.cboLastName & "' AND "
End If 

If Nz(Me.cboDiscipline, "") <> "" Then
 strSQL = strSQL & "Discipline='" & Me.cboDiscipline & "' AND "
End If


'Strip Last " And "
If strSQL <> "" Then

    strSQL = Left(strSQL, (Len(strSQL) - 5))
    Debug.Print strSQL
    'Set the Filter property
    DoCmd.OpenReport stDocName, acPreview
    Reports![View Full Staff Record].Filter = strSQL
    Reports![View Full Staff Record].FilterOn = True
    RunCommand acCmdZoom75
    
blnNotOpenMenu = True
Else
    MsgBox "No criteria specified, opening the report unfiltered. Use navigation arrows at bottom of page to scroll records"
    DoCmd.OpenReport stDocName, acPreview
    RunCommand acCmdZoom75
End If
    
Exit_cmdFindByLastName_Click:
    Exit Sub

Err_cmdFindByLastName_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindByLastName_Click
    
End Sub
 
You must log in or register to reply here.

Similar threads

W
Solved From with One-To-Many Relationship table
Replies
19
Views
674
wmix
W
E
Relationships between tables in access
2
Replies
35
Views
2,920
MajP
MajP
LarryE
Solved Archiving records instead of deleting
Replies
12
Views
937
Pat Hartman
P
B
Advice Request : Simplest method to allow multi-site access to a prototype database
Replies
10
Views
626
Pat Hartman
P
C
Assessing Project Size
2
Replies
33
Views
3,301
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom