Forms :: DatePart Function To Filter Out Sales Tax By Quarter
Apr 26, 2013
Was wondering if there is a way to incorporate the DatePart function in the below statement to filter out sales tax by quarter? I have two drop down boxes that filter the year and the state but the below only totals the tax for the year and state. I am trying to add 4 text boxes to show the quarterly break down of sales tax.
The below text box is in a form pulling the data from a query.
Text Box
=DSum("[SalesTaxCharged]","[Sales Tax Calculation Qry]","Year([Order Date])=[SelectedYear] And
[StateProv]=[SelectedState] And Not [Tax Exempt]")
View Replies
ADVERTISEMENT
Jan 19, 2007
Here's the deal, I have a table that has a flag in it. It's either a 1 or a 0.
Expr1: IIf([Is it?]=1,[REQ DATE],IIf([Is it?]=0,(DatePart("yyyy",[REQ DATE])),"ERROR"))
If it's a 1, it displays the full date. If it's zero, it's supposed to display the year of the date.
The first part works fine. If it's a 1, it shows the date. However, if it's a 0...it shows a date not even remotely close...and they're all the same. 06/28/1905.
View 2 Replies
View Related
Jun 2, 2007
I am using a DatePart function to get the week number for various dates in my project (DatePart("ww",[date],vbsunday)). Is it possible to set the firstday setting of the function (vbsunday) by referring it to a field in another form. I have tried but get an error. I am hoping to achieve this because the database will be distributed to various agencies which have different first day of the week for their schedules. I want to avoid re-writing the code for each agency. Any help would be greatly appreciated.
View 3 Replies
View Related
Jan 19, 2006
Hi,
I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.
For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"
I've tried using the datepart function i.e. datepart("mm-yyyy",[date but to no avail.... can anyone help???
Many Thanks in advance
Jason
View 1 Replies
View Related
Jan 19, 2006
Hi,
I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.
For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"
I've tried using the datepart function i.e. datepart("mm-yyyy",[date]) but to no avail.... can anyone help???
Many Thanks in advance
Jason
View 2 Replies
View Related
Nov 1, 2005
Hey all I have a I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???
View 7 Replies
View Related
Jun 5, 2014
Can Dsum function sums the sales of each customer. For example, let say we have the following query
Code:
QueryCustomerSales
Customer Name
Order ID
Item
Amount
Price
So, can i get output something like :
Code:
John 200.540 $
Mark 300.350 $
I tried the following code "
Code:
Dsum ("[Amount] * [Price]", "QueryCustomerSales", "[Customer Name]= [Customer Name]")
However, it returns the sales of all customers (all together ):
Code:
John 500.890 $
Mark 500.890 $
View 5 Replies
View Related
Jul 6, 2013
I want a very basic report that shows:* all field reps (sales people),
* their quota in terms of new customer registrations
* their actual new customer registrations for a particular month
The report should include field reps with 0 sales. The query I've created works fine as long as there's no date filter. But adding <1/1/2013 removed all field reps with 0 sales. I want to make sure the filter concept was working before getting into date ranges.
Code:
SELECT DISTINCTROW tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota, Count(tbl_customers.dateRegistration) AS NewRegistrations
FROM tbl_ksFieldRep LEFT JOIN tbl_customers ON tbl_ksFieldRep.[salesRepName] = tbl_customers.[ksFieldRep]
WHERE (((tbl_customers.dateRegistration)<#1/1/2013#))
GROUP BY tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota;
View 2 Replies
View Related
Oct 11, 2007
Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.
View 1 Replies
View Related
Oct 23, 2013
I have a pre-established database with a form that calculates total sales and tax for a time period i specify. I want to add a box that will display the number of sale records. Ex. if i had sales of 50,000.00, and that came from 200 sales i want the box to show the number 200.
View 1 Replies
View Related
Jul 4, 2013
I am trying to build a sales invoice for a pharmacy that contains a sub form in a datasheet view. The main form contains only the invoice number and date while the sub form contains the drug code, drug name, Qty., Available Qty. and Updated Qty.
Presently, I can only code the update to takes place on each line of the datasheet. I want the update to take place at the end when I must have finished inputting all drugs bought with same invoice number.
View 4 Replies
View Related
Jul 19, 2014
I need to create a production forecast form based on previous sales history.The history is based a sales and grouped by month & year
So on the form, which needs to be a continuous form, I want products to show as rows and months as columns The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.
I can write the sales history to a temp table.However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!
Attached spreadsheet shows what I am trying to achieve. Is this possible and if yes, how would I do it?
View 6 Replies
View Related
Jun 27, 2012
I have a function which I want to return the value of a field.
Public Function fieldValue(tblName As String, fldName As String)
Dim drs As Recordset
Set db = CurrentDb
Set drs = db.OpenRecordset(tblName)
fieldValue = drs.Fields(fldName).Value
drs.Close
Set drs = Nothing
End Function
I need to return the value obtained, for a specified User identified with a numeric variable.What is the best way forward? - Some sort of filter or DLookup and how to code this?
View 14 Replies
View Related
Aug 26, 2015
=Sum(IIf([Pass or Fail]="Pass" And [Pay Period]="02" And [Location]="Purple",1,0))/IIf(Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)) = 0, 1, Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)))
For [Pay Period] ="02" is there a way to make it filter by a combo box from the Mainform? This code is on a Subform the combo box is on a Mainform
Forms![Overview_]![Combo35] <the path for the combo box.
I tried inserting it where the 02 was but it just returns 0 and if I remove the "" it will error the whole thing out. Same if I try to just remove the [Pay Period]="02" and replace it fully it errors out.
View 5 Replies
View Related
Apr 10, 2007
HiI have a problem, I need to make quarter reports but they should be different from the default ones, for example first quarter should be from December, November and January.The standard quarter querry is using this format: datum By Quarter: Format$(dnevna_lista.datum;'yyyy" Q"q ')I hope it is posible :))Thanks!
View 8 Replies
View Related
Jun 15, 2013
I want to filter the records in a combo box (cboSupplier) using the In() function.
On the form I have an unbound control called "intFilter". It contains the following string: 23, 58
The SQL statement for the combo box is
Code:
SELECT SupplierID, SupplierName
FROM tblSupplier
ORDER BY SupplierName;
In the criteria for field SupplierId I want to use the In() function so that the only records returned by the combo box are those in "intFilter". I have tried
Code:
In (Forms![frmSupplierReport]![intFilter]
but this does not work.
If I put: In (23, 58) in the criteria it works, but I cannot hard code it because the string in "intFilter" will change on the fly!
How would I achieve this?
View 4 Replies
View Related
Dec 13, 2006
Have been unable to find any solutions for this in the forum or the help files in Access.
Project has a [cutindate] and an [enddate].
A [flatamount] ($$ savings amount].
I am calculating the month savings by dividing the [flatamount] by the number of months between [cutindate] and [enddate] using DatePart().
With the help of someone on the forum I have a table "MonthOffset" which just has the numbers 1-12 in the [MonthOffSet] column. Using this, I can get my query to take the monthly savings amount and disperse it to each month for the next 12 months beginning with the [cutindate]. This was my original direction. They now don't want to see it dispersed over 12 months, but rather just show savings between the [cutindate] and [enddate]. I can get the correct monthly savings in my query, but it is still showing it for 12 months, i.e. [flatamount] is $50k, monthly savings is for 5 months, $10k showing for each of 12 months. I can just use this, then make another query to only show the monthly savings between the months I need but I would like to get it done in one query.
My query:
ProjectID CutInDate SavingsMonth
1 2/1/2006 2/1/2006
1 2/1/2006 3/1/2006
1 2/1/2006 4/1/2006
Through 1/1/2007
Project ID has a cut in date of 2/1/2006 but the savings will only last until 8/1/2006.
Has anyone had to do this before?
I have tried this in the criteria of my SavingsMonth field:
[MonthOffSet] Between DatePart("m",DateValue([cutindate]) AND DatePart("m",DateValue([enddate])))
But keep getting the message: You did not enter the keyword And in the Between...And operator. The correct syntax is "expression [Not] Between value1 and value2"
Does anyone know if this can be done?
Thanks,
Toni
View 3 Replies
View Related
Oct 15, 2006
If I can get any suggestions I would appreciate it. I need to run a report on total number of calls, daily and I need to sort it by week. I used the Sorting and Grouping options and I can sort them. what I need to accomplish is to have the week start on a Monday instead of the default Sunday. When the first data of the week does not start on a Monday, the heading date of that week will be the date of when it is first recorded. So, if i have no calls on Monday or Tuesday but there are records for Wednesday, it will have Wednesday date as the heading for that week. I went through the help online for Access and it mention I can create expressions to help using the Datepart heading. But, it didn't recognize the expression. Does anyone know how to solve this problem?
View 3 Replies
View Related
Feb 22, 2013
I have a dataset in which i have a variable Bill date like this
Bill Date
1/16/2012
11/16/2012
11/16/2012
11/16/2012
11/16/2012
1/16/2012
1/16/2012
1/16/2012
1/16/2012
1/16/2012
1/16/2012
Now I want to create another variable 'Quarter' and calculate this on the basis of Date and want the output as follow
Quarter
Q1-2012
Q4-2012
Q4-2012
Q4-2012
Q4-2012
Q1-2012
Q1-2012
Q1-2012
Q1-2012
Q1-2012
Q1-2012
I was thinking of doing this with the combination of Datepart and if-elseif combinations. But as per my knowledge access donot support multiple Ifs statement and I dont want to write VBA code for the same...
View 5 Replies
View Related
Jul 1, 2013
I am having a problem trying to generate the Month of a given Quarter, I am attempting this within a Query, I have the following:
MonthOfQuarter: (Month([Order Date]) Mod 3)
This seems to work well for the first two months of each quarter, but always returns 0 (zero) for the third month.
View 3 Replies
View Related
May 15, 2013
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between]
FROM [Main Table]
WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
View 11 Replies
View Related
Aug 13, 2014
I have a continuous form based on table "INCOMES" that shows all the payments received, which mediums can be (field "PMNT_MEDIUM"):
- check
- transfer
- taxes
- cash
Table "INCOMES" is filled using another form, but in this particular form I just want to show "check", "transfer" and "cash" (not "taxes") so that I can track all the cash incomes.
Note: taxes are loaded because they appear in my invoices and I need them there to reach the invoice total amount.So my form has a search bar which allows me to search by PMNT_MEDIUM listing all "checks", all "cash" or all "transfer". I can also search by payment number (meaning: check number). To that end I have a "search" button that applies the filter. And I have another button that "cleans" the filtering by "putting a "" in the search-bar and then calling the "on click" of the search button".
What I need is, no matter if I click over the "search" or "clean" button, it NEVER shows me the "taxes".Search button, on click code:
If IsNumeric(Me.busq_chq_med) Then
Me.Filter = "[PMNT_MEDIUM_NUMB] =" & Me.SEARCH_BAR
Else
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*' or [INVOICE] like'" & Me.SEARCH_BAR & "*'"
End If
Me.FilterOn = True
Clean filter button, on click code:
[SEARCH_BAR] = ""
Call [Search button]_click
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.FilterOn = True
View 14 Replies
View Related
Sep 5, 2007
Hi
Can anyone advise on the following query problem:
I have been using the function DatePart to select records from a field of a datetime type, and had seen somewhere (an old Access refrence book?) that it is possible to use DatePart to select more than one time setting, but I have not found out if this is actually possible, or the syntax for it if it is.
In selecting a time range, I would like to use a single query (eg Between 0900 and 1730) to represent a working day, with the criteria "hn" in the datePart function. At present I have to use two selection criteria to achive this (h Between 9 and 16; h = 17 AND n <30)
Ay help/suggestions, and especially an example, would be greatly appreciated. I am using Access 2003.
View 7 Replies
View Related
Feb 18, 2008
Hi all,
I have a report set up that is derived from a query. In the query I have a DATE by Quarter field, which works just fine. I added criteria of "Between [Enter Begin Quarter] and [Enter End Quarter]"
Once I double click the report, I enter "Q1 2007" "Q4 2008" into the prompts. It correctly pulls data from each quarter, however, it sorts the data by the Q# rather than by the actual date.
Meaning I get:
Q1 2007
blah
blah
blah
Q1 2008
blah
blah
blah
Q2 2007
blah
blah
blah
And I WANT it too look like:
Q1 2007
blah
blah
blah
Q2 2007
etc
Q3 2007
etc
Q4 2007
etc
Q1 2008
etc
Any ideas on how to fix this?
View 1 Replies
View Related
Feb 3, 2007
Is there a simple formula to return the fiscal year quarter that applies to a date? I simply want January thru March to show as Qtr 1, etc.
thanks
View 6 Replies
View Related
Aug 10, 2005
I need a query that lists each day of the month (or quarter) that is selected from a form. Meaning, if a user selects June 2005 on the form, the query will return to show every date from June 1, 2005 to June 30, 2005. Is this simple, tiny little thing possible?
The reason behind this is that on a report, I need to show data for every date. If there is not data entered for that date, it still needs to appear with the date listed, but with no entries represented.
Thanks!
Tess
View 3 Replies
View Related
