Queries :: String (via Non-visible Textbox) From Search Form As Criteria Using Checkboxes

Mar 11, 2014

I have an unbound form (named frmReportSearch) with unbound text & combo boxes providing the criteria for a query (named qSeqStreets). The form / query utilize 4 optional fields as search criteria plus date from / to. The results are returned via a report (named rptSeqStreets). The whole operation worked perfectly, however I realized I needed to change one of the criterion to a multivalued field. The change in the table (named Tasks) worked perfectly. I used three checkboxes (named chkA, chkB and chkC) to allow the user to select any combination of the 3 choices, including none (to be treated as no filter on [fldShifts]).

The three options in the field (named fldShifts) are "A" "B" and "C". I am able to manually run the query from design view by typing in the criteria "A" Or "B"... "A" Or "B" Or "C"... and any combination of the three options in the criteria box and running the query. I am using the following code under the OK button's OnClick. The Code below has other items related to all the options .... I didn't want to give partial code so you may understand better:

Code:
Private Sub btnOK_Click()
Dim strShift As String
Dim strA As String
Dim strB As String
Dim strC As String

[Code] .....

My problem is that the query criteria needs to be entered into the criteria box with quotes and separated by "Or" depending on if multiple checkboxes are selected.

I can get the results to show correctly in the textbox, however I imagine the query is adding an extra set of ""s to the string so rather than "A" Or "B" .. it is getting ""A" Or "B"". My query Sql and even design mode are pretty complex, so I wouldn't know how to use the sql in VBA without blowing some fuses.

View Replies


ADVERTISEMENT

Queries :: 2 Checkboxes In A Form - Criteria To Be 00 Or 01 When Cost Checked

Nov 10, 2014

So I have 2 checkboxs in a form. Future cost and LRM cost.

When future cost is checked, I want the criteria in my make table query to be 00, when LRM cost checked, I want the criteria to be 01.

But the problem is when they are both checked I can't get 00 or 01 at the same time.

I have tried different ways but none of them seem to work because with IFF, can only return one value.

I have tried to put the 2 iff in criteria and the or criteria below it exceeds the max time.

The criteria I have right now is:

IIf([Forms]![TPTC Analyzer]![P Future cost]=True And [forms]![TPTC Analyzer]![P LRM cost]=False,"00",
IIf([Forms]![TPTC Analyzer]![P Future cost]=False And [forms]![TPTC Analyzer]![P LRM cost]=True,"01"))

View 14 Replies View Related

Forms :: Multiple Checkboxes In A Form To Build A String?

Mar 18, 2015

From a dropdown field in the form it's currently possible to choose a geographical region for which to generate a report. The data populating this dropdown is pulled in from a Value List as follows:

" ";"*";1;2;3;4;5;6;7;8;9;10;11;12;13;EU;WD

I now need the ability to choose various different regions simultaneously which is not possible with the current method. I've looked into a nested continuous form and a multi-select combo box or list box, but none of these are as user friendly as my preferred method.

What I would like is 15 checkboxes plus a 16th to select/unselect all. When any of these checkboxes is checked, I need to create something like a dynamic value string or temporary table to hold the list of chosen regions until the generate button is clicked at which point the data is used to generate the report and cleared. I also need a piece of code to check/uncheck all the boxes.

View 3 Replies View Related

Queries :: Insert Text From Textbox On A Form When Combo Box Meets Criteria?

Jul 12, 2013

Is it possible to insert text from a textbox on a form (Data) when a combobox on (Data) meets a criteria?

Example: Test: IIf([Results]="Positive" text207)

So if the Results combobox is Positive then the text from Text207 is inserted.

View 9 Replies View Related

Queries :: Keyword Search - Find Field Names Of True Checkboxes

Jul 10, 2013

I've recently finished constructing a database that uses forms with checkboxes i.e. when a user opens a form there are several checkboxes associated with labels, but the table only stores the check-boxes and the form labels are the field (column) names on the table.

Now, what I'm trying to do is create a search feature whereby if a keyword search is conducted, a search through the column names from the table with checkboxes takes place and only lines (rows) with true (checked) checkboxes are returned.

View 12 Replies View Related

Forms :: Making Textbox Visible On A Form If Checkbox Is Checked On Same Form

Aug 30, 2013

