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
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 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.
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 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)
Today is 13 May and this code is supposed to return the date of next Monday (19 May)
IIf(Weekday(Date())=1,Date()+1,Date()-(Weekday(Date())+9))
but it returns 1/5/2014 (Thursday May 1), when "Date" = 13/5/2014 (Tuesday May 13)
My begin week is Sunday (1) ,
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 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.
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
I have written this query to get the last 20 records of a commodity:
SELECT TOP 20 CurrentMonthData.[Symbol], CurrentMonthData.[DeliveryMonth], CurrentMonthData.[DDate], CurrentMonthData.[Close]
FROM CurrentMonthData
WHERE (((CurrentMonthData.[Symbol])="AD") AND ((CurrentMonthData.[DeliveryMonth])=#12/1/2007#))
ORDER BY CurrentMonthData.[DDate] DESC;
But I need to compare today's close to the last 20 records from yesterday. Is there a way to say the Top 20 records from Yesterday? Then I can compare today's close to the maximum of the last 20 day's close. If anybody can help with this query I would greatly appreciate it.:)
I'm using the Date()-1 to populate a form with yesterday's date for easier data entry. However, on Mondays I always have to change the date to Friday's date because it is populating yesterday's date (which in this case is Sunday). Is there an access expression that calculate yesterday's date but ignores weekends? So that on Monday yesterday would mean Friday?
View 4 Replies View RelatedHow can I pull the last two values, grouping by property id, so I can compare them? I can use Max to get the last value but not sure how to get the previous one too!
View 1 Replies View RelatedThis may be a simple problem, but I cannot find the solution myself.
I need to design a query based on a table describing all sales during a year to a number of clients of my company.
However , even if 50 sales may have been made during the year to some clients, I want the query to return only the top 3 sales for each client.
Any suggestions
Thanks
Hi Guys I'm not really sure wether this should be in queries or forms (as not sure where the code needs to go) but heres the problem. i have a front end form (where the user has various drop down boxes to choose from) which then runs a query and outputs the results in another form.
At the moment when there are no results it just brings up a big grey empty box, which is annoying since I want to use custom close buttons and disable the red x. Is there any way that if there are no results the query could open a different form (where I'd put some kind of error message and navigation choices).
Any help would be appreciated.
Hi All,
I made a form with a specific field. If I enter a value into this field it automatically runs a query. The results are shown in another form. However if I return to the previous form and enter the same (or another) value it still runs the query but this time with no results? I think it is a memory problem. The previous form is closing.
Does anyone have the answer to this one?
Thanks
I'm trying to run an SQL statement to Sum the values of a table column. No problem. It would be useful for me to take that total and be able to set it equal to a variable within my VB code that's running, I just don't know how to do that, I'm very new to VB programming.
I cannot find help anywhere on the internet for this. :( I'd like to do something like this, just don't know how:
VariableName = Run.SQL ("Sum(tblName.Column) AS SumOfColumn FROM tblName")
I'm not a programmer by trade, so I am picking up things piece by piece, and just cannot find my way out of this task. Thanks for your help guys.
I'm doing a table for Future Business Leaders of America competitive events assignments.
I want the same table to hold Name and Event Name and be able to sort
However, I want the database to be able to sort to tell me each event that a person has, and at the same time, how many people are in each event. Presently, I have to switch between databases.
On one table, the event name is the primary key, and on the other the person's name is the primary key.
For the event name table, categories are "Event Name" , "Participant 1" , "Participant 2", "participant 3", etc.
For the Person's name table, Categories are "Name", "event 1" , "event 2", "event 3", etc.
I want to have ONE database that will sort lists for all participants in each event AND that will sort into a report or form so that I can inform participants of their events.
How can I do this?
I probably was not very clear, so ask lots of questions if you're unsure.
THANKS A LOT!!
Im having a real problem in creating a query to return the results that I'm after. I have a wargaming Database. Units are created on seperate forms i.e Armour, Infantry etc. There could be a number of Battalions of each say Armour1, Armour2, Infantry1 Infantry2, the amount is endless. All of these battalions are linked to a game name i.e Battle 1, Battle 2 etc.
Im trying to show in a seperate table the name of each battlion that is involved. At present whaen I create my query I use the Arour Table and the Infantry table but if Ive only created 1 Armour unit and 2 Infantry unit, it shows the record twice. ie:
Battle 1 Armour 1 Infantry 1
Battle 1 Armour 1 Infantry 2
I Want Battle 1 Armour 1 Infantry 1 Infantry 2
Any ideas, a bad explanation I know but hopefully you understand!
I am wanting my query results to appear in my main form so that i can edit them, rather than a table the query is just a look up of my form that stores about 500 records and stores all the same fields
any help would be appreciated
thanks
Greetings all,
Below is a query were I can get eveything into tow columns but I would like to have the results in one column?
SELECT tbl_Admin_Company_Details.Companyname, tbl_Admin_Company_Details_1.Companyname
FROM tbl_Data_JobOrder INNER JOIN tbl_Admin_Company_Details ON tbl_Data_JobOrder.CompanyID = tbl_Admin_Company_Details.CompanyNumber, tbl_Admin_Company_Details AS tbl_Admin_Company_Details_1 INNER JOIN tbl_Data_Receival ON tbl_Admin_Company_Details_1.CompanyNumber = tbl_Data_Receival.OwnerID
GROUP BY tbl_Admin_Company_Details.Companyname, tbl_Admin_Company_Details_1.Companyname;
The Results are;
tbl_Admin_Company_Details.Companynametbl_Admin_Company_Details_1.Companyname
Company 1Company 1
Company 3Company 2
What I would like is something like
Company 1
Company 3
Company 2
Thanks in advance
rgmb
Hi,
Is it possible to change the resulting format of a new field (expression) within a query?
I.e. Instead of the resulting when running query being displayed results in decimal places but instead being displayed in percentages
Regards