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?
Each each record in table1 has a unique four character (alpha-numeric) code to identify it. The first two numbers of this code represent the group it is in. (Ie. 15AB and 1502 are both grouped together) The second table stores values that apply to the entire group. I need to create a relationship between these two tables based on the first two characters in the ID field.
Things I've tried:
* Making a calculated field with left$() formula - Access doesn't allow relationships on calculated fields
* Create a new field for just the first two characters and create a data macro for after update and after insert to update that field with the expression - cannot edit the field the user is on
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002
I tried formatting the source field with Left -4, but it only shows the first four characters in the result:
4084
4084
4081
4078
4078
4078
How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3
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
hi,
i am querying a single table that contains card numbers and corresponding transaction codes (amongst other things). a single card number can have multiple transaction codes.
i want to select all card numbers that have both 0100 and 0802 transaction codes.
if i use a select query with ="0100" Or "0802"as the criteria, it returns all card numbers that have either 0100 or 0802 transaction types. if i then change the query to crosstab and group by card number, it is clear that some cards have both transaction types.
when i try to change the criteria to ="0100" And "0802" it returns nothing at all.
i'm sure the solution is something really simple - any idea what i'm doing wrong?
I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
For example:
If the record is a Non wheel repair then column 167 is a 50 character description
If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields
So I need to do the following
1. Read the first character in the line and determine if it is a number 1 and if not discard it
2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.
I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)
One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.
Here is an example of what I am trying to do:
I have a main form which has 4 combo boxes all based on tblPeople.
Combo0 is skill
Combo1 is discipline
Combo2 is crart
Comb03 is active
The sub form is a datatable list of the tblpeople.
How do I apply the filter to the datatable. I am assuming I need to build a sql statement somewhere like SELECT from tlpeople WHERE me!combo0 = [tblpeople].[skill] and me!Combo1=[tblpeople].[discipline] etc
Is there a sample database? or can somebody tell me how to go about this.
Thanks
Steve
I have a table with the field "Date". The date is in the format mm/dd/yyyy.
I would like to create a query that selects only the year of each record, and disregards the month and day.
Is this possible?
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.
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
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??
Is there a way to make just part of the text in a text box bold, or to use different font sizes in the same text box?
Something like this:
---------------------------------
Heading in bold: description in regular (not bold)
(a smaller height line used as a line space)
Another "normal" line
a larger height line
a normal line....all in one text box!
---------------------------------
One thing I'd like to be able to do is to specify the line height of a blank line in a text box. I'm using carriage returns created with: Chr(13) & Chr(10). I could see defining the font size of a hidden character, but I'd need to know how to assign a font size to a piece of the text in a font box.
Hello
I am trying to set up a user friendly front end to my database. I would like to have a form which allows the user to enter information into text boxes and then press a button which finds the relevant record(s).
I would like to use it to pin point an employee's record based on at least one piece of information, but possibly combined with up to four more by entering into these boxes. For example personnel number, surname, first name etc.
Is this possible and how do I go about setting it up?
I am aware of the filter function and queries but I can't seem to get either to operate in the way I have envisaged. They don't appear to offer the most user friendly solution, at least not in the format I have tried.
Apologies, but I am still getting to grips with access to be honest.
Table name: Items
Field: ItemName
Form name: Frmpayments
when I open the above form, it should create texts boxes on "frmpayments" based on how many data available in "Items" table under "Itemname" field. It'll be even better, if it can put those item's name in each text box it creates.
I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.
On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?
If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.
Hi.
My name is Amanda, and recently I’ve started using Access again for work. I haven’t touched the program in about five years, so I am rusty. In addition, it turns out that I am the only one in my department who’s ever even opened Access. I’ve tried hunting down a manual around here, and it seems to be an ever ending scavenger hunt. I also have not been able to find the answer to my question via web, FAQ, and experimentation, although, I feel it’s a fairly basic question.
So here it goes. I want to return all fields that contain part of a text. For example, I want all fields that contain the word “lux”, regardless of what else the field contains to be returned. So if a field contains “lux. cars” it will be returned in my query.
I’ve tried goggling “Microsoft Access” with “text formulas,” however, all I receive are excel formulas.
Thank you.
Hello,
I have a table with a field named SSN. This field contains data with the following format:
123-45-6789L
As you can see my field has a text format and I need to create a make table query that will extract all the character that are places in position 12 and 13 (simply the letters after the ssn).
I have tried with Mid([SSN],12,13) but nothing happens.
Any help? Thanks.
Hello, Bit of a newby to all this. Am trying to use a text box as a calculted field. I am doing so using an sql statment in control source The sql stat is built and tested using build query and then copied and pasted from sql view)
I keep getting #name? as an error.
I have managed using dlookup of the same saved sql to return the required value (which works) but find it to be a bit slow and clunky so wanted to give this a go instead. If somebody can have a look at the query below I would much apreciate.
=(SELECT Sum(QryInout!CostExVATGBP) AS TTlCostExVATGBP
FROM QryInout &_
WHERE (((QryInout.StockImportNoJoinToAll)=[Forms]![FrmSPurch]![ImportNoNew])) &_
GROUP BY QryInout.ProdType &_
HAVING (((QryInout.ProdType)="Freight"));)
Note that I have tried with and without the &_ and also with or with out the brackets..
Thanks
Is there a way to have a text box filled out based on the value of a field. There will be only 2 possible phrases for the text box.
=IF( <Expr> Forms![Workorder]![Disposition])=("Return As Is","DISAPPROVE","APPROVE")
This is what I have tried but I get syntax error.The form is Workorder and the field is Dispostion. When the Disposition is "Return As Is" then I want to have the text box yield "DISAPPROVE" otherwise it should be "APPROVE".
I'm trying to create a database for a construction company, as an experiment more than anything, and my problem is this:
I need to create a set of identical records in the table "buildings" based on the information entered into a text box by a user on the form for registering a new project. I want each record to be identical except for the "plot number" field, which I want to start at one and increase to the number of buildings defined in the text box.
"Projects" is one table and "Plots" is in the other.
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel.
he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order.
i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.
Dou you have any idea.
thanks in advance
I have created a text field on a form that uses the DateSerial function to return the value of last month e.g. Jul-06.
I have a table that contains months (e.g. Jul-06, Jun-06 etc) and a corresponding numeric value (e.g. 1,2,3).
I have another text box on the same form that I want to display the numeric value in based on the value that has been returned by the DateSerialfunction. What properties/control source should I set for this field? Or do I need to write some VBA?
Any help would be appreciated.
I want to print text based upon the content of the field but need more than the IIF statement. For example, if the field contains an "A" I want to print "Active", "C" I want to print "Closed", "N" I want to print "New", etc.
Thanks in advance for any help. You guys have always had an answer in the past!
Okay I have a report (rptHorneOstbergQuestionnaire) that is based on a query (qryrptHorneOstbergQuestionnaire). In the report I have a total (HOTotal) which is the result of an expression created in the qry. Based on this result I would like to generate text in a text field (HOType) that is found in the same report.
Basically if the field HOTOtal shows any vaue between:
16 and 30 then I want the unbound text field to show the text Definitely evening typw and so on ...(see case statement below)
I thought I could do a case statement on report open but I am getting an error that my expression contains no value.
Here is the case statement
Private Sub Report_Open(Cancel As Integer)
Select Case Me.HOTotal
Case 16 To 30
Me.HOType.Value = "Definitely evening type"
Case 31 To 41
Me.HOType.Value = "Moderately evening type"
Case 42 To 58
Me.HOType.Value = "Neither type"
Case 59 To 69
Me.HOType.Value = "Moderately morning type"
Case Else
Me.HOType.Value = "Definitely morning type"
End Select
End Sub
What else can I do?
I am having problem on formatting text on a report based on a Yes/No field using Conditional Rules Manager in Access 2010. When adding a rule [field name] = Yes the font color does not change. I have tried setting the expression to [field name] = True and still does not change the color. Adding a Yes or True in query works OK.
View 12 Replies View RelatedI have a table named ColorValues with the following three fields:
- ID
- MunsellCode
- RGBValue
30 fields from another table called Color_Analysis are linked to the above with one to many relationships (I've already thought of other ways to normalize data, but this is the most efficient, so no need to go into this topic).
Now, I've constructed a form for Color_Analysis and all 30 fields recording color are included as combo boxes bounded on the ID in ColorValues and displaying the MunsellCode for the user. I've also created unbounded text fields next to the combo boxes and want to use them to display the color that the user selected in the ComboBox. The question is how do I do this?
Essentially I need a piece of code that picks up the value of the combo box (this is essentially the ID in ColorValues), looks up that value in the ID column of the ColorValues table and uses the corresponding RGBValue of the same table as the .BackColor for the unbounded text field.