I am having a problem with making a textbox visible on a form if a checkbox is checked on the same form. I have done research on this site and have written the code below, but I am getting a Syntax error, see yellow highlight.

- My checkbox is named: Case is a Readmission from WRCA IP to WRCA IP
- My textbox is named: Date of Current IP Admission (I have defaulted this textbox to not visible).

Here's the code I have written:

Private Sub Case_is_a_Readmission_from_WRCA_IP_to_WRCA_IP_Clic k()
If Case is a Readmission from WRCA IP to WRCA IP =true then
Date of Current IP Admission.visible=true
Else
Date of Current IP Admission.visible=false

[Code] .....

View 14 Replies View Related

Visible Image If 2 Checkboxes Are True

Mar 14, 2005

Hi,

I have three checkboxes on my form and I would like to set the following properties:

I have an MP3, a WMA and Logged field.
If logged = yes and either wma or mpr = yes, then I want a play button to be visible.

I have tried using the following code:

Private Sub Form_Current()
If Me.checkboxname = True Then
Me.commandbutton.Visible = True
Else
Me. commandbutton.Visible = False
End If
End Sub

But this will only allow for 1 checkbox. Can anyone help?

View 4 Replies View Related

Queries :: Query Will Not Read String From (dummy) Textbox

Aug 25, 2013

I have a two-column list box where the user selects multiple Test Names and Test Measures. Through VBA, I loop through and create a string of the selected items and store into two seperate variables, one for each column. I concatenate with the "In" and some parenthesis to end up with the following:

In(ELA,MEAP,Star Math)
In(DRA, Math, PercentileRank)

I place each of the In statements into two seperate dummy text boxes on the form. Then I point the query criteria to these text boxes.

When I run the query, I get nothing. However, if I copy and paste the In statements above from the text boxes directly into the query criteria, I get the desired results.

I changed the code to create an "Or" statement (e.g. "ELA" OR "MEAP" OR "Star Math"), but still the same issue.

why the query will not read from the text boxes on the Form?

View 4 Replies View Related

Search Through All Access Queries For Text In SQL String

Feb 2, 2012

I need to find a way to search through all of the queries in my Access 2007 database to change the names of more than a few variables. There are close to 900 queries I need to search through many of which may use the variables as criteria, so if I miss even 1 instance I might screw myself completely.

I found this post and it tells me exactly what I need to do. Only problem is the post was written in 2002, and I've followed the below instructions only to have it not work.

As a contractor working predominantly on databases I haven't developed, it can be a daunting task trying to find references to tables, fields or functions within a list of several hundred queries.

The following code is attached to a command button on a simple form listing all queries in the database. The record source for the form is

SELECT DateCreate, Name FROM MSysobjects WHERE Type=5 ORDER BY DateCreate DESC

There is a text box where the user enters the text to search for. The code builds a table containing the SQL string of every query, then searches for the required text, and displays those that match.

Your form also needs a procedure the set the record source back to the default.

There are off-the-shelf applications such as Speed Ferret which perform this sort of function, however some employers are too cheap to purchase them!

Code:
sub cmdFilter_Click()
Dim db As Database
Dim rs As Recordset
Dim rsFilter As Recordset
Dim tdf As TableDef
Dim strSQL As String
Dim strQdf As String

On Error GoTo ErrorHandler

[Code] ....

View 11 Replies View Related

Queries :: Criteria Using DLookup String From Another Txtbox?

Aug 19, 2015

I have a table tblDateGroups, with two field, [Group] (like This Week, Next Week, Last Month, etc) and [Code] that holds the code for criteria for each group in a query. I know the code is fine (like next week = Between Date()-Weekday(Date())+8 and Date()-Weekday(Date())+14 ) because I tested before putting in the table.

On my form I have a combo box that list all the [Group] and i hidden textbox that looks up the code based on the cbx after update...all that is good.

However, when i try to requery the report the criteria is not working. I am simply using the date criteria to read the hidden text box with the code string.

Forms!frmMainHome!subaWelcome.Form!txtCodeForQuery .

the error code is "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

View 2 Replies View Related

Queries :: Using Textbox Value As Query Criteria

Apr 10, 2013

I am trying to use a textbox value as a query criteria using the code [Forms]![Home]![mtdDateBox], following the standard structure of referencing a form item. However, I am not returning any records, when it should be giving some. The data in the textbox follows the date format "DD.MM.YYYY" but is used as a string.

