I need to design a query that contains about a dozen fields for stationery items along with another dozen fields (checkboxes) to show whether these items were delivered (Yes, I know, not great db design).
I ran out of space with the the query criteria window.
I want to run a query that returns a "small" or "medium" or "large" etc on each respective row against another columns respective numeric value...
< 500 = "small"
500 up to 1000 = "medium"
1001 up to 10000 = "large"
10001 up to 100000 = "very large".
I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.
I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.
An example of the parameters used for one of the checkbox fields in Query Builder is as follows:
Field: SumAnger: Sum([Anger]*-1)
Table: Default as only one table
Total: Expression
Show: Checked
This works fine.
My latest parameters for the date range are this:
Field: [cDate]
Table: Default as only one table
Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM!
Show: Checked or Unchecked makes no difference
Criteria: Between [From Date:] And [To Date:]
This gives a statement in SQL view of:
SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate]
FROM tblCommsLog
WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));
what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.
I have table.[reconciled] tat allows for a check box. i want to enter in the criteria filed in query design that i only want to see the unchecked or false entries. i have tried writing the criteria multiple ways and i cannot get it to filter out on my sub form query.
SELECT FuelmanImport_tbl.Reconciled, [Driver PIN].Division, [Driver PIN].[Transportation Supervisor],
FuelmanImport_tbl.[Transaction Date], FuelmanImport_tbl.[Card Number], [Driver PIN].Driver,
FuelmanImport_tbl.[Cardholder Name], FuelmanImport_tbl.[Prompted ID], FuelmanImport_tbl.[MCC Description],
FuelmanImport_tbl.[Merchant Name], FuelmanImport_tbl.[Merchant Address], FuelmanImport_tbl.[Merchant City],
[Code] ....
In an access form, I have several checkboxes. There is one checkbox titled "Complete". The complete checkbox needs to be true only if all other "non master" checkboxes are true. If all of the non master checkboxes are not checked, I need the complete checkbox to be false. This is the code that I am currently using on the after update command:
If me.checkbox1 and Me.checkbox2=True then
me.completed=True
else
me.completed=False
end if
This works fine if there is no "and" in the if statement and the condition is based on the status of one checkbox as opposed to many (Also I have about 15 checkboxes that must be checked before the "complete" checkbox is true).
I want to create a query with multiple conditions. Basically if the person Passes any of this trainings they need to show up in my query..how do you do it?
SELECT tblMasterUsers.userid, tblMasterUsers.Licenses, tblMasterUsers.firstname, tblMasterUsers.lastname, tblMasterUsers.email, tblMasterUsers.npn, tblMasterUsers.Region, tblMasterUsers.ABSID, CMPreport2014.[Ahip status], CMPreport2014.[LP Status] AS [AZ Product Training], CMPreport2014.[LP Status1] AS [CA Product Training], CMPreport2014.[LP Status2] AS [OR WA Product Training], CMPreport2014.[LP Status3] AS [Fraud Waster Abuse],
[code]....
My database has a text field "Status" where the text is either, A, W, C. There is a form to update this field; currently it uses a standard Access created text field. The users want a checkbox which will show up as checked when the status is W, if the status is anything else, the checkbox will be blank. If the user clicks the checkbox within the form the status will be changed to W.
If [table.status] = 'W'
then checkbox = 0
else
checkbox = -1
and then somewhere on the update it would be
if checkbox = -1 then [table.status] = R
Except that Access doesn't think the way I do.
I've been asked to get involved in some access development but don't seem to be get my dlookup syntax correct.I essentially want to lookup what salary band different employees are in.I have two tables:
Employee Table) has the fields: Name, Type, Salary
Salary Band Table) has the fields Employee Type, Salary Band, Lower range, Upper range..my query syntax is:
Code:
Band: DLookUp("[Salary Band]","Salary Band Table", [Salary] & " BETWEEN [Lower range] and [Upper range]" & "AND " & [Type] & " = [Employee Type]")
I can get the first criteria to work but can't get the second part to work - currently it produces an error.
I am working with a database which contains dates and a measurement value with data within the following fields:-
Record
SurveyID
DateTimez
mX
mY
mZ
Here is an snapshot of my main table "SurveyData"
Record|SurveyID|DateTimez|mX|mY|mZ
7194|A2|16-Mar-14|12797.4815|25781.8672|81.1848
7195|A2|17-Mar-14|12797.4863|25781.8672|81.1867
7196|A2|18-Mar-14|12797.4907|25781.8698|81.1875
7197|A2|19-Mar-14|12797.4844|25781.8642|81.1846
7198|A2|18-Mar-14|12797.4883|25781.8646|81.186
7199|G23|21-Mar-14|12419.8779|24822.6836|132.6177
7200|G23|12-Mar-14|12419.8928|24822.6812|132.6075
7201|G23|13-Mar-14|12419.9211|24822.6829|132.5915
7202|G23|14-Mar-14|12419.9392|24822.6837|132.5795
7203|G23|15-Mar-14|12419.9581|24822.684|132.5662
Record is the primary key which is a auto increment integer. The Survey ID is a text field that contains the name of the survey sensor, DateTimez is, we the Date and the time of survey and mX is the X coordinate. There is also mY and mZ, but once I can do the mE then its just a matter of duplication.
Now, I am trying to develop a query that will add some additional calculated fields to calculate the difference between the mX coordinates from the previous pickup, which is called 'dX'. The code will be duplicated to calculate the dY and dZ. I have managed to solve this using a Dlookup statement as follows:-
Code:
dX: [mX]-(DLookUp("[mX]","SurveyData","[Record]=" & [Record]-1))
This works fine, however I am struggling to make it work so it will only take the difference for each SurveyID. For example, the calculation does not calculate the difference between the X-coordinates between the Sensors A2 and G23,as they are not spatialy related. The code I am trying to use is shown below:-
Code:
dX: [mX]-(DLookUp("[mX]","SurveyData","[Record]=" & [Record]-1 & "AND [SurveyID]=" & (DLookUp("[SurveyID]","SurveyData","[Record]=" & [Record]-1))"))
I think I have got myself totally confused, but what I am trying to achieve is calculating the difference between the x coordinates from a previous record but only if the SurveyID matches on the previous record. I am using the record field to find the previous record.
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.
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
Can I do this using only update query?
I'm having an issue getting my query to omit records with a blank field - in fact, it omits all records.
What I'm trying to do is:
I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.
Here is what I have:
IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)
I am working in MS access 2007.
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
I am trying to create a Dlookup in Access 2010 within a query using query wizard. I want to lookup the tax rate for an employee based on a salary range and their 'tax category' (string). Through troubleshooting I can get the criteria to work separately.
These are: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'")
DLookUp("Base","TABWT",[grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")
These work and return the correct values for each column/row when I run the query.
However, when I combine the criteria (using the build wizard) as follows:
Expr1: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'" and [grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")
The Dlookup will returns 0 values but will not give an error message.
I've tried quite a few variations on syntax and quotes and so on. However, it's just not working for me.
I managed to get separate combo boxes to filter out results on a query, however now I have a slightly different problem.
I managed to get the combo boxes to filter records in different query criteria, i.e criteria A for field A, criteria B for field B, etc.
Now I would like to have several combo box filters in one criteria field, however no matter how much I try to move the code about, they either filter out nothing at all, or show up blank records as they're filtering one after the other, i.e filter for A, and then B, which clearly won't work as there will be no B if you've already filtered for A.
The code I'm using in each criteria box is;
Like "*" & [Forms]![MyForm]![Combo1] & "*"
and this works as a single criteria in a single field.
I'm trying to have a single or multiple query criteria based on what the user checks on a form.
I can't get the True condition to work at all, I get no records. Here is what I'm using
IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])
If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.
Hey all this is my first post so thanks in advance for any help you can give me.
I am trying to use multiple checkboxes on a form to try and make a select query, when the box is checked the data is queried when unchecked it is not, sounds simple enough, here is my problem.
I set the query criteria with an expression like this:
[Forms]![frmMainLookup]![Check Box Alarm Number]="-1"
As long as the checkbos is checked everything seems to work fine.
If I uncheck the checkbox then none of the data is shows up.
I still get the columns to show up just no data.
I am sure there is something simple I am missing but the Force is not with me today.
Thanks again for any help.
I am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:
1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)
2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)
I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")
However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.
I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.
I have a database where there are 3 tables. table "d_cases", table "d_user" and table "d_status"
The table "d_cases" has the users and statuses only with a number. Peter has ID 2, John ID 1. There is status ID 1 that is New, ID 2 Started and so on.
How can I run a query that will show me the names and status instead of the numbers?
I have attached the database (2003 format) ....
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
View 3 Replies View RelatedI have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies View RelatedI have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
Hello everyone,
I tried to post this yesterday but it didn't go through for some reasons.
I've attached my database that contains student, classes, and couple other tables. I also created couples of forms and I wanted to register a single student to multiple classes at once.
For example: There're 3 classes (Math, English, History) on 1/3/2006. Student A wants to register 2 classes, Math and English. I should be able to check those two and submit and I'm done.
Currently, I have to select Math and assigns to student A, then select English. That's not good at all. What if there're 10 classes in one day and a student just want to register 8 classes? I have to do eight times.
It'd be great if you could help me. Thank you in advance.
Isabel
I am using the Multi Search from that's available in the template forum, and currently it's working perfectly. I am trying to add one checkbox (chkIncludeManifests) to the form to do some filtering.
When the box is checked, I should search all records. When the box is unchecked, I should search only records with no manifest number [manifestnum].
I've tried setting up an IIF statement to filter out the values that are empty but it's not working.