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 RepliesI have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
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)))))
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 RelatedI need to figure out the amount of days between two dates that do not exist in my database...I use this calculation to do waivers...I was able to build a table and put in the two dates and then build a query which calculates the days between the two but I wanted to know if I could build a query that prompts on a [start date] which I would enter and then prompts on an end date [end date] which I would enter to get the number of days between...is that possible?
I had...
WaiverDays: [Enter start date]-[Enter end date] but it did not let me run...I added () and it still did not run...
I am creating a database to analyze prices. I have 2 tables. One table has a bunch of dates, product names and the prices. There are many of the same dates and many of the same products. I.E. a product could get priced one day and than have a different price the next day.The other table has a bunch of information and dates and product names also. The goal is to add a week to the dates in this table and use that date and product name to look up the corresponding price for that date and product and add it to the record.
I am trying to use Dlookup but evey record is then filled with the same price. I am using this Expr1: DLookUp("Price","Qry_Historic_Price","[Date]=#[End Period]# And primary_id ='[primary_id']")...When I run the query I get a message that says "Syntax error in query expression" than a message box that pops and says "Unknown" with only "ok" to click.
Access and receive the following message after trying to run a query: Syntax error (comma) in query expression, followed by the formula I wrote on the Query Builder. I use 4 tables to run the query, but only need to trim some stuff from one of them. This table is called BD_lamosa_corregida and have already selected Expression on the Total row in Design View. This is the formula:
parte: Trim(IIf(IIf(IsError(InStr(1,[Parte],"(",1)),"",InStr(1,[Parte],"(",1))="",[Parte],Left([Parte],IIf(IsError(InStr(1,[Parte],"(",1)),"",InStr(1,[Parte],"(",1))-1)))
The formula is trying to trim the left side of an expression (Part description) which may contain a code number in parenthesis or not, it might also have this parenthesis separated by a space or not.
I have an expression that sets an AI Buy It Now value that is 125% of AI Value and rounds it, which follows AI Buy It Now: Round(([AI Value]*1.25)/10)*10
I need to add to this expression that I only want this calculation applied if AI Values are greater than 999. But I can't figure out how to add this stipulation to the above expression. I have tried IIF but it is not working.
I have a field created in a query expression
BirthMon: Format([DOB],"mmmm")
I would like to create another field in the query using DLookup to return the value of the BrithMon.
BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")
This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'
Can I used DLookUp in a Query expression to refer to another Query created field?
I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4%
90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
I'm trying to run a very basic iif statement to correct hourly data for sorting. Basically, a trading day runs from 8am - 8am, so I need to adjust the hours to ensure that 1am on the 15th trading day (really the 16th on the calendar), comes after 9am on the 15th trading day (which will actually be the 15th on the calendar).
Here's what I've used. It's driving me bananas, because it keeps telling me that there's a syntax error (comma) in the query expression, but I can't understand why?
Sort2: IIf([DELIVERY_HOUR]<8,[DELIVERY_HOUR]+24,[DELIVERY_HOUR])
I am trying to get a count of the unique customers in an access 2010 database
After some research it seems i should be using
SELECT Count(Distinct [Customer]) FROM [tblMain];
But when i use this i get
Syntax error (missing operator) in query expression 'Count(Distinct Customer)'.
I have tried leaving out the square brackets but this does not work....
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.
I am trying to get Average If function to access sql. I have columns Period and Costs_Per_Capita, result should be like like this:
Costs_Per_Capita Period CALCULATED_Period_Avg_Costs
15,505 1 15976.27582
16,368 1 15976.27582
16,037 1 15976.27582
15,995 1 15976.27582
15,000 2 16000
17,000 2 16000
I tried:
SELECT Costs.Costs_Per_Capita, Costs.Period
IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1),
(Select AVG(Costs_Per_Capita) From Costs Where Period = 2)
AS result
FROM Costs;
But get "syntax error (missing operator) in a query expression ..."
I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.
Here's what it looks like in design view:
Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));
I get the error: data type mismatch in criteria expression when I run it.
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
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.
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 RelatedHi, 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
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%.
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.
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.
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.
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
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 RelatedI 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 & "'))"