Is there something I am missing out? I am using MS Access 2010

View 1 Replies View Related

Queries :: Textbox On Subform As Query Criteria

Jun 3, 2013

I'm trying to build a query to use as a filter in the DoCmd.OpenForm function.

I have done this several times before, referencing values from forms e.g.

Point To
Field: LeadID
Table: Lead
Criteria: [Forms]![GridDisplay1]![LeadID]

This filter is used in the procedure:

Code : DoCmd.OpenForm "LeadDetails", acNormal, Point_To", , , acDialog, """"

And it works great, I click a button next to the record I want to select (records displayed on continuous form) and it opens the Lead Details form on that particular record.

However now I am trying to accomplish the same thing, except instead of a continuous form I need to draw from a continuous sub-form. The form "BrokerMgmt" contains a sub-form named "BrokerSearch." The RecordSource for the sub-form is set once the user enters data into a few textboxes on the main form and clicks the search button:

Code:
Private Sub brkSearch_Click()
Dim argCount As Integer
On Error GoTo Err_Handler
If IsNull(brkFirstName.Value) And IsNull(brkLastName.Value) And IsNull(brkCompany.Value) Then
MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"

[Code] ....

The sub-form then displays basic info such as Name, Company, State, and the "BrokerID" which is the primary key of the "Broker" table. This "BrokerID" is displayed in "Textbox 20" so I set a button next to each result to on_click perform the procedure:

Code:
DoCmd.OpenForm "BrokerDetails", acNormal, "Point_To_Broker2", , , acDialog, """"
"Point_To_Broker2" is set up in the same style as "Point_To":
Field: BrokerID
Table: Broker
Criteria: [Forms]![BrokerSearch]![Text20]

However instead of the BrokerDetails form opening to the appropriate record I get a msg box asking for a parameter. Why is this? Even if I enter the correct BrokerID as the parameter it still gives me an error saying you can't go to the specified record.

View 11 Replies View Related

Queries :: Textbox In Report As Query Criteria

Jun 3, 2014

I have a simple report which i open from a navigation form, i have a textbox in the report i wish to use as criteria, on start the report will be blank until i enter a name into the textbox and requery. However im not sure how to link this and how to stop access asking for this information before the report is opened?

View 5 Replies View Related

Queries :: Enable Or Disable Criteria In A Query Based On Value Of Textbox?

Apr 20, 2015

Is there a way in access, through vba or any other means, which would allow me to either enable or disable criteria in a query based on the value of textbox??

Like i have a query that displays bookings customer has made between two dates, so i made a query and in the starting and ending date fields i get the value from form. In case if the use does not enter ending date, i would like to run the query with starting date only that displays booking made after the starting date.

Currently the starting date criteria is set to a textbox of form, and so is ending date. So if customer selects 1st April 2015 as starting date and 30th April 2015 as ending date the query should display the bookings between 1st and 30th of April. And if the user enters only 1st April in starting date it should display bookings starting from 1st April onward.

View 3 Replies View Related

Forms :: Checkbox Be Visible Only If A Textbox Has Text In It

Jun 12, 2014

I would like to have a checkbox be visible only if a textbox (Text246) has text in it.

Code:

Private Sub Text246_AfterUpdate()
Me.Check275.Visible = Text246
End Sub

View 2 Replies View Related

Search For Queries That Use Specific Criteria

Jun 6, 2005

Hi,

Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.

Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.

I hope my explanation is clear.
Thanks upfront for any suggestions!

BJS

View 4 Replies View Related

Queries :: 2 Search Criteria On Same Field

May 14, 2013

I need to be able to print records for certain students showing their best and worst results for each exercise. Must be printed in portrait A4. Each student must be on a new page. I am trying to create a query which will find the results for studentID AA111 and DS1119. For each student:

- Find the best and worst RepsOrTime for each exercise
- Display studentID, StudentSurname, StudentForename, ExerciseID, Description, Best and worst RepsOrTime
- Name best records "best"
- Name worst records "worst"

Please see attached document.

I can find the result for one student id but when i try putting both student ids in it returns no result, also, I do not know how to rename best records best and worst records worst.For this i have two headings shown as RepsOrTime and it shows the max and min value for each exercise.

View 12 Replies View Related

Queries :: Multi Criteria Search

Aug 12, 2015

