Below is the SQL for a query I have, which returns events that are scheduled for today, I need to change this so that The user can specify events to be displayed in a date range and If possible only show those events for the users windows ID
Code:
SELECT tblEvent.EventStart, tblEvent.EventOwner, qryCompany.Company, ltDescriptionType.Description FROM (tblEvent INNER JOIN qryCompany ON tblEvent.Company = qryCompany.ContactID) INNER JOIN ltDescriptionType ON tblEvent.EventDescrip = ltDescriptionType.[DescriptionType ID] WHERE (((tblEvent.EventStart)=Date())) ORDER BY tblEvent.EventStart, tblEvent.EventOwner;
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I've inherited a database that has a table with a large number of Yes/No fields. Apart from normalizing the database, I was wondering if the following was possible:
ContractTable has Yes/No Fields for rights granted for each order (television, dvd, internet etc).
Using a form, the user selects the specific right they'd like to run a report on - selecting this right from a drop-down menu, this is labelled "RightsCombo21" on the form.
When the button is clicked, the right selected is now the "Field" section of the query.
The SQL query right now looks like this (shows all rights sold for the territory):
WHERE ((([Contract Table].[Contract Cancelled])=False) AND (([Contract Table].[Contract End Date])>Now()) AND (([Contract Table].[Contract Type])="License Agreement") AND ((ContractIDAndCountry.CountryName)=[Forms]![TerritorySpecificRightsChooser]![TerritoryCombo7]));
I'd like to add something to this WHERE statement:
AND (([Contract Table].RightsCombo21)=True)
Not sure if it's a syntax issue, or I'm attempting something that isn't possible.
I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,
Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600
and the second is,
Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]
When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?
Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?
I have a report that is based on a query. In the query, I have a field called "Mode". In the criteria section for the Mode field, I am calling the selection from a combo box on a form called "frm_main". So the criteria for the Mode field is "[Forms]![frm_Main]![Mode_ComboBox]". This combo box has selections for 1,2,3,4,5. I want a selecton on the combo box that will work with the query to show all modes. Sometimes something weird gets entered into the database like "NoMode" for example. I have tried adding a combo box selection of "*" , "Is Not Null" and "". If I manually type these into the criteria section of the query, it works fine. But when I use these in the combo box and call the combo box selection from the query, it does not work.
Does anyone have any idea what I can use in my combo box selection that when selected, the query (and hense report) will show all data?
Hi, I have been reading through the forum and found alot of really interesting stuff but cannot seem to find the answer to my specific problem. I'm sure it's easy but i'm sick of trying to figure it out now.
I am trying to build a form to display a shift rota. My tables contain daily shift patterns for 6 staff members for 2007. I want to build a form that has a subform displaying the rota for a particular analyst and a control in the master form to select which of the 6 staff members you wish to view. I have built a master form with a combo box. The combo box holds the names of the six staff and gets this data with a select statement in the Control Source field from the employee table. I constructed this with the wizard. I have constructed a query to drive the sub form. The query builds the rota, getting data from my tables but gives all shifts for all dates for all staff . I want to restict this in my subform to all shifts for all dates but for 1 member of staff. The member of staff should be defined by the selection made in the combo box in the master form. I then have a statement in the criteria of the query driving the subform to restrict the results by name to the name selected in the master form combo box. I'm sure this should work as i have used similar methods in the past. The statement in the query criteria reads like this:
[forms]![frmRota].[cmbAnalystSelect]
this is in the field of analystName, so the criteria should find all instances where the value in the analystName field matches the value in the cmbAnalystSelect control. However, the query always returns no results.
When i run the quey i have already opened the form (frmRota) and selected a value for cmbAnalystSelect, so the quey should collect the value from the combo box and filter the result according to the criteria but it seems to filter out all the results. If i run the query without first opening the frmRota form then i am prompted by a dialog box to enter a value for [forms]![frmRota].[cmbAnalystSelect]. If i enter a corresponding correct value (a valid name for a member of staff) the query returns a correct set of results, i.e. all shift patterns for all dates for the selected member of staff. When i have the form already opened i am not prompted for a value by the dialog box which would suggest it is at least recognising the form is open and that the control is there.
The last thing is that my ComboBox control is not bound to anything, when i view the control in design view it says it is unbound. I have nothing in the control source for the ComboBox. In the row source i have a select statement to pick out the employee names from the employee table, this then poulates the list in the ComboBox.
I hope this is enough info for someone to understand my problem and offer some advice, i can elaborate, attach screen shots etc. if needed.
I'm trying set the query criteria from a textbox on a form. It's a Status field in the query. 1 = Open, 2 = Closed. If I set the criteria to "1" it shows all open, "2" and it shows all closed and "1" or "2" it shows all. The problem I'm having is setting these in the form. I've set the query to pull the value from the form. I can get the Open or Closed to work but not the All. My textbox shows exactly how the criteria should read "1" or "2" but doesn't show any results.
I've got a continuous form based on a query.Each of the fields have a search box below it (in the footer) which should ideally filter the query.I'm starting with the FirstName field.In the form's query, I've set the criteria to the following for the FirstName field:
Code: Like "*" & [Forms]![frmStudentDetails]![txtSearchFirstName] & "*" frmStudentDetails is the form name. txtSearchFirstName is the search box's name (in the footer). The AfterUpdate event for txtSearchFirstName is:
Code: Me.Requery
But, it doesn't work; when I switch to form view, it displays an error. The Microsoft Access database engine does not recognize '[Forms]![frmStudent Details]![txtSearchFirstName]' as a valid field name or expression.
I am trying to set up a query by form with multiple criteria.For the majority of the criteria I'm using the format: Like [Form]![formName].[txtInputboxName] &"*" .
I copied the Like (...) &"*" from someone else in order to allow for multiple, optional criteria, which does do the trick, however I don't understand why.
I now want to set up a criteria on my query to search for values between two input values to gives the records in the range e.g between 50 and 100 Following the above format, I want to put something that achieves this:
Between Like [Form]![formName].[txtInputboxName1] &"*" AND Like [Form]![formName].[txtInputboxName2] &"*"
with Inputbox1 being 50 and Inputbox2 being 100, however that code doesn't work and I don't know how to get around it as it keeps giving the 'incorrect syntax' error.
What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed. I tried using a function on the OnLoad event of the form to set a variable called IsOpen to 1 if the form is open, and reset that variable to 0 when the form closes, but when I tried using the variable in an IIF statement in the query criteria, I got a "Its too complex" error.
Here's what I really want to do. I have a very complex form with multiple tabs and subforms. The subforms populate based on a query of what is selected and loaded into a textbox control on the first tab of the form. The first tab has a subform that is based on the main table. Rather than recreate that form, I want to copy it and change the rowsource on the first tab to a subset of the main table, and tell the query to use the textbox on the new form so I don't have to go and replicate all the other subforms. Is there a way to do that or am I just screwed?
Am not getting a value from a form text box when using in the criteria line in my query. Am referencing like [Forms]![FormName]![FormLabelName]. If I copy the data in the form and paste it into the query, it works fine, but if I just reference the form, I get no results.
How can I add an additional field to a table based on the output of a query? For example, I have table A with 2 types of records and table B with only 1 type of record. Based on this, I can determine which records in table A are the same type as those in table B. I would however like to be able to flag within table A, those records in table A that are the same as those in table B.
I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code: =IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
This works for the records with fields that have data.
Code: Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
I am trying to make a form where the user can check/uncheck query criteria via several check boxes. The idea is that the user can start with many criteria and then deselect criteria if the search does not return enough results.
I have been setting up several queries and thought I would combine them in a "Master Query", since I thought it may be easier to deal with each criterion and the respective switch this way.
Lets say we run a hairdresser.
I have a field in the form that allows me to select clients. This is also used in the query. Works fine. Now to the hard part.
Example 1:
Each customer has a budget to spend on haircuts. Each hairdresser offers haircuts from $x to $y. The query should return all hairdressers that are appropriate for the budget of the selected customer. There should be a yes/no button on the form to ignore or use this criterion.
Example 2 (this completely threw me off):
Each customer has a set of preferred services from a table (e.g. cutting,washing, coloring). Each hairdresser offers certain services also based on this table (e.g. cutting,washing, coloring, drying). The customer and hairdresser table use the services table and a multi combo box to select the services. The query should return only hairdressers that offer some or none of the services wanted by the client.
Again, there should be a services yes/no button on the form to either ignore or include this criterion.
To clarify, the hard part for me is the query. I am fine with setting up the tables and the form. Just not sure how to implement something like "IF (ServicesCheckBox = -1, 'then use service criterion', 'ignore service criterion')".
I have a combo box on my form that passes criteria to a simple select query. There are four possible selections to make from the combo box. For some reason, when I select the first option on the list the query runs perfectly. However, if I select the second, third or fourth option from the combo box, the query returns no records, even though I know there are records in my table which should be returned.
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is 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".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
I've been writing queries in the following format for years in Access 2003, but having recently transistioned to Access 2010, I've found the following sql doesn't work.transform
sum(s.value) SELECT s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name FROM supply_points s , codes c WHERE s.code = c.code and s.id is not null and s.code = 1075 and s.month >=[forms]![main]![gppstart ] group by s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name pivot s.month
In Access 2010, this query returns the following error message:the Microsoft Access database does not recognize '[forms]![main]![gppstart]' as a valid field name or expression
I am attempting to update an oracle table using MS Access.
One of the fields to be updated is a primary key number field.
I'd like to use the autoincrement field to update that field.
My sql is:
Code:
Alter table testAutoNum Add column progid autoincrement (1001,2)
This sql nicely creates the new field in the table, but it doesn't start at 1001 and increment by 2; it starts and 1 and increments by 1.It is also creating an autonumber and I need it to be a number.
I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
I'm working with a form in Access 2013 that uses the navigation buttons. I'm trying to have my criteria for a query link to a dropdown box on the form. I had no problems doing this in Access 2010 with normal forms, but I can't link the criteria, in the query to the Navigation buttons form box. I'm tried using
I have a query that allows the user to put in a date range - this works.He also wants to limit what he prints.
For example at the moment he gets
OrderA rest of line 1 for orderA OrderA rest of line 2 for orderA OrderA rest of line 3 for orderA OrderB rest of line 1 for orderB OrderC rest of line 1 for orderC OrderC rest of line 2 for orderC OrderD rest of line 1 for orderD . . .OrderZ etc
He only wants to say print lines for OrderA and OrderD. It's he's choice and not based on anything else already in the table.
I see that in the criteria on the design for the query it allows me to bbuild an expression and I can select a list box on the form such as
[Forms]![name of form where list box is]![name of list box].[EXPRESSION VALUES]
I don't know which expression value to choose. I have tried afterupdate and beforeupdate but none of the rest look like they are the ones I should be using.
I am currently working on an existing database (not created by me) which contains several queries. Each query relates to a particular product.
I am wanting to create a form which has a combo box so that a user can choose a particular product from the combox options and then click on the command button to run the correct query.
How to do this. I have created the form and the combox (together with the list of products). I just need to know what Event Procedure code I need to enter to programme it to look for the correct query and then run it.
For example.
I have the following queries product1query product2query product3query
I have the following options in the combo box product1 product2 product3
How do I get the command button to look at the product1 option in the combo box and then find and run the product1query.
