Need sorted Query to be used in a Listbox. The attachment shows a made-up view of the objective.
The first column shows the change in value, then blanks for the first column's next rows, until there is a change of value again.
I do something like this in Excel where the first change in value is bolded. This is to make a list box more readable.
trying to create an update query to Budget table using the Access Design View:
Field: PctSls (in tblBudget) Update to: [Expense] / [Sales]. The update query always returns 0. However, if I create a Select query using the same calculation, the correct results is displayed. PctSls is defined in the Budget table.
I haven't worked with Access (2003) for several years but this seems too simple to be causing me such frustration. (Was only a casual user even then).
I need to come up with a way to derive field X (see below) in a query.
For each change in field A, Set X=1
For each change in field B, X=X+1
The below table shows what the query results should look like.
A
X
B
[code]....
I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this
IDENTITY:"TEXT(ROW(A17),"-0")"
What am I doing wrong?
I am using a query as part of a mail merge, there are two forms that use the query, create and view, both forms use the same table. When I click the print button the query runs, and mail merges in a word document.
What I need is a filter on the query which only shows the record currently open on which ever of the forms is open, so the mail merge only happens for the 1 record you want.
I'm having a format problem. I have a query (Q1) that, among other things format a date field as YYYYMM (Field1).
I have a second query (Q2) whose data source is Q1.
In Q2 I need to link Q1 to a table but Field1 is being reformatted as text (confirmed by running a make table query).
I want Field1 to be Number format to eliminate a mismatch error.
I know how to change the format of a field in a table using VBA but I cant seem to find a way of changing the format within a query.
In a table of a SQL-Server 2008 I have a fieldname "Alter", the German word for age.
When sending a PT-qry to the server I get an error "Incorrect syntax near the keyword 'Alter'"
Even when I try to set "Select tblmytable.Alter as Age" I get the same error.
To change the fieldname is almost impossible, as there is to much code to change.
I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
Users are viewing a record on a form that gets it's data from a query. I want to make it so that if they edit that record from that form, a last updated field will timestamp the date/time that the record was edited. I added a lastupdated to the source table which of course adds it to the query and so it's on the form.
But whenever i access the form and change something, the lastupdated only shows the time the record was created (which is from a different form based directly on the table). Whenever I edit any other field data in that query based form it changes in the table. Why not the Lastupdated?
I have some incorrect time entries in a column that I need to fix with an update query.
So, 04/11/2013 08:00:00 needs to be changed to 04/11/2013 09:00:00
I'm trying to create a query that generates random numbers for each record, sorts them by that field, then selects the top record. This should randomize the record being selected.
I can use the Rnd([ID]) function which does appear to generate a random number. Problem is that each time I exit the program and come back in, it always selects the same record. When I remove the Top = 1, to show all the records, every row does have a different random number but it does not appear to be sorting by this field.
If I run the query, here is the number I get: 0.98609316349029
Exit the program, restart, and run the query again: 0.98609316349029
If I refresh the query, the second and third time does appear to be random but the first result is always the same. how to generate truly random numbers?
How can we do this? Can someone pls help? The query should be part of the code. Like create a variable strSQL = "SELECT * FROM ...." and so on
Newbie here :)
I have a column "CAT" each time that CAT switches between 0 and 1, I would like my query to auto-create a "grouping" and increment the group by 1. What I am looking for is the output as shown below.
ID CAT GROUP
67 0 1
68 0 1
69 0 1
70 1 2
71 1 2
72 0 3
73 0 3
74 0 3
75 0 3
76 1 4
77 1 4
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
I run a query daily and have to alter the date in the design view (as below).
>=#01/04/2013# And <#11/03/2014#
All I want is for the query to run 2 days behind, so for example today is the 12/03/2014, I use the above, and then daily change this every day.
I have windows 7 access 2013, I had a query made, and wanted to add fields to it, so i went into the SQL and added several fields. Only the first one i entered didn't work and on the query is called "Expr1003" when in datasheet view. Its right in layout view, and even when i tried adding the same field in layout view, it made it "Expr1004". spelling is correct, in both source table and query, i don't know why this field doesn't work especially when the others did.
View 14 Replies View RelatedIn some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
My attendance dB has some last name in lower case and others in all caps base on user input. Is there anyway to make it consistent? Also, please there will be about 250 users scrolling through the name column for attendance. What would you recommend for the most user friendly way to display names? How to create a form that would perhaps allow a button for all last name like "A- C" member name
SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First Name]),IIf(IsNull([First Name]),[Last Name],[last Name] & " " & [first Name])) AS [Student Name], Students.*
FROM Students
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));
A have a query that selects a multivalue field. The query forms the record source for a subform. The text values in the multivalue field are displayed in the query, but when I save this and view the data in the subform datasheet view the values revert to the primary key values. They are 1,2,3,4 instead of the text values
View 3 Replies View RelatedI am trying to , create a field using the IIf function that will display a $250 bonus for agents every time they sell a car for at least $20,000. Otherwise, the function will return a 0. Name the field Bonus. I put in Bonus: IIf(SalePrice >= "20,000" Then + "250",True, 0) and it is saying that I have an improper syntax.
View 2 Replies View RelatedI have a query with a float/delta column which is the expression:
Code:
Float: [Date1] - [Date2]
When I try to change the order by in the column filter drop-down i get a data type mismatch in criteria expression error.
I have a Date field in the format X/XX/2012 for all my records.
I want to create a field that labels each record according to its quarter. So if a date is 3/29/2012, I'd want the corresponding field entry to be: Q1 2012. If it's 3/29/2013, I'd want it to be: Q1 2013, etc.
I guess I would run an update query, but I don't know how to build the proper expression in order to update my table with a new field.
I have figured out how to create a quarter #, but I actually need output in the format mentioned above.
I have two fields.
Field 1 contains the names of local authorities
Field 2 contains the names of electoral wards - preceded by the names of the local authority area and " - "
I want to create a new field containing only the names of the wards.
How can I get Access to read the LA names from Field 1 and strip them and " - " from Field 2 and post the resulting text in a new field?
As LA names all vary in length simply stripping out first ?? characters or words doesn't work.
I have a table called Price list, and in it I have two fields, one called Service and The other called cost. The services are listed and their matching prices are listed beside.
In another table called appointments, when making an appointment, I have linked the information so that I can choose from a dropdown from the price list table, under Service type.
I need to be able to create a receipt for the appointment. How can I do that in a query format?
I only want to view unchecked boxes in my query. What I can tell you is that I used most of what I could find on google like false/ 0/ Where [fieldname] = False/ and some others but I'm having no luck. This is a yes/no field with checkboxes.
View 4 Replies View Related