I have an events database with a description field which I would like to be able to search for multi criteria which would be determined by the user. For example, the user might want to search for all events which are Merchant Navy, Dieppe and Vimy related or they may want to search for only Veterans Week related events.

I've tried creating a form with text boxes that the user would put the criteria in and then basing the query on those text boxes using a like statement.My problem is that I don't know how many criteria the user will be using so I can have too many or too few text boxes. If too few, the user is not getting all of the records that they are looking for. If too many, the user ends up getting all the records in the database as the system uses the bank boxes to bring back all values, so basically all records.

is there anyway that I can have only one text box where users would enter in as many or as few crietia words seperated either by a , ; space etc., and the system would return all records which contain any of those words.

View 1 Replies View Related

Forms :: Search Form Using Textbox And Multiselect List Box

Jul 24, 2015

I have created a multi field search form that have 2 textbox and 2 multiselect listbox(extended). How to make the search form query correctly? Below are the details.

Form
frmSearchForm

2 textbox and 2 multiselect listbox
Textbox 1 > txtFirstName
Textbox 2 > txtLastName
Listbox 1 > lboSports
Listbox 2 > lboSchool

[code]...

I also have a button that run the query qrySearchForm .how am I going to make the query run successfully with multiselect listbox ? I understand that there are a lot of examples of the vba code for multiselect but that is only for multiselect alone and not like my search form that combine textbox and multiselect listbox.

View 14 Replies View Related

Connect Criteria In Query With Textbox In Form?

Oct 28, 2011

I have a query, one of fields have a criteria. On other hand i have a form. User entry a value in textbox an click on a button then query run. criteria is value in textbox. What i must write in criteria?

View 1 Replies View Related

Queries :: Using Working Days In Search Criteria

Apr 22, 2013

I have a query where I use the search criteria of =Date()+1.

However I really need this to add on a working day (i.e. excluding weekends).

Is there an easy way to do this?

View 1 Replies View Related

Query Linked To Form With Textbox To Search Multiple Fields

May 22, 2006

Right what im after sounds complicated but its quite simple.

I have a client database, in this database each client has 3 skill fields, i also have a form with a listbox and an unbound textboxes that are linked to the query so when the textbox is typed in the listbox updates.

Now ive sorted out the problem of not all fields showing it some of them are blank now the problem is.

When input text into one of the boxes the listupdates, now if theres text in two boxes. and theres data in one of each that matches it will show neither, because the query is looking for data that matches in both fields, now i need partial matches so it will give results of data in both fields.

not understanding fully what i mean?

i have zipped my db, if you open the db then open the form 'MainListRecTrades2nd' and fill in data in both primary and secondary trades that matches from the list you will see.

http://awilderbeast.com/CWSystem.zip

The solution i was thinking of would to have 1 textbox that searches all the fields and displays data from any of the fields in the list

Thanks for any help

View 7 Replies View Related

Forms :: DCount In Textbox On Form Greater Than 5 Criteria

Mar 14, 2013

I have a textbox on a form. I am using

Code: =DCount("*","suspend_time_lapse",[Days from Target to Planned End dates]>5)

as the text box source. I get name? or error no matter what i do.

I want to count all records in the [suspend_time_lapse] table where [Days from Target to Planned End dates] is greater than 5.

View 4 Replies View Related

Modules & VBA :: How To Make Label Visible On A Report If A Textbox Is Empty

May 10, 2014

I'm working on a report. I have a text box that contains directory path for a picture which is showed in a image box on my report which works great. I have now created a label called "nopiclb" that has "no Pic "entered in it but I want to set the label to not visible if path1 has a file path in it but visible if path1 is empty

I put the code below in onformat event in the detail section, but is not working

Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.path1) = True Or Me.path1 = "" Then
Me.Nopiclb.Visible = True
Else
Me.Nopiclb.Visible = False
End If
End Sub

View 3 Replies View Related

Modules & VBA :: Unable To Search A String Within Subform To Find Information Stored On Main Form

Dec 2, 2013

I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.

The problem is that the subform is generated from a query which uses a number from the main form to generate.

So the subform record is only generated when the correct mainform record associated with it is loaded.

Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.

But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.

View 3 Replies View Related

Queries :: Return Result When Enter Month As Search Criteria

Jun 15, 2015

I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.

I have attached two example for your reference.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved