Does MS Access Has Functions Like Excel? If Sunday Move To Monday
Jan 5, 2008
Hi!
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'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 was wondering if i can get some help here. The aim here is moving data from excel to Access '03.
At the moment, i'm having troubled in finding out a way to open a database and execute SQL - insert the data into the table.
Set cn = New ADODB.Connection With cn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source= C:MydocumentsmyDB.mdb;Extended Properties=Excel 8.0;" .Open End With
then, i got stuck in here whereby i need to execute the SQL insert command to insert record to the table.
Hi all, I have some records lying on ms access db, I need to find the first monday and the last friday of the months in db then select and group them . How will I do that ?
I spent all day trying to figure out why sometimes I can call an excel function from access and the value will appear on my form, but for some functions (F.inv, binom.inv), it errors out as "object not supported". I browsed all the available libraries in tools/reference but found nothing that seemed to solve this issue.
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.
Hello peeps, I have been working on a hours checking application, in which the user can enter their worked hours for the week and the application calculates how many hours at normal rate , time and half rate or double time rate have been worked for the week. All has been going swimmingly, with some help from here of course :)
What I would like to achieve now however, is, testing for a Sunday. This is because all hours worked on a Sunday, is paid at double time, regardless of the previous hours already worked for that week.
Example if a person worked 60 hours in a given week, there would be 47.5 hours of normal rate, 7.5 hours of time and a half rate and the remaining 5 hours would be at double time rate. But, if a person worked 60 hours in a week and 10 of those hours are worked on a Sunday, this would mean 47.5 at normal rate, only 2.5 hours at time and half rate and the remaining 10 hours at double time.
Therefore, is there a way I can test for a Sunday and perhaps automatically set a flag for the overtime calculation? Or is it possible with DateDiff() function, and if so could someone please assist me with the syntax or at least point me in a direction where I could solve this?
All replies gratefully recieved. Thanks in advance to anyone taking the time to read this post.
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 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?
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 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.
I'm trying to group and display data on a weekly basis starting on Sunday. I'm using the following in a totals query with "Group By" Week Number: DatePart("ww",[Date])
My problem is that the results are returning some unexpected things.. March seems to have 6 weeks and the query is returning two months containing week number 14.. (as shown below).
2013 March 9 2013 March 10 2013 March 11 2013 March 12 2013 March 13 2013 March 14 2013 April 14
I have a tab control on my form. In the change event of the tab control I am trying to tell it to move to the first field on each page of the tab control whenever the page no. changes:
Private Sub TabScreenAssess_Change() 'when a new page gets the focus, set the focus to the first control on that page Dim PageNo As Integer
PageNo = Me!TabScreenAssess.Value Select Case PageNo Case "0": gynFemale.SetFocus Case "1": tbPriorHx.SetFocus Case "2": suSubUseAssess.SetFocus Case "3": mhaCognitiveFunct.SetFocus End Select
End Sub
This works when I am tabbing normally through the controls. But when I click back to the first tab, it says "Access can't move the focus to the control gynFemale" (gynFemale is the first control on the first tab.) It works for the other tabs. That is when I click on another tab, the focus goes to the first field on that tab. When I enter a value in the gynFemale field (it is a combobox, with options Yes or No), sometimes it appears to save the value, then I discover that it has been saved as record #0.
I am working on a calibration database which keeps track of measurement equipment and their scheduled calibration date. There are several gages that become "In-Active" because they are old, lost, or whatever. I have two tables that have Active and In-Active gage listings containing all of the pertinent information regarding that gage. I would like to put a button in the Active gage form which in the event the gage becomes "In-Active", pressing the button moves all of the information in the "Active" page to a new page in the "In-Active" table. So essentially one can look at Active gages and In-Active gages depending on the switchboard button that is clicked.
I know that is probably a crazy question but any thoughts or insight would be greatly appreciated.
Is it possible to set a property to allow the user to click and drag an open form out of the Microsoft Access 2010 parent window? Specifically to a second monitor.
I've created a table that is linked to a folder named New within MS Outlook. I've created a form that will read the emails within the table, and manipulate the data into another table. After the manipulated data is inserted into the other table, I'd like to move the email from the New folder in Outlook to another folder named Pending within Outlook.
I am creating a report that has the variable address as usual. Name Address1 Address2 City, State zipcode
If there is no value for address2, is it possible to shift the locatoin of city state zip up into the Address 2 location? I would like to shift the lable up with code, but can't find a way to do it.
I am using an unbound data source so i have full control over the variable data.
I have a list box contains 10 times . I have UP and Down buttons to move item up and down. My VBA works only if i set listbox multiselect property to 'None'. For the multiselect=simple option it throws error like in valid use of null in this line of code :
sText = lbfNames.Column(0, iIndex)
My VBA
Code:
Private Sub cmdUP_Click() Dim sText As String Dim iIndex As Integer iIndex = lbfNames.ListIndex 'check: only proceed if there is a selected item
How can I find out the date for whichever the date is of this monday. So, if today is 23/03/2006, then it would give 20/03/2006, which was the date of this monday.Thanks for any advice.