Modules & VBA :: Displaying Results Of A Select Statement?

Oct 7, 2014

I have a database with several one-to-many relationships and a nested subform based off of those relationships. Relationships are as follows:

One Lender to Many Relationship IDs
One Relationship ID to Many Tax IDs
One Tax ID to Many DocumentsRequired

My forms are nested as follows:

Relationship ID form (contains info for Relationship ID and Lender)
-> Tax ID
-> DocumentsRequired

I believe I'm just overcomplicating this. But I have simple search box (an unbound text box with a command button), which is located on the top-level form for Relationship IDs. As you'll see above, that form only contains the fields for Relationship ID and Lender. However, users have to be able to search by Relationship ID, Customer Name, or Tax ID number, the last two of which are only available on the nested subforms. Currently I have the search box reaching out to grab results from a query. When I assign the results to the Me.RecordSource, it works perfectly except that it's in read-only format. The users have to be able to edit the results of their search. I'm not even sure I'm doing this in the easiest fashion. I would have preferred to just use the select statement to search through the subform, but I'm guessing my syntax was wrong because I never got it to work. Below is what I currently have.

Dim strtext As String
Dim strsearch As String
strtext = Me.SearchBoxTxt.Value
strsearch = "SELECT [Relationship ID] " & _
"FROM CustomerNormQuery " & _
"WHERE [Relationship ID] like ""*" & strtext & "*"" OR [Customer Name] Like ""*" & strtext & "*"" " & _
"OR [EIN/SSN] Like ""*" & strtext & "*"" " & _
"GROUP BY [Relationship ID]"
Me.RecordSource = strsearch

View Replies


ADVERTISEMENT

Join Two Fields In Select Statement Results

Apr 12, 2006

I'm sure this is easy to do, but I'm not sure how to word my search criteria when trying to look for a solution.


Let's say that I have 2 fields in MyTable: FirstName, LastName.

I've been trying to use the following query, but obviously it does not work:
Select (FirstName + ' ' + LastName) as MyName from MyTable.

I would like to have the following data returned to me in the following format:
John Doe
Jane Doe
Jim Doe
Jill Doe

Thanks,
CRhodus

View 1 Replies View Related

Modules & VBA :: INSERT INTO SELECT Statement

Mar 4, 2014

I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:

Code:
INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role )
SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role
FROM tblPeople
WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));

What I wrote for the VBA code is this:

Code:
Dim strSQL As String
Dim db As Database
Set db = CurrentDb

[code]....

Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it.

View 4 Replies View Related

Modules & VBA :: Adding WHERE To SQL Select Statement

Sep 29, 2014

I have a SELECT statement which works

Code:
"SELECT Bookings_Table.Booking_Time, Bookings_Table.Num_Slots, Bookings_Table.Booking_Date FROM Bookings_Table ORDER BY Bookings_Table.Booking_Time;"

But when i add the WHERE

Code:
"SELECT Bookings_Table.Booking_Time, Bookings_Table.Num_Slots, Bookings_Table.Booking_Date FROM Bookings_Table WHERE (((Bookings_Table.Booking_Date)=[TB_CAL_DATE])) ORDER BY Bookings_Table.Booking_Time;"

it doesn't work [TB_CAL_DATE] is a textbox with a Date in it...

View 3 Replies View Related

Modules & VBA :: Using Select Case Statement With Array?

Jun 26, 2014

I have a boolean array, foundState(3), whose 4 elements correspond to 4 variables describing conditions that will dictate what action is taken upon closing a form.

There are only 6 possible outcomes for the array, and they can be divided into just 4 cases:

Case {T,T,T,T}

Case {T,T,T,F} OR {T,T,F,F} OR {T,T,F,T}

Case {T,F,F,F}

Case {F,F,F,F}

What the proper syntax would be for this if I'm trying to create a "Select Case" statement for these 4 cases.

View 7 Replies View Related

Modules & VBA :: SQL Select Statement - Space In Column Name

Jul 22, 2013

Code:

strSQL = ("INSERT INTO " & strTable & " Select c.OrderNumber, c.Item, c.RepId, p.ProductNbr, p.Name, [tbl_LBP_Sales Location Num].[Rep Region Code] FROM CalculateTotal c, dbo_PartNew p, [tbl_LBP_Sales Location Num] WHERE ([Structure] like '*" & u & "*') AND ([ProductNbr] = '" & txtPartNumber & "')")

CurrentDb.Execute strSQL

It keeps giving me this error and I don't know why?...

I tried replacing the brackets around Rep Region Code with quotation marks as well as these things `` but kept getting the same error...

View 3 Replies View Related

Modules & VBA :: How To Create And See Results Of SELECT Query

Mar 22, 2015

I was beginning to think I had got the hang of creating queries on the fly in VBA. After several hours I have just learned that docmd.runSQL does not work for simple SELECT statements. I'm not going to worry about why that would be. I'm sure there's a jolly good reason.

So... I am able to dynamically create the SQL string for the SELECT statement that I need.

