Expression's Within Queries?????

Jul 5, 2007

Hi People

Got another question for you all, since i'm on a mission of doing my annual upgrade of the DB.

My sales manager has asked me to implement a system where the database will flag up any customers who haven't used us for 6 months or longer.

I know i will hav to use a query and the Fields "Customer" & "Date", but cant think of the expression i would hav to use inorder for the database to do this operation.

I also want to add a check box so that, we can tick off customers who arent going to be usin us again, so that the query doesnt flag them up every month.

If any one could help that would be great!!

Thanks
gary

View Replies


ADVERTISEMENT

Queries :: IIF Expression With Three Criteria

May 14, 2015

I'm a little new to Access but I'm trying to create an IIf Expression:

iif ( condition, value_if_true, value_if_false )

Mine is: Au_ppb: IIf([Element]="Au" And [Units]="ppb",[Result],Null)

I need to have three criteria's (each looking into a different column). I can get two criterias but not three:

[Element]="Au" And [Units]="ppb"

When I add in the third (And [Priority]="12") it doesn't work .

Also, I need to have about 6 columns with similar IIF statements in the one query in my query and if a row has Null for all then I don't want it visible however if I make the Criteria "Is not Null" on one column or on all it comes up with no results.

View 2 Replies View Related

Queries :: Using A Query In Expression

Aug 17, 2014

Users add transactions via a form after they put in the total of the transaction I want to run a query that gets a running balance of the client's account to make sure that paying this transaction will not over draw the clients account.What would be the best method of approach?

View 12 Replies View Related

Expression Builders/Equations In Queries

Aug 11, 2006

Ok my goal is to have a calculation in a query that includes a bunch of addition, division, and multiplication. (It uses 10 pieces of data) I tryed typing it in the expression builder but it doesn't work. I don't know if I am setting it up wrong or putting it in the wrong spot or what. I've tried some different things and I either get a blank box in that field or I get a syntax error. Also is there any way I can put the info received by the equation into a field in my table? Thanks

View 2 Replies View Related

Functions In Queries / Expression Builder

Dec 3, 2004

Hi, I need some help using functions in queries. I can calculate simple arithmetic in calculated fields, but fail when using functions. For example I have attached a doc file showing a query. One table displays record ids, the other four have data for consecutive dates for each record. I cannot make the query calculate the standard deviation of the data for each record. What expression would I use, I know it is stdevp but when i try and create the expression i get error messages galore.

thanks

View 2 Replies View Related

Queries :: Using Total Of Field In Expression?

Oct 7, 2013

I have a Table which has a column that contains a Data (last day of the month: 8/31/2013, 9/30/2013) and also contains a Balance field.

What I am trying to accomplish is a query that will provide me with the total for the last month (Sept), and then also provide me with a Percent break down from a whole balance.

I have the following SQL for it:

SELECT ATB.FC, Sum(ATB.Bal) AS SumOfBal, (select sum([Bal]) from ATB) AS Total, Sum([Bal]/[Total]) AS [Percent]
FROM ATB
GROUP BY ATB.FC, ATB.MMDDYY
HAVING (((ATB.MMDDYY)=Date()-Day(Date())));

Right now, when I run the above, the Sum(ATB.Bal) pulls just the month of September, but the Percent is pulling a percentage of each Sept. Value against the whole Balance for the Year. So, when I sum the percent column to get a total of 100% for the month of September, I am ending up with a number less than 20%.

View 1 Replies View Related

Queries :: Using OnClick In Expression Builder?

Jan 27, 2015

I have query that filters my list in Access. The list can be filtered in different ways(like using a combobox). This is the criteria of the query for the field Title of the table Book:

Code:
Like IIf([Forms]![frmName]![cmbSearch].[OnClick];"*" & [Forms]![frmName]![txtSearchBar] & "*"; [Book]![Title])

What I want is when the cmbSearch is clicked, the list must show the Tiltes where the title of the book contains the words of the search bar. When its not clicked on that button, the list must show all the titles.

Here is the SQL of the query:

