Hi,
i would like to return information in the below format but not very sure how it is to be done.
Role, Name, ID, Contact Number
Z, Mary, 05, 1234567
, Matt, 02, 2233445
A, Bob, 10, 7654321
C, Tammy, 11, 9988776
and the order of Role (Z,A,C) is fixed it has to be in this order and if Role Z has more than one person, it will return only the name, id and contact number but will not repeat the role.
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
IIf(DatePart("w",Now())=2,Date()-3,Date()-1)
Using >Date()-3 doesn't work.
Thanks
Bruce
I have set up a basic database that has a customer table , orders table , join table and products table. I have created an invoice form that has customer details and then in a subform has the order ID and product ID , name , price and quantity.I have a text box on the form to which I am trying to output the sub total to when the form is launched(quantity X price for the current order).I was trying to create a query that will return the subtotal for an order.I had the query calculating the subtotal but I was then trying to get t the cost based on the order ID. I was then attempting to get the Order ID for the query from the open form by adding this to the criteria in the query:
[Forms]![Invoice]![invoicesubform].[Form]![Order ID]
I was then trying to use Dlookup to put the result of the query into a subtotal text box by using:
=DLookup("[cost colum from query entered here]",[the query name was entered here]")
Hi all,
I have two tables: ImageSoft and Software.
All items of software which have a type SA (Standard Application) must have an entry in ImageSoft under every Image Code.
When I add a new software title of type SA, it won't be included in ImageSoft for every Image Code and as I have a lot Image Codes I'd like to automate the addition of these new software items to the ImageSoft Table.
If I do a query with three fields: ImageSoft!ImageCode, Software!SoftwareCode and Software!Type, where Type was the criteria of "SA", the query returns the results of all the items that are already in ImageSoft and not those that aren't. Is there a way to return all those that are not in ImageSoft and not return those that are? I can the use these results to append them to the ImageSoft table and therefore ensure that all type SA software items are matched with each Image Code in ImageSoft.
Cheers,
Matt
Hi,
I am not much familiar with VB Coding.Can any body please help me with this Please:When i click on submit button,it has to show the query results on the Form.Please find the below code:
Private Sub Command_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Set db = CurrentDb
strSql = "SELECT Interface.[Interface ID], Interface.[Interface Name] from [Interface]"
Set rs = db.OpenRecordset(strSql)
Do While Not rs.EOF
Debug.Print ("Interface ID: " & rs![Interface ID] & "Interface Name: " & rs![Interface Name])
rs.MoveNext
Loop
rs.Close
db.Close
End Sub
#######################
It is not retrieving any thing.IF my code is wrong,Please advice me with the necessary steps to view the results on the form,when we clicl on the submit button.
Thanks,
Kanuri
I have a query pulling data from several tables. In the middle I've got join between a part number in a BOM table and a part number in an inventory table.
What I want is the query to return the inventory location when we have stock. When we have nothing in, then there would be no entry in the inventory table and it should return a blank or null in this and all the proceeding tables.
Can this be done?
This is probably failry simple if you know what you're doing, but I don't know what I'm doing....
I have a form (Purchase Orders) and on that form there is a search button to allow the user to search the customers table by customer name. When the user clicks the search button a search form opens and the search results (customer number, first name, last name) populate a list box named "lstCustInfo". How can I set the list box double click event to return the customer number, first name and last name to the Purchase Orders form? To make it even more complex I would like to return the data in two fields: customer number and LastName, FirstName. The problem with returning the results in two fields though is that I need to be able to break it into three to save it in the customers table because the table has a place for Customer Number, First Name and Last name.
Does anyone have any suggestions? Is there a better way to do this than returning 2 fields and having to break it back into 3 to store?
I have 3 main tables: tblEmployees, tblJobs, and tblProcedures. (See attachment for relationship diagram and additional supplemental tables).A job can have multiple procedures and an employee can have multiple procedures too.
I need to write a query such that when searching by a specific job I can see all of the employees who are qualified for that job. This is done by seeing which employees have the procedures that belong to a job. But here's the catch: since a job can have multiple procedures, if an employee only has some of the procedures I don't want that particular employee to return as a search result. The employee must have ALL the procedures that belong to the selected job.
So for instance if I have:
tblJobs
Job1
tblEmployees
Emloyee1
Employee2
[code]...
If I search by Job1, I want only Employee2 to return as a result, NOT Employee1.I am at a lost for how to construct the SQL for something like that.
I have a form that displays multiple records in datasheet view. I want to query off of that form and return all results for all records being displayed. However, I'm not sure how to tell Access to do this. Access instead wants to return records for only the first displayed record in the form or the record that is highlighted by the cursor.
In the attached image you can see a list of AccountingUnits (AU) going down. My query using AU from the form as criteria will only return records for AU 114510 since it is the first record, but I would like to see records for all AU's being displayed by the form. How can I do this?
Event#
EID
Arrived Time
Code
Cancelled Time
[code]...
I need to find all EIDs where the Code is 611 where an Event# starting with F was Cancelled along with the time of the cancellation. For these results I also need the Arrived Time for the Event# starting with E for that EID.For instance, a result I'm looking for would be:
EID
Event# (F)
Cancelled Time
Event# (E)
Arrived Time
[code]...
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 2 Replies View RelatedI have an Access 2010 application that talks to SQL Server. Part of the application creates tables on the SQl Server and inserts data to it (I know SQL Server has its on data inport/export wizard and bulk insert capabilities but for reasons outside of my control it needs to be done from Access). So I have a connection to the SQl Server and i have VBA that happily executs SQL statements on the SQl Server. The problem is I want to return a recordset from the result of a SQL query issued to the SQL Server. The idea being to check for the presence of tables already created, and if so, append to the already existing table, otherwise if it does not exist, create it and insert data to it.Ive tried the following code but it seems no recordset is being returned:
Set objConnection = New ADODB.Connection
objConnection.Open "DRIVER={SQL Server};SERVER=10.200.3.14;trusted_connection=yes; DATABASE=" & Me.Combo54
strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & Me.Combo54 & "'"
strSQL = "USE " & Me.Combo54 & " " & strSQL
Set Rst = cmd.Execute("USE " & Me.Combo54 & " " & strSQL)
I'm taking my first database class and I'm working on a hospital project in which I need to determine which pair (one doctor and one nurse) has a decremental performance as weeks go on.
I have an "r" and "s" queries.
These are the fields:
r: [DOCID,NURSEID, WEEK, RESULT]
s: [WEEK, RESULT]
I would like to find what pair had a "Good" result in week 1, "Average" in week 2, and "Bad" in week 3. My problem is that the results need to be in this specific order.
The professor told us to use a formula and I got the solution after building 6 queries that involved union, difference, and cartesian.
I am having an issue with the sort order on a subform.
Basically I have a main form with Client Details and a subform hich displays the associated test results for this client. Each client can have multiple test results which should be displayed in date order.
Initially the form was based on the table 'TestResults' and I set up the form with the Orderby property set to the field 'TestDate' and OrderByOnLoad set to True. This didn't work and the records were displayed in random order.
Next I tried setting up the form based on an SQL query with the field 'TestDate' sorted in Ascending order - same result. Tried this with OrderBy and OrderByOnLoad both set/unset as a pair and individually - always the same result.
How can I get the subform to display the test results in date order?
Is it possible to once you have run a query be able to somehow double click on the results in order to either go to original record or see more details? Similar to "show details" in excel?
I have made some queries that are working well however they are specific in nature and once found display a list of results. I have left a field that uniquely identify them and are after a quick way to view the details of the results from the query rather than take the ID# and manually search the original table.
I have after much stress gotten my comments history field to appear on a form.
The following code gives me what I needed
Code:
=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))
I have only one simple thing left, that is perplexing to say the least.
Some comment fields will be many short notes, spread over time.
My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.
Greetings,
I've been banging my head on this for days, and what really makes this difficult is the number of websites which say it should work just fine!
I'm working with Access 2003 and trying to make use of the IIf function. I have given my fields generic names in the examples below.
I have a number of items I want to search upon in my search form. Each item has a check box and a combo box. I want the query to look at the check box. If the box is checked, I want the query to use the value in the combo box. If the check box is UN checked, I want the query to ignore this field and return all values. I thought this was simple, until I tried to do it!
Here is my code, placed in the Field-name criteria box of the query:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name])
Now I know that the function works, because I can manually add a value:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],2)
This will return all records where Field-name = 2.
So... my original code should work! Because no "false" condition is stated, the query should be blank, and should return all records. But it doesn't! Truly maddening.
Here are some attempts which did not work:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],>0)
---> The ">0" caused an error. In fact, I can only specify an integer - all else, including things with operators, do not work.
Working with nulls - it does not seem to recognize an unchecked box as a null.
That's the short version of the story - any and all ideas welcome!
Regards,
-- Mark
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
Hi:
Using MS Access 2000.
Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.
criteria: [Enter date - mm/dd] Or >#1/1/2001#
When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.
I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.
Any help is appreciated.
:confused:
I have a problem when I want to create an "ORDER" form, that will allow user to enter more than one order.
I have no clue how to do it.
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
Hi,
In a query, I am using the following code to return the average length of each tenancy. This works fine unless the length is 0. If so, it only brings back 'blank'. How can I change this code to return a 0 value?
Tenure Length AVG: IIf([Average Tenure Length]>0,Format([Average Tenure Length],"Fixed",Format(0,"Fixed")))
Thanks in advance
Lee
Hi guys
Im trying to set my label to a string.
Something like
myLabel.caption = StringOne & chr(13) & StringTwo
The problem is I just get funny symbols instead of the carriage return.
Please help
Thank you in advance
Hi There,
Can anyone tell me how to send a Carrage Return in Access? When I try to use the 'SendKeys with{Enter}', Access starts to run in an endless loop that seems to be some Wizard. All I want to do is go to a new line within the same text field.
Can any one help me? I'm an amature at Access and VBA.
Hi all
i would like to know if there is a way to return a field to null
thanks in advance
Hi all
i would like to know if there is a way to return a field to null
thanks in advance