Access :: Restarting Custom # Each Year Using Dmax Function

Using A Query To Run A Report With Dmax Function

Jan 11, 2012

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:

tblBusinessCustomer:

CustomerID (Primary Key)CustomerNameAccount StatusInstallationDate
tblBillingInfo:

BillingID (Primary Key)CustomerIDBillingNotesDateBillingNotes

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

View 6 Replies View Related

Modules & VBA :: Type Mismatch Error On DMax Function Using Dates?

Oct 17, 2013

Anyway I've got a type mismatch that I can't figure out.

Code:
dMaxLstReq = DMax("reqNumb", "FlightLog", "Month([txtDate])='" & frmMonth & "'" And "Year([txtDate])='" & frmYear & "'")

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.

Code:
frmMonth = Month(sysTime) & frmYear = Year(sysTime)

Am I correct in thinking that the fact that my variables are integers and not times, that this is the cause of the mismatch?

View 2 Replies View Related

Custom ID With Year And Incrementing Log Number

Dec 16, 2014

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

View 1 Replies View Related

Modules & VBA :: DCount With Custom Fiscal Year

Jan 21, 2014

I have having an issue with determining how to use a customized fiscal year with a DCount function.

My Code:

ThisYear: DCount("*","Open Issues","year([RequestDate]) = " & Year(DateAdd("y",-1,Date())))

The code only does from Jan 1, YYYY TO Dec 31, YYYY

I'd like the code to read from Oct 1, YYYY TO Sept 30, YYYY

View 2 Replies View Related

Forms :: Custom Autonumber With Year And Month

Aug 12, 2013

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...

View 3 Replies View Related

Queries :: Grab Custom Fiscal Year Count Based On System Clock Date

Aug 13, 2015

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

Code:
fiscalYear: IIf(Month([Issues].[Opened Date])>=10,"FY" & Year(DateAdd("yyyy",1,[Issues].[Opened Date])),"FY" & Year([Issues].[Opened Date]))

View 4 Replies View Related

Year Function Malfunction?

Jul 15, 2005

hello,

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.

G

View 1 Replies View Related

Last Day Of Year Date Function

Jun 23, 2006

Hello,

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).

Any help? Thanks.

View 2 Replies View Related

Modules & VBA :: Argument In Custom Function

Jun 6, 2014

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.

View 8 Replies View Related

Reports :: Custom Reports Creating Chart Based On Month Not Calendar Year

Jun 15, 2015

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?

View 2 Replies View Related

Modules & VBA :: Using A Custom Aggregate Function With GROUP BY Clause

Dec 20, 2013

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.

View 14 Replies View Related

Modules & VBA :: DLookup In Custom Function Not Returning Values

Sep 13, 2013

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.

View 7 Replies View Related

Modules & VBA :: Function To Get Fractions From Double - Error 91 With Custom Class

Jun 10, 2014

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.

View 1 Replies View Related

Modules & VBA :: Custom Function Module - Find Records With Specific Keywords

Feb 28, 2014

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.

View 2 Replies View Related

Restarting The Application

Aug 17, 2007

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.

Any suggestions?

Cheers,

JMH

View 14 Replies View Related

Change User In Run Time (without Restarting Access)

Mar 8, 2007

Hey guys!There is any possibility of changing user on the run without restart ms-access and prompt for user again?Cheers!:)

View 1 Replies View Related

Consecutive Invoice Numbers, Restarting Each Month

Oct 14, 2005

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.

Thanks

View 5 Replies View Related

Tables :: Increment Number For Related Records Restarting At 0 For Each Parent

Sep 18, 2012

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.

The result would be

Code:
article1 version0
article1 version1
article1 version2
article2 version0
article2 version1
article2 version2
article2 version3

What's the best way to accomplish this?

View 3 Replies View Related

HELP: Changing Dates To FY (fiscal Year) And YTD (year-to-date) Values

Apr 25, 2006

I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.

For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006

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.

What do you suggest? Many thanks.

Mehran

View 7 Replies View Related

Sum Of Current Year Minus The Year Of A Date In Past?

Apr 25, 2014

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 have been trying to tweak this:

SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))

but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"

View 2 Replies View Related

Percentage Of Year Employed - Prior Year

Aug 1, 2005

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.

Any help would be greatly appreciated.

Thanks,

View 6 Replies View Related

Queries :: Determine Date Given Day Of Year And Year

Jul 3, 2014

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

View 6 Replies View Related

Year And Date - Show Day Of Year As Three Numbers

Jan 10, 2012

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 would prefer to see 120101304

View 4 Replies View Related

Add 1 Year To Year Part Of Date

Mar 14, 2006

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.

View 3 Replies View Related

DMAx Help

Sep 15, 2006

Thanks for all the help to date.

Problem trying to get a dmax field for an invoice no in a query - am doing something wrong current attempt below won't work

Expr1 :dmax([Invoice no],[Table name]![Invoice no])

what am I missing - do I need something on the end when I get this to work I will use it to add 1 for the final query prior to print

View 4 Replies View Related