Code:
SELECT Book.ID, Book.Title FROM Book
WHERE (((Book.Title) Like IIf([Forms]![frmName]![cmbSearch].[OnClick],"*" & [Forms]![frmName]![txtSearchBar] & "*", [Book]![Title]))

The problem is the list filters whether I click on a different cmdButton or an optionButton. I dont know why [cmbSearch].[OnClick] doesnt work.

View 1 Replies View Related

Queries :: Set Decimals In Expression On Dates

Feb 19, 2014

Using Access 2007
Win 7

Total_Time: Format(nz(DateDiff("n",[Start],[End])/60))

This is my expression in a query.

I need to only show 2 decimal places in the results field.

View 6 Replies View Related

Queries :: Adding Expression As A Criteria?

Sep 8, 2014

I have a query I'm working with that finds data that changes, calculates the numbers, then prints a result in another column. The problem is that if I don't have any changes, then I have a blank result. Normally this wouldn't be a problem, but I'm making reports available to other users and would like this populated.

What seems simplest is to add an expression in the criteria field for the column that displays data after crunching numbers. Since this only has a display if something has changed, then I need an expression added if there is no data. Can I add something like an IsNull expression to display the results from another column in the same table? The data will end up being redundant, but I'm ok with that for now. the report should probably have the expression, but the column is already here in this table.

View 5 Replies View Related

Queries :: Expression To Remove TIF Extension

Mar 14, 2013

How to create an expression that removes the ".tif" extension of a file path.The data looks like this in it raw form:

J:201303080056273_ELECTRICALRETAILER_00100562 73_ELECTRICALRETAILER_001_3.tif

In my query grid in the filed row I have the following:

ImageRef: Mid([strImagePath],111,38)

which produces the following:

00056273_ELECTRICALRETAILER_001_3.tif
00056273_ELECTRICALRETAILER_001_31.tif

and so on...

What I need to do is remove the ".tif" part of the data. because of the way the path is output with regards to the tif image number, I'm having difficulty in targeting only that data that comes before the ".tif" extension.Is there a method I can use that will remove the right 4 characters and in conjunction with my expression above produce the following:

00056273_ELECTRICALRETAILER_001_3
00056273_ELECTRICALRETAILER_001_31

I've managed to work out how to get the data without the file extension ".tif" with the following expression:

ImageRef1: Left([strImagePath],Len([strImagePath])-4)

which gives me the following:

domgennt.dggroup.comglobalResourceApplication sUNIeFlowIMAGES201303080056273_ELECTRICALRET AILER_0010056273_ELECTRICALRETAILER_001_3

and

domgennt.dggroup.comglobalResourceApplication sUNIeFlowIMAGES201303080056273_ELECTRICALRET AILER_0010056273_ELECTRICALRETAILER_001_31

How can I combine the following expression with the above expression to get what I need:

Combine - Mid([strImagePath],111,38) with Left([strImagePath],Len([strImagePath])-4)

to get this result:

00056273_ELECTRICALRETAILER_001_3
00056273_ELECTRICALRETAILER_001_31

View 8 Replies View Related

Queries :: Use Expression For Field Header

Jul 22, 2014

I'm writing a group by query to transform data and need to use an expression to set a column header such as max(Date([cDate]).

View 2 Replies View Related

Queries :: JOIN Expression Not Supported

Dec 18, 2013

I am using vb.Net to connect to an access db. In access 2010 this query works fine, but in vb.Net, it errors with 'JOIN expression not supported'.

Code:
sql = "SELECT Boxes.Box, Boxes.CustRef, Boxes.Customer " &
"FROM (Requests INNER JOIN [Request Boxes] ON Requests.[Request no] = [Request Boxes].[Request no]) INNER JOIN Boxes ON [Request Boxes].Box = Boxes.Box" &
"WHERE (Requests.[Request no]) = '" & item & "' " &
"AND ((Boxes.Customer) = '" & customer2 & "'))"

View 3 Replies View Related

Queries :: Merging Two Expression Columns Into One

Oct 2, 2013

I have two column of expressions

1=(<(Now()+365)-([Service Interval]*30) Or <(Now()*365)-(([Service Interval]*2)*30))

2= (<(Now()+365)-(([Service Interval]*2)*30) Or <(Now()*365)-(([Service Interval]*2)*30))

I want to merge these two columns into one but then have two entry's not one !

And then I can sort by date in a report (i am trying to sort all service due for the customer in the next year)...

View 10 Replies View Related

Queries :: How To Optimize A Query Expression

Jan 18, 2015

how to optimize a query expression. I have the below query that determines the employees rate, based on which bracket their total income falls into. I'm concerned that I have not writting this expression in the most efficient manner.

SepEmployeeIncomeTaxRate: IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup1_Min] And [Sep_Txl_TaxGroup1_Max],[Sep_Txl_TaxGroup1_Rate],IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup2_Min] And [Sep_Txl_TaxGroup2_Max],[Sep_Txl_TaxGroup2_Rate],IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup3_Min] And [Sep_Txl_TaxGroup3_Max],[Sep_Txl_TaxGroup3_Rate],IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup4_Min] And [Sep_Txl_TaxGroup4_Max],[Sep_Txl_TaxGroup4_Rate],IIf([SepEmployeeTotalIncome] Between [Sep_Txl_TaxGroup5_Min] And [Sep_Txl_TaxGroup5_Max],[Sep_Txl_TaxGroup5_Rate],0)))))

View 5 Replies View Related

Queries :: Expression To Output Value As A Number?

Apr 17, 2014

I've created several expressions in a query to test for values in a field (one column for each value for use in a report).

The expressions output the value 1 where the test is true. I planned to sum them to establish how many times the value is true but this isn't working. I am only offered a Count and this returns the number of records. Other fields (numeric) are offering me a sum at the total line and are working as expected.

I assume the expressions are outputting the value one as text rather than as a number but I can't work out how to change this.

View 5 Replies View Related

Queries :: Sum 2 Expressions - Output With 3rd Expression

Apr 26, 2013

I'm trying to sum up 2 expressions.