How do I run it? It just needs to return results as a datasheet so I can see them. I'm running Access 2013. I've been searching on this subject for quite a while and found many references to DAO, ADO, ADODAOD, YODELAEYYOUDELAYIO! and other things I don't yet understand ...

View 8 Replies View Related

Modules & VBA :: Allow User To Select Record From Subform And Add It To Table - Insert Statement

Jun 18, 2013

I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"

Code:
Private Sub Command3_Click()
Dim dbs As Database
Dim sqlstr As String
Set dbs = CurrentDb
Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "")
Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")

[Code] ....

View 1 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Displaying 2 Results In One Field

Dec 13, 2006

Hi All

Is it possible to display two result in one field.
In essence i have a table of tasks with a due date.
I would like to display a count in red of overdue tasks in reference to the current date and a count in green of the tasks which are after the current date. The date is based on month year.

I think I may have to have two seperate fields and make them look like one by having no outlining but if its possible i would rather do it in one field.

My knowledge of VB etc is very limited but I can follow code and sometimes adapt it successfully.

Any help much appreciated.

ChrisD:D

View 1 Replies View Related

Displaying Results In A Form - Any Help!!!!!

Dec 15, 2006

:confused: I am setting up a referral system with a datebase on access. I want to have a form where I can enter a date of referral and the results are displayed in a form style? How do I do it?

I have set up a form to enter the date, with a submit button, and this return the results in a dataview/spreadsheet style. But I want to display in a form so that I can edit later.

I am a novice in Access - Any help!!

View 1 Replies View Related

Displaying Query Results In A Subform

Sep 14, 2005

Hi,

This is a Query / Forms question...

I have 2 tables setup with a 1 to many relationship, simialr to 1 Customer having 1 or more orders. I created a query to prompt for all orders for a particular customer. I then created a form based on this query - i.e. linked the query to a form.

Now when I open the form I am prompted for the customer id and when I enter this all of the results of the query are displayed in 1 form. However, this is not what I want. I would like the orders for the customer to be displayed within a subform on the actual form. In this way the customer details will static and I can cycle through the customer orders in the subform separately.

Is there any way I can setup my query to display the results in this way? I need the prompt for the customer ID. Or is there any way I can setup the form to display the results in the above way?

Any help would be greatly appreciated.

Thanks

View 2 Replies View Related

Displaying Query Results In A Form...

Jun 17, 2006

I have created a Query to search for clients in my database, the query and the search all work fine, however it would be nice to have the query post the results into a form I have created to display clients data rather than posting the results in a tables view.

Is there anyway todo this?

Thanks in advance.

View 1 Replies View Related

Displaying The Results Of A Dynamic Query

Feb 11, 2007

Hi, I'm building a form where the user can choose various options to build up the query they want. It's going to be easier this way because the different combinations they might want would mean me writing well over 100 different queries. So I think I can build the sql statement in vb and then get a recordset back with the results, but the question is how to display it. I can't just assign the recordset to an existing form because the fields to be displayed will be different for different options. Ideally I'd like it to open just as if I had run:

DoCmd.OpenQuery <name of the query>

