I'm trying to obtain all of the information in a text field for a query.
The user entered information and used a return (enter key) to enter information on a new line within the same field. The information after the return is not captured in the field in the query. Is there anyway I can capture all of this info?
I have a table (tblInstallations) which has records of date installed (for machines).
It is calculating that "next due maintenance" is one year after installation.
Sometimes we need to visit the site to maintain or service the machine before/after the specified date in "next due maintenance" and we need this to be calculated to reflect a year after date of visit.
I have a table called "tblMaintenance" which takes information about the machine an engineer is visiting from where originally the information are kept and that is in "tblInstallations".
How can I link the new date of the visit to update the record for "next due maintenance"...
I built a form that feeds data to tblOrders. In this form I have a button "Send", that must send a message to person from tblPersons.
I have a problem to get an "email" from tblPersons.
Any assistance will kindly appreciated.
Code:
Private Sub btnSend_Click()
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
mess_body = "Dear " & PersonID & "," & vbCrLf & vbCrLf
With MailOutLook
.To = PersonID ' <-- here
.Subject = "Order Accepted"
.Display
End With
End Sub
Attached also tables relationships.
I'm trying to summarize the value from multiple fields in a table and the total value will be updated on a different table as per highlighted below (taken from Northwind Web Database).
View 3 Replies View RelatedHi All
I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.
1)
I have the following in a field called PROBLEM STORE NAME:
SALE (DAY 1 - HD)
Mt DRUITT (WOWPOS) (E)
THE MALL (WOWPOS)
TOWN HALL (RF)
LIVINGSTON
I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as:
PROBLEM STORE NAME.............................STORE TYPE
SALE (DAY 1 - HD)............................................... ..ISS
Mt DRUITT (WOWPOS) (E)......................................WOWPOS
THE MALL (WOWPOS).......................................... ...WOWPOS
TOWN HALL (RF).............................................. ......ISS
LIVINGSTON........................................ ..................ISS
I use the following formula in Excel to achieve this when I export query results from Access:
=IF(ISNUMBER(SEARCH("wowpos",K2)),"WOW","ISS460")
2)
I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel:
=IF(ISBLANK(X2),"NO","VENDOR CALL")
Any suggestions would be greatly appreciated
cheers
sallee
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View 14 Replies View RelatedI have a query with a parameter query but what I need is the information to be passed onto another parameter query automatically.
I have a Field called ITEMID and ITEMIDFK
the criteria for ITEMID is [ENTER Item ID Number]
once entered I need ITEMIDFK to use the same information inserted into the first parameter is this possible?
I have a field which contains "01501 PRIKLJ. VRHNIKA - LJ / Stevno mesto" .
1.) I would like to extract just text part from that field, how? *number is always on beginning but different 1-10000
2.) Optional, how could I delete in this new only text field part of text after / ...*some rows have some dont /
I am trying to search for specific text in a field and returns its value. For instance some of string includes Sub, L2L, Temp, Model, or MTM and I would like a query to return these values if found.
View 2 Replies View RelatedI am trying to create a query that returns records whether a field has data or not...
There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.
I would like to do this in the criteria box in the query.
The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...
I am trying to create a query pulling from several tables. I will use the example below to illustrate what I'd like to do.
I have a field called 'Acc_Num', one called 'Stat_Code', and a third called 'Cat_Code'.
Each 'Acc_Num' can have multiple records because there are multiple Stat_Code and Cat_Code values.
What I want to do is isolate just the Acc_Num records where Stat_Code = 1 and Cat_Code equals A1.
I also want to isolate the Acc_num records where the Stat_code = 1 and where Cat_code does not exist.
How do I design a query to return a result in a wildcard format? So that I could enter a part of a name, and it returns all the names that include that part of name?
View 1 Replies View RelatedI have two tables of data. One contains balances for a set of accounts (one unique balance per account, per date). The other contains transactions for a subset of the accounts (multiple records - or possibly none at all - per account, per date)
I'm trying to write a query to return the sum of the transactions from the Transaction table, for each unique account + date combination which is present in the Balance table.
Here is my SQL :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(tblTransactions.Amount) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
This works fine with one major problem; if there are NO transactions for any given account + date in the Transaction table, I get no record for that combination in the dataset.
What I need is for the query to return a 0 in those situations (i.e. I should have as many records in my queried dataset as there are in my Balances table, but SumOfTransactions may be 0 for some of those balances)
I've tried the following but it has no effect on the outcome :
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Nz(Sum(tblTransactions.Amount),0) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
Quote:
SELECT tblBalances.BalDate, tblBalances.AccountID, Sum(Nz(tblTransactions.Amount,0)) AS SumOfTransactions
FROM tblTransactions RIGHT JOIN tblBalances ON tblTransactions.AccountID = tblBalances.AccountID
WHERE (tblTransactions.TransDate=tblBalances.BalDate)
GROUP BY tblBalances.BalDate, tblBalances.AccountID;
I have a field created in a query expression
BirthMon: Format([DOB],"mmmm")
I would like to create another field in the query using DLookup to return the value of the BrithMon.
BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")
This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'
Can I used DLookUp in a Query expression to refer to another Query created field?
I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.
The only way I know how to do this is by an IIF statement :
Code:
SELECT [fld1], [fld2], [fl3], .....
IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4
FROM...
(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)
Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.
I have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
but this returns errors when I try to run it.
I'm using Access 2010.
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
Produce a query that returns a yes or no (Y or N) field if other text field contains particular text (with wildcard on either side).
Idea is to have a column for each preset word.
Hello, I am having problems with one of my forms.
I use a very simple database (4tables), basically I have a table for customers, and a table for sales. When I want to enter a new sale, I have to input the customer's ID and the total amount for the sale. But since I can't memorize all of the custemer's IDs, i have to go and manually check the customer table to get their ID.
What I want to do is to get the customer ID from the same form I use to enter the sale, by just typing the customer's name.
How can I do this? thanks!!
How does one cause certain data to appear in another field based upon the data entered in another field.
For example: I enter in the field the word "Carrot" in a field called food and the word "Orange" appears in the field color. And then if I enter the word "Beans" in the field food then the word "Green" appears in the field color.
Thanks for you help in advance.
Eric
Hi..(",o) anyone can HELP?
I'm currently using MS Access 2000...
May i know how can I translate the following TABLE
Doc# Team# Status
D1 TA Done
D1 TB WIP
D1 TC WIP
D1 TD Done
D2 TA Done
D2 TB Done
D2 TC Done
D2 TD WIP
to a QUERY reflecting in the following format?
TA TB TC TD
D1 Done WIP WIP Done
D2 Done Done Done WIP
Your help will be greatly appreciated!
How can I obtain the value of a record from my subform in datasheet view?
Ideally I could double click in the "cell", for lack of a better word, and grab that value to pass through to another function.
On double click event? What then?
Edit: I suppose the user could select the row that the value is in as well. As long as I could then find the field I need from within the row. That might make things easier.
Edit 2: Sometimes I post before doing proper research. I've figured it out. Here is a quick example. The below code references an unbound subform called "Main_AU" where the field I want is "Description". The MsgBox is used to confirm that the code is grabbing what I want it to.
Code:
Dim test As String
test = Main_AU!Description
MsgBox test
I have a field [CurrentDay] of type Date/Time. The values for this field are entered through the selection from the date picker. However the time of the day always come along with the date - even if it is not seen. This creates a huge problem when making a query based on this field because no results will be returned unless the time is included with the date in the criteria.
The other part to the problem is that I cannot find a built in function in the expression builder to generate the current date without appending the time - now() certainly does not do that - even if the time is not seen it is there.
Any way to obtain all individuals that report to a particular person (directly or indirectly) through a query. A sample of the table is below:
tbl_Hierarchy
Enumber
FName
Mngr_Enumber
E111
John
E000
E222
Mary
E111
E333
Paul
E111
E444
Sarah
E999
E555
Sam
E333
E666
Tom
E111
E777
Karen
E111
E888
Jim
E666
E999
Deb
E666
For example I would like to see everyone who reports through Tom(E666). The result should yield:
E888
Jim
E111
E999
Deb
E666
E444
Sarah
E999
Hello Everyone,
Ok, here's the scoop. I have a tournament / player score tracking database that we are developing.
1. I have a form that I would like to use to select a table number. I use a combo box to select this table number. This part is easy.
2. Once I have selected the table number, I would like to have two sets of fields automatically filled with information from two different queries (ie: player #1 and player #2). Each set of fields will include the player name and the appropriate score fields for the event. The majority of the time the data that will show up will only be the player name since we will be receiving scores and entering the information as it comes in. However, this same form could be used to edit player scores.
3. I would like to be able to tab from one field to the next when entering the information. After all of the information is entered, I would like to then hit a button that will write the information back to the two queries.
The problem that I seem to have is trying to enter information from two queries on the same form. Alas, I have been experimenting with unbound forms, subforms and unbound text fields, but have not been able to get anything to work. Would anyone have some suggestions? Any help is greatly appreciated.
Thanks,
Greg
Okay I have 2 tables and I used queries to filter and delete the info off those tables. Now I combined the 2 tables by using a query which is query 1. What I found was a few cells that are blank cells.
I tried creating another query based on query 1 from the 2 tables to try and delete it but it won't work. I tried everything and nothing seems to work. I can't filter the data since I am creating another query called query 3 to display the final results.