I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I have a field defined as Date/Time Short Date which shows values like 8/01/2006, 8/10/2006, etc and I need to select dates in a range.
However, when I write a query with code like:
SELECT AMOUNT
FROM INVOICE_TBL
WHERE PICK_UP_DATE BETWEEN 08/01/2006 AND 08/31/2006
It doesn't pick up the records I also tried >= <= with the same results.
Any help would be appreciated.
Thanks,
Steve
I have a value list combobox (cboShift) on my form with selections as "1st Shift";"2nd Shift";"3rd Shift" and a subform that should filter my records based on the shift selected on the mainform.
My data fields in my query are [TimeStart], [Time Stop], [other fields].
I would like that e.g. when "1st Shift" is selected, it should filter my records based on the [timeStart] that falls within the range, and so on.
I really don't know how to handle this, even to set the time range parameter for this...
I have written this function for a start. To continue, i ask for your help or guide. Please help????
Function fShiftWorked(strTimeStart As DateTime)
Dim strOperatorStart As String
strOperatorStart = FormatDateTime(([tblTimeLog]![timeStart]), vbLongTime)
If strOperatorStart >= #8:00:00 AM# And strOperatorStart < #5:00:00 PM# Then
strTimeStart = "1st Shift"
ElseIf strOperatorStart >= #5:00:00 PM# And strOperatorStart < #12:00:00 AM# Then
strTimeStart = "2nd Shift"
ElseIf strOperatorStart >= #12:00:00 AM# And strOperatorStart < #8:00:00 AM# Then
strTimeStart = "3rd Shift"
Else
strTimeStart = "2nd Shift"
End If
End Function
I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:
Code:
Option Compare Database
Private Sub cmdCal_Click()
Dim sql As String
Dim rs As Recordset
Dim qdef As DAO.QueryDef
Dim hours, waste, produced As Integer
[code]....
But it returns nothing, When i remove the errorHandler, it says that no records were found.
I would like to create a excel file to let the user to input the following column.
Date No. of success No. of failure
==== ============ ==============
" " "
" " "
" " "
I would like to have a function that can allow the user to select the date range and its total no. of success and no. of failure
For example , the user select from 31-03-2001 to 31-12-2012 , then output the report which show the total no. of success and no. of failure in this date range , is there existing tamplate that I can use ? if no , what is the simplier way to do it ?
if the data is very large , I want to create a access db to store it , how to do it ?
I would like to create a excel file to let the user to input the following column.
Date No. of success No. of failure
==== ============ ==============
" " "
" " "
" " "
I would like to have a function that can allow the user to select the date range and its total no. of success and no. of failure. For example , the user select from 31-03-2001 to 31-12-2012 , then output the report which show the total no. of success and no. of failure in this date range , is there existing tamplate that I can use ? if no , what is the simplier way to do it ?
If the data is very large , I want to create a access db to store it, how to do it ?
I Have a table that I collect data for numerous ID's. One ID may have 5 dates that is was serviced. It is due to be serviced at set intervals which is calculated from the most recent service date. I'm calculating the next service date in a text box with the DateAdd func. =(DateAdd('m',[Cal Freq],[MaxCalDate])) with the MaxCalDate being a Max func in a different text box for selecting the most recent sevice date. I'm trying to update the [Next Due] field in the [Master Asset] table with the calculated next service date so I can do queries on items that are due service within a date range. If I do an Nex Due Expr1: in the queries to calc the next due service date then do a criteria on that Expr1: such as >=[Start Date] And <=[End Date] it states datatype mismatch? Is there a way to select a requested data range on a calculated type date/time? Or How can I update the [Next Due] filed in a different table?
View 8 Replies View RelatedI have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.
Hi
I am trying to use a query to populate a combo box. works fine as I am using one table. I want to select DISTINCT records which works. However i need the ID of that record in the select statement. Anyone know how to select two things in a select distinct query??
Select DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;
this works. but i need to select the tbl_Aplaws.AplawID as well. throws errors
I'm teaching this term, and have put my grades and roster in Access. Each grading opportunity is a separate field.
Students will have to take 3 exams (Exam1, Exam2, and Exam3); however, their grade is based only on the 2 highest exam grades. There are other grades...FinalPaper, Attendance, Paper1, Paper2... the total of the grades is 600 points.
I can do the other calculations... what I do not know how to do is to select the two highest test values from the 3 Exam fields. I coudl do it manually, but I really prefer to simply enter grades, and then have the final grade calculated.
Thanks.
Gayle Ann
I have a query that is used to capture all the fields that can be possibly used.When run & exported to excel the user then simply deletes the columns with the fields that they don't require.We do this so we don't have to keep creating queries over and over again when you just use one that does all but then delete what you don't need afterwards.
Any way of using a form with tick boxes for each of these fields so when they user ticks the fields they require reporting on then only those fields will be returned in the query.
I trying to create a query that looks at 2 field in one table and give me the resolves in a new field.
Example
Field 1 Field 2 New field
Hat Cap Cap
Dress Dress
Pants Slacks Slacks
Gloves Gloves
Socks Socks
Can you help?
I have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.
So, it would be something like:
Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;
So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.
Thank you kindly for your help.
I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.
I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.
So, I have three tables.
Table: t_Assets
Fields: AssetID (autonumber; primary key), Asset_Name, Asset_Cost
Table: t_AllMonths
Fields: MonthStartDate
Table: t_AmortPeriods
Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate
Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.
My sql looks like this:
SELECT t_AllMonths.MonthStartDate,
Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate,
t_Assets.AssetID
FROM t_AllMonths,
t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID
WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);
I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."
I am looking for a way to generate a list of all fields within a table, have the end-user select which fields he/she wants to include, and then run the query. I am trying to create this within a form for a nice, easy to use GUI.
I am using Access 2010 on Windows. The fields I need them to select from are in one table, however there are many lookup (tblkp) tables related.
In my Patients table i have a field called PatientID (AUTONUMBER) as my primary key I hve another problem. Is this possible as i hve been trying for two days now. I hve tried a lot of ways but i cant get it to work right. I have a table called dependents. ID , PatientID,,Dependents,DOB,Age id being the PK AND PatientID as a number I have a table called vitals. with ID,PatientID,PatientName( which is a dropdown of Dependents), hpp,sats etc. (This is a subform in datasheet style in another form)
In my table vitals i have the follwing for PatientName . comboxbox.
SELECT Dependents.Dependents, DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age FROM Dependents;
So it selects fine in a dropdown like John 48, smith 36.
The problem i have is that it selcts all the records irrespective of PatientID.
I am using Access 2007. This is the problem; I have a warehouse divided into four departments North, East, South, and West. Each department is assigned its own storage location range North is N1-N1000, East is E1-E1000, South is S1-S1000, and West is W1-W1000. On the access input form of my database are two fields, one combo box field for Department and one combo box field for location stored.
When the user is updating the data base for parts received or shipped, the user must enter the department and location stored to correctly debit or credit that department. The location stored entered must be assigned to the department. (Example: dept. is north and storage location is N65, the user enters D65 instead, error message is displayed, "you have entered the wrong location").
What I need is instructions how to write the code to check the fields to verify location stored matches department, and display a warning message. How do I do this? I have a table with department names and storage location ranges.
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is :
Private Sub cmd_mailreport_Click()
Dim office As Object
Set office = Checks.office
DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _
"info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _
True, ""
End Sub
I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this
On Open
Select Case Forms!frmLogin!cboUser.Column(4)
Case X = 4'the employees security level'
Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee.
End Select
of course this is a syntax error as I do not know the correct code words.
I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.
View 3 Replies View RelatedI have 3 fields on a form PU_POINTS, SU_POINTS & 2_MILE_RUN_POINTS. The user enters a number 1-100 in each of these fields. If the user enters a number in any of the 3 fields of anything less than 60 then I want it to check a fail box. If the user enters all numbers 60 or more then i want it to check a pass box.
View 2 Replies View RelatedI have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
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.
Hi Guys,
Im trying to do something that i dont think is possible, but perhaps i dont know the correct command.
I have a table, which contains costs. the costs are deceided by a circulation. if the circulation is below 10,000, then the cost is A. if its more than 10,000 but less than 20,000, the cost is B.
I have a query that brings back the titles, and thier circulations. what i want to do is use this data to lookup the correct cost. is this poossible?
thanks
%simon
I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.
SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName
FROM Staff INNER JOIN myTable ON
Staff.ID = myTable.StaffID
WHERE myTable.myDate >= #3/2/2014#
AND myTable.myDate <= #3/3/2014#
AND myTable.myTime >= #8:00PM#
AND myTable.myTime <= #11:00PM#
ORDER BY myDate desc
In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.