Sorry for this totally stupid question but my memory is just not what it once was.
I need to separate one field into two, in a table that was imported from excel
Basically it has a size and a unit in each field
Example:
24.67 sy
26.84 sf
Thanks in advance for jogging an old man's memory.
I want to do this in a query not code if possible I remember something about a formula you can enter into the update to box in a saved query.
Hi All,
I am a novice at MS Access but I am learing new things every day.
I am using MS Access 2000 for this DB.
If you need to see the DB let me know and I will strip the sensative data out of it and post or send a copy. The DB is for a small retail sales store.
What I want to do is something like this::
If [Tables]![Sales Entry]![Item Tax] [= $0.00] Then copy [Tables]![Sales Entry]![Item Price] to [Queries]![Taxable and Non]![Non-Taxable] [Else[If [Tables]![Sales Entry]![Item Tax] [> $0.00] Then copy [Tables]![Sales Entry]![Item Price] to [Queries]![Taxable and Non]![Taxable]]] End If
I want to split up the Item Price field by checking to see if Tax was applied or not and copy the Item Price to the proper new fields in the query called "Taxable" and "Non-Taxable". Then in my Report I can sum each of the new fields by date.
Thanks
Ron
REPOST from Design Area
Hello all,
I hope that the following explination of my problem in clear...
I have a table with a series of dates in individual fields: Day1, Day2, etc.
What I would like to do now is to combine those into another query/table where the dates are all in one field.
ie:
Original Table
[Day1] March 1
[Day2] March 2
[Day3] March 3
New Table
[Date] March 1, March 2, March 3.
Can you help me figure out how to accomplish this? I greatly appreciate your time and sharing of any thoughts that you have on this situation.
t
I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.
Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents
Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents
Function:
Option Compare Database
Option Explicit
Public Function ConcatField(strSQL As String) As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String
[Code] ....
I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.
Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));
What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.
But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.
Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.
I have several tables that I would like to be seperate from the forms. Bottom line is that I want the program stuff to be seperate from the tables seperate as in two different files.
I have not messed around with this yet and am wondering how big of a pain this will be to do with the code that I have already written?
Any thoughts ideas etc.?
I am new to Microsoft Access. I have built two entry forms for product release information. I have a drop box under the label customer for each specific customer. I would like for this form to send the data entered in the form to the customer specific table from the customer names in the drop box. I have tried to research and execute this myself thus have been very unsuccessful.
View 14 Replies View RelatedI have a datase that has the following fields.
Name
Comment1
Comment2
I have about 36 names and each of those names appears anywhere from 2 7 times each. Each name entry has a comment1 and a comment2.What I would like to do is create a form that allows me to choosea name and the display the comments 1 and comments 2 in text boxes.
I have made the form to allow me to choose a name and it shows one of the comment1 and one of teh comment2 in the adjacent text boxes as planned but, I want all the comment1s for Bob to show in the text box for comment1 and all of the comment2s for Bob to show in the Comment2 text box.I believe I need to concatenate the results in the comment1 and comment2 boxes, but I do not know where or how I would do that.
hi,
I am trying to pick out the ERAP no.s out of a whole string of data. ERAP no.s are like invoice no.s. Do I Append or Make table? How do i go about doing this? I want the ERAP no. in a row next to the the description field all next to its respective string of data too....
for an example..
1)ERAP43463 STAFF TEAM LUNCH MID-YEAR REVIEW
2) 0507 SUBMITTED ITEMIZED ACCR-ERAP43159
See.. the part where the ERAP no. appears is inconsistant.. if not i could simply use excel and use text to columns. We have thousands of lines like this every week... There has to be a way to pick out just this detail and fill in the column next to the respective datastring...
Can someone please advice me on how i can go about doing this?
Hi,
Is there any way that i could extract data from a single cell inside the table to the forms, and vice-versa?
Hi all. I'm working on a temporary database (Access 2003) for work until we get our system validated. Is it okay or acceptable to have a single-field table or do I need to throw an autonumber on there for the key? I really just need this table to populate a combobox/listbox in the form. I don't want to to use the "OnNotinList" with the combobox so I need to store them somewhere. Thanks!
View 1 Replies View RelatedI have a tabbed form that contains multiple rows of data just like the following: (it's a service checklist for a store where the user makes choices from the combo boxes: e.g. good, fair, poor, etc)
Paint (txtHrs), Action (cboPaintAction), Condition (cboPaintCondition)
Carpet (txtCarpetHrs), Action (cboCarpetAction), Condition (cboCarpetCondition)
Counter (txtCounterHrs), Action (cboCounterAction), Condition (cboCounterCondition)
etc.
Since the combo boxes describe data that relates only to that element I can't put all the data needed into a single table. There are 40 of these rows so does that mean I have to create 40 tables? If so, how do I add the data to them from the form? Any help with this problem would be greatly appreciated!
I have created single form and 7 different tables. In my form I have a combobox which have different branch names (Table names are as per combobox values). Now what i want to do is whenever I select a branch name from combo box and click on the save command button the particular record should be saved in particular table.
View 1 Replies View RelatedAny Single line of code available?
View 2 Replies View RelatedI am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph
Hi, this is my first post here.
My question is this: I am creating a database for a student survey. Some of the questions may have multiple answers.
For instance, question 2 reads:
"What location/time are you taking courses (select all that apply)"
and the possible answers are:
1)Daytime/Oakland
2)Evening/Oakland
3)Saturday College
4)BC
- Where a student could be taking classes at multiple locations or times. I am completely lost as to how to set this up. I have tried to set the properties for the field on the form to accept multiple responses, but of course, these do not save to the table and so it's completely useless.
Thank you in advance for your help!!!
Hi,
I have a product table in Access, like this:
product_id | product_name | product_quantity
I need to be able to do an SQL update on this table to subtract 1 from the product_quantity field.
I could do this with two SQL queries (SELECT product_quantity, then subtract 1, then UPDATE table) but I'm sure there must be a way just to run one query which subtracts 1 from the field.
Does anyone know how to do this?
Any help would be fantastic, thanks!
I want to be able to perform an AND search on a field where both specified values must be present for an item to be selected.
I have a table with some properties of items. Each record in the table is for a specific item and one of its properties. Say for instance that I want to list the items that are described with both "red and green". Assume that there are entries for widgetA to widgetG with colors "red, blue, yellow, purple" and widgetB and widgetE also has the property "green" in addition to the others ... the query should return widgetB and widgetE only.
I tried to use MSAccess 2003 query builder but cannot get the AND result to work. I switched to an OR and verified that the two values I am searching for do exist for two items in my table.
Here is the SQL statement that did not work.
SELECT Properties.PropertyIndex, Properties.HerbOilLink, Properties.PropertyTypeIndex, Properties.PropertyDescriptionIndex
FROM PropertyType INNER JOIN Properties ON PropertyType.PropertyTypeIndex = Properties.PropertyTypeIndex
WHERE (((Properties.PropertyDescriptionIndex)=80 And (Properties.PropertyDescriptionIndex)=15));
I am a struggling low level user and any help will be appreciated. I’m trying to get data out of an old database and into a new one. The following addresses will give you some idea as to what my problem is. :confused:
I would like to break up the following single field address (Note the different pieces of data.
Washington Blvd 1407
Jefferson ST 234 N
Benjamin Ave 2105 N #1
Lincoln Cir 1407 N Unit 201 (Notice the space after Unit)
and have it look like this
Field one: 1421
Field two: N,S,E or W
Field three: BENJAMIN ST, Ave, or etc
Field four: Anything left over
If you can help, please talk very slow and loud cus I'm old and not too bright. I have limited experience with Instr, Mid, Left and Right functions, but can never seem to get them to work in a SQL statement I think I need to use multiple append queries but I’m guessing and then how do I get all the pieces into one table. :o
Thank you in advance
To anyone who can help,
I have created a function that loops through an amount of records and concatenates them into a single comma-separated string. What I would like to do is concatente the values into a list. Instead of:
value1, value2, value3
I would like to see
value1
value2
value3
This needs to be in a single record only. The code I am using is:
----------------------------
while not rec.eof
string = string & ", "
rec.movenext
wend
string=left(string,len(string)-2)
--------------------------------
Thanks for all your help. I have tried to insert char(20), but this only seems to work in Excel. Access gives me invalid char symbol.
I've done some looking into how to validate in Access and I found how to do it for an entire field but I was wondering if it's possible to validate a single record in a field.
View 6 Replies View RelatedI have question regarding multiple choices fields. I would like to store constrained but multiple values in a single field. I used the assistant to create the multiple choice field in the table, but I'd like to have it displayed in my form with check boxes (more readable in my tabular form) instead of drop down list. Is it possible ...
View 6 Replies View RelatedI have a small database I am building to log and record Calibration Details Of thermometers etc. I have several table that are related, no probs so far.
However I am struggling with forms and reports that use data from the asset table more than once. IE. I have a calibration unit (this is an asset as this also needs calibrated ) that I use to test the thermometers (These Are Assets) but this is also in the same asset table as the thermometers. I need to reference the Calibration unit and the thermometer or whatever item I am testing on the same form or report. I seem to have got round the form problem but i am stuck on the report as access keeps linking to only one item, how do I make it link to 2 items on the same table on the one report.
Is there any way I can use data in the row as a column header ? I only have 1 row of data which i want to use as a header to I can link it to other tables/queries.
(Access 2007 query)
All i want to do is to print my report (with the records ideally!) which has a subreport header at the top but all i am getting is a single blank record?!
Surely this is not too much to ask but it is holding up the whole project!!!
I cannot seem to find a way to upload several rows of data to one column in Access!I have about 9 columns already in the table which have 900 rows each. These include names, numbers, address etc.I was asked to add a column on next to the 9 columns with corresponding User ID such as 0093457FX (Which I really do not want to add manually).Every time I try to upload an excel file the data either goes below the current data from the 9 columns on the new column and does not match the current data set, or it does not show up at all.
View 4 Replies View Related