Queries :: Include Date Criteria In User Defined Function That Calculate End Of Current Month
Jul 22, 2014
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
Is there a way to take today's date and calculate the last day of the last month? Without the user needing to enter any parameters. In other words, if I ran the query today with this criteria, it would only show information for April 30, 2013. Is that possible?
Looking to have a count function which calculate data in sense like if records found on 1-jan-2014 the it give answer as 1, same as records for 2-jan-2014 it should return 2 and so on .. in short the criteria must look and give same number for same dates starting from 1
I have created user defined function for lastdayofweek, lastdayofmonth, and firstdayofmonth. When I apply this function to an expression in my query, it returns a julian date. How do I apply the format for the calendar date?
Maybe I am not calling my function correctly. Here's my functions below as well as my expression..
Function LastDayThisMonth() LastDayThisMonth = DateSerial(Year(Date), Month(Date) + 1, 0) End Function
Function FirstDayThisMonth() FirstDayThisMonth = DateSerial(Year(Date), Month(Date), 1) End Function
I have a a table that stores various financial information such as sales receipt totals and variance totals (if actual cash in drawer did not match receipts, etc...) that I use to track cashier performance and identify possible problems. Part of this process includes a query that I pull reports against.
One such query, simplified to illustrate the concept, lists the dollar total that their receipts indicate they made, and the dollar amount that their actual drawer was off (either short or over what they should have taken in.) In this query I added fields that total Netsales (calculated from the first two fields) and another that calculates the percentage the variance is compared to their NetSales. The SQL behind the query is as follows:
Code:
SELECT tbl_OSRImport.Receipts, tbl_OSRImport.OverShort, [Receipts]-[OverShort] AS NetSales, [OverShort]/[NetSales] AS VarPerc FROM tbl_OSRImport WHERE (((tbl_OSRImport.OverShort)<>0) AND (([Receipts]-[OverShort])<>0));
This query works just fine. The calculated fields correctly display their results. The issue presents itself when I try to build a method for a user to run a report to see all the cashiers whose Varience Percentage (VarPerc) is equal to or within a range they specify. This allows the user to see all the cashiers who, for example, are more that 5% over or short. I have tried a number of criteria expressions in the query, with no success. I have gotten everything from a prompt asking me for paremeters to an error stating "Stack Overflow." I believe the problem has something to do with the fact that the numerical value that is calculated is a long string of numbers ending in letters and characters, which the Query displays as a neat and tidy Percentage. Below is an example of the data that I hope will explain this:
In the Query, the expression is: VarPerc: [OverShort]/[NetSales]
When the Query runs, the full numerical result is: -4.27103159497526E-02
Which visually is output as: -4.72%
Mathmatically (on a calculator using the same values) the equation is as follows:
-11.22 / 262.70 = -0.04271032
I think my attempts are failing becaue the query is trying to compare the user's input of (for example) 5, .5, .05, etc.... against the numerical value in the query result that includes the E-02 (above example.) So, rightfully it comes back with no results or an error.
Query that I have built to create a subform on one of my forms. It's my goal to make the subform easily navigable/query-able for the users, and that is where I've hit a roadblock. The subform contains a field - Balance - which I would like users to be able to search based on numeric/mathematic expressions (i.e. >0 and <40). In testing I have created a text box on the main form (BalanceCriteria), and linked it to the subform's balance field through the query in the Criteria field (forms!MainForm!BalanceCriteria).
This works fine with exact numbers - entering 19 will return client's with a balance of 19 - but returns an error - "Expression is typed incorrectly or is too complex to be evaluated" when tested with a numeric equation (>0).
Im stuck! I am pretty much a novice when it comes to access and have been given the task or creating some reports to stop us doing it manually.
Within a query, I have a date column which we would currently enter the following into the criteria:
Between #01/01/2008# And #31/01/2008#
for the month of January.
What i want to be able to do (and i know is possible as ive done it in the past but cant remember how) is have a pop up when the query is ran to enter the Start date and End date. I remember it being something to do with square brackets but cant get it to work.
I need to Criteria between currently Month and Next Month. (we are in April because of this im giving this example : 01.04.2015-31.05.2015) .I tried this code but its not working :
I want to build a query that calculates the fiscal year and the month from a date on-the-fly. I tried to do it in VBA, but it's more complicated than in query I guess. So this is my table:
The date is on the left, in the middle I want to have calculated Jan 14 and on the right I want 14/15.
In VBA I started with this: strMonth = MonthName(Month(A), True) & " " & Format(A, "YY") which gave me "Jan 08". But the date was hard-coded and not from my table. Then I tried to store the information by SQL statement into a string. But this also didn't work, the types were different.
Code: UPDATE tblSAPOD SET sapOD_month = Year([sapOD_OrderDate])-IIf([sapOD_OrderDate]< DateSerial(Year([sapOD_OrderDate]),6,16),1,0) WHERE sapOD_OrderDate Like "*/*/94";
.. but I guess it's wrong, it gives me a syntax error.
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
I have used Allen Browne's code (see http://allenbrowne.com/ser-16.html, bottom of page) as a model for my user-defined type and public function in an events management database.
Using the function in forms/VBA works fine.
But my DB crashes whenever I try to use it in a query.
My modules code: = = = Option Compare Database
Public Type EvCheks EvType As String EvAttCat As String EvUnitAss As String EvCheksAll As String End Type = = Public Function getEvCheks(EV, EvUnit) as EvCheks
'Event Type: Event or DL If (DLookup("evtype", "tblevents", "[evid] = " & EV)) = "Event" Then getEvCheks.EvType = "Y" Else: getEvCheks.EvType = "N" End If
'Event Attendance Category: INDB= in database or LIST If (DLookup("evattcat", "tblevents", "[evid] = " & EV)) = "INDB" Then getEvCheks.EvAttCat = "Y" Else: getEvCheks.EvAttCat = "N" End If
'Event Assessing Organisation Dim AOROName As String Dim AOName As String AOROName = DLookup("evunitassessable", "tblevunits", "[evunitid] = " & EvUnit) AOName = Mid([AOROName], 1, InStr([AOROName], "/") - 1)
Select Case AOName Case "NA" getEvCheks.EvUnitAss = "N" Case "ABCD" getEvCheks.EvUnitAss = "Y" Case Else getEvCheks.EvUnitAss = "X" End Select
getEvCheks.EvCheksAll = getEvCheks.EvType & getEvCheks.EvAttCat & getEvCheks.EvUnitAss End Function ==== On a form, I have a button with on-click event code that defines the EV and EVU variables and displays a message box MsgBox getEvCheks(EV, EVU).EvCheksAll
This works fine. ===== In a query I have this SQL code: SELECT tblEvUnits.EvId, tblEvUnits.EvUnitID, getevcheks([evid],[evunitid]) AS EventDetails FROM tblEvUnits;
This causes the db to crash.
I cannot get the "back-end" definition of the function into the query, as Access rejects it because of the dot, i.e. ".EvCheksAll".
I suspect there is some formatting quirk for using a public function that includes a user-defined type, in a query.
I have quite a few user defined / custom functions which I would like to be available in any new database that I create without have to copy the VBA codes in a module each time. Basically, some sort of add-in that loads on MS Access startup may be (but I am not too sure - never created one). So something that will start with the MS Access Application and not just a particular database. I know excel addins can be copied to the XLStartup folder to achieve something like this in excel, but not sure about Access.
I'm using the following function to calculate date diff in network days (excluding weekends)
Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer Dim intGrossDays As Integer Dim dteCurrDate As Date Dim i As Integer intGrossDays = DateDiff("d", dteStart, dteEnd) NetWorkdays = 0
[Code] ....
So when using this function in a query to get number of networkdays between 2 date columns ...it works fine but throws a #Error where there is blank entries in either of 2 date columns...
I need to find a way to display Null instead of #Error...
How do you calculate or find Month To Date in a query as it relates to a hand keyed criteria.
For example I have a field called Operating Day and right now my criteria in my query is Between DateSerial(Year(Date()),Month(Date()),1) And Date(). Works perfectly. I'm using this data in a sub report.
But now my requirements have changed and its possible that I may need to report on something from January (or December and so forth). Well this criteria will show data from February. But the Month To Date data should show totals for the Month I'm reporting on.
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
Each month I use Access to import text files and export cleansed data to Excel.
The information contains (i.e. forecast data) that has 6 months of data. Each month, the data is rolled over to include the new month and delete the oldest month of data.
Is there a query(s) or module that can automate this process?
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
How do you write a MS Access query criteria that looks for records like the current year only for a date field ? I tired Like *Year(Now()), it did not work.
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
I want to run an activity query (i.e. objective is to check which data lines have changed) within a user defined date range. Please advise what is the most efficient way to achieve this.
Is it possible to run such a report without having a date column i.e. does Access have a hidden line modification date that can be used in a search?
Hi! What expession should I use to set the current month as criteria in a simple query? IE in the criteria field of the query design view... Thank you for your help
I copied some VBA from one database to another. I didn't change anything and I am able to run it fine in the first database. But in the DB I pasted it to, I am received a Compile Error message with the XlApp As Excel.Application area highlighted.
Function OpenAutoCount() Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook
This is giving me the error 'User defined type not defined'. I know I have to set something in a list somewhere but have forgotten how to do that. Where to go, and what to set?
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
