Trying To Import Or Recreate A Multiplier Formula From Excel To Access
Aug 9, 2005
Really need help on this one, here is an excel expression:
X Multipliers
$ ValueMultiplier
is less than5then *3.8
is less than50*3.2
is less than100*2.8
is less than200*2.4
is less than500*2
is greater than500*1.8
so if price is less then 5 then multiply by 3.8 and so on. In excel it's in a separate worksheet, and other worksheets linked to it and adjust their Sale Price from the Cost column. Someone else created this spreadsheet, and i couldn't find a multiple discount help in access. I tried creating a query
=([PriceBook_OLD].[Cost]<=5)*3.8 Or ([PriceBook_OLD].[Cost]<=50)*3.2 Or ([PriceBook_OLD].[Cost]<=100)*2.8 Or ([PriceBook_OLD].[Cost]<=200)*2.4 Or ([PriceBook_OLD].[Cost]<=500)*2 Or ([PriceBook_OLD].[Cost]>500)*1.8
but it doesn't work. Please help!!!!
Thank you very much
V.
View Replies
ADVERTISEMENT
Mar 4, 2014
We have a spreadsheet in Excel that I need to recreate in Access rather than link to it (not an option). I would like to create it column by column but can't even get past column 1! We have two age fields; 1) current age and 2) "show till" age. I need the datasheet to autofill column 1 beginning with the current age through the "show till" age then stop.
Column 2 begins with the current year and autofills through the "show till" age then stop.In Excel it's just a matter of adding 1 to the previous cell to create the next age or year.
View 4 Replies
View Related
Feb 14, 2008
I have an excel worksheet with the following formula
=IF(b40<50000,b40*0%,IF(b40<100000,b40*2.5%,b40*5%))
and I would like to convert it for use in access.
I came up with the following:
=iif([TOTAL]<50000,[TOTAL]*0%,IIF([TOTAL]<100000,[TOTAL]*2.5%,[TOTAL]*5%))
I get the following error: "The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it with quotation marks."
I am probably missing something very simple here, but I have been unable to come up with a solution.
Any help would be greatly appreciated.
Regards,
Michael
View 3 Replies
View Related
Aug 8, 2005
In my worksheet there are 3 columns; 1. Date Completed (J), 2. New Review Date(K) and 3. Status(L)
I'm currently migrating from excel to access, the finishing tounces are needed, like the status.
The following excel function I want to convert to access
=IF(K1="","Outstanding",IF(K1<TODAY(),"Outstanding","Complete"))
(K1= Next Review Date)
I did do a search, but I couldn't really find anything.
How do I convert this please?
View 1 Replies
View Related
Jan 11, 2006
Hi does any body no the access syntax to create this excel formula
=POWER(SQRT(L5/10)*47.1,2)/3769.9
Into access
Thanks
John
View 2 Replies
View Related
Jun 1, 2005
I have 5 fields(First,Second,Third,Sum,Sum1). The Sum field is the sum of the first, second and third. In excel I am using the following formula/expression to calculate the Sum1 field =IF(E1<0,E1+10,E1+0 - and it brings back single digit.
e.g.
First=3, Second=5, Third=8, Sum=16, Sum=6
Can I do this in an Access query, if so how?
Thanks in advance for any help.
View 1 Replies
View Related
Sep 30, 2012
PRODUCT
VALUE A
VALUE B
WW
10
100
XX
20
200
YY
30
300
ZZ
40
400
I have above table in access. I want to use this table in query to find value of any product using a formula (Value A*X)+Value B. The product and X query picks up from another table. The same can be done easily in Excel using Index, Match formula but how in Access.
View 1 Replies
View Related
Nov 5, 2014
I am calculating the check digit for a 13 digit number in Excel and I need to be able to do it in Access.
My Excel formula; =1&E1&MOD(-SUM(MID(1&E1&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14}, 1)*{3;1}),10) -- cell E1 holds the base 12 digit number.
My attempt to convert to Access; =1 & [txtBase] & [-SUM(MID("1" & [txtBase] &0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}) mod 10] -- txtBase holds the base 12 digit number
View 7 Replies
View Related
Mar 11, 2008
I Cannot import excel data to an access table .The type is the same.The colums are the same number.No duplicate values exist in the colum that is primary key.
WHYYYYYY?
View 11 Replies
View Related
Sep 2, 2005
:confused: Hi all - hope someone can help I dont know where to start.
I've got a cmd button that imports data from a excel sheet - that actual code is working fine, however when it runs it causes Access to freeze.
The data in imported correctly - so it must complete the process but Access locks up and has to be ended via task manager.
Any one got any clues why this is happening and how I can stop it?
This is an Access 97 database running mainly on NT and 2000
Private Sub LoadActualsDataButton_Click() On Error GoTo Err_LoadActualsDataButton_Click
' This procedure performs a two file match between the Actuals table (the Master file) and ' The Actuals spreadsheet file (the Transaction file).
'
' Keys : Study Code|Work Package|Period
'
' If the Master key < Transaction key then
' Read the next Master record.
' If the Transaction key > Master key then
' Add the transaction record to the Master file
' Read the next Transaction record.
' If the Master key = Transaction key then
' Update the value on the Master record with the value on the Transaction record
' Read the next Master Record
' Read the next Transaction record.
'
' End of File processing
' At End of File on the Master file, set the Master key to "ZZZZZZ"
' At End of File on the Transaction file, set the Transaction key to "ZZZZZZ"
' Continue processing until both keys are equal to "ZZZZZZ"
Dim MyDB As Database, MySQL As String, MySet As Recordset Dim appExcel As Excel.application Dim MyFiles As String Dim MasterKey As String, TransactionKey As String
Set MyDB = CurrentDb()
Set appExcel = CreateObject("Excel.Application")
' Set up the transaction file (Actual Data Spreadsheet)
MyFiles = appExcel.GetOpenFilename("Excel Files(*.xls),*.xls", , "Open Actuals Spreadsheet") If MyFiles = "False" Then Exit Sub
appExcel.Workbooks.Open FileName:=MyFiles, ReadOnly:=True appExcel.Visible = False
' Check that this is a genuine Actual spreadsheet On Error Resume Next Let Err.Number = 0 appExcel.Sheets("Sheet1").Range("B1").Select
If Err.Number = 9 Then
MsgBox "This is not a valid Actuals Spreadsheet."
appExcel.Quit
Exit Sub
End If
If appExcel.ActiveCell <> " Extracted Actuals Data" Then
MsgBox "This is not a valid Actuals Spreadsheet."
appExcel.Quit
Exit Sub
Else
appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2) End If appExcel.Visible = True
' Set up the Master File (Actual Table)
MySQL = "SELECT Actuals.[Study Code], Actuals.[TBCS Code], Actuals.[Year/Month], Actuals.Actual "
MySQL = MySQL + "From Actuals "
MySQL = MySQL + "ORDER BY Actuals.[Study Code], Actuals.[TBCS Code], Actuals.[Year/Month]; "
Set MySet = MyDB.OpenRecordset(MySQL)
If MySet.EOF Then
MasterKey = "ZZZZZZ"
Else
MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month] End If
Do Until TransactionKey = "ZZZZZZ"
If MasterKey < TransactionKey Then
' Read the next master record
MySet.MoveNext
MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month]
GoTo Next_Loop
End If
If MasterKey > TransactionKey Then
' Add a new record from the Transaction to the Master
MySet.AddNew
MySet![Study Code] = appExcel.ActiveCell
MySet![TBCS Code] = appExcel.ActiveCell.OffSet(0, 1)
MySet![Year/Month] = appExcel.ActiveCell.OffSet(0, 2)
MySet!Actual = appExcel.ActiveCell.OffSet(0, 4)
MySet.Update
' MySet.Requery
appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2)
GoTo Next_Loop
End If
' Keys are equal so update the Master with the Transaction value
MySet.Edit
MySet!Actual = appExcel.ActiveCell.OffSet(0, 4)
MySet.Update
' GoTo Next_Loop
appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2)
MySet.MoveNext
MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month]
Next_Loop:
Loop
Exit_LoadActualsDataButton_Click:
Exit Sub
Err_LoadActualsDataButton_Click:
MsgBox "An has occured." & vbCrLf & vbCrLf & _
"Error number: " & Err.Number & vbCrLf & vbCrLf & _
"Description: " & Err.Description
Resume Exit_LoadActualsDataButton_Click
' = Mid(ActiveCell, 1, (Len(ActiveCell) - 1)))
End Sub
Private Sub MainMenuButton_Click()
On Error GoTo Err_MainMenuButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Main_Menu"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_MainMenuButton_Click:
Exit Sub
Err_MainMenuButton_Click:
MsgBox Err.Description
Resume Exit_MainMenuButton_Click
End Sub
Private Sub MaintainContactTableButton_Click()
On Error GoTo Err_MaintainContactTableButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmBDTSContactsMaintenance"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_MaintainContactTableButton_Click:
Exit Sub
Err_MaintainContactTableButton_Click:
MsgBox Err.Description
Resume Exit_MaintainContactTableButton_Click
End Sub
Private Sub MaintainersNBTUsersButton_Click() On Error GoTo Err_MaintainersNBTUsersButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmBDTSMaintainNBTUsers"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_MaintainersNBTUsersButton_Click:
Exit Sub
Err_MaintainersNBTUsersButton_Click:
MsgBox Err.Description
Resume Exit_MaintainersNBTUsersButton_Click
End Sub
View 4 Replies
View Related
Sep 7, 2005
Hello All
is there a way (i guess using VBA) to import an excel spreadsheet into an access table without having to go through the wizards, if you know all the field names and other variables ??
Yours in anticipation
DJS
View 8 Replies
View Related
Feb 5, 2006
Hello All,
I thought i would ask this question an how to import data from excel into access.
I am using sage to invoice clients and i can output this in to excel format then i would like the best and simplest way to pull the data though into access.
The data file has column headings at the first row ie A1 though to J1 and after this is the data, i have a table setup with the relivent table names, I had thought how easy this would be, but as always this was not the case.
The outputed excel sheet will vary in location so i think i need a diolog box to select the file and then some how link it to extract the data, is this right and if so how can it be achived.
Any thoughts
Alastair
UPDATE
**************************************Resolved Thanks to Pat Hartman *************************
View 2 Replies
View Related
Dec 13, 2007
Hi All,
I'm having a Excel Sheet With first column which has numeric data for first 70 or 80 records then comes some string data, here i'm using this statement to import data from excel to MS Access DataBase, but its not retriving the records which were in string data type, is there any other way to do this...?
SELECT [Item #], [Description], [Pack Size], [Price] FROM
[Database=C:Test.xls;Excel 8.0;HDR=Yes;IMEX=2].[Sheet1$] Where
[Item #] Is Not Null
thank's in Advance.
- Prakash.C
View 4 Replies
View Related
Nov 5, 2004
I want to import data from access to excel my excel file has columns say
a b c d e f g h i j k
my access table has columns
b d e f g i k
now when i run transferspreadsheet, since some columns are not there in the access table, it gives a error. how can i import this data?
View 14 Replies
View Related
Dec 11, 2004
OK. I feel like an idiot but I did read the manual, Googled, and Microsoft help, but still cannot do it.
The problem is on the import feature, there is no option to choose an Excel file.
I lowered the macro security level to take it out of "sandbox" mode, I reinstalled
office and selected run all features again. I updated as well.
I tried blank databases to import to. No luck. I go to external data, import and I can choose ODBC, XML,
sharepoint or Access files only.
I am using MS Office Pro 2003. Thanks for the help.
View 4 Replies
View Related
Jan 10, 2008
n import is done from access to excel using docmd.transfersheet
DoCmd.TransferSpreadsheet 1, 8, "dbo." & QuerySending, SavedName, True, "InputRange"
In ADP the error displayed is runtime error 3274 and in ade it displays "The file is not in recognizable format"
This happens for only one user.
any other reasons for this problem.
Please suggest
View 3 Replies
View Related
Jul 18, 2013
I'm trying to import an excel file that includes a column of Part Numbers that I am interested in. I am trying to import that excel file into an access table called 'TableForImport'. That table has a column called 'PartNumber'. Thing is it's not just one Excel file that I am uploading...although I am uploading one file at a time. So the column name for each excel file table is different but they all have one column for the Part Numbers that I am interested in.
My code won't allow me to import the excel file into table 'TableForImport' unless they have the same column name! They never will! How do I accomplish my task without editing the excel file information? Here is my code:
Code:
CurrentDb.Execute "CREATE TABLE TableForImport " _
& "(PartNumber CHAR);"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TableForImport", Me.ExcelInput, True
View 8 Replies
View Related
Feb 5, 2014
I'm have an Excel workbook (2013) with two dozen sheets that I'd like to import to Access. The code below is failing when it reaches the "Do.Cmd ..." and it's showing a type mismatch error.
Code:
Sub ImportAllSheets()
Dim wkb As Excel.Workbook
Dim sht As Excel.WorkSheet
Dim xl As Excel.Application
[code]...
View 4 Replies
View Related
Jan 24, 2006
I am trying to import an excel spreadsheet into access. The problem is that spreadsheet is laid out as follows:
................net.........billed.......gross.... .variance
fieldname1...1value1...1value2...1value3...1value4
fieldname2...2value1...2value2...2value3...2value4
fieldname3...3value1...3value2...3value3...3value4
(the '...' are just used for spacing in this post - not part of spreadsheet)
the 'variance' column contains a calculation. Can anyone tell me how to bring all of this into an access table?
Thanks, in advance!
View 10 Replies
View Related
Oct 18, 2006
I've been trying to load data from multiple excel worksheets in multiple workbooks into one table in Access. The first one loads fine, but after that I get errors and can't load anything else. All of the sheets are in the same format, so that shouldn't be the problem. Every solution I've tried has been a bust. All I want is to take all of my data and put it into one big database. Any suggestions?
View 2 Replies
View Related
Jan 15, 2007
When I try to import from a spreadsheet, Access crashes. No errors, no error log entry. At first I thought it was because my file was password protected, so I tried a new file with no password, but it still crashes Access.:confused:
View 2 Replies
View Related
Mar 27, 2007
Hi
I am having trouble trying to import (manually) Excel 2007 spreadsheets into an Access 97 database on Windows XP.
I have an Excel 2007 workbook saved as .xlsx. When I try to import it by changing the "files of type" drop down list to "Excel" and typing in the file name of the 2007 file with the .xlsx file extension I get an error message
“The Wizard is unable to access information in the file ‘Filename’. Please check that that the file exists and is in the correct format.
I have changed the registry to allow .xlsx files to be imported, but still get the error.
I have changed the registry
HKEY_LOCAL_MACHINESoftwareMicrosoftJet3.5Engi nesTextDisabledExtensions
To include .xlsx
Any ideas you may have would be greatly appreciated.
Thanks
View 5 Replies
View Related
Nov 30, 2004
In importing data from Excel 2002 to Access 2000, I have several columns of text data that has more than 255 characters; however, importing into Access does not give me the option to redefine those data fields from "Text" to "Memo". As a result, Access truncates the data to 255 characters.
Is there anyway to work around this issue by still using the Excel file? I have saved the Excel file as an HTML file and this seems to work, but it is an extra step that I would like to avoid.
Thanks for any help.
View 1 Replies
View Related
Apr 27, 2008
Hello guyz,
With the help of 'Import External Data' wizard, my import did not happen. My datatypes and Col. Headers in excel are matching for what I have in access table. Alternatively, I selected to import the data into new table and all of the data got imported. My question, since I already have some data in table I want data to be imported, how can I do about bringing all the data from new table I created during import to the existing table. Please suggest. Thanks in advance.
View 3 Replies
View Related
Jun 6, 2014
I'm stuck on a step where I want to import an excel worksheet into the msaccess like we do normally. I do not have any data inside, it's just the header I will be importing. The data will be feeded by other forms based on some selections. My requirement is the "Default value" of each field should be set to 1 as we see in the property of a table in design mode.
The data would be updated later for some fields via macro or commands, but the fields were nor touched should be set to 1 (Value).
View 4 Replies
View Related
Jun 27, 2012
Im familiar with Ms Access, but have never used VBA or Scripts. I have 37 Excel files with the same data and would like to import into one file. Data will be received on a monthly basis into the same directory and I would like to automatically upload the data into the same file in Access.
View 1 Replies
View Related
