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?
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"
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
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???
I 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)
Hey, 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?
I 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.
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.
I 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
Hello, i need to have my database find records that were entered between two dates. To do this i made a query that included the dates the records needed to be in between....[date1] and [date2]. Then i had the record dates be called [sent date]. i made my query and in the criteria for [sent date] i wrote (Between [Date1] And [Date2]) when i enter the two dates [date1] and [date2] the query does not show the records that are between that date. why is this happening? is there and easier way? another way? For example, say that i have records with their [sent dates] equaling 12/30/2004, 1/1/2005, 10/1/2005 i want to make it so that when the user inputs a records with dates [date1] and [date2], that those records will show up if their [sent dates] are between [date1] and [date2]. if [date1] is 1/1/2003 and [date2] is 1/1/2006, all of the records will show up. if [date1] is 12/31/2004 and [date2] is 1/3/2005, then only the record with the date 1/1/2005 will show up. how can i do this?
I am working on a project where I need to monitor employees who are on vacation. I would like to run a report that would show employees who are currently on vacation, and those that have already returned. This is the information that I am looking to get from the report.
Search Dates: Entered criteria thru qry, msg windows asks for "start date" and "end date". 1/01/05 - 1/31/05
tblName John Smith
tblEmployee# 1234
tblDate left (on Vacation) 12/25/04
tblReturn Date 1/25/05
Days on Vacation 31
tblName Tina Roberts
tblEmployee# 1243
tblDate left (on Vacation) 1/25/05
tblReturn Date (If the employee has not returned it would give the "end date" and calculate the days on vacation) 1/31/05
Days on Vacation 6
This may seem simple but I just can't get the qry to show dates beyond the "start date" and "end date". Only those records that start within the two dates.
Is it possible to define 2 dates and create a record for each day in between? I am creating a "sick occurence" database. I want to define the first day the individual calls off sick and the day they will return, then calculate how many sick days they will be using. However, I need to take into consideration any relief days or assigned time off.
Basically, I want to be able to assign what is going on everyday between these 2 defined dates. Be it, holiday, annual leave, personal leave, sick leave, or regular relief. If further clarification is needed, I can go more in depth.
I 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;
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)
While 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
I have a table tbl_PolicyDetails with details of the policy the customers have with us.. In the tbl_PolicyDetails, I have two fields (these are the ones in question) called
"policyStarted" - Start Date of a Policy and "policyPeriod" - Term of Policy Monthly/Annually/Quarterly
So in a Form view I just get the Next installment for the customer based on this information for display.. For example..
Mr Butters Stotch's policy started on 26/04/2013 and is paying Quarterly; the next (i.e. 2nd) installment would be on 26/07/2013..
Ms Wendy Testaburger's policy started on 07/04/2013 and is paying Monthly; the next (i,e. 3rd) installment would be on 07/06/2013..
This is not hard to get.. I have that sorted.. But the problem is, my manager wants to be able to specify two dates and search all policies that will be 'paying in' that Date range should be picked up..
So if the search range happens to be.. 01/07/2013 and 31/07/2013 Then Mr Butters Stotch's policy should be picked up, as his next installment falls on 26/07/2013.. Since this information is not stored, I cannot run a direct Query on this.. Also as the Installment number varies for each customer, I would not be able to just add 1 - Month or Quarter or Annum, and see if the date falls in that range..
I have a table that contains the following fields: ID (autonumber), Item, billing date(mmm/yyyy), count, and approved.
My first query returns records where the approved field is null. My second query returns returns all records for 2 months ago where the approved field is not null.
This is my problem, I need to pull records that have NOT been approved for the prior month BUT if they were approved 2 months ago AND the count is the same, it does not need to be returned in the query. Also, if it was approved 2 months ago but the count is different than what it is for 1 month ago, it needs to show in the query.
Only one table : Customer ID,Customer name , subscription period, subscription start date and subscription end date.
I need values where subscription start date is between date1 and date2 and subscription end date is between date 1 and date2.
Detail explanation:
customer1 subscribed from 2-feb 2012 to 2-feb 2013 customer2 subscribed from 5-aug-2012 to 5-aug-2013 customer3 subscribed from 1-Jan-2013 to 31-Dec-2013 when the user enters date 1 as 1-jan-2013 and date 2 as 30-Jun-2013
Even though the subscription start date is not within the range entered by the user.I need to get all three customers because the subscription enddate falls within the range.And customer 3 is also required as subscription start date is within the range.
I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like: tblEmployeeHistory ID (PK) EmployeeID (FK to Employee table) ManagerID (FK to Manager table) MoveDate (date employee assigned to manager)
The Employee table is set up like: Employee ID (PK) EmployeeName ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit AuditID FileNumber AuditDate EmployeeID (FK to employee table)
tblAuditResults AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions) Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
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).
Any 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...
I have a few queries which are used to create reminder email on training which is due for renewal.Some training required reminder 6 months before 2 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-18,1) And DateSerial(Year(Date()),Month(Date())-17,0)
Some training required reminder 3 months before 1 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-9,1) And DateSerial(Year(Date()),Month(Date())-8,0)
My problem is with training requiring reminder 6 months before 3 year expiry. Using this criteria:
Between DateSerial(Year(Date()),Month(Date())-30,1) And DateSerial(Year(Date()),Month(Date())-29,0)
Doesn't show any results (although there is training which was done 30months ago, expiring in 6 months time).
Changing the -30 (months) and -29 (months) in above down to -22 & -21 shows records as expected, but anything below -22/-21 doesn't show any records.
