Hallo guys,
I am stuck with a simple fact. Nz() function works in Access queries. When I try to import data via these queries into Excel, it says : Nz is not recognized. So anyone knows a good replacement for Nz() that works in Excel as well?
With kind regards
Maxim
P.S. I use office 2003.
I have an expression in one column of my query and It keeps returning a negative result and there are no negative numbers in the source data. Can anyone help. I just want to sum the sums the two IIf expressions, but its not working. Thanks
Other Qty: Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=95 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],Null)) Or Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=100 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],Null))
I made a program for litle hotel registration. It worked enoudh good for me but after changing a comuter function Date() isn't work
In Build-In-Functios Date() persist but every try to use it cose massage "The function you entered can't be used in this expression"
In some queries I change Date() with Now() but can't do this in all.
HELP
I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);
But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA
Hi All
I've created a database where a query who'se result is loaded on a form uses following function --
DateDiff('d',Date(),[Target Date])-(DateDiff('ww',Date(),[Target Date])*2)-(Select Count(*) from [tblHolidays] where [HolidayDate] between Date() and [Target Date])
I've notice that 2 ppl get an error message when they try to open database, with thatt function coming on an error message
Does this mean they have older access version ?
How can I tell ? Any advise on what to do ?
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
I am trying to prepare a mini budget program in ms access for my personal use in my office , but every time i fail to do so, my tables are as under
Field Name Properties
Code ID AutoNumber
001 Cash
002 Bank
846 conveyence
dt: Date/time
Code text (list selected by drop-down list)
des Description
Bill currency
yearly Budget currency
quartlyBudget currency
Query Question:
Group Aggregate function in my Budget data base file
(A) (B) (C) (D) (E)
Code yearlyBudget quaterlyBudget quaterlyused Budget Available Budget
846 50000 12500 5000 7500
850 100000 25000 20000 5000
There are 100 entries in code no. 846 and 50 entries in code no.850 the above given amount is total of each code no.
My question is that how can i get result subtracting amount in column (D) from column (C) to get result in column (C)
Budget Available: in Code 846 i.e column (E)
I am extremely new to writing code, but was able to successfully write code that transfers the data in my form to and excel spreadsheet template and saves it with a different name. The code works perfect and without issue as long as Excel is not running. However if Excel is already open the template remains blank and a warning message appears saying that the form is locked for editing. Below is the code I used.
Private Sub ExcelClear_Click()
Dim xlApp As Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
[Code] .....
Hi,
I am trying to use the YIELD worksheet function in Access but can't get it to work. It comes with Error Sub or function not defined.
As per help file I have installed msowcf.dll but it still doesn't work.
Any ideas?
Thanks
I have linked an excel workbook as different tables in Access.
The workbook contains a Microsoft Query. I have now found out how I can open and edit the workbook from access, how can I Update/refresh the MSQuery before I close it?
Here is what I have:
Private Sub cmdTest1_Click()
Dim MyXL As Object
Set MyXL = CreateObject("Excel.Application")
Set MyXLSheet = GetObject("c: est.xls")
MyXL.Application.Visible = True
MyXLSheet.Parent.Windows(1).Visible = True
MyXL.Application.Cells(1, 1).Value = Now()
MyXLSheet.Close SaveChanges:=True
MyXL.Application.Quit
Set MyXL = Nothing
End Sub
eroness
Is it possible to compare rows, ie in excel I could do =A1=A2 to compare certain fields then if they are different/the same highlight.Is there a way of doing this in access?
View 1 Replies View RelatedI am trying to use the Excel worksheet function "Forecast" to calculate from existing data in my database. I have successfully gotten the data points into arrays, but when I try to call the forecast function I keep getting the following error: "Unable to get the Forecast property of the WorksheetFunction class". I have imported the object library. This is on a company computer and I do not have write privileges to the C: drive. The database resides on a network drive.
Am I facing some sort of security issue?
Public Function xlForeCast() As Double
Dim MyHeight As Variant 'Will be the point for which you are forecasting, in this case height
Dim MyRange() As Variant 'Will be the independent element of the forecast function
Dim MyRange1() As Variant 'Will be the dependent element of the forecast function
Dim MyArray() As Variant 'Temp array to hold the query result set values before being split into the two preceding arrays
Dim db As DAO.Database
[code]....
I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel Here is what i am trying to do The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID: aaaa
Date
01012013
02012013
03012013
CustID: bbbb
01012013
02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for
CustID: aaaa : aaaa
Date : aaaa
01012013 : aaaa
02012013 : aaaa
03012013 : aaaa
CustID: bbbb : bbbb
01012013 : bbbb
02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF( B6<>"",B6,C5)))but in access i am stucked.
I am importing an excel text field into a table. I am then appending the data to another table. I am having a hard time with the Cdate function
Excel Date Field:
YYYY.MM.DD HH:MM:SS
Example Value:
2014.04.21 18:24:30
My Query Code:
NewDate: CDate(Left([DateQ],4)+"/"+Mid([DateQ],6,2)+"/"+Right([DateQ],2))
My Query Result:
4/30/2014
What it should be:
04/21/2014
We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate
The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive
Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
Thanks for the help.
Hi guys,
I regularly export the main table in our database to an excel spreadsheet, to provide an additional level of data back-up. (Using File/Export)
I was wondering if there is a way of automating this process, either through a command button on a form, or by linking it with shutting the database down.
Any ideas?
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
I searched the archive and didn't find quite what I was looking for, so..
I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.
I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.
Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!
A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.
I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.
Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject
I have an excel file linked to a table in Access. Several fields are date data types in excel but are showing up as text fields in Access.
My real goal is to do a comparison between two tables, but only if the date of the one piece of data is newer than the other. I had planned on comparing the two date fields but even though I have formatted the date fields in my excel file to be "Date", when I look at the design view of my table it is showing up as "Text" and therefore I am unable to do this comparison.
I'm not sure if it's just something that I'm missing but maybe someone else knows an easy fix to this. I know this is probably a simple question, but I did search the forum and didn't find a thread that specifically dealt with this issue.
Thanks in advance for your help.
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
i want to export a table to excel , open this file and execute a macro from another file.
the code i have now is :
Code:
DoCmd.OpenTable "Overzichtaanwezigheid", acViewNormal
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acTable, "Overzichtaanwezigheid"
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open ("C:UsersErwinDocumentsOverzichtaanwezigheid.xlsx")
XL.Visible = True
XL.Run "d: est.xlsm!Macro3"
Opening the excel file goes ok, running the macro however not.
Hello,
sorry to post again my question but just cannot find a solution.
I have a table and would like to copy data into a template in excel. I know that the code will have to make a copy of the template and then copy the data into the new workbook into sheet1.
My table (table1) has 3 fields: SSN, FNAME and LNAME.
I want to copy these fields into cells B1 (for SSN), B2 (for FNAME) and B3 (for LNAME).
I will use a combo to select the recorset to copy.
My problem is how to copy data into the template. I understand that it is necessary to run a copy of the template and then copy the data into the new xls file.
Is there a way I can do this via code? Code help is appreciated. Thank you.
I’m a newbie to Access, I have done some simple things but I now have to do something that is out of my league.
I work in an engineering company and I'm trying to make a database that the engineers can select a specification quickly, a specification or spec is like a table. Things like materials, ratings, pipe Schedule, etc are stored in it. The spec says how strong a pipe will be.
When selecting a spec you have to look at the service (what the pipe is caring, eg high pressure steam, or low pressure water). Services also store things like Design Temperature and Design Pressure.
You are giving the Service, and you have to select a spec that will be able to handle the Design pressures. To do this you compare the Design pressure to the Maximum allowable Pipe pressure (it is calculated at the join (flange) as this is the weakest point).
One other pike of information is the Tables that have the Maximum allowable Pressure. To find the Maximum allowable Pressure, you need to know the Material, Rating and the Design Temperature. Each material has its own table.
If you have a look at the attached file then in tables, SPec has Spec ID (Spec) which is Primary Key. Flange Material (FlangeMat) and Flange Rating (FlangeRating).
The Flange Material should take you to another table, FlangeMat. This table is used to get you to the correct Maximum allowable Pressure table.
There are 2 Maximum allowable Pressure Tables, TABLE_211 and TABLE 212. Here the rest of the information from the Spec is needed (Flange Rating (these are the field names with 150, 300, 1500, etc numbers)). And also the Design Temperature is needed of the service.
Then it looks up the Maximum allowable Pressure from the Table and displays it. I was thinking a form.
Also the design Pressure needs to be displayed so the engineer can compare the two.
So for the form, there will need to be a drop down that has the Service, A drop down that has the Spec, and the two values, the Design Pressure and the Maximum allowable pressure.
All I have done is shown on the file, I started to play around with the Relationships and tried to create a Form but I could not get it to work so. I deleted it and left it with just the tables. If possible can someone please help me, maybe even get my file to work and the post the working one, I will then be able to understand it.