I have the code working great to generate the different sections.
I have a table with 3 fields (seperating the number) and 1 field that is the combined number. The fields are:
FormatID (Text field),YearID (Text field),BaseID (Number field ) (These are populated using a form called "Test")
FormatID = AR-CR- (This is set as the default format/value)
YearID = 2007- (I have this obtained automatically using code in the default
value of the data tab) =Right(Date(),4) & "-"
BaseID = 00001 (Sequential by +1)
I have a button on the form called "Save" which I have placed this code in the "On-Click" event.
Private Sub Save_Click()
If IsNull(Me![BaseID]) Then
Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblTest]"), 0) + 1, "00000")
End If
Me![CustID] = [FormatID] & [DateID] & Format([BaseID], "00000")
End Sub
What I can't figure out is how to get the Base ID to restart with 00001 at the beginning of each year? What am I doing wrong?
I have also attached an image showing the increment change if I was to change the year to 2008 how it just incriments the last numbers instead of resetting to 00001.
This qry pulls from multiple tables to create a job ticket for the technician/installer. I will not list all the tables because I have narrowed down my problem to two specific ones. These two tables:
tblBusinessCustomer tblBillingInfo
Have a one to many relationship, due to a single customer could have multiple billing notes, this is how the tables are structured:
I have a one to many relationship using the CustomerID field from tblBusinessCustomer to tblBillingInfo. When I run the report I have a field for billing notes. So what happens is that when I run the report (using the qryJobTicket as a filter) the report will have as many iterations as there are Notes. So for example:
CustomerID: 1 BillingNotesDate: 1/1/12 BillingNotes: (some form of gibberish) BillingNotesDate: 1/2/12 billingNotes: (different gibberish)
so the report will run twice, one for each "line" of notes. I have tried using a form of the Dmax function as a filter in the qry so that I would only grab the most recent date from the BillingNotesDate field, thus only running a report and grabbing the most recent info.The problem is that I have inherited trying to clean up this database, and I added the BillingNotesDate field, since before they were running a constant "log" in the notes field:
1/1/12 Customer changed Name to ABC company, 1/2/12 Customer was notifited of late payment, 1/3/12....and on and on.SO there are NULL values in the BillingNotesDate field for all of the OLD data. And instead of trying to clean that up right away, I was trying to use the Dmax function to give me the most recent date, but the ones that had no date at all (NULL) it would not pull that record. I am trying to use the DMax function on a Date Field with Null values
In plain english: Select the highest value in the field named reqNumb from FlightLog where the Month of txtDate is equal to the variable frmMonth and the year of txtDate is equal to the variable frmYear.
txtDate is a Date/Time field in the table FlightLog
frmMonth and frmYear are both integer variables that take the system time (sysTime) and determine the month and year: I.E.
Been looking around for a solution for a custom ID and can't quite find one that's specific to my problem.
I am creating a log and I want to give each new log entry a new log number. The format would be: IEyy-xxx. The ID is the letters IE, then 2digit year, a dash, then 3digit incrementing number. The ID should reset back to 001 every year (e.g. if IE14-623 is last log entry of 2014, first log entry of 2015 would be IE15-001).
My table has an autonumber that I will use as the primary key- which is not the log number- and looks like this:
These are the fields in my table:
Index Log Number (autonumber and primary key) IE number Program Name Date Created
I need to create an "autonumber" field in the following format:
FYYMMXX
Where F is a constant, YY is the year, MM is the month, and XX is an incremented number. So for example, the first record in August of 2013 would be "F130801".
I've been checking other threads but haven't been able to follow them to a resolution. I think there is a way to do this within my form...
How do you grab a custom Fiscal Year's values based on the system clock's date?
I am building a query where I want to see the number of closed cases based on the current custom fiscal year with the system clocks date. The report that it feeds only cares about the current FY.
I need the System Clock's FY value in this query
Code: SELECT shortname AS Station, NZ(TotalCount.TotalCases,0) AS [Cases Complete] FROM StationList LEFT JOIN (SELECT station, count([Open Issues].ID) AS TotalCases FROM [Open Issues] WHERE [Status]="Closed" GROUP BY Station) AS TotalCount ON StationList.shortname =TotalCount.station;
Within the Query Open Issues I have the FY broken up
I'm confused. For the longest while, I was using the Year function to grab the years in the dates of my record and put them in a field called "year." Seemingly out of the blue, it no longer works, but gives me a "type mismatch error" everytime i use it. The date field uses short date format and, to my knowledge, would not allow any data in that field if not in the correct format. But, the debug says that Year(Rs!Date) = <Type Mismatch> Can anyone help? Is this a reference problem? Thanks sooo much in advance.
I would like to add a control on my form (textbox) that by default will always show the last day of the previous year with the format mm/dd/yyyy (ex. 12/31/2005). This should then update every new year always showing the last day of previous year (ex. next new year it should show 12/31/2006).
I'm having a problem with argument in custom function..My table is like this:
myValues 123.5 32.7 65.8 11.1
What I want to achieve is to multiply each value with the average of all values, so that at the end I get (avg(myValues) = 233.1):
myValues2 7196.963 1905.593 3834.495 646.8525
For this I would like to have a function (this is simplified example of bigger problem, that is why I need function, otherwise simple SQL would suffice as mentioned below also), so that I could do something like this:
'SELECT TestFn([myValues]) as myValues2 From MyTable' or 'SELECT TestFn("[myValues]") as myValues2 From MyTable'
and I should get the table above.
My code is like this
Code:
Public Function TestFn (FieldName) as String Dim myAvg as Double myAvg = Davg(FieldName, "myTable") TestFn = FieldName * myAvg End Function
I understand the problem is that Davg needs string, but if I change to string then the multiplication does not work. The 'FieldName' parameter does not have a Type specified because that is where the problem is if I give string Avg works ,but calculation does not. If I give Number, avg does not work.
P.S. "as String" at the end of function is not a mistake, I need number as a string
P.P.S I did not inculde NZ either as in my example it can never be Null.
I am editing a database that provides the option of creating custom reports, where the user can input a date range of their choice and receive aggregate data for that time frame. Although all of the numbers in the report are correct, I am having trouble with a chart that I inserted into the report.
Specifically, if the date range requested spans 2 calendar years (i.e. April 2014 through January 2015), the data for January 2015 appears at the beginning of the year (so the chart x-axis is for Jan through Dec, and the Jan 2015 data is showing up in Jan (as if it was 2014, not the end of the given range in 2015). When I try with smaller time frames within a calendar year, it adjusts just fine (i.e. shrinking the window so just March-May is displayed on the graph).
How to adjust the axis so that it properly records the data range- so that it would start the axis with April and end in January, for example?
I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code: Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant Dim priSubID As Long Dim priLocusID As Long Dim priResult As Variant priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
I haven't created classes in a while, and I don't see what is the problem
I'm modifying a function to get fractions from double. It worked well, but it returned a string.
I need it to be in parts, so I created this class
Code: Option Compare Database Public Entier As Integer Public nominateur As Integer Public denominateur As Integer Public Function getText() getText = Entier & " " & nominateur & "/" & denominateur End Function
I changed the function type and the assignation:
Code: Function GetFraction(ByVal Num As Double) As nombreEnFraction Set GetFraction = New nombreEnFraction If Num = 0# Then GetFraction = "None" Else Dim WholeNumber As Integer Dim DecimalNumber As Double
[Code] ....
For some reasons, when I get to the end of the function, I get a 91 error, like if it was nothing
But when I use a spy, I see values in the object until the end.
I'm looking for some type of custom function that will search a specified column for any keywords listed inside another table.
I can run a query on each keyword individually, however there are 50 and it takes a long time each time I do it. I was hoping to write in a function for that column and it would just select all records that match.
These would all need to be a "like" with an " * " on each end of the word.
With SQL it would look something like:
Code: select a.address1 from main_tbl as a where a.address1 like '* north *' or a.address1 like '* park *' or a.address1 like '* south *';
I just want it to read each of the table values instead of hard coding them and the column name would be the function name so it can be used in any column I specify. I'm just not sure how to incorporate this into a custom function.
Wow, been a while since I've had to ask a question here. But I've been looking for a solution (if it's possible) to restart the open database app I've been working on.
I have a form in which I create and set a database property. In order for it to take affect, the database has to be reopened. Sure, this can be done manually, but I'd like it to be automatic. Just like when you use the compact and repair tool; the database closes and reopens. I've tried the OpenCurrentDatabase method, but of course, I can't close the current one before the code runs.
Hi there, Some scene setting to my problem. I have a corporate system numbering invoices consecutively globally. For a subsidiary in Poland this presents a problem - all invoices for the subsidiary must be consecutive. ie. where the globally issued invoices for Poland might be 1,4,5,7 these need to be renumbered before issue in Poland.
It gets more complicated as the Polish Office standard is to invoice in the format "PL/Year/Month/Number" with the Number restarting from 1 for each month.
My database is very simple. Table 1 = Link table to Oracle corporate data Table 2 = Local table of invoices already produced (Global invoice number and date recorded)
I have a filter to find all new invoices (unmatched items), a report to print them and an Append query to populate the new items to the Local table.
My challenge: (1) to recreate the Polish numbering scheme and (2) have it consistently when existing data goes up to mid month and a new append operation has to follow the invoice number sequence.
Any ideas? I guess Autonumber is no good and I have little array experience.
I have two tables, Articles and Versions, related 1 to many. In my Versions table I want to set up a field that will start at 0 for the first version record related to an article record, and as version records are added in relation to that same article record I want it to increment by 1, no gaps. It ideally would be editable. It will not likely count above 5.
THEN . . . . I need to also isolate certain periods, for example July- March for YTD (year-to-date) analysis and compare YTD of 2006 with that of 2005.
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.
This is being done in a query and we're using Access 2000.
I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different.
I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014
I'm going to try using the year, day of year, hour & minute (24 hour clock) as a report number. It's set up in a field on a table. Right now I have....
Default Value =Format(Now(),"yyyhhnn") 'which works but not exactly how I would like
yy = Last two digits of the year y = Number of the day of the year (1 to 366) 'can this show three digits all the time? hh = Hour in two digits (00 to 23) nn = Minute in two digits (00 to 59)
For instance, right now for Jan. 10th, 2012, 1306 hours the result would be 12101304 which, for all intents and purposes works, but I would prefer the "day of the year" to always be represented by three digits and not just when it hits day 100 of the year.
I have a query based on payment date which I have extracted the Year part as a seperate Field StartYear, but I want to now add EndYear which just adds 1 year to the StartDate. e.g. EndYear = StartYear +1. Anyone kow please I know i's proably simple but I keep getting syntax errors.
