i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.
in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))
how to do it in access query.
Hi does any body no the access syntax to create this excel formula
=POWER(SQRT(L5/10)*47.1,2)/3769.9
Into access
Thanks
John
Access 2003
Excel 2003
I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?
This is the formula I am trying to pass to the "AZ" column of the Data tab
Code:
MyCalc::"IF(T2="","0",TODAY()-T2)"
=TEXT(TRIM(MID($A2,5,2) &" "&LEFT($A2,3)&" "&MID($A2,8,10)&" "&RIGHT($A2,2))+0,"dd/mm/yyyy hh:mm:ss ")
Is it possible to replicate the above formula from Excel into criteria in an Access Query.
Assume the cell reference $A2 is a text field which has customized date info in it but is not a recognized date format.
I'm trying to work out the difference between 2 records both of which have a call out date [bas start date]..basically the structure is
equipment number call number bas start date
12345678 112255 1/7/14
12345678 112256 3/7/14
What i'm after is the 4th column to work out the date diff... in this case 2 days the equipment can be multiple times so i might see equipment number 12345678 - upto 10 times with consecutive dates - all of which i need to know the difference between the current call date and the previous call date..
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
View 2 Replies View RelatedNeed help with basic formula.
In column A, I have a list of dates going down the page.
In column B, I have the number of hours worked on each day.
I need a formula that will give me a total (sum) from column B for only the last 30 days as well as 90 and 365 days.
Thanks
Greetings,
This is my first try at asking for help on the net. I hope I do it correctly.
I have an Excel spreadsheet that has three columns. One column has numbers and two have text. I have about 15,000 entries (records).
The number column is the week of the year. It contains the WEEKNUM formula Sun-Sat (1 through 52 based on the date)
One text column contains event descriptions. One text column contains names.
What I want is a count of each description for each of the 52 weeks. (i.e. week 36 there were 120 "traffic accidents", 300 "traffic citations", 13 "assaults", 1 "armed "robbery", etc. etc.. I have been surfing and tried every formula that seems to say it will do this but nothing works.
I designed a special spreadsheet that looks at the "master" spreadsheet for the data and I had hoped would keep a running total on all descriptions for each week. I am self taught in Excel and need a no brainer. And yes, I now know excel is not a data base program.
Jim the data guy
Hi,
Sorry to be a pain but I have not used this in a while and need some assistance - eg
I need to create an If And statement to formulate in bonus paid (Cell B8) - if number of new customers greater than 5 (cell B2), and the sales value (cell B5) is greater than 100,000, then return the message, "bonus paid" otherwise return "no bonus"
Can any one help?
Thankyou
I have an excel worksheet with the following formula
=IF(b40<50000,b40*0%,IF(b40<100000,b40*2.5%,b40*5%))
and I would like to convert it for use in access.
I came up with the following:
=iif([TOTAL]<50000,[TOTAL]*0%,IIF([TOTAL]<100000,[TOTAL]*2.5%,[TOTAL]*5%))
I get the following error: "The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it with quotation marks."
I am probably missing something very simple here, but I have been unable to come up with a solution.
Any help would be greatly appreciated.
Regards,
Michael
In my worksheet there are 3 columns; 1. Date Completed (J), 2. New Review Date(K) and 3. Status(L)
I'm currently migrating from excel to access, the finishing tounces are needed, like the status.
The following excel function I want to convert to access
=IF(K1="","Outstanding",IF(K1<TODAY(),"Outstanding","Complete"))
(K1= Next Review Date)
I did do a search, but I couldn't really find anything.
How do I convert this please?
I have 5 fields(First,Second,Third,Sum,Sum1). The Sum field is the sum of the first, second and third. In excel I am using the following formula/expression to calculate the Sum1 field =IF(E1<0,E1+10,E1+0 - and it brings back single digit.
e.g.
First=3, Second=5, Third=8, Sum=16, Sum=6
Can I do this in an Access query, if so how?
Thanks in advance for any help.
Really need help on this one, here is an excel expression:
X Multipliers
$ ValueMultiplier
is less than5then *3.8
is less than50*3.2
is less than100*2.8
is less than200*2.4
is less than500*2
is greater than500*1.8
so if price is less then 5 then multiply by 3.8 and so on. In excel it's in a separate worksheet, and other worksheets linked to it and adjust their Sale Price from the Cost column. Someone else created this spreadsheet, and i couldn't find a multiple discount help in access. I tried creating a query
=([PriceBook_OLD].[Cost]<=5)*3.8 Or ([PriceBook_OLD].[Cost]<=50)*3.2 Or ([PriceBook_OLD].[Cost]<=100)*2.8 Or ([PriceBook_OLD].[Cost]<=200)*2.4 Or ([PriceBook_OLD].[Cost]<=500)*2 Or ([PriceBook_OLD].[Cost]>500)*1.8
but it doesn't work. Please help!!!!
Thank you very much
V.
I have a report exporting to excel using late binding techniques. When exported into excel i have numbers for 1,2,3,4 tblPreSiteSurveys. PreSiteSurvey Stop TheClockReason entitie and I am trying to either change the numbers here casting from int to string
1 = a
2 = b
looping through the record set, Or i have a blank field in my SQL for the column "P" and adding the formular to that column, but it only goes into the first row of the record set, which is a expandable table.
On Error GoTo Command29_Click_Err
'Utilergy Master Update report
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim UserDate As Date
[code]...
Is there a way to replicate NETWORKDAYS formula in Excel to Ms Access?
View 2 Replies View RelatedPRODUCT
VALUE A
VALUE B
WW
10
100
XX
20
200
YY
30
300
ZZ
40
400
I have above table in access. I want to use this table in query to find value of any product using a formula (Value A*X)+Value B. The product and X query picks up from another table. The same can be done easily in Excel using Index, Match formula but how in Access.
I am calculating the check digit for a 13 digit number in Excel and I need to be able to do it in Access.
My Excel formula; =1&E1&MOD(-SUM(MID(1&E1&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14}, 1)*{3;1}),10) -- cell E1 holds the base 12 digit number.
My attempt to convert to Access; =1 & [txtBase] & [-SUM(MID("1" & [txtBase] &0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}) mod 10] -- txtBase holds the base 12 digit number
I am using data from a sales and a finance system and essentially need to understand the difference between them and account for this "balancing" row.
My data is as follows
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, A, F123, 100, 1000, 10
January, A, F123, 800, 8000, 80
January, B, F123, 10, 100, 1
January, A, F124, 500, 10000, 1000
February, A, F123, 100, 1000, 10
February, A, F123, 800, 8000, 80
February, B, F123, 10, 100, 1
February, A, F124, 500, 10000, 1000
TBL_Finance
Month, Factory, Quantity, Sales, Margin
January, F123, 1000, 10000, 100
January, F124, 550, 10500, 1000
February, F123, 950, 9500, 95
February, F124, 600, 10000, 1000
I would like a query which would look at the difference between TBL_Sales and TBL_Finance and will then add rows to TBL_Sales to make them balance. In the example above I would want it to add the following (I've used C as a Product_Type to show that it's a manually entered value) -
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, C, F123, 90, 900, 9
January, C, F124, 50, 500, 0
February, C, F123, 40, 400, 4
February, C, F124, 100, 0, 0
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.
I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
How can this problem be rectified?
I am trying to convert an excel formula into the expression builder on my form.
the excel formula is this:
IF(AND(E4>=2.65,F4<1.85),"Poor",IF(AND(E4<2.65,F4< 1.85),"Unacceptable",IF(AND(E4>=2.65,F4>=1.85,F4<2 .65),"Generally",IF(AND(E4<2.65,E4>=1.85,F4<2.65,F 4>=1.85),"Poor",IF(AND(E4<1.85,F4<2.65,F4>=1.85)," Unacceptable",IF(AND(E4>=3.45,F4<3.45,F4>=2.65),"H igh",IF(AND(E4>=2.65,E4<3.45,F4<3.45,F4>=2.65),
[Code] ....
In the expression builder I put this:
= IF(AND( [Objective Total] >=2.65, [KSF Totals] <1.85),"Poor",IF(AND( [Objective Total] <2.65, [KSF Totals] <1.85),"Unacceptable",IF(AND( [Objective Total] >=2.65, [KSF Totals] >=1.85, [KSF Totals] <2.65),"Generally",IF(AND( [Objective Total] <2.65, [Objective Total] >=1.85, [KSF Totals] <2.65, [KSF Totals] >=1.85),"Poor",
[Code] ....
It gives me an error 'expression you entered contains invalid syntax, you may have entered a comma without a preceding value.'
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
View 1 Replies View RelatedNever tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rates.
For example:
Usage up to the first 500KHW is billed at .067 per KWH
Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH
Usage from 1000 up is billed at .0318
So if my usage was 1200 KWH...
((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86
I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?
I have four fields
Date Sent (date) Time Sent (Time) Date Recvd (date) Time Rcvd (time)
I want to know how many hours have elapsed between date and time sent and date and time rcvd
Any Ideas please as I am having a blonde moment
I'm trying to figure out how many day are between now and a date on my form.
I've done a search, but I can't find much that talks about DateDiff().
Here is what I have so far and it's not working. I'm typing this value into a text box on a form.
=DateDiff(Day,Now(),"ASSIGNED_DATE_TIME")
I want the interval to be in days.
Thanks.
I'm trying to find the difference between 2 dates [Start_Date] and [End_Date], however I want to strip out out of hours work, which is [OOH_START] eg, 17:30 and [OOH_END], eg, 08:00. Also I need to strip out the weekends as well.
Can you help?
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?