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.
I have the need to display the return of my "Sum Query" to display in a text box.
I need the attached below value (40500) in the "SumofQuery" attachment, which updates every 5 minutes to display on the "StatusBoard" attachment text box.
I have been searching for a good 10 hours on how to do this and still cannot find it.
I couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:
table Books: - BookID - Author - Title - Year - Cabinet
table Papers: - PaperId - Author - Title - Year - Cabinet
table Catalog: - CatalogID - Author - Title - Year - Cabinet
And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.
So the resulting query would look like:
Type.....ID....Author.....Title....Year....Cabinet Book.....001...John.....Good book....2005....C01 Book....002.....Mary....Other book...2006...C01 Paper....001....Albert...PaperABC....1987....C01 Paper...002....John.....PaperXYZ.....2006....C02 Catalog...001...Mark....Cat00A....1989.....C02 Catalog...002...Bill......Cat00B.....2004.....C03 etc.
How to get this one to display in a single column.
I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.
Customer Table with PK Customer_ID.
There are two tables with FK Customer_ID.
1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.
2. Table Lease2 - Has 1 field with 0 to Many records.
Goal:
The Type shows up in a single column.
Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)
Challenge:
Lease1 table has 3 fields that need to be transformed into a single column.
Lease2 table has 1 field to be appended to the single table.
Then, there is the column that identifies where the data came from based on the column name.
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.
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 am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code: Private Sub Command33_Click() Dim strEmail As String Dim strMsg As String Dim oLook As Object Dim oMail As Object Set oLook = CreateObject("Outlook.Application") Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is: one paragraph a blank line a hyperlink to a website a blank line another paragraph
Hi, thanks in advance for any help you can offer. I've got a table that has
Date Time Tag ID Power Level
throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day
I've tried using "group by" and max in the query but this gives me all the times throughout the day.
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
I'm new to access, and have discovered its easy to write large queries by breaking them up into smaller queries and having each subsequent query reference the one before it. My problem now is that I want to combine these queries into a single query, so as to make it easier for anyone who comes after me to edit/understand what was done. I can't seem to find a good explanation of how to do this, but it seems like there should be a way.
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
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.
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.
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]
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.
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?
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.
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?
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.
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?".
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?
