Queries :: Incorrect Year In Query?
May 19, 2014
i have a query that pulls a read-only SQL table, i have check in that table and the date field is set up as a Date/Time type, however the db was set up with the date and time in the same field. (1/1/2014 9:00:00 AM)
I have set up the SQL code below to pull out just the Date and not the time. however when i enter my criteria on the form, only the correct month and days load, it pulls in all other years with that month/day.
my Access level - on the lower end of medium experienced.
SELECT IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," "))) AS [Date], IIf(InStr([dbo_Rides]![ApptDatetime]," "),Mid([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")+1)) AS [Time]
FROM dbo_Rides
WHERE (((IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")))) Between [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtFromDate] And [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtToDate]));
View Replies
ADVERTISEMENT
Apr 26, 2013
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
qdf.Parameters(0) = Forms!frmmain!dt1.Value
qdf.Parameters(1) = Forms!frmmain!dt2.Value
qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
View 3 Replies
View Related
Jul 31, 2013
I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business
In this query, I am trying to use parameters to filter the results based on these three fields i.e.
true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.
false, true, false would return all records where only parent is true, and the other fields do not matter.
View 2 Replies
View Related
Aug 16, 2015
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
Using Access 2013
View 2 Replies
View Related
Jul 3, 2014
I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different.
I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014
View 6 Replies
View Related
Feb 3, 2014
I'm trying to create a query to show me records for a given year. The issue I'm having is that each record has (4) dates fields and each record can contain null values.Is it possible to do this in a query with data like the example below?
Field 1 ID (1), Field 2 Date (12/22/2012), Field 3 Date (2/06/2013), Field 3 Date (Null), Field 4 Date (Null)
In this example I would want 2013 data but would need to be able to search any year. I could also have dates in all (4) fields
View 3 Replies
View Related
Jan 23, 2014
I'm trying to filter an append query by a year selected in a combo box [cboYear] for a field [ProgramDate]. The AfterUpdate on the cbo filters the append query based off of the selection (or selections-I'm using multiple combo boxes on the form). I then run a report based off of the appended table for a report.
I set my criteria for [ProgramDate] the query to be:
Code:
Year([ProgramDate])=[Forms]![frm_rpt_Programs]![cboYear]
The above code isn't working, even when I just try to run the query while the form is open. It's still returning all records.
View 2 Replies
View Related
Feb 5, 2015
Using Access 07
This is my query
SELECT QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation, Sum(QryReserveTotals.SumOfAddtlAllocationAmt) AS SumOfSumOfAddtlAllocationAmt
FROM QrySiteAlloc LEFT JOIN QryReserveTotals ON QrySiteAlloc.Site = QryReserveTotals.Site
GROUP BY QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation;
In the QryReserveTotals is a datereceived field.
I need the year of the date received field to match the QrySiteAlloc.AllocYr that will be selected by a parameter..
what would be the criteria to achieve this?
View 3 Replies
View Related
Feb 28, 2014
I analyse things by the date they are ordered by the office.
The date gets put into a spreadsheet and i import this into access for the querying.
I need to summarise it by weeknumber and year.
I use the following queries:
To get the week: Week Number: DatePart("ww",[DateOrdered],0,2)
To get the year: Year: Year([DateOrdered])
To put it together: Weekyear: [Year] & "_" & [Week Number]
The problem is when the year changes.
The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.
Therefore when the year and week number is put together the following results are given for that particular week:
DateOrdered - Result of above query
30/12/2013 - 2013_1
31/12/2013 - 2013_1
01/01/2014 - 2014_1
02/01/2014 - 2014_1
03/01/2014 - 2014_1
04/01/2014 - 2014_1
05/01/2014 - 2014_1
All of the above should all read 2014_1 but some obviously are 2013_1. This causes me problems when doing further analysis and cross tabulations.
How can I amend my query so that the above all show the correct year and week number: 2014_1 ?
View 9 Replies
View Related
Dec 11, 2014
I have a POLEFFDATE field with dates in date/time format. A single record has POLEFFDATE equal to 12/15/2013. I calculated a simple field called EVALUATE as follows: IIF([POLEFFDATE]< 1/1/2014,1,0). EVALUATE should equal 1 for this record, but it equals 0. Why ?
View 3 Replies
View Related
Feb 18, 2015
I'm making a query that has the following Fields
Product (Table PDZRN1)
Whse (Table PDZRN1)
Description (Table PDZRN1)
On Hand (Table PDZRN1)
Unit Cost (Table PDZRN1)
Unit Price (Table PDZRN1)
LastDate: Date (Table Sales_History) (using the Max function)
Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:
Field - FY2013Sales: Sales
Table - Sales_History
Total - Sum
Criteria - <= 04/01/2012 and <= 03/31/2013
how to link my criteria back to the "Date" field, or if I'm even going down the write path.
View 14 Replies
View Related
Jul 1, 2014
This is my expression to format crosstab query by month
Expr1: Format([Recharge_Date];Month([Recharge_Date]))
but i can't make it with month and year....
View 2 Replies
View Related
Mar 4, 2014
I am trying to create a query which will return records which are from a specific month of the previous year...
To better explain:
If I have sales from 2013 and 2014 in Jan, I want a query which will show me the results from Jan in 2013 only... I'm just unsure on how to make a query do this and it's probably really simple.
But then in 2015 I will want it to show results from 2014 without having to change the expression or criteria.
View 4 Replies
View Related
Apr 3, 2013
My question is that I wish to have a query that looks up on a name in a form (No problem got this bit) and also the Date field from the same form so that from the date :-
example 24/03/2013 only cares about the Month and Year so would look up only March in 2013
The end result would be to supply me with a number (using count) of how many times this persons name has occurred within the calendar month and year of the date supplied in the form
View 1 Replies
View Related
Dec 16, 2014
I'm working on a database to track our program's performance under different grants. Services performed for each grant would be entered on a quarterly basis. I am tracking the number completed for each service during the quarterly period.
I want to create a report that shows progress against benchmarks depending on the most recent quarter completed. Rather than fooling with dates, I put a field in the table where the data is entered for the number of the quarter in which the service was performed. The field, 'Quarter', holds numerical values 1-4 corresponding with the quarter. The table holds entries from different projects, distinguished by the field 'GrantID'.
There will be more than one service entered for each GrantID.
I would like to be able to identify the most recent quarter in which services were performed for each GrantID.
I have tried using the MAX function in a query but it seems to be adding "1" to the highest quarter number in the field for each GrantID. The code is:
SELECT DevEntryQ.GrantID, Max(DevEntryQ.Quarter) AS MaxOfQuarter
FROM DevEntryQ
GROUP BY DevEntryQ.GrantID;
With this query, if the highest quarter number entered in the table for GrantID1 is 3, the query returns "4".
In Excel, I would use an array formula: = {MAX(IF(Table1[GrantID]=GrantID, Table1[Quarter])}.
I want to use the most recent quarter to retrieve that quarter's benchmarks and show the progress toward the benchmark rather than the overall progress toward the goal for that grant and service.
View 2 Replies
View Related
Dec 31, 2013
I am trying to split the year into thirds instead of the quarter. I will be needing to set the criteria to only show me the previous year from the current year. Can this be done in one query?
View 6 Replies
View Related
Jul 18, 2013
all using access 2010. I have a date field. I need to write a query to pull out dates with current year only. ex data: 1/1/10, 1/1/11, 2/1/12, 2/1/13. Need to pull all dates with current year which would be 2/1/13. Tried in query criteria of the date field: =Format(Now(),"yyyy") I received data mismatch. this is a date/time field.
View 4 Replies
View Related
Aug 5, 2014
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code:
SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));
and the Aggregate query:
Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
[code]....
I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.
View 8 Replies
View Related
Jul 24, 2014
I have a form (frmMetrics) with 2 Combo Boxes: "Year" and "Quarter" These are just number fields (which might be my problem?)
I have a date/time field in a query, and I want the criteria to be based off the year and quarter selected in frmMetrics.
View 2 Replies
View Related
Mar 4, 2015
I am trying to have a query sort out my invoices by year but also to have the possibility to show all invoices.
I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.
On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.
My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :
IIf([Forms]![Invoice Listing]![Combo957]="ALL",([Invoice].[Year])<Year(Date()),[Forms]![Invoice Listing]![Combo957])
If I select the year from the combo, it works, if I select "ALL" nothing is shown.
here is the full SQL
SELECT DISTINCTROW Companies.Company, Companies.City, Companies.Country, Sales.Brand, Sales.Type, Sales.Date, Sales.QuoteNo, Sales.Delivered, Sales.Account, Sales.Branch, Invoice.InvoiceNumber, Invoice.InvoiceDate, Invoice.CustOrderNumber, Invoice.PaymentTerms, Invoice.DueDate, Invoice.NetAmount, Invoice.TotalAmount, Invoice.Tax, Invoice.PaiementRCVD, Invoice.Overdue, Invoice.Month, Year([Invoice]![DueDate]) AS DueYear, [Invoice]![month] & " - " & [Invoice]![Year] AS InvoiceMonth
[Code] .....
View 5 Replies
View Related
Mar 20, 2015
I suspect my design is flawed
Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?
View 4 Replies
View Related
Nov 1, 2005
SELECT [FA Ctr], [SAP Co], [SAP Ctr], [GL Co],
Format (([SAP Co],"0000") AS NewField), (([cst ctr], "0000000000") as costcenterappended) INTO [Interim Table]
FROM Asset_Map;
View 1 Replies
View Related
Nov 23, 2007
Hi all
I have a query field doing a simple calculation: 874*(18,3/55,65*0,0592)
Access calculates this to: 17,0144948838454
Excel as well as my own calculator gets: 17,0144948787062
All table fields is defined as double.
If anyone can help me with an explanation as to why access doesn't seem to get this simple calculation right, I would be most thankful.
View 4 Replies
View Related
Feb 25, 2008
Thought this thing was working great.Seems this is happening:If the sum of credits exceed the sum of charges, the query doubles the sum of charges. The query-SELECT TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, nz([SumOfCharges],0)-nz([SumofCredits],0) AS RunBalance, TblCustInfo.HerbieFROM (TblCustInfo LEFT JOIN TblCharges ON TblCustInfo.CID=TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID=TblPayments.CIDGROUP BY TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, TblCustInfo.Cancel, TblCustInfo.HerbieHAVING (((TblCustInfo.Cancel)="n"));When I run the query, the SumofCharges calc is the culprit since it shows in that column. I can't dup it in the SumOfCredits, but I would suspect it will also do it somewhere down the line since they are virtually the same.HELP !!!!
View 14 Replies
View Related
Feb 16, 2007
Hi,
I am using
Total_Time: Format([totaltime_mins]/60,"00") & ":" & Format([Totaltime_mins] Mod 60,"00")
To display the time as hours and minutes but this formula doesn't always work correctly.
for instance if the totaltime_mins = 90 then total_time is displayed as 2:30
also if totaltime_mins = 40 then thotal_time is displayed as 1:40
does anybody know why this is not working correctly ?
kind regards,
jamie
View 2 Replies
View Related
Apr 5, 2006
I always have a problem getting this to work without a lot of effort and I know it should be easier than I make it out to be. Problem is say I have a bunch of records I need to summarize by month. Say Dec 05, Jan 06, Feb 06, etc. When I bring down the date col, I do something like:
Format([date],"mmm - yy")
And do a totals query with a second col that returns say a count for each period. This works fine except that when I sort I get Dec 05, Feb 06, Jan 06, etc.
How can I do this with out having to create a third col to do the sorting stuff? Should I somehow wait until the data gets to the report to do the date format stuff???
:confused: :confused:
View 1 Replies
View Related
