General :: Stripping Out Unnecessary Access Functions?
Aug 3, 2014
Our Access database is getting a bit sluggish when loading/running reports. My mate said to me that the Access database that our 100+ users at work use to run reports should be "stripped back" to speed things up and to stop unnecessary actions running when they aren't needed. He pointed me in the direction of File>Options. Users don't edit data from this database, just to run preset reports. They have no editing capabilities either.
I am trying to automate a data match I've been doing in Excel into Access.
Basically, I have 2 spreadsheets:
1. First has a "Start Date" for each user 2. Second spreadhsheet is a lookup table with 2 fields: Week Start Date and Week #. In other words, Dec 30 - Week 1, Jan 6 - Week 2, Jan 13-Week3, etc
For a given date in the first spreadsheet (e.g. Jan 10th), I am trying to figure out which week this falls into. In Excel I use this formula where Column A stores the week start dates in the lookup table spreadhseet and Column B stores the Week#, with F4 being the "Start Date" in my first spreadsheet.
I can use barcode scanning to track items through the process, on the condition that the relevant field on a form is highlighted.
The issue is I want the scanning/tracking with wireless scanners to be going on in the background while an operator can use the other functions of the database.
I'm thinking the solution is two computers, one to run the barcode scanning & tracking the other for the rest of the database functions. The problem is we only have one work PC and getting money for a new PC will be like pulling teeth.
I hope someone can help with this one. After many years of using Access for ad-hoc data conversion this has beaten me. I need to produce an ascii text file with fixed column widths, separated by commas, strange I know but the customer is always right. As it is fixed width I have inserted the commas by using a separate column for each one. Numeric columns need to be left padded with zeros. I have constructed a query to do all the column selection and reformatting into a new table which I then export using a fixed length export file spec. Everything works fine except for 3 columns which are calculated by subtracting one column from another. I can get the data to look fine in the output table, the datatype is text, but when I export the table the leading zeros are stripped. This is my expression: String(9-Len(FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0)),"0") & FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0). The result in the table is exactly what I want: 000200.00 but when I export it I get a left adjusted 200.00. I've tried using format with a "000000.00" mask which gives the same results. I've tried removing the preceding comma column and including the comma as a prefix using the format mask ",000000.00" and also by concatenation. This looks fine in the table column ,000200.00 but I get an error when I export the table which blanks the column. Error attached.
I have a need to strip of letters from a string but i needs to look for / as the length would change, below is an example of the data I am working with
record 1 example ) REP/1349/999/426066/XX/9 record 2 example ) REP/UDKBS01N/1/448174/XX/
what i need to extract is
1) = 1349 2) = UDKBS01N
I need to get the information between the first / and the second / is there a function in access to get this.
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue but I cannot be sure.
Both computers run the same version of Office. Does anyone have any ideas.
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue stopping her running the functions.
Both computers run the same version of Access. Does anyone have any ideas.
My access installation opens databases reasonably well. I can create databases, add tables, etc. However, I cannot:
1) Add a command button or data-related fields to a form: the "command button wizard" stops responding on the first screen. If I click "Cancel", the button depresses, but the wizard doesn't close. 2) open the "Linked table manager": Access crashes 3) import an Excel spreadsheet: Access crashes
I suspect these might be related.
I've tried these with various databases, some on my local C drive. I have also tried these with various excel files. Not being able to add data items to forms is particularly irritating -- I don't want to build my own front end (that would sort of ruin the purpose of using Access, except that it happens to be a database already on my desktop).
One other thing -- when I start up a database, it always gives me an initial "Installing components" message. This occurs similarly for other MS office applications as well. I don't know whether this is related. I do have admin rights on my local machine, and have tried the "Repair MS Office" function in the Control Panel.
I don't necessarily expect an "answer", though some advice on how to proceed would be great.
I'm trying (without luck) to use some excel functions within an access query (namely NORMSDIST and NORMSINV). Error is being returned 'undefined function'. I have switched on the MS Excel 11.0 Library. Anybody any ideas? Thanks
I’m running a small web-hosting service, and my password-database is in Access 2000. I have trouble making to functions, both e-mail functions:
1. Send e-mail with password A function that sends one mail to a single e-mail address containing a password. Based on a form or query.
2. Start outlook and export mail address Just like the HTML code MAILTO:adress. I can’t figure out how to get this to work. The address is a variable. Get the value from the form or a query.
First of all, which technique should I use? Macro or script? Second, how do I use it?
I would like to know if MS Access can handle functions that Excel has, for example, "weekday" function, that returns a number representing the day of the week (sunday=0, monday=1, ...), given a date value.
If so, I have a conditional like this in excel: =IF(weekday(DATE-3)=0;DATE-4;DATE-3)
If DATE is sunday, then move to monday. Is there a way to make something like this in Access?
I have quite a large select query that pulls in Date values from different tables. The end result is a list of records, where each record has 7 types of dates, put in columns A, B, C, D, E, F, G. What I need to do is, for each record to compare dates in those columns and put the LATEST one of them in the column MDATE.
I've tried using IIF function, but it grows exponentially with the number of values involved, and it can only handle 5 values before its cause error "too large". Alternatively I thought I could write a function to return the max date and call it from within the query, something along the lines of "select *, fnMaxDate(A, B, C, D, E, F, G) as MDATE from tblBLAH etc". The function is:
---------------------- Public Function fnMaxDate(a As Date, b As Date, c As Date, d As Date, e As Date, f As Date, g As Date) As Date
Dim Temp As Date Temp = Nz(a, "01/01/1901")
If Nz(b, "01/01/1901") > Temp Then Temp = b If Nz(c, "01/01/1901") > Temp Then Temp = c If Nz(d, "01/01/1901") > Temp Then Temp = d If Nz(e, "01/01/1901") > Temp Then Temp = e If Nz(f, "01/01/1901") > Temp Then Temp = f If Nz(g, "01/01/1901") > Temp Then Temp = g
fnMaxDate = Temp
End Function ----------------------
I've put a break at the start of the function, to see what it'll come up with but when running the query it didn't appear to call it. All values in MDATE where #error.
Can someone advise me, please, on the best way of picking the LATEST date? I'd also appreciate it if you tell me why the function didn't get called, as I was quite certain it is possible to call user defined functions from within the query.
and i would like that if Text0 is "test.exe" i want to return "exe", however, with the above i am getting ".exe" ... can someone tell me what i am doing wrong please ?
I currently have a text box that is calculated off of 6 other text boxes. I want the one field that is calculated to update any time I change any of the other fields. I can do this by putting the same calculation in each ones afterupdate event. Below is one section of my code for the arithmetic, and I want to perform that code many times. Can I use a function to do this, and if so some help would be nice? thanks
In access, I'm trying to create a database to track material that has an expiration date of one year from the appointment letter date. I'm not sure what I need to do here. Here's what I have: I have a table that has fields for the individual (UNIT), the appointment letter date (Appointment Letter Date), and a True/False field (Out of Date) that I want to make True when the current date is greater than the (Appointment letter date + 365 days). Is this possible through Access tables and/or queries or will I have to incorporate VB? I am an extreme novice when it comes to expressions and query building in Access.
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
I will firstly give you the low down on the business that I am doing the database for. It is a Car Restoration Business, where we deal with vintage/classic/muscle cars and restore them to the former glory.
I do not know Microsoft Access very well, I have attached a file with what I think is a databae :) I could be wrong. The main function of this database is to search for suppliers for a specific car part for a specific car.
So if I want to find a doorhandle for a 1970 Ford Mustang, I need the suppliers pertaining to those criterias to come up.
I need some help with a function inserted into a query.
I created a function to convert numbers into text. I then created a query to pull some fields from a table. the last field of the query, I inserted the function using the build feature. I was able to see the function in the list of custom functions for the query.
However, when I run the query I get an error message of undefined function - name-. Can I use my function there? or is there some other way to do so.
Hi Im trying to run two count functions on one column in sql (access 2003). I need to return two new column, pass and fail. something like this; select count(grade) as pass, where grade>=40 and select count (grade) as fail, where grade<= 39.
my table is student_ID, module and grade:
student_ID Module Grade 0012 history 59 0034 history 34 0045 maths 78 0031 maths 45 0046 spanish 66
I have looked and looked at this expression line and cannot find what is wrong.
Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31])),1) And DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31]))+1,0),1,0))
This gives me an error "Expression entered has a function containing the wrong number of arguements"
However this one: Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0)) works fine... The only difference is I inserted forms![enterprise report card].[combo31] inside of the Date() function.
I am doing some work on a loan calculator which I have done successfully in Excel. I now wish to do this with an Access form (A2K). Can anyone please confirm that the Financial Functions (ie CUMPRINC etc) that are available in Excel can be used in Access. The Access help menu calls them Worksheet Functions and I can't see any reference to the above function in this forum. I have entered it in a control and get a #name? error. I dont know if I have made a syntax error or that Access doesn't recognise it.The following is the formula I have used =CUMPrinc([rate]/[freq],[nper],[amount],1,[nper],0).
I have not had the same problems with the following formula =PPmt([rate]/[freq],1,[nper],[amount],0)
I`ve got a problem with access. I Have data in several columns like:
Date Value 1 Value 2 Value 3 Value 4 1-1-01 12 10 11 9 2-1.01 14 7 16 11 etc. Now I would like to add two columns that determine the min and max value of a row. Like below:
Date Value 1 Value 2 Value 3 Value 4 Min_value Max_value 1-1-01 12 10 11 9 9 12 2-1.01 14 7 16 11 7 16
Is this possible with the standaard min function? or how else do i do this?
