Queries :: Using DateValue Function With Query
Aug 1, 2014
How do I use the DateValue function with a query that pulls data between dates? Here is my code below. I'm getting an error that my expression is too complex or incorrect.
Code:
SELECT IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))) AS LOB, Count(tblPACSTemplate.temID) AS CountOftemID, tblPACSTemplate.Type, DateValue([ApprovedDate]) AS ApproveDate
[Code] ....
View Replies
ADVERTISEMENT
Apr 23, 2013
In my QBE, I have a field call [startDate]
In my criteria, I am passing a parameter from a form.
Currently, I am using: DateValue([forms]![myForm]![txtStartDate])
From myFORM, the value of the text box is 3/1/2013
When I run the query, it runs as expected and returns all records with the 3/1/2013 dates.
But now I want to make it where if the parameter is null.
How do I change that criteria if that parameter I am passing is null from my form's textbox? I want it to return all records that have a null entry in the [startDate].
View 5 Replies
View Related
Apr 24, 2008
I have a select query with a number of expressions and I cannot seem to get the expressions to work. Access keeps giving me syntax error on the following code:
SELECT Run.Test_Case,
Sum(IIf(DateValue([Attempted_Actual]) < DateValue([Attempted_Actual]),[Points],0))/Sum(IIf(Not IsNull([Attempted_Actual]),[Points],0))*100 AS ActualAttempted,
Sum(IIf(DateValue([Completed_Actual]) < DateValue([Completed_Actual]),[Points],0))/Sum(IIf(Not IsNull([Completed_Actual]),[Points],0))*100 AS ActualCompleted,
Sum(IIf(DateValue([Verified_Actual]) < DateValue([Verified_Actual]),[Points],0))/Sum(IIf(Not IsNull([Verified_Actual]),[Points],0))*100 AS ActualClosed,
Sum(IIf(DateValue([Attempted_Planned]) < DateValue(Date()),[Points],0))/Sum(IIf(Not IsNull([Attempted_Planned]),[Points],0))*100 AS PlannedAttempted,
Sum(IIf(DateValue([Completed_Planned]) < DateValue(Date()]),[Points],0))/Sum(IIf(Not IsNull([Completed_Planned]),[Points],0))*100 AS PlannedCompleted,
Sum(IIf(DateValue([Verified_Planned]) < DateValue(Date()),[Points],0))/Sum(IIf(Not IsNull([Verified_Planned]),[Points],0))*100 AS PlannedClosed
FROM Run INNER JOIN Task ON Run.Run=Task.Group
GROUP BY Run.Test_Case;
HAVING (((Run.Test_Case)=IIf(IsNull([Forms]![Status]![ComboStatusTestCase]),[Test_Case],[Forms]![Status]![ComboStatusTestCase])));
I am sort of confused on where the error could because it just says there is a syntax error. My assumption is that I am using DateValue() in the wrong context. If anyone has any pointers or sees my error please let me know.
Thanks,
View 3 Replies
View Related
Jul 24, 2007
In Excel there is a function called Datevalue() that will return the dates computer serial number. See below example:
Formula Description (Result)
=DATEVALUE("8/22/2008") Serial number of the text date, using the 1900 date system (39682)
=DATEVALUE("22-AUG-2008") Serial number of the text date, using the 1900 date system (39682)
=DATEVALUE("2008/02/23") Serial number of the text date, using the 1900 date system (39501)
=DATEVALUE("5-JUL") Serial number of the text date, using the 1900 date system, and assuming the computer's built-in clock is set to 2008 (39634)
Is there a function in Access that will do the same thing?
View 11 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Oct 11, 2013
I have a query driving a report that fills in the on time or late based on comparing the release date to the production due date. The code works except for if we haven't released it yet and the release date is void. I need it to then check versus today's date and then populate the late/on time. But I need this to take a back seat to if both fields are populated. Here is the code I am currently using and I have attached a screen shot of the query with the 2 boxes highlighted in red.
On Time / Late Eng: IIf([Released to Prod]<=[Prod Due Date],"On Time",IIf([Released to Prod]>[Prod Due Date],"Late",""))
View 7 Replies
View Related
Apr 3, 2015
I have a table that holds the dates of various approvals for documents.
Approval DatesDocumentID
ApproverA_DateApproved
ApproverB_DateApproved
ApproverC_DateApproved
LastUpdated
In a query, I'd like there to be a column with the date of the most recent approval (max date) for each document. Using the following formula in the Expression Builder gives me the error below.
Code:
Name: Max( [Projects]![ApproverA_DateApproved],[Projects]![ApproverB_DateApproved],[Projects]![ApproverC_DateApproved])
"The expression you entered has a function containing the wrong number of arguments"
How can I get the most recent date a document was approved by any approver?
BTW - I initially thought I could just use the date the record was last updated. However, sometimes people remove their approval. This causes the LastUpdated date to be wrong (for this purpose), since it then reflects the date they removed their approval, not the most recent date that someone gave it.
View 3 Replies
View Related
Jul 5, 2013
I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard
Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status
The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon..I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013. My Dashboard needs to have the below fields
Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days
I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero.
SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days]
FROM TblWO
WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected"))
GROUP BY TblWO.[Operational* Categorization Tier 2]
HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));
View 3 Replies
View Related
Apr 8, 2014
I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.
View 2 Replies
View Related
Apr 26, 2013
I'm trying to build (a rather simple) totals-query, but the "last" function doesn't give me the right values.Suppose I have a database where the amount of products that were sold are stored per month. It's possible that some products are not sold in some months. Now, I want the LAST REAL VALUE (= amount) for each product, no matter what month :
prod1 = 4
prod2 = 3
prod3 = 3
How can I do that, because the LAST-function gives me
prod1 = (empty)
prod2 = 3
prod3 = (empty)
View 6 Replies
View Related
Mar 25, 2015
I have a list of product sales for various regions.How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?For example, If I run an aggregate query using the max function I still get the record for region:
SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region
FROM Stats
GROUP BY Stats.ProdCode, Stats.Region;
I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)
View 3 Replies
View Related
Sep 28, 2013
I have an option group that lists a variety of categories which I want to query on.
I know that the option group only returns values.
In the Option Group, On Time = 1, Over Due =2 ,,,,
I am using a Choose function in my query. That is working fine. But ... I would like to have a last choice called "All" such that when selected all data is shown (i.e., On Time, Over Due, ....).
I am using the following:
Choose ([optiongrpStatus],"On Time", "Over Due", .... ,???) as my query criteria.
Have not figured what I can put in the last choice to get all data.
Tried "Is Not Null", tried "On Time or Over Due" - know these don't make sense -just wanted to try.
View 7 Replies
View Related
Aug 29, 2013
I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);
But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA
View 9 Replies
View Related
Nov 21, 2013
Would I use an update query or function to complete my task?
Task: Automatically update [Status] based on DateDiff calculation of [RequestDate] and [DueDate]
Issue: [Status] can be one of 6 values, the user can manually select Review or Completed.
[Status] values: Review; Completed; Due in 24; Due in 24-48; Beyond 48; Over Due
Query: Below is the query I just started to ignore the calculation if either Review or Completed exist.
Expr1: IIf([Status]="Review",[Status],IIf([Status]="Completed",[Status],DateDiff('d',[RequestDate],[DueDate])))If the value from above DateDiff equals to the below, I want to update the [Status] field to the [Status] value.
Due in 24 = (0-1)
Due in 24-48 = (2-3)
Beyond 48 = (greater than or equal to 4)
Over Due= (-1)
View 14 Replies
View Related
Nov 18, 2014
I have a parameter query in which consists of a search statement as below:-
Search: [Model] & " " & [FiscalYear]
It works very well. I am wonder if there is any options available to affect the search results. As far as I understand, when I enter the key word into the pop up box, it will search the fields sequentially and populate the result. Can I enter different key word for each field? If so, what is the syntax for entering the key words.
View 2 Replies
View Related
Jun 21, 2013
I need to select the second word from a product description. Here are two examples;
2156015 Dunlop SP-30
1756514 Goodyear Sport
I found the correct code on another website to do this in Excel;
=MID([field], FIND(" ",[field],1)+1, FIND(" ",[field],FIND(" ",[field],1)+1)-(FIND(" ",[field],FIND(" ",[field],1))))
It works a treat. In order for it to work in Access you apparently change FIND to Instr.
Now this worked for the person on the other forum but when I try it, it doesn't and I get the #Error returned.
Here is what I am using. Description is obviously the field I am selecting from;
TEST: Mid([Description],InStr(" ",[Description],1)+1,InStr(" ",[Description],InStr(" ",[Description],1)+1)-(InStr(" ",[Description],InStr(" ",[Description],1))))
View 9 Replies
View Related
Feb 14, 2014
I am running a query for an apparel manufacturing facility. In my query I have a table called 'OrderForm' which is where the orders are put in. There are more than one type of fabric that can be a part of an apparel item, so as a result, there are multiple fields pulling from the 'FabricType' table. In order to get this to work in my query I created 'SubTables' for the different fabric fields. For example, I have tblFrontfab, tblbackfab, tblsleevefab, and tblcollarfab which are just extra copies of the 'FabricType' table. The actual question is that when I want to replace characters like ,./& in the fabric field but I can't use the replace function. It says that it is too complex to calculate.
I used this notation
Frontfab: Replace([tblFrontfab].[fabric],".","")
and the error was that it is to complex to evaluate.
View 1 Replies
View Related
Apr 18, 2014
Is there a built in function which can be used to create line numbers in a query?
I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?
View 1 Replies
View Related
Nov 2, 2014
I managed to produce a working Query with AllenBrowne's ConcatRelated function.What i'm unable to do is a new query with left join on the result of the query using the ConcatRelated function.I get error 3075: syntax error (missing operator) in query expression (free translation - MS ACCESS not English version)
If I do the normal join then it works fine, but I only get rows for which there are related concatenated values. But I do also need those rows where there are no concatenated values' fields.
View 1 Replies
View Related
Apr 3, 2014
I'm doing some debugging over the phone.My friend has Access 2003.What does not work:When he codes the MID function in a query he gets an error like this:"Undefined function 'Mid' in expression."
In the Visual Basic editor, he gets the same error: MID is undefined.What DOES work:I walked him through the use of LEFT and RIGHT in the query and they work.In Excel, MID works.Might there be some odd ball Access configuration mess up, or maybe more likely, his Access installation is incomplete.
View 3 Replies
View Related
Dec 3, 2014
How does one handle function errors when running a query? Specifically:
(a) I have a user-defined function that lacks any error handling code except for an Exit Function that gets triggered if there is an unacceptable input parameter (e.g. typo in a flag value).
(b) This works fine when the function is simply part of VBA code or used in the Immediate window of the VBA editor. However,
(c) when the function is used in a SQL Select Query, I get bumped into the de-bugger, and Exit from there does not get me out of the Query. It just gets me out of the current record being processed and then hangs up again on the next.
It seems, therefore, that the function needs to have some instructions that abort the entire query when the function aborts the very first time.
View 9 Replies
View Related
Aug 16, 2014
I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"",[Forms]![FormName]![FormControl])
This works for the records with fields that have data.
Code:
Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
Code:
"" Or Is Null
View 3 Replies
View Related
May 13, 2014
I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:
NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed
I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:
NameColour
CarBlue;Green;Yellow
How i would do this.
View 5 Replies
View Related
Mar 21, 2013
I am receiving this error when i try to run a query:
"You tried to execute a query that doesn't include specified expression as an aggregated function"
There are two images attached which show the SQL view, the error and the design view.
View 11 Replies
View Related
Jan 3, 2014
I have a query with about 7500 records that are grouped as follows and sorted by Mtr_Reading
ID Name Event_Start_Time _Hour, Mtr_Reading
2210 XYZ 7/15/2013 13:00 17 150
2210 XYZ 7/15/2013 13:00 14 143
2210 XYZ 7/15/2013 13:00 16 115
2210 XYZ 7/15/2013 13:00 15 110
2210 XYZ 7/15/2013 13:00 13 100
2210 XYZ 7/16/2013 12:00 12 100
2210 XYZ 7/16/2013 12:00 17 150
2210 XYZ 7/16/2013 12:00 14 147
2210 XYZ 7/16/2013 12:00 13 113
2210 XYZ 7/16/2013 12:00 18 110
2210 XYZ 7/16/2013 12:00 15 100
There are about 75 distinct sets of ID's and Names not shown here
The rows were sorted by the Mtr_Readings and we have to take the best four readings out of each group and average them. If a group has more than four rows then the top 4 are taken out and the balance is discarded. If the group has four rows or less all of the rows are taken.
I did try nested select statements and kept getting "invalid argument to function" exceptions.
View 14 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
