Queries :: Using If Function For Query

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 Replies


ADVERTISEMENT

Queries :: Update A Query Based On Results From Another Query Using Count Function

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

Queries :: Max Function In Query By Row Not Column

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

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 8 Replies View Related

Queries :: Average Function In Query

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

Queries :: Group By Function In A Query

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

Queries :: Last Function In Totals Query

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

Queries :: Using Max Function In Aggregate Query

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

Queries :: Using Choose Function In Query

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

Queries :: Does Rnd Function Work With A Union Query

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

Queries :: Query Or Function To Update Another Field Value

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

Queries :: Search Function In Parameter Query

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

Queries :: Instr Function In Select Query

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

Queries :: Replace Function In Query With Sub Table

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

Queries :: Function To Output Line Numbers In A Query - YTD

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

Queries :: Left Join On A Query After ConcatRelated Function

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

Queries :: Undefined Function In Query And Visual Basic

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

Queries :: How To Handle Function Errors When Running A Query

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

Queries :: Query Criteria To Pull Its Value From Control On A Form - IIF Function

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

Queries :: Combining Data In Access Query Using Builder Function

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

Queries :: Execute Query That Doesn't Include Specified Expression As Aggregated Function

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

Queries :: Counting Rows In Query Groups - Invalid Argument To Function

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

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

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

CheckBox Function In Queries

May 17, 2006

I'm looking for someone that knows how to link check boxes on a form to the "show" check-box in a query. Basically, all I'm trying to do is have the end-user tell the query which fields to show in the output. If checkbox "A" is checked on a form prior to kicking off the query, I'd like field "A" to show in the query output...and so on.

Thanks, Paul

View 3 Replies View Related

Queries :: Max Function To Get Only One Number

Jul 21, 2015

How do I use the Max() function to get only one number. And also what do I put in the parentheses?

View 14 Replies View Related

Queries :: Using A Function To Set SQL Criteria

Feb 5, 2014

I am using a function to set criteria in a an query. It reads the selected values from a combobox on a form and passes the appropriate value into the sql criteria.

E.g., the sql criteria is set to : like fnCountry()

And the function fnCountry() is something like;

If SelectedCountry = "All" Then
fnCountry = "*"
else fnCountry = SelectedCountry
end if

This works fine for a single selection (SelectedCountry = Africa) but doesn't work if I try to combine multiple selections into the criteria string.

E.g. SelectedCountry = Africa Or Italy

So the criteria would need to be
Like "Africa" Or like "Italy"

How else can i build this criteria with multiple values?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved