Hello,
I have a report that is supposed to show the teachers that have taught specific classes this year. I'm almost done, I just need the report to filter the data before opening.
In setting the button that opens the report to filter the data I'm being asked for input for my Fall, Spring, and Summer... which shouldn't happen. If I add a single quote to those terms, I no longer am prompted for input, but am told that...
Vaulcul
Last Activity: Apr 11 '16, 08:05 PM
Joined: Jan 17 '14
Location:
-
Report Filtering Issue
-
Thanks NeoPa.
I'll try and keep that information (which is good to know) in mind as I post code.
Sorry for any inconvenience. -
I found my final answer here: http://msdn.microsoft.com/en-us/libr...ffice.12).aspxLeave a comment:
-
Okay... So I got the statement to work for the most part...
But now I'm having an issue where I can't refer to the control as "me.[controlname]"
How would I do that with the below (fixed) SQL statement:
...Code:"SELECT RM_Room_T.Room_ID, RM_Room_T.RoomNumber, RM_Room_T.Building_ID, RM_Room_T.Not_Dept " & vbCrLf & _ "FROM RM_Room_T " & vbCrLf & _ "WHERE
Leave a comment:
-
Hi jimatqsi,
I've tried making the change that you suggested:
However, after doing that it stops on the last single quote with the error: "Expected:Expre ssion"Code:Like '* & Nz(Me.BuildingList, "") & "*'")
I keep changing out the quotes and I'm just not finding the right mix.Leave a comment:
-
Why Doesn't This SQL Statement (VBA based) Work?
Hello,
I have a form with 2 list boxes (1 for buildings and 1 for room numbers) and 2 text boxes (1 for low room numbers 1 for high room numbers). I'm using these to filter a form. For the most part, all of my code works, however I'm having a tricky time getting the VBA based SQL statements to work. I know the query itself works in Access, as I built it in the Query builder before copying it into the code and tweaking it to how... -
Seth,
Thanks for the input.
After reading what you had to say I've updated my code to the following (and feeling really good about it):
...Code:Dim strC As String ' criteria Dim ctl As Control strC = "" For Each ctl In Forms!RM_Room_Filter.Controls If Not (Len(Nz(ctl.Tag)) = 0) Then SelectLeave a comment:
-
jimatqsi,
Honestly... I should have known that I needed to wrap the text in quotes... shame on me.
Thanks for all of your patience and help on this. I think I finally have the working code for this.
Here's what I finally ended up with:
...Code:Dim strC As String ' criteria strC = "" Dim ctl As Control For Each ctl In Forms!RM_Room_Filter.ControlsLeave a comment:
-
Luk3r,
Thanks for your interest, the more heads the better. :)
jimatqsi,
I've found why I was getting the run-time errors mentioned above... I think.
So the code for setting the filter was set for this:
...Code:If strC <> "" Then strC = Mid(strC, 10) ' chop off first " and " Me.Filter = strC Me.FilterOnLeave a comment:
-
jimatqsi,
I have changed the code from (Len(Nz(ctl.Nam e)) to (Len(Nz(ctl.Tex t )) and (Len(Nz(ctl.VAL UE )) and all of them give me the run time errors that I've reported earlier.
... I'm not really sure what else to try at this point.Leave a comment:
-
Luk3r,
Thanks for your reply.
I think that .Value is a valid property in VBA...
at least I can find it on Microsoft's Textbox members page
(http://msdn.microsoft.com/en-us/libr...ffice.15).aspx)
I tried changing the code to the .Text property as you suggested. It still errors out on the same line, but with a different run-time error (2185) "You can't reference a...Leave a comment:
-
Thanks jimatqsi,
I struggled with deciding whether to use .Name or .Value...
However, switching over to .Value moves the error (same error) from line 21 to line 18.
I thought that maybe this might be a corruption issue in my database, so I created a new database, reconnected all of the pertinent tables, copied the appropriate SQL statements and then copy/pasted the forms. After doing so and running the filter...Leave a comment:
-
Thank you zmbd and jimatqsi for your help.
I think I'm close, but I'm having some problems.
I've been struggling with getting the code not to error out and I'm baffled at the error.
I'm getting a 'Runtime 2447 error "Invalid use of . (dot) or ! operator or invalid use of parentheses' (Marked error area w/ underline)
...Code:Dim strC As String ' criteria strC = ""Leave a comment:
-
jimatqsi,
Wow! That's awesome code, I had no idea I could do that (still teaching my brain to think like a programmer). I made very few edits to what you provided.
The code I ended up with was:
...Code:Dim strC As String ' criteria strC = "" Dim ctl As Control For Each ctl In Forms!RM_Room_Filter.Controls If ctl.ControlType = acListBox ThenLeave a comment:
-
Alternative to 500+ lines of If, Then, Else... To Filter Form
Hello,
I'm struggling w/ trying to get a form to filter another form through code. I know the code works on a smaller scale... but have a suspicion that there is a better more efficient way of writing it. In my current form I get the "Procedure too large" error for the filtering code (There are over 500 lines of If, ElseIf statements).
Currently, the filtering form has 9 control fields to use to filter another... -
My apologies NeoPa... I struggle with providing full/good information. Thus I don't post often to help forums, as it's usually frustrating to everyone :(
Not trying to make other people do the ground work, just not very good at asking for help.Leave a comment:
-
Thanks for the quick response zmbd and sorry for being vague. I'll try and clarify.
I'm trying to make it so that as a user tabs through one of the datasheet subforms, when they get to the end of a record contained in the datasheet it will jump to the appropriate subform and record in the datasheet in that subform.
In looking at the link I provided in my first post, I was able to figure out how to set focus to cells in...Last edited by NeoPa; Mar 28 '14, 04:22 AM. Reason: Fixed [CODE] tag position to include first line.Leave a comment:
-
Tabbing Through Multiple Datasheet Subforms
Hello,
Currently, I have a main unbound form with 5 subforms, all of which are in datasheet view. I need to be able to navigate between the subforms as if they were one big datasheet.
I found this article, and it kind of helps... but isn't exactly what I'm looking for either:
http://www.techrepublic.com/article/...-and-subforms/
Does anyone know... -
Ah... I see what I was doing wrong.
Thanks Rabbit.Leave a comment:
-
InStrRev Not Giving Correct Results
Hello,
I have an Access database where I'm importing book/journal publication data from JabRef in a CSV format.
When I import the data to Access one of the odd things that happens is that the page numbers are given two hyphens in between them, so the data in the "pages" column in Access would look something like "200--213"
I need to be able to count the number of pages that are referenced....
No activity results to display
Leave a comment: