Hello,
There are 40 columns in a table in MS Access Database, i want to fetch all the columns data; but without only one particular column and without mentioning all the column names. How it is possible?
Please guide me...!
Thanks
I am trying to write a query to extract the first 3 characters from a character string but have no idea where to start since we usually use Select "field" etc etc
Example, for a Code ACC01, the query result should give ACC
Would appeciated any guidance. Thank!
TK
Hi,
Our shops use MS Access to save the details of customers. The shops have to submit us the customer records at the end of each month. How can they make some settings (e.g. query) to extract only the modified records and new records of that month? For example, at the end of Nov 04, I just want them to submit the modified and new records of Nov 04. Records created in or before Oct 04 should not be extracted.
Many thanks.
Using Access 2007 - My (Data Type) Memo field contains the following type of data ...
First Name: Jake
Last Name: Pies
Phone: 123-456-7899
Email: jakepies at apple dot com
Comments: Interested in all more information
How do I parse these records, about 400, located in the memo field column to multiple column fields: FirstName, LastName, PhoneN, Email, Comments.
Thanks, James
Hi, i have a fox pro memo field that has bill of materials information in like so:EACH KIT COMPRISES
1 CR10070SW £29.60 HALLITE PRODUCT
1 RBS24690 £0.82
1 RBS354 £0.95
2 RBS345 £0.75
1 S95MMEXT £3.90
£36.77 PER KIT
I need to take out the carriage returns but keep the items delimited in some way so they all apear on the one line. These are the child parts for kits so i need to keep them on one line as i import them or via a query so i can see the parent part they belong to. I can then extract them for upload into our system retaining the association with the parent part.
Thanks
how to write VBA to extract data from .msg files and import that data into a table in my database.I have over 5000 .msg files of which I need to extract the following information:
First name: Tim
Surname: O'Rourke
Street & Nr: 399 Upper Eastern Green Lane
City: Coventry
[code]...
The field headings are all in black bold and the required data come after the colon, I have name the fields in my database exactly the same but using the normal naming conventions:
strFirstName
strSurname
strStreetNr
strCity
[code]...
Any way to extract a postcode when there are double space on some and not others.
SA1 1AB (i need SA1 1AB)
HU1 2AB (i need HU1 2AB)
HU17 0BG (ok)
For some reason the postcode field returns 2 spaces when the postcode length is only 6 digits and 3 spaces when the postcode length is only 5 digits. Im assuming that the postcode field needs to be 8 characters in length (including a space).
All I want to to remove the addition spaces and return a normal looking postcode!
I'm currently in the process of moving from Excel to Access and am setting up a user table with 5 Fields (Full Name, First, MI, Last, Alias). I would like to only transfer the Full name and have the other field be calculated fields, so they automatically fill in. I was able to get the "Last" field to work, but the others are being tricky. Especially the "Alias" field which would need to take the first letter of the first name, MI letter and the entire last name, like (JPDoe).
The current format of my "Full Name" field looks like, (Doe, John P.) without the brackets of course. I'm new to Access, so I was trying to use the Expression Builder to do all of it, unless there is an easier way?
How I can retrieve the following information from a field
[CONTENTS]
1 x 17.7ml
2 x 17ml
1 x 181.1ml
I need
17.7
17
181.1
I was using a combination of CAST(LEFT(RIGHT...)) AS Float
but that obviously won't work due to too many variations...
Hello,
I manage an access MDB, which contains two tables.
In one of the tables (Table A), I would like to insert a "Default Value" that will be extracted from the other table (Table B), everytime I add a new row.
For instance, the value I would like to extract from Table B is "900".
When I add a new row in Table A, I would like it to use "900" as the "Default Value".
But tomorrow, for example, the value in Table B will be "910", and so when I add new rows in Table A, I would like them to be "910", but at the same time I don't want the rows that I added a day before to change (from "900" to "910").
I tried going to the "Design View" and under the "General" tab, use the "Default Value" field by using an SQL query, but that didn't work (or maybe I just did something wrong).
How can I do this?
Thank you!
Hi,
There is a requirement for
1)Populating a datasheet in a subform with a querystring which is dynamically built in VBA.This querystring is constructed based on the search criteria fields selected by the user in the main form.How can this be immplemented?
2_To click/double click a record in the datasheet,extract data and populate textfields,comboboxes with it which are in the main form.How is this achieved?Also,I can't find click events in a datasheet.
Pls let me know.
Thanks,
Savita
How can I create a field in a query, extracting the date portion of a date/time field? The format is mm/dd/yyyy hh:mm:ss. I need just the mm/dd/yyyy so I can prompt the user for a date range, without them having to key in the date mm/dd/yyyy 00:00:00 to mm/dd/yyyy 23:59:59. Please Help
View 3 Replies View RelatedHow do I select the first column of a multi-column list box (called "List1") for a query.
A single column list box works fine.
Code:
SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies View RelatedHi all,
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
How do I do this?
Kind Regards,
Matthew
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code:
Private Sub Command11_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
i have a columns as 1. contactname, 2. firstname 3. lastname 4. email and in this columns some emails are not matching with the contactname or some time firstname or some time lastname so i need the to find out the un matched contacts from the database.
View 1 Replies View Relatedhi,
im wanting to reference a value of a dropdown which is already serving a function - the dopdown already has it's bound set to 3 but i want this new function to refercne the second column-
im tyring:
"Tutor = '" & Me.ComboStaff.Value(2) & "'"
which isn't working- how do i reference the second column of a dropdown if its bound to its third?
cheers
dubs
Hi. I have a question I'm hoping someone can help me with. I would like to take data from multiple columns and put the data into one column. Additionally, I do not want to exclude any data (union all) and I would like to group the resulting union by another field. For example:
Original data layout:
Column Headings: Sample Event, Depth 1, Depth 2, Depth 3,
1st Row Data: 1, 6, 9, 12, 9
2nd Row Data: 2, 7, 9, 8, 3
Desired data layout:
Column Headings: Sample Event, Depths
1, 6
1, 9
1,12
1, 9
2, 7
2, 9
2, 8
2, 3
So far I'm using the following SQL. What do I need to add or change to get my desired result of grouping the unioned depths by the 'sample event' field?
I appreciate any help anyone may have to offer. Thank you.
SELECT Depth1 AS Depths
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth2
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth3
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth4
FROM Depth_Velocity_Substrate_Correct
Union all
SELECT Depth5
FROM Depth_Velocity_Substrate_Correct
I want to convert a number column into a text column.
Number Column is called Customer.
Below I can't match to the other table containing the text column Customer No.
How can I convert columns in vba?
Code:
strSQL = "SELECT CALCULATED_Forecast_TNS.*, USEFUL.TRP_in_EUR " & _
" FROM USEFUL INNER JOIN CALCULATED_Forecast_TNS ON (CALCULATED_Forecast_TNS.[Reference No] = USEFUL.[Reference No]) AND (USEFUL.[Customer] = CALCULATED_Forecast_TNS.[Customer NO])"
Set qdf = CurrentDb.CreateQueryDef("REPLACABLES", strSQL)
DoCmd.RunSQL "UPDATE REPLACABLES SET CALCULATED_Forecast_TNS.TRP_in_EUR = USEFUL.TRP_in_EUR"
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
View 3 Replies View RelatedIn my table for duplicate "line no" I have different "contractor" like below.
LINE NO CONTRACTOR
L-0001 C-1000
L-0001 C-2000
L-0003 C-6000
L-0003 C-8000
L-0003 C-9000
L-0004 C-5000
Now I would like to make a query for transposing values like below:
LINE NO CONTRACTOR1 CONTRACTOR2 CONTRACTOR3
L-0001 C-1000 C-2000
L-0003 C-6000 C-8000 C-9000
L-0004 C-5000
how I have to make this query?
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name
1 xx yy KPP USA John
2 zz kk KPP USA John
3 ss ff TLL USA John
4 ww qq PPO Italy Marco
5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT
COUNT(DISTINCT department) AS NumberOfDifferentDepartments
FROM people
GROUP BY name;
But Access says me there is a syntax error.
I'm working with MS Access 2002.
I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?
View 1 Replies View RelatedPlease would you be able to advise me how I would copy data from a column in one table to a column another table.
But I need to only use table not a query.
Thank you in advance for your help.