1st. UsedAF: nz(DSum("AF_lengte+AF_zaagbreedte";"VRMUTSTAF";"VR ID = " & [VR].[VRID]);0)
2nd. UsedRES: nz(DSum("RES_lengte+RES_zaagbreedte";"VRRESSTAF";" VRID = " & [VR].[VRID]);0)

Now with a third expression I like to sum those 2 up.

3rd. Used: [UsedRES]+[UsedAF]

The thing I run into is when 1st has a value of 15 and 2nd a value of 0 the outcome of 3rd is 150 while it should be 15 ofcourse.

When I change the first 2 expressions to Sum (at Totals) the 3rd works properly. Yet the other 2 comes with wrong values.

View 5 Replies View Related

Queries :: Date Diff Expression

Oct 17, 2013

I am having a problem with a datediff expression.I have a query that pulls the first date and the last date out of a list but are put in seperate fields. I thought I could build an expression for datediff to calculate the number of dates between them. I can't just put in the dates because they are constently changing.The expression I have is:

[datediff("d",[min/max edging unit #2]![firstofupdate/time by month],[min/max edging unit #2]![lastofupdate/time by month])]

View 4 Replies View Related

Text Box Expression Builder With Queries

Jan 9, 2012

I know how to do this in VB.NET ... I have a form with a combo box. I would like to create an "on change" event where it updates the text boxes on that same form depending on what's been selected in the combo box.

I don't know how to change the combo box's selected index so it defaults to the first item so there's no blanks/errors in my form.

So when the combobox contents is changed, I'd like a routine that updates all the text boxes kind of like this:

sub update()
TextBox = "SELECT * FROM Table_Name WHERE ID =" & Combobox.text
end sub

View 7 Replies View Related

Queries :: Return A Value Of 0 In Expression Yields A Null Value

Aug 14, 2014

I have an expression in my query and i'd like to return a value of 0 is the expression yields a null value. Here is the expression i have:

IsNull(Sum(([qryTime].[hours]*[Rate])+([qryTime].[minutes]*[Rate]/60)+([qryTime].[seconds]*[qryTime].[Rate]/3600),0)

I'm getting an error "the expression you entered has a function containing the wrong number of arguments". How can i resolve this?

View 14 Replies View Related

Queries :: Expression Builder Calculated Yes / No On Dates

May 8, 2013

I having a problem with the expression builder in a table.I got a lot of fields with dates and I what a calculated field so I can see if any of the dates are newer than 7 days.I have been trying this formula:

IIf([Field1]or[Field2]or[Field3]<"Date() -7";true;false)

But it don't seen to work.

View 1 Replies View Related

Queries :: Expression Or SQL To Count Query Columns?

Jan 7, 2014

I'm looking for an expression or SQL for use in a query that will count the number of columns in another query. I do not need to count the records, I just need to know how many columns. I can't seem to locate a reference to a column count - everything points me to record count.

View 13 Replies View Related

Queries :: Numeric Expression May Contain Too Many Complicated Elements

Oct 31, 2014

Once again I am stuck. Basically I have a query as followed:Current: Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=30,[dbo_GD-AgedDebtors]![Outstanding],0))I'm trying to show outstanding bills if the bill date is less than or equal to my date parameter. However, it keeps coming up "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)"

View 12 Replies View Related

Queries :: Copy Result Of Expression In A Field

Dec 5, 2013

I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.

exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)

"exp4" is the clean name of the song.

Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?

View 2 Replies View Related

Queries :: Correct Syntax / Expression For Log Of Number

Mar 23, 2013

Correct syntax/expression for log of a number. The number i am getting in Access is twice as big as the correct number i get in excell

View 3 Replies View Related

Queries :: Format Expression Field As Currency

Jun 4, 2014

I have this expression I created and I want to know how to format it as currency. I know how to do it with a regular field but the option is not available for an expression.

The expression:

Code : Charge: IIf([Rate] Is Not Null,[Rate],"0.00")

View 1 Replies View Related

Queries :: Expression Summing From Fields With Dates

Aug 3, 2015

I am involved in centrally managing a project , a part of which involves employees of certain companies being given a weekly subsidy for study on academic courses. I have successfully set up a table for all the learners and a related table for companies.

Each learner will receive a fixed weekly rate subsidy in the first year, and a lower fixed weekly rate subsidy in the second year. Somebody (not me) will need to check their paychecks to ensure that employees have been paid and all is above board. The learners/employees can start on the programme at any time. I need a way to track this (total claimed amounts, where they're up to etc).

My theoretical solution, is to have a field for each week a learner is on the programme (52 first year, 52 second), and instead of entering a currency amount in the field, the check-person can enter the date they saw the evidence and are happy with it. I thought then if I create a query that searches any dates that fall within the current claim period (eg >01/01/2015, <=Date()), and multiplies field count that fall within those parameters by first/second year subsidy rate if they're in that table. I think my logic is sound, I am just not sure how to write the query/expression, or if it's possible, or if there is a much simpler way to do it.

View 4 Replies View Related







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