I have an issue I've run into and have searched around and tried to resolve. The issue is that I have a table that is updated once a week with new data, calculations are performed on new fields, etc...Ok, no problem on that. Oh, to give you a quick overview, the table is for Open and Received orders. Ok, the new field is Actual Received Date. Well, if open, obviously it's not received. If received, it's placed in that field, if Open, that field is blank for that record. I need to create a SELECT QUERY, would save TONS of time here if possible, to do an IF THEN ELSE type aspect that basically, if Actual Receive Date is present, then use this calc, if not, use that calc, etc...ANY IDEAS? Take a look below for additional information. The following are ONLY EXAMPLES to give you a better understanding of the issue.
1 Table, 1 Query, 2 Fields, IF THEN ELSE EXPRESSION within SELECT QUERY under 1 Field.
If IsNull([Tables]![tblOrdersMain]![mActReceiveDate].value) = True Then
[Query]![Query]![DAYS LATE].value = Value1
Else
[Query]![Query]![DAYS LATE].value = Value2
End If
Well, definitely need HELP! Any questions or anything please let me know.
I currently have a table showing activity for multiple staff and their availability throughout the day in 30 minutes segments.
I am currently trying to pull the information on who is working by 30 minute timeslice, but as the information is held in a different field for ech period, it is proving difficult.
My thought was to make a query rounding the current time to the nearest hour/half hour and use this to choose the field, but I don't know how to make a query which will allow me to pass a variable (Field name) into the Select query?
I am trying to write a query that looks at a set of parts that are assigned to the same "Asset" number, and takes the sum of all prices for that collection of parts.
I have the query set up to do this without any problem. Where I am stuck is that in the table containing the details of parts, there are three possible prices. What I need it to do is take price c if there is no value in prices b or a, price b if there is no price in price a (otherwise take price a).
I have tried this using the criteria ="price a" = 0 AND "price b" = 0 (for price c) and ="price a" = 0 (for price b criteria). When I run the query I get a "Data type mismatch in query expression" error.
I have three tables. TableA contains certain order information, such as the username of the employee, type of order, alias number, etc. TableB contains phone numbers, username of the employee, etc. TableC contains supervisor names and alias numbers.I didn't make these tables but that's what I'm working with. I connected TableA to TableB with the username of the employee, and then TableA to TableC using the alias numbers.
I have a report that summarizes how many orders of each type there are. It shows the supervisor, employee username, alias numbers and then each type of order with the amount of orders of each type under the headers.My boss wants to be able to click on the amount of that type of order and have it show him the details for those orders. This is where my problem is.
I was able to use an Inner Join type of query that allows me to filter the data by order type (however, I have to make a new query for each order type since I'm not too experienced on this). The problem is that I cannot find a way to have a query filter the data by order type AND employee username, so that it only displays the orders taken by a certain employee and it only contains one order type at a time, depending on which order type you clicked.
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
I have joined 3 tables using the query design and I have 3 different fileds. There are fields that have information that I don't need. e.g. I don't want the first 10 characters of field 1. How do I do this in query design (even in SQL view). Or do I need to do this in the table itself before joining the table. Hope my question makes sense. Thanks for any input.
So I have another how do i get a form feild to automatically pickup the largest value. it is for an auto number and i want it to show only the last record that was created so it has to be set to show the largest number. any help would be great.
Is it possible to have a specific tab in a tab control selected automatically based on the value of a combo box? Not by what is selected, but by what is already stored in the field.
For example, if have a combo box with a value "shirts," the "shirt types" tab is automatically displayed in the tab control box.
I have a table with 84000 records in I need to fined all the records that have the same First two starting characters and the same last two characters but are different between the first two and last two characters.
Ie. 30123456757 302356757 301234567
I just want the ones that have different number in between . I have tries ever Select statement I can think of and still coming up with every thing but what I need.
I have a table called myTable and it contains two fields Year and Month. Both are type of integer.
Now in my select statement, I like to combine these two fields in to one and name it as period and it will have the format, for example 200501, 200502, ..., 200512. How can I do that in Access query statement?
I know how to do this in SQL Server 2000. That is:
Select Convert(varchar(4), [Year]) + right('00' + cast([Month] as varchar(2)), 2) as Period from myTable
I have a calculated(unbound) text field named(Age) on my main form. The Age is calculated based on patient's DOB(Date of Birth) Field using datediff function, and in the subform, I have a field named (AgeNo).
There is a criteria for AgeNo Field depending on (Age) field for each patient.
Criteria:
If Age is 18-40 = AgeNo should be 0 If Age is 41-60 = AgeNo should be 1 If Age is 61-70 = AgeNo should be 2 If Age is >71 = AgeNo should be 3
MRN (Med. Rec. No) field & Age are in Main form and AgeNo field is in a subform. I want the AgeNo field to automatically select ( 0, 1, 2 or 3 ) based on the calculated unbound text field(Age).
Hi, I want to have the entire field in a textbox selected, automatically, everytime the control is entered--- so the user can just begin typing and automatically type over the existing data in the textbox.
How do I do this?
I thought it would be as simple as adding something like the following to the textbox's on_enter event:
Sendkeys "doubleclick"
The textbox will always contain just a single "word", so this idea should work....but I can't find anything like a "doubleclick" that can be used with SendKeys.
Okay - have hunted the web for the answers to this query on and off for months. Thought I had something but it didn't work.
Problem - basic form with subform of a 'select * from table' dataset. So far I've added one textbox. I'd like this textbox to show the recordnumber selected in the subform or the KeyID of the record selected in the subform. This is so I can populate other textboxes with details of that record for editing and then control/validate data entry.
Have tried: form timer, form load and some version of seltop (not bothered about selheight at this stage) - sort of worked but not properly - at least it gave 'an' answer but not always the correct one and you had to click in and out of the subform to generate an answer.
Private Sub MySubform_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) Text4.Value = MySubform.Form.SelTop End Sub
Private Sub Form_Load() Me.TimerInterval = 10000 End Sub
Private Sub Form_Timer() Text4.Value = MySubform.Form.SelTop End Sub
I think the _MouseUp sub isn't working as its not a recognised event for a subform.
I started looking at various DisplayRowID, SelRecord functions (in a separate module) run from a command button in the form footer but that's not really the right sort of thing and the referencing to the subform is a bit iffy.
I'd be grateful if anyone could advise on, even complicated, code. Thanks, Lez
On an access 2010 form if there are 5 fields (Number,Address,Gender,Age,Date). Is it possible to use a button to export 3 fields (Number,Age,Date) to an excel spreadsheet (Master) and place them in a specific worksheet (Info)?
I have a table which contains business details (name, address etc) and also a field for clients. I then have a table which contains client details. Is there anyway I can click a button on a form containing business details which brings up a form containing the clients which would allow me to click the clients I want to be inserted onto the clients field on the business details table?
I have a dataset that consists of Stands within a forest inventory. There are two Fields: the first field is StandValue (dollars), the other field is the StandArea( hectares).
I want to select the highest valued stands BUT the sum of the individual areas found in "standArea" cant exceed 10,000 hectares. Can anyone help me figure out an SQL query for this, or any other suggestions on how to proceed would be greatly appreciated, thanks Greg
I have a split form, populated with my DVD movies, with the datasheet on the left. On the right I have 3 fields. Title Movie Type and Movie Description. All are enabled. But, today I was unable to select the Description field. I would click in it but the cursor would not enter to field. I needed to select another field and the then I was able to select Description. I was able to select the field programmatically. I needed to find a form from a backup and replace it.
I'm trying to allow my users to do some analysis of existing data. they would like to be able to generate a report which will summarize sales orders against the month they were placed. They would also like to summarize this by between 1 and 4 other fields.
I have created a "filter" form to allow them to choose the values for each of the 4 fields and my plan is that they will leave them blank if they do not want to narrow them down. My problem is that I would like to remove the grouping on the field if they do not select a value for it.
Example data:
Field1, Field2, Field3, Field4, Cost, Month, Year 1,2,3,4,£20,4,2014 1,3,4,5,£10,4,2014 1,2,4,5,£5,4,2014
If they select nothing for any of the fields I would just want to see that my total for April 2014 is £35
If they select 1 in Field1 then I want to see one line with a total of £35 (i.e. it ignores the distinction of the other selectable fields)
If they select 1 in Field1 and 2 in Field2 the total would be £25
If they select 2 in Field2 and 4 in Field3 the total would be £5
etc. etc.
I am struggling because I need the distinct records to allow me to group on them when I need to but if I don't need to group on them I need to ignore the grouping.
I've looked through the thread and was unable to find exactly what I'm looking for ?
Basically I have a table with multiple fields & records and I would like to key on the ID field which has a slew of different ID's..... these ID's can be the same & show up on the table anywhere from 1 to 600 times.
What I'm trying to do is select each ID and pull only 10 records for each or if less than 10 pull in those as well.
So instead of pulling in the 600 records for that particular ID I only want to pull in 10 of those records, and for the ID that only has the 1 record pull in the info for that as well.
