Modules & VBA :: Count Of A Variable Range
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 Replies
ADVERTISEMENT
Mar 14, 2014
I wanna extract a variable count of columns and transpose it to another table.
My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC
My target table is called Forecast_Quantities
Tried the following:
Code:
Public Sub TRANSPOSE()
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim varItm As Variant
Dim I As Integer
Set rs = CurrentDb.OpenRecordset("FC")
[Code] ....
Somehow it doesn't recognize the I FC column in the table FC.
View 1 Replies
View Related
Jan 19, 2015
Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb
[code]....
View 4 Replies
View Related
May 8, 2013
I am trying to write a query from a main table which will show records which have a date of less than or equal to a Variable (tempvar), however the variable (selected from a form) may be left blank in which case all records should be shown.
I have successfully used the following but the records returned are only the specific date choosen not that date and all prior
Like Nz(([TempVars]![tvcldate]),"*")
So I have tried the following but it doesn't work.
Like Nz(<=([TempVars]![tvcldate]),"*")
View 10 Replies
View Related
Jul 8, 2013
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
View 14 Replies
View Related
Oct 3, 2014
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
View 3 Replies
View Related
May 8, 2006
I am trying to run a query on a table [tblMain]. I have an input form where the user enters a date range. I would like to count the Month to Date Sales. The month would be that of the end date (forms!frmflash!enddate). How could I count the number of sales between the first of the month and the end date? Each record has a salesdate1, salesdate2, salesdate3, salesdate4, salesdate5, and salesdate6. I need to look in each of these fields and count the sale if it is between the 1st of the month and the enddate.
View 3 Replies
View Related
Aug 20, 2014
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
View 7 Replies
View Related
Jul 8, 2015
For example, I have 1 table with 3 fields : Name, Birth date, Gender. Then I want to create a report where all data are grouped by age range and gender
View 1 Replies
View Related
Jul 31, 2007
The below code gives me a count based on a month and I would like to get the same count based on date range. I use a calendar control to provide the date range. See quote below: I am hoping someone can steer me int he right direction to convert the code from monthly count to a date range count.
Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]
SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS FORMS,
DCount("[Date_of_Change]","all_trucks_table","[PQC #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS PQC,
DCount("[Date_of_Change]","all_trucks_table","[ECN #]=True AND Month([Date_of_Change])=month(now) AND Year([Date_Of_change]) = year(now)") AS ECN,
DCount("[Date_of_Change]","all_trucks_table","[MCN #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)") AS MCN
FROM all_trucks_table
GROUP BY DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)"),
DCount("[Date_of_Change]","all_trucks_table","[PQC #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)"),
DCount("[Date_of_Change]","all_trucks_table","[ECN #]=True AND Month([Date_of_Change])=month(now) AND Year([Date_Of_change]) = year(now)"),
DCount("[Date_of_Change]","all_trucks_table","[MCN #]=True AND Month([Date_of_Change]) =month(now) AND Year([Date_Of_change]) = year(now)");
View 1 Replies
View Related
Mar 21, 2014
I am looking for Access VBA code to count number of Day Name in given range
E.g. March 01, 2014 to March 31, 2014
Mon = 5
Tue = 4
Wed = 4
Thu = 4
Fri = 4
Sat = 5
Sun = 5
View 11 Replies
View Related
Sep 3, 2013
I have been stuck trying to write an expression that will calculate the Yes values in a range of 16 Yes/No fields.
I have attached a screen print of the report I am working on.
View 5 Replies
View Related
Sep 1, 2006
Apologies if this is a basic question. Here's my situation:
I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).
I have a query that seems like it should do the trick, but Access doesn't like it:
UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");
Here's the error I get:
Operation must use an updateable query.
Thoughts?
View 5 Replies
View Related
Aug 23, 2012
I need to identify and parse (substring) a number of digits from a barcode. The barcode digit count will vary, based on the number of details included in the barcode.
For example: the barcode for GS1 DataBar begins with a 4 digit code of 8110 identifying the code as GS1. The 5th digit then identifies the number of digits directly following which constitute the manufacturers code, which can range anywhere from 6 digits to 12 digits. I need to pull the number of digits out of the barcode based on the value of the 5th digit placeholder and pull only that many digits from the string and replace them into another field. There are potentially 70 total digits in the barcode. I have only provided the first section, which I am asking the question on.
Simply put: I need to tell the substring to identify the number to parse from digit 5, (which will have a min of 6 digits and a max of 12)
How do I pull the information out of a string to create a substring in another field by first defining the number of digits to pull out (Digits 6 to 17 min of 6 digits and a max of 12 digits) based on a value within the string (digit 5= value)
So if Digit 5 is 6 the returned value would be: Digits: 6 7 8 9 10 11 (6 6 6 6 6 6 )
If Digit 6 is 9 the returned value would be: Digits: 6 7 8 9 10 11 12 13 14 (9 9 9 9 9 9 9 9 9)
The first portion of the barcode is below: There are other pieces necessary to be pulled the same way, but from other sections, and based on yet another X desinator field (the next with a min of 1 and max of 5) I assume the pull would be similarly completed.
I don't know how to write code, I have used formulas but not this detailed or specific.
Ex: I know = Min (barcode, 5, 6) would pull the 6 digits after the 5 spot, but how do I vary the 6 (count) number in the formula?
Example below: No additional spaces, dashes, letters etc. (A's = 1-4, X = 5, M's = 6-17)
A A A A X M M M M M M M M M M M M
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
View 2 Replies
View Related
Sep 30, 2014
I've got a single table with multiple fields, three of which are a date field ('DDate'), a time field ('TimeET') and a unique identifying field ('Unique Call Key'). I'm attempting to write a query in the QBE that will allow me to count the number of instances of 'UniqueCallKey' for each hourly time interval (7:00:00 AM - 7:59:59 AM, 8:00:00 AM - 8:59:59 AM, 9:00:00 - 9:59:59 AM, etc) for any selected date range (BETWEEN 'DDate'(1) AND 'DDate'(2)). When I try to simply use the Count function on 'UniqueCallKey' as an Expression and 'TimeET' with 'Like '7:*AM' as a Where criteria then do the same with another instance of 'UniqueCallKey' and 'Like 8:*AM' as the criteria for a second Where criteria for 'TimeET' the query returns an empty set. What I'm trying to accomplish would be a column of dates, a second column that counts the number of instances of 'UniqueCallKey' at the 7AM interval for each date in column 1, a third column that counts the number of instances of 'UniqueCallKey' at the 8AM interval for each date in column 1, etc to a final column for counts at 7PM.
View 3 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
Aug 26, 2014
If I set a variable inside of a subroutine, it is set to nothing upon the end of the sub. Can I set a variable outside a sub and set its value, so that you can use it within subs?
View 14 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
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 4 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
May 12, 2014
How can I define a variable which can be used by another Sub and of course the value stored in it?For instance:
Private SUB A ()
DIM A1 as String
A1 ="ABC"
END SUB
PRIVATE SUB B()
PRINT A1
END SUB
View 5 Replies
View Related
Apr 8, 2014
I have an application where several different procedures are run repeatedly every xx seconds to get live updates from a server (different procedures depending which form the user currently has open.) Within each procedure, a separate single procedure is called only if a stored boolean setting is set as true. The setting itself is only changed very infrequently, such as on application startup, but needs to be initially set (i.e. it cannot just have a default of False if it is not set.)Because of this I am storing the setting in a single record table and have a function 'UsingWPilot' to return the boolean value. However, given the frequency that the setting needs to be accessed, and the fact that speed is crucial in the running of the app, I am considering adding a global string variable and modifying the function to look at the variable (possible values 'N' and 'Y') and only going to the table if the variable has not been set.
I have heard various things about how you should never if possible use global variables. My question is: is it faster to use a global variable than to open the table record each time?
option 1 :
Code:
Public Function UsingWPilot() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("rbl_Settings_System")
Option 2:
Code:
Public Function UsingWPilot() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
If Len(g_WPilot) > 0 Then
UsingWPilot = (g_WPilot = "Y")
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Settings_System")
[code]....
View 4 Replies
View Related
Nov 6, 2013
I am just in the middle of writting a little bit of code that will go through each record in a query and export it into a word table. It's in the early stages but all was working yesterday. I've come to it this morning and ran it and now I get an error message;Run Time error 91: Object Variable or With Block Variable not set.
Code:
Private Sub Command15_Click()
Dim MyDb As DAO.Database
Dim rsLogin As DAO.Recordset
Dim ObjHead As String
Set MyDb = CurrentDb()
Set rsLogin = MyDb.OpenRecordset("query here")
[code]....
View 2 Replies
View Related
Nov 6, 2013
I have some code that will allow a user to pass to it the name of a report. The code will do various things to that report before presenting it for Preview, opening in Word etc.
At one point in the code I would like to refer to some control or property of the report. If I was to include this directly for one report, I might type:
Reports!Annual_Budget_Report.Recordsource = xyz
But instead of a specifc report, it would be the report as a variable (srReport_Name). However, if I type:
Reports!stReport_Name.Recordsource
The code complains, rightly, that there isn't a report called stReport_Name. The stReport_Name variable is currently set as String. I *think* the answer might lie in the variable type, but I'm still struggling.
View 3 Replies
View Related
