Applying Percentage
Jan 30, 2007Sorry I removed the post because i answered my own question by typing it up =)
View Replies
ADVERTISEMENT
Applying A Filter In A Query
Feb 20, 2007Good Morning
I am attempting to apply a filter to a query i have setup. I only want the user to be able to search for specific words within two fields (technical and behvaioural) i have setup. I know i have to use a WHERE expression but am confused as how to exactly setup the expression... any idea's???
Applying Dates To ALL Records
Dec 9, 2005Dear all:
Have a Access databaseform with 300 names, ID's, dates and other work information.
On this form I have an unbound comboxbox called "date" where I select various dates. Also on the form, I have a bound textbox called "completed by". The date selected in the combox goes into the "completed by" textbox. Last, a checkbox called "apply_date_to_all".
I wish to select a date form the unbound combobox when the checkbox is checked, the date is applied to ALL records in the database into the "completed by" textbox.
This is to be printed form time to time.
This is my second request, sorry I I did not make myself clear the first time. Any ideas on how to start?
Many thanks,
Dion
Applying Dates To ALL Records
Dec 12, 2005Hi all:
Code:
Private Sub APPLY_DATE_AfterUpdate()
Dim mytext As String
Dim mystring As String
mytext = Me.[Text1074]
Dim mysql As String
mysql = "UPDATE APPLICANTS SET [GRAD_DATE_IN_WORDS]=" & mytext & ";"
DoCmd.RunSQL mysql
I am applying a date to ALL records on a form. Text1074 is a combobox with dates in it. It gets the dates from a table called Grad_dates.
The dates are defined as text in the design view of the Grad_dates table. In the first column is called Graduation_date of Grad_dates table are dates in this format: Feb 1, 2005. This is defined as text. The second column is called Text_graduation_date. It is defined in this format: February first two thousand five.
I wish to pick the usual date format from the combobox and it applies the TEXT version to all in the dabase. Is the code above correct? in the code above I get an error message saying "syntax error"
Heinikens to Colm!!
Thanking in advance,
Dee
Applying A Parameter Query To Subdatasheet
May 11, 2007I want to apply a parameter query to a subdatasheet.
I have a primary query that has a list of all the machines we service, this same query will count the number of service calls within a given date range that the user inputs.
(For the date criteria- Between [Enter Period Start Date] and [Enter Period End Date])
The resulting query datasheet is linked to a subdatasheet from another query that searches all the service calls and links it to the Machine ID.
So when I expand the subdatasheet it shows all the service calls for that machine, beyond the date range.
How can I make it so that the date range from the primary query applies to the subquery?
(without having to re-enter the date range everytime I expand the subdatasheet)
Forms :: Applying A Filter To A Subform?
Jun 22, 2014Was trying to use a button to apply a filter from a form to a subform
the subform (frmLogOverview_Subform) has a field call AttendedBy_FK and I wish to filter this from the main form (frmCallOverview)
how can I do it ?
Design Process - Applying Multiple Discounts
Mar 6, 2006Hey, i'm working on creating a database.
it's involves a customer booking a ticket for a flight.
i have three tables, customer, booking and flight.
anyways, i'm having trouble applying the price the customer needs to pay
say there's an attribute on table Flight -> flightprice.
when a customer makes a booking, discounts are applied to the price.
discounts include:
1. Special seasonal discount, (eg from Sept to November) - 4%
2. Member discount - 6%
3. frequent flyer discount - 5%
4. other discount - 4%
the thing is that these discounts are stackable. ie, applied together.
(eg a person eligible for member discount and frequent flyer discount will get 11% discount total)
i know that if only one of the discount rates apply,
then i'd be able to create a new table called Discounts
and have a one to many relationship with the Booking table.
but i'm not sure how i need to bypass this when several discount rates can be applied at once to a single booking.
-------------------
also on another note.
how can i make the seasonal booking only be applied if the date of the booking lies in between the promotion season?
Applying A Date Range From A Form To A Query...
Jul 13, 2005I have a simple form where a user selects a query from a drop down list. I need to have the query apply a date range that has also been selected in the form.
Would I call to the date form fields from the query as a variable somehow?? If so, could someone show me the syntax? I am extremely new to access.
Help!
Applying User Entered Data From One Field To Another
Aug 14, 2006Hi,
I am currently building a stock control system in access. I have a form with which the user selects a stock item from 3 combo boxes (stock name, stock weight and stock grade). The combo box links to a table of stock items. A stock item is made up of a name, weight and grade (all separate fields in the stock items table).
I wish to setup the form so as the user selects the stock name from the first combo box. Based on the value selected by the user, the appropriate stock weight(s) assoicated with that stock name will be displayed in another combo box.
Basically, I wish to take the result from one field in a form and apply its result to a query(?) in another field so as to filter(?) my result accordingly.
Hope that makes sense. Any suggestions?
Cheers
Turbojohn
Modules & VBA :: Applying Filter In Form With 2 Variables
Jun 6, 2013I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).
I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.
Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.
If Me.txtRole = "Agent" Then
DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())"
AgentFilter = "CASEOWNER ='" & Me.txtName & "'"
DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter
Exit Sub
End If
Queries :: Applying Calculations To Date Ranges
Aug 10, 2015I need a query that will relate values to given date ranges. For Example:
Table: TaxTypeRange
Values:
TaxType; FromDate; ToDate; Contact
W2; 1/1/15; 3/31/15; Tom
1099; 4/1/15; 6/30/15; Tom
W2; 7/1/15; 12/31/15; Tom
Table: Bills
Values:
Contact; BillAmount; BillDate
Tom; 1000; 3/31/15
Tom; 1100; 4/30/15
Tom; 1200; 5/31/15
Tom; 1300; 6/30/15
Tom; 1400; 7/31/15
Requirement: For every period where the TaxType = W2 the query should multiply the Bill amount by 1.20, so the result should be something like this:
Query: BillsAndTax
Values:
Contact; BillAmount; BillDate; TotalBill
Tom; 1000; 3/31/15; 1200 (TaxType = W2 so 1000* 1.2)
Tom; 1100; 4/30/15; 1100 (TaxType = 1099 does not apply 1.20)
Tom; 1200; 5/31/15; 1200 (TaxType = 1099 does not apply 1.20)
Tom; 1300; 6/30/15; 1300 (TaxType = 1099 does not apply 1.20)
Tom; 1400; 7/31/15; 1680 (TaxType = W2 so 1400 * 1.2)
Not sure how to set this up the right way.
Modules & VBA :: Applying Filter On Subform - Type Mismatch
Mar 16, 2014I am trying to apply a filter on a subform but i get "Type mismatch" and i dont know why below is the code.
Basically when they select a line on one subform it filters another from that selection is it somthing to do with the dates?
Code:
Dim MyProd As String
Dim ReqDate As Date
MyProd = Me.ProductCode
ReqDate = Me.RequestDate
Forms!FrmReplenishments.FrmMasterReplenDetail.Form.Filter = "[ProductCode]= '" & MyProd & "'" And "[DeliveryDate]= #" & ReqDate & "#"
Forms!FrmReplenishments.FrmMasterReplenDetail.Form.FilterOn = True
Queries :: Listbox Returns Empty Before Applying Filter
Jul 14, 2015I am using two combo boxes to filter a list box with the code below. The combo boxes work, but when the form opens, the list box is empty until it is filtered using the combo boxes.
I want the list box to return all records when no filter is applied.
Here is the code:
SELECT Q_Gender_Statistics.ParticipantID, Q_Gender_Statistics.Gender, Q_Gender_Statistics.Date,
Q_Gender_Statistics.Year, Q_Gender_Statistics.[First Name], Q_Gender_Statistics.[Last Name],
Q_Gender_Statistics.[Other Names], Q_Gender_Statistics.[Passport No], Q_Gender_Statistics.[Duty Station], Q_Gender_Statistics.[Contact Number] FROM Q_Gender_Statistics WHERE (((Q_Gender_Statistics.Year) = Forms!F_Gender_Statistics!cboYear) AND ((Q_Gender_Statistics.Gender) = Forms!F_Gender_Statistics!cboGender)) ORDER BY Q_Gender_Statistics.Date DESC;
Reports :: Applying Criteria In The Button That Opens A Report?
Jul 15, 2013I have the onlick of a button programmed with
Dim stDocName As String
Dim stCriteria As String
stDocName = "Rallies this year"
stCriteria = "[Year of Rally] = " & Year(Date)
MsgBox (stCriteria)
DoCmd.OpenReport stDocName, acViewPreview, "", "", stCriteria
the output in the msgbox is [Year of Rally] = 2013 which seems fine but the OpenReport command then gives me a type mismatch error the [Year of Rally] is a calculated field in the query - the record source.
I have tried
stCriteria = "[queryname].[Year of Rally] = " & Year(Date)
and same problem
Forms :: Applying Filter - Display Data Even One Of Field Is With 0
Sep 23, 2014While applying filter in forms, i have 6 fields in the form and i want to display the data even one of the filed is with 0.
I used the below code, but this one display the data where all the fields are 0. I tried or in place of and but still its not working.
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[Placed]=0 and [receievd]=0 and [Ordered]=0 and [processed]=0 and [delivered]=0 and [closed]=0"
Me.FilterOn = True
End Sub
Forms :: Applying Filter To Subform - Values Stored As Foreign Key
Mar 25, 2013I am having a problem with a filter that i am trying to apply to a subform.
I have a button on the form that when clicked should filter the subform (which is in datasheet view) to the criteria i set.
This criteria will eventually run from a combo box but I wanted to just get the filter working first.
I put the following code into the onclick event of the button:
Items.Form.Filter = "Items.Form.[Master Category] = 2"
Items.Form.FilterOn = True
I chose the criteria 2 for the filter just as a test as I knew there are some records with that value in the master category field.
The problem is when ever i click the button to apply the filter it clears all the data as if it has not found any records with that value.
Is my syntax and method OK? Why its filtering everything out?
The only other thing to consider is that the field I am filtering on was set up using a lookup wizard linked to a table so the values stored are a foreign key (hence the value being 2 rather than something descriptive).
Queries :: Applying Multiple Count Functions With WHERE Criteria Without Affecting Others
Dec 11, 2013Any way to use multiple count functions in a query with their own individual filters without affect the others?
For example:
SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2]
FROM [E&I Table]
WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV"))
GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;
I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...
Queries :: Not Recognizing Expression As A Valid Field Name When Applying Data To A Chart
Jun 18, 2015So I'm new to Access, and I am trying use a query that can be referred to by a chart. So the idea is that I use the query to select data only from the date range that the user chooses on the home screen of the database for their chart (using the command Between [Forms]![Home Screen]![From] And [Forms]![Home Screen]![to])..Although it has been working fine for charts that only have two parameters, when I attempted to make a line graph that sorts by 3 parameters (i.e. date and amount for different types of something), it stops. I get the message that "The Microsoft Office Access database does not recognize [Forms]![Home Screen]![From] as a valid field name or expression" or something to that effect.I'd rather not remove the whole specification created by using the dates from the home screen, as it has been working fine on all other aspects of my charts and reports.
View 9 Replies View RelatedForms :: Applying Parameter Based Wildcard Search On Subform Filter
Jul 31, 2014I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
Forms :: Applying Quick Filter In Main Form Causes Data To Not Show In Subform?
Sep 3, 2013I have a pretty simple form that includes subform. Subform's table is linked to main form's table with parent/child relation. Connecting fields are main table's ID field and corresponding field in child table. Subform is in datasheet view. This is pretty basic stuff so there should not be any problems, but every time I apply a quick filter in main form it causes data in subform become invisible. There is single row in subform, but all it's fields are empty.
View 5 Replies View RelatedForms :: Applying Sort And Filter In Form Results In Design Changes To Form
Dec 27, 2014When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
Applying Filter With Selected Field Based On Another Field
Apr 30, 2015I have a login screen to open a form. I would like to filter the form based on what user logs in. In my tbluser, I have a trainer name, a UserLogin, and a password. I would like the form to filter on trainer name based on userlogin.
I know the code: DoCmd.ApplyFilter , "Trainer = 'Joe Smith'" but how to I edit the code to change as the tbluser changes?
Modules & VBA :: Applying One Text Box Value To Collection Of Another Text Box
Mar 25, 2015I have a form with tabular format containing a text box with the name of session and text box with the name of classSession. This form containing multiple records but when i want to copy the value of session to the classSession text box the copy function is only applied to the first record and not applying to another records.
View 1 Replies View RelatedPercentage Help
Jul 20, 2005I have a table that documents the result of unit inspections. The data is Date inspected (once/month), Unit, and about 12 yes/no fields.
I want to run a query that shows the percentage compliant (yes) of all of the categories per month. I also want to be able to graph the results for one year per month to show trends.
What is the best way to do this?
Thanks,
Jason
Percentage In Report
Aug 9, 2005Hi, i have poduced a report and i want to calculate some percentages. Some of the fields i have are:
Total Sales Clothes Shoes Pants
200 20 120 60
% 100% 10% 60% 30%
As you can see in total 200 sales were made, 20 clothes sales, 120 shoe sales etc and below it a percentage of each of the sale types. I have put this ' =Sum([Clothes]/[Total Sales])*100 ' in to calculate the percentage...but it does not work...Any help would me much appreciated :)
*This hasn't shown up very well...hope you understand it?!
Maths/Percentage
Jun 19, 2006Hello, I'm stuggling to work out how to go about setting up a formula.
I have 497 students 186 are Male 311 are Female. How do i work out the percentage of Male and Female students? I have no idea how to do the Math behind this so cannot start to add it to my database. If anyone can show me how to calculate this it would be really helpful.
regards
Pete