I have one field with complete url´s.
Example
http://www.rocketspanish.com/links_spanish_immersion.php
or
http://apps5.oingo.com/apps/domainpark/results.cgi?q=naco&cid=ca-dp-260com_nopop&url=http%3A%2F%2Fnaco.com%2F&hl=en&ac=s
I like to make a groupby but only from the left side up to ".com"
Is there a way to select only the text untill ".com"
Thanks
Wouter
I know how to Grab the Right and left Characters and grab the mid characters from a field value
ex: Right([fieldName],4)
My question is how to I grab characters based on a space
Say for instance "Fred Smith" or a name field. There are going to be varing lengths of last names...but the format is the same...First Name, Space, Last Name....
QUESTIONS:
How do I search the string, count to the space, then take all characters after the Space?
One step further how would I grab the middle name in this example?
"Fred M Smith"
If I have three fields
Surname - text
Given name - text
Dateofbirth - dd/mm/yyyy
how can I generate a field consisting of
surname(then)first letter of given name (then) year of birth
(ie SMITHK66)
Thanks in advance
I have text boxes on a Form that I need to update with figures each week.
I have tried to have the entire field selected by right clicking with the mouse but I keep getting an error.
The coding I am using in the 'OnClick event' is:
Private Sub Ctl1_Click()
Me!R1.SelStart = 0
Me!R1.SelLength = Len(Me!R1)
End Sub
R1 being the txtName.
The errors I have received are Object doesn't support this property or method.
I have tried to replace the Ctl1 with R1 but still get errors.
Can anyone please shed some light on what I am doing wrong.
I am using Access 2002.
I have a database spread accross a number of tables, I'll simplify it a bit here - there's questions, answers, respondents each as seperate tables.
Respondents includes
RID
Name
PostCode
Questions
QID
Question
Answers
AID
QID
RID
Answer
The answer table contains answers to all the different questions.
How can I query this to compare questions ? grouping answers to spot trends in the data
So I'd want to pull out the answers for questions 1 and 2 showing someone that answered question 1 with Yes and question 2 with No.
Any help would be much appreciated, or even a point in the right direction.
I've played with Crosstabs, multiple queries and pivot tables - none seem to be able to do this.
Thanks :)
I have a form with bound textboxes used for entering currency values. When the form loads the fields are automatically populated with default values. When the user clicks on one of the fields, I would like to automatically select all of the data in the field, so as new values are entered, the defualt value is overwritten. I imagine that I should use the OnFocus property, but is there a command to highlight the data?
-Don
Hi All,
I am not sure if this is an easy one or not..
I have a field in a table named Stock_Alias. This field was originally set as a Number field. I have a combo box on a form, which contains the Stock_Alias numbers. When a number is selected, the remaining fields are shown in text boxes.
I have had to change the Stock_Alias field to a memo, to incorporate Numbers & Characters.
I cannot seem to hit on the right code to get the same results as i did when the field was a number field..
This was the code I was using to select the field as a number...
Private Sub Cmbo_Stock_Alias_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Stock_Alias] = " & Str(Me![Cmbo_Stock_Alias])
Me.Bookmark = rs.Bookmark
End Sub
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));
Two tables, Contacts and Jobs
On the Jobs I need to select the ID and show the Last Name, but only enter the ID. This has been done okay.
How do I get the Last Name to auto fill in from the selection of the ID number?
I am having some trouble with an iif statement as selecting the field..I have two columns, one called [sdCounterpartyprice] and one called [bid]I want to select the [bid] if it's not equal to Null or 0, if it is equal to that then pick [sdCounterpartyprice]this doesn't work
Code:
CptyPrice: IIf ([bid] is null or = 0; [sdCounterpartyprice]; [bid])
I trying to create a query that looks at 2 field in one table and give me the resolves in a new field.
Example
Field 1 Field 2 New field
Hat Cap Cap
Dress Dress
Pants Slacks Slacks
Gloves Gloves
Socks Socks
Can you help?
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
I am working on a school database, today I am working for fee collection form and I am facing trouble.In attachment At the top right area the hidden field I would like here changes according to selecting Fee Type under Fee Detail. I want to achieve this,
If feetype is ABF then Hidden field = 1500110
If feetype is ASF then Hidden field = 1500120
If feetype is EXF then Hidden field = 1500130
If feetype is MTF then Hidden field = 1500140
If feetype is RBF then Hidden field = 1500150
How I can perform this ?
Table structure is
(tblstudents) Contain students data
(tblFee) Contain, Student Id linked with student table, month, year, amount, date, narratives and FeeType that looup values from FeeType tbl, this hidden field also in tbl fee.
The codes that are 1500100,1500120,1500130
These are necessary for GL entries that's why I must have it on my form. I had as well apply method of Default value but in that case I only present only a single value, but I want that in hidden field values take changes according to the fee type.
I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example
SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);
Can I not select a value that has been calculated this query?
I am trying to create a query in Access 2010. I have 2 tables BU and Color. BU has employee ID, the BU the employee is assigned as of a date. The Color table has the employee ID, a color and a date. Based on the date in the Color table, I want to select the BU from the BU table. The query should bring in the BU that the employee was assigned to as of the date in the Color table.
For example, if emplid 12345 was hired into BU abc as of 12/31/2013. Emplid 12345 was transferred to BU xyz on 1/15/2015. In the Color there are 2 rows for 12345. One with a date of 12/31/2014 and one with a date of 2/15/2015. I want the query to return abc on the row with the 12/31/2014 date and xyz on the row that contains 2/15/2015.
Attached are the two tables. I have highlighted the expected results in yellow on the Color spreadsheet.
Hello, I'm working with SQL and databases in general for the first time, and was wondering: how would I select just the most recent entry for each device? my data looks [roughly] like this:
device1 data_1a 15.2.2000
device1 data_1b 15.2.2001
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3a 15.2.2001
device3 data_3b 15.2.2002
So what I'm looking for is:
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3b 15.2.2002
Thanks for any help you can offer!
-Eric
I have a field on frmOutpatient which is called EpisodeID.
I need to populate this field from a selection in a pop-up form.
The pop-up form (frmSelect) is a continuous form with a number of different EpisodeID numbers according to the date the patient was admitted. At the end of each record I want to put a command button (cmdSelect) and when pushed I want this SelectEpisodeID to be transferred to the field on the frmOutpatient.
Any ideas??
I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"
I want to run a query against the table to select records for any one country, but not sure how to do this.
Any suggestions please?
Trying to update a field in a record in my form after selecting from a comboox.
This an orders form with a record per row for order items. I select the product from a dropdown list which is populated by a dynamic query from the afterupdate event on another combo. When I do the selection access reports the following error:
Runtime error 3331
to make changes to this field, first save the record
Debug takes me to the line :
Me!product_id = DLookup("product_id", "products_table", myvar)
from :
[code]
Private Sub comboProd_description_AfterUpdate()
Dim strFilter As Integer
comboProd_description.Value = comboProd_description.Column(1)
myvar = comboProd_description.Column(0)
Me!product_id = DLookup("product_id", "products_table", myvar)
[end code]
I'm lost as to what to do (no such thing as beginners luck!!). I'm not even sure if the error is from the combo box or from the field that it is trying to update (product_id)
Thx for looking
Kev.
i have 2 tables jobs and jobs_details jobs have the fields name date account etc.
jobs_details have the print area, file, etc.
i need a querie to give me the total print area for each account on a particular date but i can't get it to work.
I've tryed this:
"SELECT DISTINCT jobs.Account, Sum(jobs_details.PlottedArea) AS SomaDePlottedArea, Sum(jobs_details.UsedArea) AS SomaDeUsedArea, jobs.JobDate
FROM jobs LEFT JOIN jobs_details ON jobs.JobID = jobs_details.JobID
GROUP BY jobs.Account, jobs.JobDate
HAVING (((jobs.JobDate) Between #3/1/2005# And #4/1/2005#))
ORDER BY jobs.Account;"
but it returns all the jobs.Account(it doesn't give the distinct) by all the dates
what am i doing wrong???
Hi there
I've got the following query in my application:
SQL = "INSERT INTO tblreport3 ( exchange, NoCustomers2, [time] )"
SQL = SQL & "SELECT Table1.Exchange1, Table1.Customers1, Table1.Duration"
SQL = SQL & "FROM Table1"
SQL = SQL & "WHERE (Table1.IncStart Between #txtdatefrom# And #txtdateto#);"
DoCmd.RunSQL (SQL)
The problemis when I run the application it gives me a error 3075 saying missing operator in query expression.
Can someone please tell me what is missing here?
Thansk
Hi
How i cun Run Queries with VB
My Querie : Delete Records in table1, It Query in access no string of VB
I want when i delete one record in table2 run this Querie
I try with this command DoCmd.OpenQuerie DelNoExistsRec.
but i hav one message : The action or method requires a Query name argement????
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency
Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency
Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1.
UPDATE Costs
SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2.
UPDATE
Costs
SET
ExchangeRate = [Exchange Rate]
FROM
[Exchange Rates 13/14]
INNER JOIN
[Exchange Rates 13/14]
ON
Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3.
SELECT [Exchange Rate]
FROM [Exchange Rates 13/14]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
Hi
Please can some help me asap. I need to do a querie which joins two tables together. I have a telemarketing table and an appointment table. The idea is that when an appointment is made telemarketing should be done afterwards to the same company. Therefore I want to view all of the telemarketing done and all of the appointments done and then match companies which have had both an appointment and telemarketing. However when i do this if there has been more appointments made for one company that there has been telemarketing the telemarketing duplicates itself so that the two have the same amount done which is not what I want??? Does this make sense?? Can some one please help????
Thanks
Hi everyone,
Now, I'm totally lost!
When I create a query with the Query Design view in Access with this SQL query:
SELECT T_Cryzout_Disponibles.LCLCL_Disponibles_Total, T_Cryzout_Disponibles.DateEmission, T_Cryzout_Disponibles.NomReceveur, T_Cryzout_Disponibles.Ordre
FROM T_Cryzout_Disponibles
WHERE (((T_Cryzout_Disponibles.LCLCL_Disponibles_Total) Like 'C*') AND ((T_Cryzout_Disponibles.DateEmission) Is Null) AND ((T_Cryzout_Disponibles.NomReceveur) Is Null Or (T_Cryzout_Disponibles.NomReceveur)=''))
ORDER BY T_Cryzout_Disponibles.Ordre;
everything is good, I get what I'm suppose to get. But When I'm doing it in VBA with this code:
RExtraction.Open "SELECT TOP " & NbrLCLCL & " LCLCL_Disponibles_Total FROM " & QuelleTable & " WHERE (((LCLCL_Disponibles_Total) like 'C*') AND ((DateEmission) Is Null) AND ((NomReceveur) Is Null)) ORDER BY Ordre", ConnectionBD, adOpenStatic, adLockOptimistic
RExtraction.MoveFirst
It gives me an error that says:
BOF or EOF is egal to True.
And I really don't know what is wrong with this. It is pretty much the same as the query I entered in the Query Design.
I'm really confuse.
Thank you in advance for your help!!!
I'm trying to construct a query that will show all fields where the date is greater than today’s date less eight days, and also pick up fields which contain no data.
The first part I have achieved with the criteria >Now()-8
But I've been unable to find the correct syntax for picking up fields which contain no data.