Modules & VBA :: Returning Correct Number Of Records
May 3, 2014
I am having trouble with the below returning the correct number of records, and can't see why.
I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID
ID - Autonumber
DeviceRecNo - Number
ExcludeFromCheck - Number (1 = yes, 0 - No)
StockLocationID - Number
I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records.
I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1
Code:
Public Function test()
Dim db As dao.Database
Dim rs1 As dao.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'")
Debug.Print rs1.RecordCount
End Function
View Replies
ADVERTISEMENT
Aug 7, 2014
I am using MS Access with SharePoint Lists and some Access Tables to generate reports in pre-formatted MS Excel Worksheets. My main module opens several classes. One of the classes receives a worksheet to keep in a historical book. This class can receive any one of several different types of pre-formatted worksheets. After all is said an done the class adds a cover sheet to the consolidated workbook and adds hyperlinks to the other sheets, kinda like a table of content.In the class I have the following in the general declaration.
code:
Public Enum BookType
A = 1
B = 2
End Enum
Private pBookType As BookType
Later in the class I have the following:
Code:
Public Property Let LoadBookType(ByVal btNewValue As BookType)
pBookType = btNewValue
AddWorkBook
End Property
in the main modual I have the following
Code:
classConsolidated.LoadBookType = A
When assigning the above property it passes in "11"
if I change it to the following
Code:
classConsolidated.LoadBookType = BookType.A
it passes in "1" as I expect and use in a Select Case statement later in the class.So why does one pass in "11" but the other passes in "1"?
View 14 Replies
View Related
Mar 2, 2012
The code I have is.
Code:
Private Sub Command26_Click()
If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then
Forms![test site]![number correct] = Forms![test site]![number correct] + 1
End If
DoCmd.FindNext
End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
View 4 Replies
View Related
Jul 3, 2006
Hi everyone,
I'm having an issue with a query I created. When run the query requests an Artist Name. I enter this and it returns one result. However in actual table ther are two results for that artist. The only difference is that for the record that doesnt appear the field labelled "Gallery" has a zero value.
I have checked the table and the gallery field is not set to a required field so I dont understand why it wont show it as a result of the query.
Any ideas?
View 3 Replies
View Related
May 4, 2006
I have the following two querydefs. Here is the SQL;
qry1
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE DateDiff("n",[dtReading],[dtFind]) Between 1 And CInt([intMins]);
qry2
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE dtReading Between DateAdd("n",-1*[intMins],[dtFind])
And DateAdd("n",-1,[dtFind]);
[dtFind] and [intMins] are parameters.
I use the querydefs is VBA code as such
Dim db As DAO.Database
Dim rstDataSQL as DAO.Recordset
Dim qdfData As DAO.QueryDef
Dim strQdef As String
Set db = CurrentDb()
strQdef = "qry1" ‘or qry2
Set qdfData = db.QueryDefs(strQdef)
‘Set values of parameters
qdfData![dtFind] = dtDate
qdfData![intMins] = intMins
Set rstDataSQL = qdfData.OpenRecordset
qry2 executes significantly faster than qry1, but I am having issues getting the correct results.
If I set [dtFind] = 12/28/2005 10:47:00 AM, both queries work fine. The last returned record has a value for dtReading of 12/28/2005 10:46:00 AM.
However, if I set [dtFind] = 12/28/2005 10:48:00 AM, only qry1 returns the right records. qry2 will not return the record with dtReading = 12/28/2005 10:47:00 AM, but qry1 will.
:mad:
I changed the Between statement to “Between DateAdd("n",-1*[intMins],[dtFind]) And DateAdd("n", 0 ,[dtFind])” to see what happens. As I expected, records where dtReading = 12/28/2005 10:47:00 AM and 12/28/2005 10:48:00 AM are returned.
The data should be in increments of 1 minute, although there are periods where data is missing. None of the dates have values like 12/28/2005 10:47:01 AM, i.e. seconds value is always 0.
I’ve tried adding “PARAMETERS [dtFind] DateTime, [intMins] Short;”, and also using CDate(DateAdd()) without any luck.
Whether I am using the queries using VBA/DAO or user input to set the parameter values, the results are the same.
What am I doing wrong?
:confused:
View 4 Replies
View Related
Sep 10, 2013
It is basically a DCount, and it should find records, but returns 0 all the time. My code is:
Code:
Function cntkit(sftd As Date, sftn As String, typid As Integer, specpaint As Boolean) As Integer 'Counts jobs kitted during shift given by sftd and sftn
Dim timeformat As String
timeformat = "#mm/dd/yyyy hh:nn:ss#" 'need this, to convert it to US datetime format
cntkit = DCount("[JOB]", "Archive", "[Type] =" & typid & " And [Autfinish]=False And [SpecPaint] =" & specpaint & " And ([Kit] BETWEEN " & Format(sftstart(sftd, sftn), timeformat) & " AND " & Format(sftstart(sftd, sftn), timeformat) & ")")
End Function
sftstart and sftend are functions which are returning dates. The funcion works fine if I omit the Between part of the criteria. So the problem is in that part.
View 5 Replies
View Related
Nov 14, 2013
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code:
Private Sub Command18_Click()
On Error GoTo errorcatch
Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
View 3 Replies
View Related
Jan 6, 2005
Wondering if anyone can advise...
The 1st/2nd Jan of this year were treated by most people as still being week 53 of 2004, and week commencing 3rd January being week 1 of 2005. In VB I have a formula to do this:
WeekNo = Format(Now(), "ww", vbMonday, vbFirstFourDays)
However my problem now is that I need to replicate this format in a Query within Access. The formula
WeekNo: Format(Date(),"ww")
treats 1st/2nd Jan as week 1, the 3rd as week 2 etc. Any idea how I can get it to treat 3rd Jan as week 1 instead?
There's a challenge for someone!! Help appreciated.
View 1 Replies
View Related
Mar 23, 2013
Correct syntax/expression for log of a number. The number i am getting in Access is twice as big as the correct number i get in excell
View 3 Replies
View Related
May 24, 2007
hi all,
here's the dealio... i need perform an aggregate funtion off of a certain field, but it contains null values. i tried using Nz and an Iif IS NULL statement to get rid of the nulls. but both methods return the values as text. obviously, i can't get an Avg of these text values.
here's the two methods i tried:
Format(IIf([AvailTab].[Per00] Is Null,"1",[AvailTab].[Per00]),"Percent") AS Per01Test
Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00
how can i return the data as a number?
thanks,
mj
View 4 Replies
View Related
Mar 11, 2008
I have a form on which I need to print a sequential number - increasing by 1 each time the form is printed. I cannot think of a way to do that - perhaps something returned from a query? Any suggestions?
Robert
View 3 Replies
View Related
Jan 10, 2014
In a query I would like to extract the last ten years. This is what the data looks like:12/13 (Data type = text)(Short for 2012/2013 which actually represents 7/1/2012 to 6/30/2013.)
I am able to extract the "12" and turn it into the general number 2012 using: ("20" & Left([TAXYR],2))*1..But how can I convert that to the data type-date so I can include those records in the past ten years from todays date? I was trying to use DateAdd but I think the problem is the data type and where converting to date gives me "1905" or "9/##/1905." I get why it does that, but is there a workaround? Maybe adding 39,785 days?? (2014-1905)*365
View 4 Replies
View Related
Apr 3, 2014
I have a query which runs fine, however one of the outputs is a calculated field and i use the Format(someValue,"Fixed") method of specifying the format for the % Change .Here is the query
Code:
SELECT tblTempBearsWeek3.SymbolCode AS [Short Ticker], tblTempBullsWeek3.SymbolCode AS [Long Ticker], tblTempBearsWeek3.[Week 3 Date], tblTempBearsWeek3.Short, tblTempBullsWeek3.Long, tblTempBearsWeek3.[Short Week 3 Close], tblTempBullsWeek3.[Long Week 3 Close],
[code]...
The problem is that the % Change calculated field for some reason comes out as a String. i know this because when I try and sort on that column, it sorts it as if it is text and not a number value i.e. it doesn't put it in Ascending order.
View 2 Replies
View Related
Jul 23, 2014
I need to fill in 200000 records counting from 100000,100001,100002.... and so forth, just one column (and maybe the auto numbering).
make a new DB with these columns: ID, counter
set counter to 100000 where ID=1 (in the first record)
move to next record (or make a new record)
if ID < 300000 then set counter = 1+ (the value of counter in the previous record)
continue until ID=300000
View 8 Replies
View Related
Jan 23, 2015
I need to first generate a random number between 1-4.
Then I would like to use that number to pull that many records out of the database; the records pulled can be random, or whatever.
For example, we have 4 people and my goal is to choose a random # of records to pull for each person. So, for person #1, it might only pull 1 record, then for person #3, it may pull 2 records, etc.
Ideally, it wouldn't pull the same # twice when run for that "session".
After this, I'm hoping to generate all those results into emails (eg for person 3 it would generate 3 emails, etc)
Currently it's all done manually and I think it's doable with Access.
I have some code already for generating "1" email message, but do not know how to pull X number of emails all at once. (X is the random #).
View 2 Replies
View Related
Jul 11, 2014
I have a table which has 2 fields 1) Project_Priority_Number and 2) Previous_Priority_Number. If there were 100 records these would be numbered 1-100 in the order that the user originally sets the priority (this number is in addition to the record ID number). I have created a form with code that moves the Project_Priority_Number to the Previous_Priority_Number and then shows the Project_Priority_Number as blank and displays all of the records.
The user can then set new priority numbers in the blank column. Say they choose to make the old priority number 4 the new number 3 and priority 27 now becomes say 2, etc. I want the user to press a button that re-numbers the remaining ones based on their old position + or - 1 (basically to fill the gaps but based on their previous positions). I understand how to renumber if one is deleted but I don't know how to be more specific and re-number based both on their previous priority number and whether something else is now set to replace that.
For Example (the following numbers need to re-order based on previous priority 4 becomming the new priority 1 and the previous number 8 becomming 3 - so the previous 1 becomes 2 and then everything after the new 3 moves forward 1):
PROJECT PRIORITY NUMBER PREVIOUS PRIORITY NUMBER
1
2
3
1 4
5
6
7
3 8
9
10
View 14 Replies
View Related
Sep 10, 2013
I am trying to calculate the number of days between incidents.
If I have an incident that happened on the 10th August and another on the 15th August then this would be 5 days, the next incident occurred on the 28th August so that would be 13 days. The problem I am having is how to get access to use the latest date rather than the first date.
View 14 Replies
View Related
Sep 24, 2014
I have a table which specifies the delivery date
I have a from that allows you to choose a year and a month.
I have an unbound textbox which I wan to display the count
I want to be able to count all the records from a table with the year and month specified in the comboboxes and display this in the texbox.
View 4 Replies
View Related
Jul 4, 2015
I have a form which uses a loop command to output reports as a PDF. The reports take a bit of time to produce and the record set could contain 100-150 records. Any way that you can split the recordset down into batches. Maybe have a button which creates 1-20 and another 21-40 and so on.
View 1 Replies
View Related
Jun 13, 2013
I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".
The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.
Code:
Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer
[code]....
View 1 Replies
View Related
Mar 31, 2006
I have several tables that I am trying to get information from:
Clock Number Table with fields: Clock #, Name, Title, Dept, Term, HireDate, & TransDate.
Completed Training with fields: Clock #, Data Completed, Doc # & Rev Level
Linked Table, Controlled Documents with fields: Doc #, Title, Effective Date & Rev Level
Linked Table, Distribution Table with fields: Doc #, Distribution, Rev Level & Effective Date.
Some of the tables have more fields that those listed, but they do not pertain to this query.
I am trying to create queries that will provide me with the names of employees who have not been trained on Controlled Documents that have been distributed to their department.
So far, I have been able to determine if no one has been trained, but if even one person has been trained, they do not appear on my list. I would like to know the Clock # and name of those that have not been trained.
I have created 3 queries to get this far. Query 1 is the Clock # Table and the Completed Training Table joining the Clock #. Query 2 is the Controlled Document Table and the Distribution Table joining the Doc #, Rev Level. Query 3 takes these queries and joins Doc # and Rev Level. and pulls records where the Rev. Level and Doc # is Null.
View 2 Replies
View Related
Jul 26, 2013
Table Info:
EmpProfile: EmpID (Primery Key), EmpFirstName, EmpLastName, Emptitle, EmpPhoneNumber
EmpTimes: EmpID (1 to Many relationship with EmpID primery key), Timein, Break, TimeOut, Totalhours
Query Name:
Selects the table from EmpTimes
Calculates the total hours worked for an empid, on a given day. It produces the total hours as "CalcTime"
Form Info:
1st Form Name: MainForm
Text name to enter empID : Txtempid (unbound)
Record Source: EmpProfile
2nd form name: SFTimeSheet
This is a subform within main form
Record Source: QryTime
Child and master fields are linked using : EmpID
Expecting Result:
When enter empid on the MainForm, in txtempid
subform should retrieve all entries for that empid
Getting result:
regardless what empid entered in txtempid, subform shows only the 1st record available in EmpTimes.
View 3 Replies
View Related
May 29, 2014
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
View 7 Replies
View Related
Jun 28, 2013
I want whenever I'm updating or adding records to my form, the ID automatically take the value of the previous ID and increment it by 1.
The field type is text (mixed with number) - PM0000000.
I've done some research, what I understand is that I need to:
-do a lookup and
-find the MAX of the number portion.
Name of form - Payment
Name of table - Payment
Name of field - payment_id
I tried these, but to no avail...
Private Sub payment_id_Click()
payment_id = DLookup(("[payment_id]", "Payment", "[payment_id]=Forms![Payment]![payment_id]-1")payment_id + 1)
End Sub
[Code] .....
View 8 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
Feb 23, 2005
Hello. The problem I am having is related to two tables. I have a table called Void and a table called Panel. A panel can have multiple voids and a void is assigned to only panel (one-to-many). In the Panel table, I set an autoincrementing number(SprayPanelId) for the pK. In the Void table, I set an autoincrementing number (VoidId) for the pk. I place the SprayPanelId into the Void table as a foreign key (same name in the void table). The problem I have is in my forms. I created a Panel form with a button to open a subform for data entry on the voids related to the panel in a new window. Data can be entered and is properly saved in the Void table. I open the Void table in a datasheet view under the table section and see it is properly there. I can run queries where Void.SprayPanelId = Panel.SprayPanelId and it returns the correct void with the correct Panel. The problem becomes when I open my void subform, it no longer displays that record associated with that panel. however, the record is there in my void table. Any thoughts? Do I need a filter perhaps, run any sql commands? Thanks in advance, Kevin
View 2 Replies
View Related
