In a Query, how can I display in one column a value that can come from 2 different fields based on a selection.
Say I have two date fields (Date1 & Date2) they can both be blank, or one or both of them could have a real value. To make it simple here is the logic I want to employ.
If Date2 > " " display Date2, otherwise display Date1. I want the value to appear in the same column. Can this be done in a query or must it be done in a report?
Thanks!
I have a field in a query that I would like to display differently than what is stored in the table. The field is call apt# and the output looks like this 0000000011409468Ø. I would like to display this data without showing Ø. Is there a way to do this? Thanks..
I need to display the results of a query on screen when somebody clicks a button on the form. So I put code in the event of the click that looks like this:
Private Sub Command20_Click() On Error GoTo Err_Command20_Click
So far, so good, but my problem is that this query is one row with a lot of columns - that creates a lot of pages to look through. Is there a command that would loop it around on the same page, or rotate it clockwise so it's going down the page instead of across?
I am attempting to run a query with fields from 6 tables. These tables have been related, but just a simple ID to ID throughout all of the tables. When I select the fields I would like to be in query using the disign wizard it will allow me to create the query, however it will not display and data.
Originally, the query wizard would not allow me to insert all of the different tables fields in the query because "they were not related", so I went back and did the ID to ID relationships. Like I said this now allows me to create the query, but unfortunately no data appears.
I am trying to make a query that includes 3 other queries. The primary key is customer number and I am trying to pull the sales for each customer by week, month, and quarter. If a customer has not purchased anything it does not display at all. Also, if they purchased something in the quarter or month but not in the past week it doesn't display. I would like the sales for the week, month, or quarter to display 0 if no transactions were made. It is not feasible to add a record for each customer with "0" since this would be updated daily.
In one table i Have component group Id and the description as fields then another table has the sub groupings for each Component Group Id
Example Component Group ID = 1 which is the primary key Description = Keyboards
Component Sub datasheet has Group ID = 1 foreign key Descriptive Options = USB, Wireless, PS2, Serial
I have joined both of these in a query but what i want is the query when i link it to the combo box on the form i have to only show the Group ID once and not repeat it for as many times as each subdatasheet has records.
Example: When i click External Device I dont want to see the word Keyboard being repeated 4 times (which is because there are four subrecords/types of keyboards) Please Advise.
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?
Appreciate some help on a update query if possible. In this query I wish to total two sets of figures in about five seperate queries and then export them to another DB. the Dsum function is fine and will produce a result but that result needs to pick up the record ID (called PlanID) which is the primary key for the records being updated. I have struggled with this a bit as no matter what I did the PlanID field would not even display in the query results.
The section of code that is highlighted will now display an empty field and as such the rest of the query is blank. If I manually insert the PlanID figure into the record that the query is updating the other figures will appear.
How can I change this query to add the Planid to the update after it gets it from the customers form?
I run a Query based on some data in a "drop-down" box. It works in just datasheet view, but when I print it on a report, it displays the position that the data is in the drop-down box. How do I display the data, not the position on the report? Here's an example: My drop down box contains: apples oranges bananas
My query returns apples, but in the report view it displays the number "1" instead of my data "apples". If the query returned oranges, the report would display "2" instead of "oranges" and so on.
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.
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).
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).
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 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.
I have a combo box which queries a table and then stores said tables primary key field in a field for my main table. The table queried has two fields; one for the primary key and the other for the string, such as "chris".
My problem is when I run new query from the main table it shows the bound column, which is the primary key, instead of the string column which would display my name. I can't figure out if there is code that I should deposit in the criteria field that would tell the query to display the name field (ala "Chris") instead of the bound column field (ala primary key).
Do I need to add something to the criteria field or run an expression that says display column 1 instead of column 0 in your quey?
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.
I have a very simple table with a field that will have only the values open or closed
I created a query based on this table and used the count function (to count open or closed cases) and in the next querie field I used the group by open.
This gives me a count of all open cases
I can do the same for closed cases.
I want this count to be displayed on a Switchboard form but i cant get my nehad round how to get it there.
I have two tables, and I made a union query (tbl1 UNION ALL SELECT ...tbl2). Problem is, I have two entries that are identical in both tbl1 and tbl2, but I want to include BOTH of them in the union query. Even though I used the ALL operator, only one set shows up!! I'm pulling out my hair trying to figure this out. Please help, and thanks so much in advance!:(
perhaps it could be a fault in my PC; though I designed a form which depends on a selection-query and when I fill in the criteria in the selction query to look for a certain record, my form display stays empty. Even empty records won't be shown. Is this phenomenon occuring either normal in Access or is this problem related to the configurations of whether my query or relations.
It's already pissing me off the whole day,
so I would be very eager to know what's the solution,
I want to display that query data in a Pie Chart, one chart for each theme with a section of pie for each Type (with the value of the count determining the size). Unfortunately my mind has gone blank. Everything I try does not appear correctly, some queries also require an repeated entry of the parameters but even if I do that it doesn't display correctly. I think I need to write another query to the format:
