Modules & VBA :: How To Do A Join Range Query
Dec 9, 2013
I am currently trying to do a range join query in access (not an expert at all with it) but I realized that it's not easy. It probably needs a VBA code but still not sure how to do it. I basically have a column with number of days e.g
Number of days
18
1
0
56
8
-19
369
Eventually I would like to do a range join query (similar to excel range vlookup) with the following table
NumberofDaysWeeks
0 Same Day
1 1 Week
7 1 Week +
14 2 Week +
21 3 Week +
30 1 Month +
60 2 Month +
90 3 Month +
120 4 Month +
150 5 Month +
180 6 Month +
210 7 Month +
240 8 Month +
270 9 Month +
300 10 Month +
330 11 Month +
360 12 Month +
After joining the two tables with an approximate range (as in excel) the final table should look like below
Number of days Weeks
18 2 Week +
1 1 Week
0 Same Day
56 1 Month +
8 1 Week +
-19
369 12 Month +
Note that value -19 is an error so it should not return anything however i still want to see the value in the final table.
View Replies
ADVERTISEMENT
Apr 15, 2015
I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.
ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2.
[ABCTABLE]
ABCORDER
ABCPART
[code]...
View 4 Replies
View Related
Jan 24, 2006
ive got two tables that i need to merge.
I need the join to work so a date within table b links to a range in table a of 2 columns start and end- is it possible?
Ie
Table A
patient number
admitted date
discharged date
etc
etc
Table B
Patient number
operation date (will lie in admit dis date)
NB a patient number may exist in table A and not B, or table A twice at differnt times and B once.
thanks, ren
View 1 Replies
View Related
Jul 29, 2013
Context is I have a TABLE 1 that has a record for every package shipped with various details about it, include how it shipped, and the date. Table 2 is a key to go from the ship method to a better grouping we deem service level. Table 3 is a Fuel Surcharge (FSC) Table. It includes various FSC % that vary by different service levels and different date ranges. In the end I need to know the amount of pkgs shipped (by month and servicelevel) and the relevant FSC.I have several tables in my database but we should be able to focus on 3 to explain my situation and only the relevant fields:
TABLE 1 (Main Data):
ShipDate
ShipVia
Table 2:
ShipVia
ServiceLevel
FSCType
Table 3:
FSCType
StartDate
EndDate
FSCPercent
DESIRED RESULT:
Month
ServiceLevel
FSCPercent
PkgCount
Table 1 is Left join on Table 2 based on ShipVia
Table 2 is Left join on Table 3 based on FSCType
The issue is Table 3 is dependent on Table1.ShipDate being between the date ranges as well.
There are overlapping date ranges, however they are differentiated by the FSCType field.The only semi-feasible solution I currently have is to expand Table 3 to include every date, and not date ranges. This will create an issue due to size of date ranges etc.
View 8 Replies
View Related
Dec 20, 2013
I have a excel object I have opened, and want to copy a query into a named range on the workbook.
heres the start have made
Sub openExcel()
'WillR - opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
[Code]...
View 2 Replies
View Related
Nov 2, 2007
hi im pretty new to this, using access2000 i believe
i have a large amount of data and have been able to join them successfully. using the "left" "Operator" "right"
one of the things id like to join are dates, but the dates i want to join are not exactly equal to each other so i cant just use a operator "=".
i need to be able to tell access to link the dates within say 30 days of each other.
Ideally something like “left” “>” “right” -30
currently im using excel to do this for me but if access can do it then it would be great.
in excel i use a macro which matches dates within 30 days, and just use a simple
If range("A1") < range("A2") + 30 and range("A1") > range("A2") - 30 then ...........
so is it possible to have access join things within a range?
would be grateful for any help ;)
View 2 Replies
View Related
Jun 12, 2013
We have a huge Database (relative huge ). Because of that I had to split the database on multiple tables to not lose performance. The main seperation is for every year and Terminal so I have now 6 tables with the same data and structure. Its making maintaining, changes and coding realy hard
Tables:
tbl_DL_Salzburg_2012
tbl_DL_Salzburg_2013
tbl_DL_Munchen_2012
tbl_DL_Munchen_2013
tbl_DL_Ulm_2012
tbl_DL_Ulm_2013
At the end of every year we have more than 10 000 records in each table.
The second Problem for joining the tables is that i have to filter them on Terminal (Salzburg, Munchen, Ulm) and on Year (2012, 2013, 2014). The Form I use on that table is a split form (reason is the fast filtering and searching in the datashed).
The first problem with the large amount of records i can solwe with an sql server but there is still the problem with the filtering
I tryed to use the form RecordSource and an SQL statement but in that case (depends how the SQL is struktured and the RecordSource is used) the users cant add or delete records in the table. It shows them that the record is added or deletet but when they leav the form they are all gone
P.S. It's an multiuser database. Making things more complicated
How to solve this second problem with filtering and not disturbing adding and deleting records.
I know that splitforms are not good but the users don't want to use something other that also takes me away the solution with unbound forms
I would like to move to one table until the end of the year. If not I have to handle 9 same tables.
View 2 Replies
View Related
Sep 4, 2014
The below code exports a table (via a function) to a spreadsheet and saves it in a defined location. the code then opens the file does some work with it then moves it(left the move bit out as it works fine)
so when i run the code it works absolutle fine, table is exported, work is done and file is moved. however if i run the code again it fails, i get the error message out of range. i was originally getting this error when using the .usedrange.copy so i commented this out and now i get it on the next line that tries to work with the file so obviously something is wrong in my logic.
Code:
Private Sub Export2JDE_Click()
Application.Run ("JDE_Export") 'Exports to an xlsx file in the location described in the function.
Dim xlApp As excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
[Code] .....
View 8 Replies
View Related
Oct 4, 2013
I am trying to simply find the sum of a range of data...I have tried putting the range into a the formula, and also creating aliases for the cells but it either doesn't work or I keep getting a name error in excel and I just can't seem to get the right code:
The range I am trying to sum is:
Range(Range("B2"), Range("B2").End(xlDown)).Select
And I can find the cell that I want the sum to go into by:
Range("B2").End(xlDown).Offset(1, 0).Select
As I said I have tried a few things but just get this right. Should I be using Value, Formula or FormulaR1C1?
View 5 Replies
View Related
Apr 9, 2014
I've got an issue pertaining to inner joins within an update query. This query will not run now (giving the expected "must use an updateable query" error) due to two fields involved in one of the inner joins are now both memo fields.
Is there any way (and I assume there must be in vba) to recreate this query so it will run with inner joins involving memo fields?If not, is there a way in vba to do a find and replace within a table? For example, replace the "A" in "A-001" with "B"?
View 5 Replies
View Related
Oct 23, 2014
I'm trying to create some code that will create a new folder depending on the ID that is currently being added to the database.What happens at the moment is a new ID is generated which in turn is job reference.When this is added to the database a folder is created with a prefix of this ID number and a 20char test specified in a text field by the user.
What I am trying to achieve is this:If the ID = 57...Then a folder is create called 1 - 500 (and ignore if one has already been created, which it should have at ID number 1)...When the ID Number 501 comes along another new folder is create called 501 - 1000 etc etc.The idea is not to have 3000+ folders in just one folder making it look a bit messy and lengthy to look through.
View 3 Replies
View Related
Jul 30, 2015
1. I have a sample database table in excel where depending on column 2 value >1 is checked and logical results by IF check placed in Column D. The values having "Y" in column D are placed in Columns E:F.
2. Now I want to place count of values meeting the condition on last cell of the filled E:F range.
3. VBA code written by me proceeds fine and places values meeting condition in column E:F
4. I am beginner to VBA and finding difficulty in proper code for placing sum of counts of acceptable values in range F2:F7 in cell F8 ie last value in the array. Presently I have placed the value by In-built Count function in Excel.
File Count on Macro from another table is attached.
View 1 Replies
View Related
Jul 17, 2014
I am having problems with Dlookup from a table which is linked to Excel.The heading on the fields from Excel have been imported as F1, F2, F3 etc... the code I am using is as follows .....
LOOK = DLookup("F6", "DATABASE_LOOKUP", "F1 = 1338087")
F6 = A date I am looking up.
DATABASE_LOOKUP = table I am looking up.
F1 = 1338087 = The record I am looking for.
View 5 Replies
View Related
Jul 16, 2014
I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.
Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.
Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long
DoCmd.Hourglass True
[code]...
I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.
Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function
how to alter the code to make it work for my specific situation.
View 6 Replies
View Related
Dec 17, 2013
How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?
On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:
Code:
Private Const strNomeTblFonte as string = _
"SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos
ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;"
Private Sub Class_Initialize()
Set mCol = New Collection
[Code] ....
Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.
After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():
Code:
Recordset.Properties("Unique Table").Value = "tblClientesExamesRequisitados"
But only generates this: Run Time Error 3265 - Item not found in this collection...
I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?
View 3 Replies
View Related
Nov 10, 2006
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows
Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
View 7 Replies
View Related
Nov 16, 2013
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
View 6 Replies
View Related
Sep 10, 2013
I have written the following program code:
Dim xlApp As New Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strXLS As String
strXLS = "c:AccessMyfile.xls"
Set xlwrkBk = xlApp.Workbooks.Open(strXLS)
[code]...
The program is stopping at " Set xlSheet = xlwrkBk.Worksheets("singles")"
and I am receiving the following error box:
Runtime error 9
Subscript out of range.
I don't understand this error because I am using the same program code with another Excel file in another module and it works perfectly.
View 6 Replies
View Related
Aug 8, 2013
I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:
Code:
Option Compare Database
Private Sub cmdCal_Click()
Dim sql As String
Dim rs As Recordset
Dim qdef As DAO.QueryDef
Dim hours, waste, produced As Integer
[code]....
But it returns nothing, When i remove the errorHandler, it says that no records were found.
View 2 Replies
View Related
Mar 18, 2015
I've adapted some code I found which works and allows me to import data from my access table "Device Text" to a specific range in an existing excel worksheet by copying a field "TextEdit" in the recordset. the code is placed in a module behind a command button on an excel worksheet. I have used import to excel because this seems to be less complicated for my needs than exporting from access.
Everything works fine if a single range is set , however I would like to set the range(rng) criteria in the code below to start at a certain cell depending on the value in another field in my access table called "LoopID" this is a number field which is not unique and can be 1,2,3 or 4. I know it wont be evaluated but I've entered the gist in red in my code.
I don't need to import it but I suspect I have to bring LoopID into the recordset somehow to use it,
Code:
'DIM STATEMENTS
Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim i As Long, n As Long, lFieldCount As Long
Dim rng As Range
'instantiate an ADO object using Dim with the New keyword:
[Code] .....
View 5 Replies
View Related
Mar 17, 2015
I am trying to update the range for the graph automatically and I'm calling this from Access because my data exists in Access. I've attached a sample file.what I am trying to do:
1) click on the button
2) button will update the usedrange in "trending" tab
3) have the graph's data range based on the usedrange.
Code:
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "C:Documents and SettingsgjiaDesktopmetric_DEV.xlsx", True, False
[code]...
note: the columns will grow bigger to the right, which is dynamic, so I've used usedrange method, but the code fails on the usedrange line saying the object or method is not being supported...
View 3 Replies
View Related
Jun 17, 2015
I have a multiselect listbox and two date fields (StartDate & EndDate) in an Access form.I am trying to add records to the Table through the form on a button click.I select multiple items from the list box and the date range between the start date and end date will be equal to the items selected from listbox.For each item selected from the list box I need to add a separate record with a date.So the first record will have List box item selected1 and the start date.Next record will have item 2 from list box and date as dateadd("d",startdate,1)And final record will have last item selected from the listbox and date as enddate.
View 5 Replies
View Related
Oct 2, 2013
I am trying to use VBA to create a filter by date range. the user inputs 2 dates and the database filters all records by dates in between the 2 dates,
Code below
Dim var_CustDate1 As String
Dim var_CustDate2 As String
var_CustDate1 = InputBox("Please enter start date in format DD/MM/YYYY", "Enter Date", Date)
If Not IsDate(var_CustDate1) Then
MsgBox ("not a valid Date")
[Code] ....
I've tried every combination of format for the dates but this is the closest ive got it to work,
if i enter dates 01/09/2013 and 12/09/2013 the filter works for the days in the month but also displays previous years, but if i change the dates to 01/09/2013 and 13/09/2013 it starts displaying all dates for all years in the months September, October, November and December.
View 3 Replies
View Related
Nov 30, 2014
I am trying to create a form that will select records by date range. The form contains 2 textboxes, with pop-up calendars to select dates. I managed to come up with this code, however, it's not working. Whenever I run the code, my database just goes blank, as though it's selecting something that is not in the records.
Code:
Private Sub Command16_Click()
Dim Task As String
Dim startDate As Date
Dim endDate As Date
startDate = Me.Text12
endDate = Me.Text14
Task = "SELECT * FROM Final WHERE Final.Timestamp BETWEEN #" & startDate & "# AND #" & endDate & "#;"
Me.RecordSource = Task
End Sub
How does one actually do the date range search? Could it be because my timestamp actually has both date and time, that's why the date search is not working?
View 5 Replies
View Related
Jul 16, 2015
I have a form that has several drop down menus on it. I use the form to filter the database to only the options that are put into the drop down windows. I have it set up so that every window does not need to be filled in for the filter to work. Now I am trying to figure out how to put the option of a date range on my form as well. The code that I am using to make everything up to the date range work is.
Code:
Private Sub Search_Button_Click()
DoCmd.Close acForm, "Admin_CompletedPartSearch"
str_Form = "Admin_CompletedPartSearch"
str_Filter = "(1=1)"
[Code] ....
I can't figure out what I would need to do to make a date range also fit into this code but not to be required.
View 1 Replies
View Related
Jun 2, 2014
I have a form with textboxes and comboboxes and a Button,when button is clicked, a new query is made, with criteria based on boxes on form.For example this one :
Quote: If Not IsNull(Me.cmbType) Then Where = Where & " AND [TypeID] = " & Me.cmbType
What would be the statement for a range between to combo boxes.I have 2 comboboxes (cmbBox1 and cmbbox2), show only records equal and between the ones selected in cmbBox1 and 2.
View 9 Replies
View Related