But it doesn't look like this is going to work for a query I've generated in the code, even if I use a querydef object (correct me if I'm wrong?). So does anyone know how I would go about doing this? (It would also be fine if the results appeared in a subform on the form I'm calling the query from, but I can't see how to do this either).

Any help gratefully appreciated.

Sim Bamford

View 2 Replies View Related

Displaying Query Results In Form

Sep 13, 2006

Forgive me for what may sound like a dum question -- Access is completely new to me.

I have my table. I have this displayed as a form.

At the bottom of the form, I have a text box and a command button.

The user enters in a key value. The command button triggers a macro that runs a query.

Now, I'd like the form to display the data returned from the query. How?

I tried creating a new form with its Record Source set to the query. I had the macro close the original form, run the query and open the new form. But before the new form could be opened, Access prompted me for a 'parameter value' (I assume so that the query could be run again).

What am I doing wrong?

View 3 Replies View Related

Displaying Results Of SQL Query On Form

Dec 15, 2006

I searched the archives and Access Help and couldn't quite find the answer to this question, which is no doubt simple.

I have a form showing Projects with an embedded subform showing all the Buildings associated with a given project. In the form footer section of the subform, I would like to display summary information about the buildings in the project being displayed, such as count of buildings, count of residential buildings, count of commercial buildings, and so on, and I would like these summary values to update as users add or change building information.

I know how to write the SQL query to pull this information, but do I use a text box or some other vehicle to do this? Using a text box as a calculated control, I was able to produce the count of buildings using:

= COUNT([UnitID])

To get the count of residential buildings, I need to be able to specify "WHERE bldgtypeID = 'R'"...I can't get the text box to display results of an SQL statement. Can text boxes handle SQL statements?

I really appreciate any suggestions you have.

View 10 Replies View Related

Displaying Query Results In A Form

Feb 26, 2008

Hi everyone,

I have a query which totals the amount of all invoices for a specific project number. I would like to take this total and have it displayed on the form from where overall project information is inputed.

Right now I have a table which shows general project information and a table showing invoice infomation (linked to the general table via a project #)

I also have two forms, one for general info and there's a button which opens the invoice form. When the invoice form opens, it opens an invoice with the corresponding project number shown on the general form. I would like the total invoice amount (from the query) displayed on the corresponding general form.

ex: in the general form for project# 100, I click invoice once and enter $50, I click invoice again and enter $100. The query automatically shows a total of $150 for project# 100. I would like this $150 displayed on the general form for quick reference.

Thanks for all the help

View 1 Replies View Related

General :: Displaying Results On The Same Page?

Sep 19, 2013

I have created a table with name "Main". It consists of fields ID, Author, Title, Abstract, Page and Published.

I have created a form named "MFORM" with text boxes Author, Title, Abstract, Page and Published,and then I created a search and clear parameters buttons below the text box.

Then I dragged my Search Query into the bottom of my form MFORM and named it as "All Results".

When I click on search then the results are displayed in the new tab instead of bottom of my main form in Search query.

If I enter author name as Mike and then click on search button then results should display all the details related to Mike on the bottom of my form.

All Results [At the Bottom of my page]

Should display all the details related to Mike.

I had attached my database. I just need the search results at the bottom of my main form in stead of displaying in new tab.

View 11 Replies View Related

Displaying Query Results In A Textbox

May 8, 2013

I currently have a search form that when criteria is entered it then opens up a query with matching results. What i would like to have it do is have a macro that runs the query so that it gathers the results and then displays the results into a form with a textbox called search results.

View 2 Replies View Related

Displaying Search Results Using Subform Or Other Control

Feb 23, 2006

Hi, I'm just wondering if it's possible to display the result of a query on a subform which does not have a relationship with any other table.

What I'm doing is creating a search form where a query is built from the users input (through combo boxes and text boxes) of what table, attribute and condition they want to search from. When they press the search button, I want the result to be displayed in a datasheet below the selection criteria

The first thing I thought of using to display information with was a subform, but I think the purpose of having the subform is so that it can be linked with a form. So, is there another control or way of displaying results. The results can come from any table.

Thanks.

sugoi_kat

View 1 Replies View Related

Forms :: Displaying Results From Query In Subform

May 9, 2013

So i have a combo box that has a criteria set for the query. I have a search button that runs the query that users click.

What i need to happened is i want the results displayed in the sub form the when query is ran.

View 7 Replies View Related

Forms :: Displaying Query Results In Single Row

Aug 7, 2014

I have a Query, which is running on 2 tables and when I run the query I am getting the results like this.

Name----------Position
Rock----------1
Rock----------2

I want the Results like

Name----------Position----------Position
Rock----------1 ---------- 2

Is there any way to do this.

View 1 Replies View Related

Queries :: Displaying Entire Contents Of Search Results?

Dec 8, 2013

I'm trying to make a search form that makes use of queries that search a table based on what fields I have. How would I go about having the query display all the fields for the record(s) that match the search criteria? This is for Access 2010.

As far as the query is set up, I have several fields with a search criteria, and the others are for displaying the relevant information about the results (since they're not search criteria, I used "Like '*'"). When I ran the query, it doesn't come up with anything - even if the record actually exists in the table with specified criteria.

View 9 Replies View Related

Queries :: Displaying Query Results On Datasheet Form

Jun 24, 2014

I have a simple database with a query that "SUM"s an amount.

I created a form with a sub form that is linked by Account number. I want to be able to open the form in datasheet format and it to display the SUM value from the query.

When I open the form it responds with #Type until I click the + symbol to open the linked query then it updates the #type field with the data from the query.

View 14 Replies View Related

General :: Displaying Results With Starting And Ending Year

Sep 24, 2013

I have created a 6 search boxes with names Author, Published, Language, Title, Keywords and University. If I enter "A" in Author search field then it should return all the 6 fields which contains "A" in Author field. It worked well.

My Requirement :

The column published is the year. In search box of Published if I search for 2000 then it's returning all the 6 fields which contains "2000" in Published field.

But most of the people doesn't know the exact year in which the book is published so they need some favour like, If they search for 2000-2010 in Published field then it should return all the fields which are between 2000-2010 in the published field.

For this do I need to create 2 extra text boxes and name them as Starting year and Ending Year? or else we can achieve this in single text box as xxxx-xxxx?

View 6 Replies View Related

Counting Results Of Multiple Queries And Displaying The The Reults On A Single Form

Apr 17, 2007

Im making a database about past bid results. In one table i have a list of bidders and which place they came in (along with some other information). I have 20 positions (1st place, second place and so on...), so i made 20 queries that search through the table for my company's name so we can see how many times we came in first, second, third and so on. Now i would like to bring the number of records in each query to a single form. I tried making another query to using the count feature on the other 20 queries but it keeps returning 0 as the number of results per query. But when i open each query up there are obvisouly more that 0 results in each. Is there a way i can bring up the number of results per query all on one form?

Thank you

Dan

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved