I am querying a linked ODBC table with a date column called "db_created_tms" that has dates in formatted like "2/25/2004 8:54:02 PM" , because it is a linked table I can't edit the data type. How can I limit my query results to the last 30 days.
My query without the date limit currently looks like this:
SELECT QBReportAdminGroup_v_txn_po_line.doc_num_h AS po, QBReportAdminGroup_v_txn_po_line.quantity_qnty AS qty, QBReportAdminGroup_v_lst_item.name AS REFERENCE, QBReportAdminGroup_v_txn_po_line.unit_price_amt AS cost, QBReportAdminGroup_v_lst_vendorODBC.name AS vendor, "" AS LOCATION
FROM (QBReportAdminGroup_v_txn_po_line INNER JOIN QBReportAdminGroup_v_lst_item ON QBReportAdminGroup_v_txn_po_line.item_id = QBReportAdminGroup_v_lst_item.id) INNER JOIN QBReportAdminGroup_v_lst_vendorODBC ON QBReportAdminGroup_v_txn_po_line.vendor_id = QBReportAdminGroup_v_lst_vendorODBC.id;
I am working on my DB for work and am almost finished with my DB. I have a query that gives me totals for days. This is the code that I have been using.
The "TotalTMD" and "TotalLOA" days are limited to 180 days for each table. However what I need to do now is limit the combination of both fields to 180 days. Is this possible?
Since "TotalLOA" is more serious, I would like to use what ever days are in this field and then include what ever days are left over to total 180 days from "TotalTMD" days.
Maybe I could use this as an example:
Name TMDDays LOADays Total days John 190 20 210 Jim 80 90 170 Tony 250 190 440
Should display: Name TMDDays LOADays Total days John 160 20 180 Jim 80 90 170 Tony 0 180 180
I have an unbound form with a subform which is bound to a query. The subform prioritizes and individually lists all projects my company has on its agenda and the project’s corresponding cost. Is it possible to enter a dollar figure (which would represent our budget) on the main form and have the subform list only the top prioritized projects whose running total costs would fit within the budget figure stated? Any help would be appreciated as I don’t even know if this is possible. Thanks.
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No). So what im trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code: SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU FROM Calls GROUP BY Calls.SKU HAVING ((Not (Calls.SKU) Is Null)) ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
I am designing a database for a marketing company. One of my tables has all of the company's contacts. On the contacts form, I have a button that opens up a query that shows the information for the company that contact works for. I have a problem where if I have more than one contact per company, it will return that many results (which is just a repeated list of the same company information). It is an eyesore to look at the same information in two or more rows, so I was wondering, can I make the query return only one result? I've been looking online, and I haven't found this at all. Thanks a lot.
I'm currently trying to create a KPI report (Key Performance Indicator) to log jobs & their response times
I can easily produce a query that gathers all the information needed but I need to find a way to limit the results to 1 record per job as one job can have many records - Primary key being the TaskID field
I need a way to only pick out the oldest record & ignore the rest
For example
A priority 1 job that was logged at 01/05/2008 08:00 that needs to be responded to within 2 hours
Two engineers have carried out the work & logged the following details
1st Engineer responded at 01/05/2008 09:00 and the other returned later to complete the job at 01/05/2008 13:45
I need the query to only pick out the entry for 01/05/2008 09:00 as it's the oldest.
Any suggestions would be most welcome
this is what my query looks like at the moment.
SELECT tblTasks.TaskID, tblTasks.DateReported, DateAdd("h",DLookUp("[ResponceTime]","[tblPriorities]","[tblPriorities]![PrID]=" & [PrID]),[DateReported]) AS DueBY, tblTaskTime.TTStarted AS DateStarted, tblPriorities.Priority, tblPriorities.ResponceTime, [DateStarted]-[DateReported] AS Responded FROM tblBuildings INNER JOIN (tblContracts INNER JOIN (tblPriorities INNER JOIN (tblTasks INNER JOIN tblTaskTime ON tblTasks.TaskID = tblTaskTime.TTLINKTask) ON tblPriorities.PrID = tblTasks.TaskPriority) ON tblContracts.ContID = tblTasks.ContractNo) ON tblBuildings.ProID = tblTasks.BuildingID WHERE (((tblPriorities.Priority) Not Like "P8*" And (tblPriorities.Priority) Not Like "P5*" And (tblPriorities.Priority) Not Like "P3*" And (tblPriorities.Priority) Not Like "P6*")) ORDER BY tblTaskTime.TTStarted;
I have a form in which the user inputs crane inspection results (a series of check boxes). When the user clicks the save button, I would like to run a query (I guess a query is how to do this) that searches records for the past seven days (less weekends and holidays) and alerts the user to days that inspection results were not entered for that particular crane (cboCrane.Value) and that particular shift (cboShift.Value) via a message box.
How do I get it to only look at records for the past 7 days (less weekends and holidays)?
Property (used for look-up values) Service Type (used for look-up values) Rate Table (uses the Property Table and Service Type Table and is used as a rate look-up table for the daily service table noted below) Daily Service
Using the Daily Service table, I need to choose a property, choose a service type that is limited to the property (100's of service types, but a property may only have a limited number), and enter a quantity, and have the Rate Table return the specific cost of the service. I didn't think this was too hard in Access (2013), but I seem to be hitting a wall and spending too much time figuring it out. Now I am behind in my project.
I have been trying to do this in a table format, but have been unsuccessful in limiting the results of the Service Type combo box based on the property chosen.
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?
The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.
How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks
Tim
Below is the code i'm using for defining Regular days and Sunday Working Days:
Private Sub LogDate_AfterUpdate() Dim rst As DAO.Recordset, rs As DAO.Recordset Dim db As DAO.Database Dim dteToday As Date, dtePrior As Date Dim i As Integer
Set db = CurrentDb Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
rst.FindFirst "[HolidayDate] = #" & dteToday & "#" 'See if the date entered is a holiday If rst.NoMatch Then Holiday.Value = 0 Else Holiday.Value = 1 End If If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if Dim strSQL As String 'it will be a regular day or a Sunday
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.
I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
I rent out a property , on the rental statement I have a field [Datepaid], [RentpaidFrom] and [rentpaidTo]. I would to have a field that can calculate the rent that is paid to show if the rent is in arrears or in Advance. I guess the code would be something like (datediff ???? etc) but i have no idea what it should be. the calculation should appear on the query as i.e.2 days in arrears or 12 days in advance etc
I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.
Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.
'*********** Code Start ************** Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer On Error GoTo Err_WorkingDays
Dim intCount As Integer
StartDate = StartDate + 1 'If you want to count the day of StartDate as the 1st day 'Comment out the line above
intCount = 0 Do While StartDate <= EndDate 'Make the above < and not <= to not count the EndDate
Select Case WeekDay(StartDate) Case Is = 1, 7 intCount = intCount Case Is = 2, 3, 4, 5, 6 intCount = intCount + 1 End Select StartDate = StartDate + 1 Loop WorkingDays = intCount
Exit_WorkingDays: Exit Function
Err_WorkingDays: Select Case Err
Case Else MsgBox Err.Description Resume Exit_WorkingDays End Select
I have a Top 10 query (it is based on $ amount) and everything works fine except...say I have 1,000 records and 5 are >$0 it shows those fine but since the other 995 records are $0 it shows "all" of them. I would like to limit the record count in the query to only 10. Within that 10 records if there are $0 records then I would like to show them up to the 10 record limit.
I see both sides I think but you would think that if you say "Top 10" it would be "10".
Hello I have an access 2003 db. I have a csv file that regularly downloads from a propietary system. This file contains about 18,000 records. Some customers have more than one record so there are about 7000 customers. I have linked this table to access to benefit from the regular download changes. About 150 of these customers are accounts I want to filter out of my db. I tried a query criteria Cust# Not "(Cust#) or (Cust#) and so on. Access will not let me have this many in my query. Is there another way?
I have built a query (Loading_query) that brings in the prefix, number and date_loaded (=null) for each rail car.
I now want to display the rail cars numbers in a drop-down but be limited by the prefix selected in another drop-down on the form.
For example:
If the prefix that was selected = GATX then the next drop-down should use "Loading_query" to only show the rail cars that are empty (according to date_loaded) and have a prefix that = GATX..... Still new at this and do not know how to use a value from the form to limit the query.
trying to think my way around this one. i am in search of a query that will allow users to see what they have ordered within the last week (7days max) which starts on monday of each week eg if they run the query on thurs, they will see mon, tues, weds & thurs and not just 7 days from thurs [Date()-7]
i'm assuminging its gonna be something like if day=monday then query will = date() else if day=tuesday then query will = date()-1 and so on.....
