General :: Subform Query Filter Do Nothing If Not Exact Match
May 28, 2014
there are certain txt boxes that once filled out, will filter a subform of a table of 1000's of records to give them a number to use on the form around 200 of the records have a depot in the "depot" field on the table, when they input a depot, it filters to them 200 fine if a depot is there,
what i want is, when they input the depot, if its there, filter it, if its not, to do nothing, as they could still get a unique number if the depot isnt in the list what happens now is, if the depot isnt in the list, it displays no records
Field : Fld_Depot
Table : Tbl_Agreement_Summary
Show : False
Criteria : Like "*" & SearchForText([Forms]![Frm_New_Accounts]![Fld_Depot]) & "*"
Or : Is Null
View Replies
ADVERTISEMENT
Oct 8, 2007
Hello,
i'm not sure how to write a query, maybe it'll take VBA function to accomplish this, but I need to display the results for all invoices for an item.
Little background:
1. This set of invoices are of only one particular product line as it's this product line that needs this special handling. These are Guitar orders.
2. Each guitar item can have option items on the same invoice. So basically these are all considered item numbers.
3. The guitar starts as the bare / basic guitar. The customer can choose to select one or many options for a custom guitar.
4. These orders will need to have special guitar programming codes that need to be entered into their guitar programming machines. Call these "Codes".
5. Depending on the option items for the various guitar items, the Codes could vary.
6. Need to be able to display ONLY the Codes specific to the guitar or guitar and option combinations.
I have several tables:
1. Guitar for all the guitar items
2. Options for all the option items
3. ProgrammingCodes for all the programming Codes
4. ProgramCodes for creating the above combinations with a field named ComboID.
5. GuitarHeader - the invoice number and guitar item
6. GuitarDetails - the invoice number and option item (invoice number to invoicenumber link to the GuitarHeader)
Then there's a main form and a sub-form for the end-user (guitar programmer).
1. The main form is the Guitar items
2. The sub-form is the ProgramCodes fields
this form is used for this end-user select the Guitar and options (where necessary) and select the Codes and enter the ComboID for all.
for instance:
These are all the possible codes for AE185.
AND depending on the Options selected for AE185 on invoices, the Codes will differ.
The ComboID is the differentiator.
Code:GuitarOptionCodeComboIDAE185 185RR 1AE185 186RHT1AE185 187RT 1AE18538185RR2AE18538186RHT2AE18538187RT382AE185BB185RR3AE185BB186RHT3AE185BB188RT-B3AE18538185RR4AE185BB185RR4AE18538186RHT4AE185BB186RHT4AE18538188RT38B 4AE185BB188RT38B 4
so for the Codes that repeats in the ComboID is only because the Option(s) also requires that Code as well as the other Codes.
for instance, if the invoice ONLY has Option 38 from the Options defined for Codes, then the result would be:
Code:AE18538185RR2AE18538186RHT2AE18538187RT382AE185BB185RR3AE185BB186RHT3AE185BB188RT-B3AE18538185RR4AE185BB185RR4AE18538186RHT4AE185BB186RHT4AE18538188RT38B4AE185BB188RT38B4
Code:Invoice Guitar Codes123456 AE185 185RR 186RHT 187RT38 <-- say this invoice had Option 38 that was the only match234567 AE185 185RR 186RHT 188RT-B <-- because BB was the only one that matches345678 AE185 185RR 186RHT 188RT38B <-- because both 38 & BB matches
So guess the question is how do I write a query or function (VBA) to be used in a query to get the results for ONLY the matching options so the correct codes for just these options are displaying?
It's difficult to me in figuring that out. how to make sure that the result does not show the ALL the codes that match but ONLY the codes in the combinations. Again, hence the reason why I got to the point of having Combination IDs to differentiate that.
hope I made sense here. Not sure how else to ask the question other then to try and provide examples
let me know how else to explain if this is not helping.
View 5 Replies
View Related
Jun 17, 2005
How can I get an exact match in a search. If I search for say C1 I get results showing everything with C1 i.e C1, C11, C12, C13 etc. I just want to show C1's
This shows my query at present.
Like [Forms]![Search]![BoxNo] & "*"
View 4 Replies
View Related
Apr 3, 2008
Hi all
I'm using an array with the Instr function. The array (or the Instr function) doesn't seem to be case sensitive... which is a pain because its picking words within words where the case doesn't match!! I'd like the vales in the array to match exactly when using the Instr function
Any idea how I can get around this??
Thanks
Damon
View 3 Replies
View Related
May 31, 2007
Hi,
Does anyone know how to perform an exact match using file search. The code I have below searches for the users AcroRd32.exe file (I have done it this way to allow for different versions of the reader). The problem is that foundfiles returns also AcroRd32info.exe ie FoundFiles(1). i only want to search for the prior!
Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "C:Program FilesAdobe"
.SearchSubFolders = True
.FileName = "AcroRd32.exe"
If .Execute > 0 Then 'check if there is such a file
stAppName = .FoundFiles(1) 'then define this as the applicate string name
stAppName = stAppName & " " 'append a space or file string not correct
'specify path name for location of file.pdf
stlocation = GetPrivateProfileString32("C:WINDOWSSSI_PROGRS_DATABASE.ini", "DIR", "DATABASE_FILELOCATION")
stpathname = stlocation & "file.pdf"
Shell stAppName & stpathname, vbMaximizedFocus
Else
'if no file is found then need to instruct user to install reader
MsgBox "You need to install Adobe Acrobat Reader to open this file", vbExclamation
End If
End With
Cheers to any help..
Tania
View 2 Replies
View Related
Oct 12, 2013
How to find an exact match in a Access DB table using Sql Query in VB6 ?I know that "Like" keyword will give out all those rows which contain the search-for-string. But I want exact match.
View 4 Replies
View Related
Sep 3, 2005
Background:
I have a database that keeps track of containers -what they belong to, and what is in them. Each container has a “Call #” which is labeled on the container and is a primary key in the database. The “Call #” field is set to only allow 5 characters in this field. A standard “Call #” for example is "A 152".
When a new container comes into the system, which has not been physically labeled yet, I enter it in the database a little different. So let us say container "A 152" just came in, and has not been physically labeled yet. I enter the “Call #” into the system as "MA152". The "M" tell me it still needs to be physically labeled.
When I go to label all the unlabeled containers, I print a report with all the "Call #''s that start with "M".
I have another field in the same table called "Mark". In my forms I can then go through and click a "Mark Button". The Report prints all containers where the "Mark" field = yes(True). This feature is used for a number of different reports, not just unlabeled containers. To keep the database clean and in order, all “Mark” fields reset to no(false) whenever it opens up.
Because, I used the “Mark” field for other reports, simply clicking the “Mark Button” when I enter the new container into the system will not work.
My Question:
Is There a way to have a Query search for the first letter in a field. So when I go to print my report, a query runs looking for all "Call #s" that start with the letter “M”.
View 2 Replies
View Related
Sep 28, 2012
I need to filter a subform based on the values on the mainform.
Criteria are Multiple. The idea is to show the user the records that are already existing in the table.
First two criteria works, stuck with the third one
I.Criteria - Creative(text type)
Private Sub Creative_AfterUpdate()
Me.frmCommercialWithDetails_subform.Form.Filter = _
"Creative ='" & Creative & "'"
Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub
[Code] ....
Also, how to split the code into two lines
"Creative ='" & Creative & "' And Act_ID = " & Act_ID
when broken into:
"Creative ='" & Creative & "' _
And Act_ID = " & Act_ID
is automatically saved as:
"Creative ='" & Creative & "'" _
And Act_ID = " & Act_ID " and this generates an error on execution.
View 5 Replies
View Related
Mar 19, 2014
I am self thought and fairly new to access (quite fun )
It is a simple setup: I have a main form called "customer_information_display_form" within that form is a sub-form called "customer_information_form" in addition there is one combo box drop down menu
I would like to filter the information presented in the sub-form by company name located in the drop down menu.
View 3 Replies
View Related
May 3, 2014
I have a text box ( Supplier_Name) on the main form i would like to filter subform by any part of entered charactor on the feild (suplier_Name)
Code:
Private Sub Find_Click()
If Not IsNull(Supplier_Name) Then
Me.Suppliers_Details.Form.Filter = "[Supplier_Name] = '" & Me.Supplier_Name & "'"
.FilterOn = True
Exit Sub
End If
End Sub
this code is work fine but i have to enter all the characters of long name , but i would like to only insert few chars.
View 2 Replies
View Related
Aug 30, 2006
I have a search box and it looks up project id number.
the orginal code works but filters anything with that particular number.
I like to adjust the filter to equal to the number enter.
ex,, I enter 22 , i get records with 622, 223, etc
I want it to be only 22
I can't seem to figure this out.. here's te code
Private Sub txtSearchString_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
LSearchString = txtSearchString
'Filter results based on search string
LSQL = "select * from [Projects - Active]"
LSQL = LSQL & " where ID LIKE '*" & LSearchString & "*'"
Form_frmSearch_sub.RecordSource = LSQL
lblTitle.Caption = "Project ID: Filtered by '" & LSearchString & "'"
'Clear search string
txtSearchString = ""
End Sub
View 6 Replies
View Related
Sep 13, 2014
I've attached a rudimentary example db to give an example of what I'm trying to achieve.
There is a form on the db called Resourcing with several combos.
When I choose a Training_Type from the first combo it automatically filters the Project_Title combo to show only the projects that match that training type. When I choose a Trainer_Name the text box for team automatically completes.
The bit I'm struggling with is Trainer_Name. As you will see from the table Course_Details not every trainer can deliver every Project_Title. So what I want is to display only the trainers associated with the project title chosen rather than displaying the whole team in the Trainer_Name combo.
Someone suggested a junction table but I'm not sure how this would answer my question. I'm thinking some sort of Dlookup but don't know how I would write it when it has to include data from four fields.
View 14 Replies
View Related
Nov 30, 2013
It's been a while since I last used Access but now I need to be reminded how to populate a sub-form with all rows for a query (Access 2013).
- Do I need to establish a relationship between Table A (main form) and Table B (sub-form)?
- What sub-form properties need to be set to display all retrieived rows from the main form query (select where Table A PK = Table B PK)?
View 1 Replies
View Related
Jul 25, 2005
Hello All,
Thank you for taking time to view my access problem, I hope somebody can help / give advice on my problem.
I have a Form, within that form I have a subform. On the main Form I have several Combo Boxes.
ComboBoxes include; Year. Movie. Country.
What I want to do is when the user selects from the various values within each Combo Box to display the data in the subform accordingly.
I was able to use the Wizard to display the Movie Information correctly. The code is below for that. How can I go by doing this.
' Code to change the movie information, when they select the combo box
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & Str(Nz(Me![cmbMovieList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
I am very sorry if my question seems broad, ill try to be more specific upon request. THANK YOU for your time and your help.
View 3 Replies
View Related
Mar 14, 2006
I have a form which is linked to a query. The main form which calls this form sets the filter for the query. This works fine and I can navigate all the filtered records.
The problem is when I go to add a new record the filter information goes away.
For backround this is DB for project information. The user clicks a button to view notes on the project. The notes are stored in a different table and the project_id is the common field which links the two together. I want this to function where the user can open the subform read the notes and add a new one if needed.
View 1 Replies
View Related
Apr 10, 2014
So I have a Form with 3 fields. 2 Date fields (Beginning & Ending) as well as a combo box (CName). There is a button (Filter).I want to fill in both dates and select from the dropdown box. Onceeach field has a value then it will send a msgbox saying there were "# of records" between the 2 dates by CName
View 14 Replies
View Related
Oct 16, 2013
I have got a combo box with a product code in it, and depending on which code is selected I want the data to come from one of two different tables. Is there any way I can do something like a match formula in Excel to see if the stock code is contained in a table?
View 3 Replies
View Related
Apr 24, 2014
I have a form that shows multiple rows of linked/child records.The form in question is the "frmFilterNumberTypeView" form. When a particular filter is used (installed on an automobile), I would like to be able to click on the corresponding "Installed 1" button left of the filter number, so that by code, I can reduce the Qty on Hand by One, and insert the corresponding filter ID to the History table, along with the date/time the filter was used.
how to reduce the Qty on Hand, nor how to do the updates to the History table, I'll figure that out myself over the next week or two (hopefully it won't take that long). What I'm trying to figure out here is how I can associate the red "Installed 1" button with the filter to the right of whichever button I press.
how to sort the filter column on this same form. I'm fairly certain that this would be very easy to do if the subform in question was populated by means of a corresponding query, but I'm afraid that if I go that route that I'll spend another 20+ hours trying to figure out how to get the proper filter records to match the filter manufacturer, not to mention having to next figure out how to link the table so that if I modify any particular filter record, that I'll be able to have the associated table update accordingly.
Is it possible to sort the filter column without the subform record source being a query?
View 2 Replies
View Related
Jun 15, 2014
There are 2 tables:
Table 1 has National Insurance Number, first name, last name, phone no, address.
Table 2: has National Insurance Number and email address.
Table 1 is the master table where it will link to table 2 containing the email addresses of the individuals.
However, there will be a lot of email addresses in table 2 which do not relate to any record in table 1 because I do not have the individual in my database yet.
BUT I want to keep their email address because in the future this person may enlist in my imaginary business and therefore - if they do I would have their email address!
1. Is this possible in Access?
In Excel, it would be the case of a simple vlookup from the email address field into another sheet containing the National Insurance Number and email address.
2. Would it be possible to establish a one-one relationship while enforcing referential integrity?
3. Is there a way I can establish a lookup which can be built into table 1 which can lookup the email address of a person in table 2, matching on National Insurance Numbers?
So in the future if new data is input into table 1 or 2 which results in a match of National Insurance ID numbers, the outcome would mean an email address is now paired to the corresponding individual it belongs to.
View 10 Replies
View Related
Mar 19, 2015
I've set the column widths properly on the subform, but the down arrow on the list box is still outside of the selection area. It may have something to do with lookups or concatenations, but I'm not finding that so in what I can think of to trace down.
I exaggerated the width of the list box to show where the down arrow actually shows itself.
See the form "frm_Class_Skills_Update" in the attached database. I need it to be visible when the list box is about 1.25" more narrow.
View 2 Replies
View Related
Jun 12, 2015
I am using access 2010. I have "classlevel" table with 2 columns-Class and Value1 .Value1 column has numeric values that i ll input from webpage (webpage to ms access connectivity).
I want to sum the values of column "Value1" and i have another table-"Volume" which has 2 columns "VolumeLevel" and "Value2". So i want to match the sum that i calculated from first table-"ClassLevel" with the "value2" column in "Volume" table and get the corresponding "volumelevel" column value from that table and there is a third table that will get this volumelevel value.
There is no common column to join these tables.
Classlevel-
Class Value1
Class 0 3000
Class 1 2000
Class 2 300
Class 3 400
Class 4 500
Volume-
Volumnlevel Value2
Vo 0
V1 5000
V2 10000
V3 30000
....upto V10
Answer that i am looking for is V1 as sum=6200 lies between 5000 and 10000 so its V1.
Table 3-
Volume-
V1
View 14 Replies
View Related
Dec 19, 2013
I am trying to automate a data match I've been doing in Excel into Access.
Basically, I have 2 spreadsheets:
1. First has a "Start Date" for each user
2. Second spreadhsheet is a lookup table with 2 fields: Week Start Date and Week #. In other words, Dec 30 - Week 1, Jan 6 - Week 2, Jan 13-Week3, etc
For a given date in the first spreadsheet (e.g. Jan 10th), I am trying to figure out which week this falls into.
In Excel I use this formula where Column A stores the week start dates in the lookup table spreadhseet and Column B stores the Week#, with F4 being the "Start Date" in my first spreadsheet.
=INDEX('Enrollment Week'!$B$1:$B$53,MATCH(F4,'Enrollment Week'!$A$1:$A$53,1))
Is there a way to do this in Access?
View 5 Replies
View Related
Oct 20, 2013
I've got an access form, and all I want to do, is to be able to type a number into a text box, click a button and then for the record containing that number to be displayed (in that same form).
-And I dunno where to start.
I don't like using the navigation bar, although that is almost what I'm after.Also, search functions I've seen on here that use combo box lists to display results seem like too much for me, as each number typed into my search box will be unique.
View 4 Replies
View Related
Mar 12, 2013
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b
I would like my query to now display a third field and group field :
field1 field3
apple both
banana a
carrot both
dog b
elephant b
View 1 Replies
View Related
Dec 26, 2012
I would likw for my first and last name fields width to match the length of the name. So if the last name is Smith then the field width will resize itself to fit perfect fit the name. Then if I have a last name of Connor then it will grow to fit it perfect. I dont need this but it will be nice so everything looks a bit nicer an cleaner. I thought the following would have worked:
Code : Me.Lastname.width = Len(Me.Lastname]
View 1 Replies
View Related
May 10, 2014
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
View 3 Replies
View Related
