ADO Query Result Set
Sep 5, 2011
I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).
I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.
Code:
Public emailaddress, ccaddress, Subject, body1 As String
Public baserow, toprow, countnumberofrows, emails As Integer
Public tempdir, projectlistdir, WBPATH As String
Option Compare Database
Option Explicit
[code]....
View Replies
ADVERTISEMENT
Mar 5, 2014
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
View 1 Replies
View Related
Aug 18, 2013
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
View 4 Replies
View Related
Oct 12, 2013
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies
View Related
Sep 27, 2006
Hello, I have a combo box on a form which lists some names generated from a table.
I would like the selected name to be inputted into the 'critera' of another query called 'qryPBCustLevel' and for that query to be run.
I have tried to code this, but it is crashing at the point it trys to add the name into the query.
Can anyone help? Code listed below.
Sub cmbPB_AfterUpdate()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT DISTINCT tblTempPB.PB_NAME" & _
"FROM tblTempPB"
strOrder = "tblTempPB.PB_NAME;"
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PB_NAME] = '" & Me![cmbPB] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryPBCustLevel")
qryDef.SQL = strSQL & " " & strOrder
'Open the Query
DoCmd.OpenQuery "qryPBCustLevel", acViewNormal
End Sub
Thanks, Steve. :confused:
View 2 Replies
View Related
Mar 1, 2006
Hi,
I'm designing this system in which each employee has different area of strength (i.e. Math, Languages,..)
if an employee has 2 or 3 area of strength his name appears in the query more than once. I want his name to appear once.
I tried "group by" but it gave me an error. I think I'm doing it wrong.
Please Help!
CS.
View 7 Replies
View Related
Apr 15, 2013
I have 2 tables, Event and Person Particulars.
In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).
Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.
If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?
Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.
View 10 Replies
View Related
Jun 15, 2007
I am trying to calculate the average patients age from 2671 records using this SQL:
SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);
why am I getting the result:
68.1131066106
I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.
thanks
View 3 Replies
View Related
Jan 5, 2006
Hi
I have a query with 2 fields, when it is run it returns a result based upon the result of the set criteria.
Can you get this value to be displayed in a text box after a button is pressed.
dave
View 3 Replies
View Related
Mar 1, 2006
Hi
I am new to VBA with access. Im wanting to get the result of a query called "qLastRotaDate" into a variable called "datLastRota". The query returns a single date, it is not possible to have more than one result for this query. I have tried different variations of : datLastRota = qLastRotaDate but cannot get any to work. I'm assuming this is very simple yet I cannot figure it out as am very new to VBA.
Any help would be greatly appreciated. Sorry if this has been dealt with in previous posts but I could not find any info by searching the post.
Thanks
Mikee
View 3 Replies
View Related
May 27, 2006
Hi,
Can anyone see anything wrong with this query?
I have 10 combo boxes where years are selected. If the last year nothing is selected (meaning is empty) then my query should return the last selected value. It seems the IIF query does not seem to work (Still showing empty). Can you please advise and help me on this?
The query I am using is shown below.
Thank you
dfuas
IIF([Trade].[Vintage_ to] = ' ',([Trade].[Vintage_from9]) OR ([Trade].[Vintage_from8]) OR ([Trade].[Vintage_from7]) OR ([Trade].[Vintage_from6]) OR ([Trade].[Vintage_from6]),[Trade].[Vintage_ to]) AS [Vintage to]
View 4 Replies
View Related
Mar 19, 2007
Hi,
I have a query which I run in the query design view. Then I have the same exact query which I run on the 'background' of a form.
The issue is, both queries turn out to have different record counts(eventhough I am using the same query and same tables). What is going on? Please help.
Thank you.
Joe
View 6 Replies
View Related
Feb 5, 2008
hello every one,
first, im apologize for my English.
I have form that runs until the user answer the all the questions.
I place the last question Id in listbox. do I can place the result of the query (max of question id) in a variant in VB or I must place it in listbox/combobox/textbox?
Tsur triger.
View 14 Replies
View Related
Mar 2, 2008
Hi all,
Would appreciate help on this. I have a query which combines the results of 3 queries. Once the underlying queries each has a result I get a result in my query, but if 1 of the underlying queries has no result I get nothing. (I hope this makes sense).
How can I set my query to show results even if the underlying query doesn't.
thanks in advance.
rgs
Ginny
View 5 Replies
View Related
Apr 13, 2008
Hi,
I have the following query in a access 2000 db
SELECT [5k1].[Race Number], [5k1].[Name], [5k1].[Club], Date()-[Date of birth] AS Age, [5k1].[Handicap Time], [5k1].[Male / Female], [5k1].[Date of birth], [5k1].[handmin], [5k1].[handsecs], [tblRaceEntries].[intEntryRace1], [tblRaceEntries].[intEntryRace2], [tblRaceEntries].[intEntryRace3], [tblRaceEntries].[intEntryRace4], [tblRaceEntries].[intEntryRace5]
FROM 5k1 INNER JOIN tblRaceEntries ON [5k1].[Race Number]=[tblRaceEntries].[RaceNumber];
it is used as a means of input from a form.
When it is used in windows xp then all is good.
When it is used in windows 2k it doesn't work as I intended. The problem being that [5k1].[Race Number] field is reset with a default '0' value of [tblRaceEntries].[RaceNumber]
Whats the difference between the 2k/xp system that causes this.. what the solution?
many thanks
View 3 Replies
View Related
Feb 9, 2006
I want to know how to generate a result from a query in a control of a form? For exampel I have made a SQL count function in a query and I want to use this result i a control of a form.
View 1 Replies
View Related
Sep 17, 2006
I have a simple database I am trying to put together. One table, one form, very vanilla. I created a query that ranks values that I enter in a table. I would like to put the result of the query (the rank) next to the value in the form itself. I thought if I created a text box and entered the rank field in the text box, that would be it, but when I run the form, I get "#Name?".
Any thoughts? Is this even possible?
Thanks in advance.
View 8 Replies
View Related
Nov 9, 2006
I have a query the SQL view is
SELECT Max([customerID]) AS Total
FROM tblCustomer
WHERE (((Left([CustomerID],3))=[forms]![frmNewCustomer].[txtFirst3]));
I run the query and the result is
Total
BIS007
How do I get Total into a text box on my form so that I can use it?
View 2 Replies
View Related
Mar 1, 2006
I have a saved Query that returns a single line and a single field. I want the data in that field to be a VBA var, or straight into a field in a different table.
And Ideas?
View 2 Replies
View Related
Nov 15, 2011
I have a query of 11 employees and their pay for jobs done. Only problem is there are 15 completed jobs with some of the employees doing more than one. When I run my query its only displaying unique name results so I'm only getting 11 results of the 15 jobs instead of all 15.How would I change the query to that the results are only unique employee names?
View 1 Replies
View Related
Jun 14, 2005
I'm pretty sure there's a simple solution to this, yet I can't seem to find it.
I have a table that contains the Months of the year with an MonthID:
1|January
2|Febuary
...
I also have a form that contains a combobox with the Months.
When the user chooses the month, a query gets created, returning the MonthID.
I want to store the MonthID, in an invisible textbox, so that I can use it for my calculations.
If I use a textbox, I can't seem to display the result and if I use a listbox, I can't access that value.
How would I make it work?
This is the code used on the combobox:
Private Sub cbToMonth_Change()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("getMonthID")
strSQL = "SELECT Months1.MonthID " & _
"FROM Months1 " & _
"WHERE (((Months1.Month) In ('" & Me.cbToMonth.Value & "')));"
qdf.SQL = strSQL
Me.monthIDTo.Value = "getMonthID"
Set db = Nothing
Set qdf = Nothing
End Sub
View 3 Replies
View Related
Jun 22, 2005
I wish to print the result from query in a subform. In my main form, I had a textbox call 'year' which asking user to input a valid year. The query will have to find out all the records that are in that particular year, one year before and one year after. After that, it should display the result in datasheet form. Can anyone out there help me in this matter?
My second question is Can we use crosstab query in the subform?
Thank you.
View 1 Replies
View Related
Aug 21, 2005
I am trying to create a form using VBA which will be a list of people, in a particular group, with a checkbox next to each name. The point of the form is so that the user can check those individuals who shoud not be included in a particular process. The names are the result of a query and the list will be of different lengths each time. I have tried to create a query with the first field being the person's name and the second field a checkbox, which would default to No. Then I would be able to use each name with it's checkbox to accomplish this. What I really need help with is the query, so that one of the output fields is a checkbox.
View 1 Replies
View Related
Sep 28, 2005
Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.
View 1 Replies
View Related
Oct 21, 2005
I have a stored query in my db. The query gets the recordcount of all the items in a table.
In my VBA, rather than code a new db conection, set a recordset etc, I want to execute the query using DoCmd
=======
DoCmd.OpenQuery stDocName, acNormal, acEdit
=======
The above line displays the results of the query.
What I really want is to run the query and store the recordCount in a variable - without displaying the results.
Can this be done?
Thanks all
View 2 Replies
View Related
Nov 7, 2005
Hi,
I have a two column table and I want to display all records in the second column but I want it to be displayed in another single field.
Anyone has a trick on this?
Example:
Column1 Column2
001 Basket
002 Jar
003 Pillow
004 Hat
Result should be something like this:
Basket, Jar, Pillow, Hat
View 1 Replies
View Related