 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / June 2007Multiple If statements in the criteria of a query
I'm trying to instert the following in the criteria section of a query in query builder; the itself is the record source for a list box ******************************************************************************************************* If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation" Then Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _ "WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]" Else: If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Device" Then Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _ "WHERE fldRNnotesCode = 'D'" & "ORDER BY [fld.order] " End If End If **************************************************************************************************** I started this by placing it in the onOpen event of the form, but then I realized I don't want to hard code this in the form. I'd rather to just be able to do it within a query. Then if I add new VisitTypes I can simply modify the query without touching the code in the form. I know my criteria statement is wrong, therefore your help is greatly appreciated. Rob First of all, your existing code is incorrect: you're missing a space between the condition, and the ORDER BY clause. If those are the only two options, try creating the following query, and using it for as the RowSource of your listbox: SELECT * FROM tblRNnotesLU WHERE fldRNnotesCode = Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1) ORDER BY [fld.order]  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > I'm trying to instert the following in the criteria section of a query in > query builder; the itself is the record source for a list box [quoted text clipped - 27 lines] > I know my criteria statement is wrong, therefore your help is greatly > appreciated. Rob My Access 2002 SP3 will not let me create a field named fld.order so as to sort on. SignatureKARL DEWEY Build a little - Test a little > First of all, your existing code is incorrect: you're missing a space > between the condition, and the ORDER BY clause. [quoted text clipped - 38 lines] > > I know my criteria statement is wrong, therefore your help is greatly > > appreciated. Rob Thanks Doug, actually there are 5 different visit types and each type can have more than one code, i.e., Ablation = A and E. I only wrote re: the 2 to make things easier. Once I have the syntax down for one type I can do the others. I'd rather do this in a query as there maybe future visit types. Then I can alter the query and not the code. So back to my original post, can I place the criteria, based on frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType in the criteria of the field from my initial query that populates the list? Hope this makes sense. Thanks, Rob >First of all, your existing code is incorrect: you're missing a space >between the condition, and the ORDER BY clause. [quoted text clipped - 12 lines] >> I know my criteria statement is wrong, therefore your help is greatly >> appreciated. Rob Ok Doug, here's my latest try. This goes in the criteria section in my query qryRNnotesLU.fldRNnotesCode. Of course I get syntax errors. Could you please help if you have a chance. Thanks, Rob If (Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation") Then WHERE "fldRNnotesCode = 'A' AND 'E'" >First of all, your existing code is incorrect: you're missing a space >between the condition, and the ORDER BY clause. [quoted text clipped - 12 lines] >> I know my criteria statement is wrong, therefore your help is greatly >> appreciated. Rob I'm not sure what you mean by "in the criteria section in my query". Are you saying you've typed that into the criteria cell in the graphical query builder? It's not a valid If statement (there's no End If), but even if it were, you can't put VBA into a query like that. Additionally "fldRNnotesCode = 'A' AND 'E'" is invalid syntax (it would likely need to be "fldRNnotesCode = 'A' AND fldRNnotesCode = 'E'", but that won't return anything, since it's not possible for there to be a row which has two different values for the same field.) How about explaining in words what you're trying to do? As you saw, your first attempt to describe was a little to abbreviated, since what I posted in response to the literal question apparently wasn't sufficient to meet all your needs. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Ok Doug, here's my latest try. This goes in the criteria section in my > query [quoted text clipped - 24 lines] >>> I know my criteria statement is wrong, therefore your help is greatly >>> appreciated. Rob >I'm not sure what you mean by "in the criteria section in my query". > >Are you saying you've typed that into the criteria cell in the graphical >query builder? Yes! It's not a valid If statement (there's no End If), but even >if it were, you can't put VBA into a query like that. Additionally >"fldRNnotesCode = 'A' AND 'E'" is invalid syntax (it would likely need to be >"fldRNnotesCode = 'A' AND fldRNnotesCode = 'E'", but that won't return >anything, since it's not possible for there to be a row which has two >different values for the same field.) Here's the sql statement as it was before I tried to insert the If statement in the criteria cell of fldRNnotesCode: SELECT tblRNnotesLookUp.fldRNnotesLUno, tblRNnotesLookUp.fldNote, tblRNnotesLookUp.fldRNnotesCode, tblRNnotesLookUp.fldOrder FROM tblRNnotesLookUp WHERE (((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp. fldRNnotesCode)="E")) ORDER BY tblRNnotesLookUp.fldOrder; What I want is the query output to modified based on the value in the fldVisitType. If the valueof fldVisitType =1, then the criteria for the for fldRNnotesCode will be (((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.fldRNnotesCode)="E")) Hope that clarifies. Thanks for your help. >How about explaining in words what you're trying to do? As you saw, your >first attempt to describe was a little to abbreviated, since what I posted [quoted text clipped - 6 lines] >>>> I know my criteria statement is wrong, therefore your help is greatly >>>> appreciated. Rob Since you need to map from one visit type value in the form field to multiple possible notes codes in the table you're querying, you'd likely be best off creating a table that maps between the two. (If it's a many-to-many relationhips between them, you'll need to introduce an intersection table to resolve that). You can then join that mapping table to your existing table, and then simply compare to the value on your form. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >>I'm not sure what you mean by "in the criteria section in my query". >> [quoted text clipped - 39 lines] >>>>> I know my criteria statement is wrong, therefore your help is greatly >>>>> appreciated. Rob Ok Doug, could you take on more crack at it. This is what I have in the criteria cell now. If Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] = "Ablation" WHERE=(((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp. fldRNnotesCode)="E")) ORDER BY tblRNnotesLookUp.fldOrder; I get a syntax error. I don't know how to do the transition between the If Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] = "Ablation" and the Where statement .Could you pls help. The previous query I posted using multiple criteria works, that's why I believe it will work if I can just pluck the visit type of the form. Thanks, Rob >Since you need to map from one visit type value in the form field to >multiple possible notes codes in the table you're querying, you'd likely be [quoted text clipped - 8 lines] >>>>>> I know my criteria statement is wrong, therefore your help is greatly >>>>>> appreciated. Rob I don't understand why you're so insistent for me to help you if you're going to ignore my advice! As I wrote earlier, "you can't put VBA into a query like that" Switch to SQL view: you'll see you've created a nightmare! I don't see why you think it'll be any easier to maintain the hard coding in a query than it is in a form. The correct approach is to maintain the mappings in a table. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Ok Doug, could you take on more crack at it. This is what I have in the > criteria cell now. [quoted text clipped - 33 lines] >>>>>>> greatly >>>>>>> appreciated. Rob I'm sorry I have upset you. Not everybody is as keen as you are. Thanks for your help. >I don't understand why you're so insistent for me to help you if you're >going to ignore my advice! [quoted text clipped - 12 lines] >>>>>>>> greatly >>>>>>>> appreciated. Rob Try this -- SELECT tblRNnotesLU.* FROM tblRNnotesLU WHERE (((tblRNnotesLU.fldRNnotesCode)=[Enter Code])) ORDER BY tblRNnotesLU.Order; SignatureKARL DEWEY Build a little - Test a little > I'm trying to instert the following in the criteria section of a query in > query builder; the itself is the record source for a list box [quoted text clipped - 23 lines] > I know my criteria statement is wrong, therefore your help is greatly > appreciated. Rob Is this to go in the criteria of the underlying query. if so, it doesn't work. I get "subquery can't return multiple records." Again, I need to use If Then statements in a query's criteria. thank for your help and I look forward to anymore suggestions. Thanks, rob >Try this -- >SELECT tblRNnotesLU.* [quoted text clipped - 7 lines] >> I know my criteria statement is wrong, therefore your help is greatly >> appreciated. Rob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.