Hi
Is there anyone who knows how to help me with this matter.
I have a form that I can search for first and last name in.
But when I try to do the same with the field for Social sequrity number I don't get it to work.
Can anyone have a look at this attached zip database and help me?
Sorry for the social sequrity number beeing named "personnummer" (it's in swedish.
//Mikael
How to use like to search for items containing filed
I have a table as such and want to have a select statement look for instances where the RequirementName has Evaluation>Child this is used as the row source for a combo box.
Table Name: Requirements
Column Name: RequirementName
Observation:
Evaluation:
Evaluation>Child
Evaluation>Child>Site
Evaluation>Child>Semester
Evaluation>Child>Supervisor
Evaluation>Child>Certification Number
Evaluation>Child>Language
Evaluation>Child>Speech Disorders
Evaluation>Child>Speech Disorders>Articulation
Evaluation>Child>Speech Disorders>Voice
Evaluation>Child>Speech Disorders>Fluency
Evaluation>Child>Speech Disorders>Dysphagia
Evaluation>Child>Related Disorders
Evaluation>Child>Audiology
Evaluation>Child>Total
Treatment:
Treatment>Child
Treatment>Child>Site
Treatment>Child>Semester
Treatment>Child>Supervisor
Treatment>Child>Certification Number
Treatment>Child>Language
Treatment>Child>Speech Disorders
Treatment>Child>Speech Disorders>Articulation
Treatment>Child>Speech Disorders>Voice
Treatment>Child>Speech Disorders>Fluency
Treatment>Child>Speech Disorders>Dysphagia
Treatment>Child>Related Disorders
Treatment>Child>Audiology
Treatment>Child>Total
Evaluation>Adult
Evaluation>Adult>Site
Evaluation>Adult>Semester
Evaluation>Adult>Supervisor
Evaluation>Adult>Certification Number
Evaluation>Adult>Language
Evaluation>Adult>Speech Disorders
Evaluation>Adult>Speech Disorders>Articulation
Evaluation>Adult>Speech Disorders>Voice
Evaluation>Adult>Speech Disorders>Fluency
Evaluation>Adult>Speech Disorders>Dysphagia
Evaluation>Adult>Related Disorders
Evaluation>Adult>Audiology
Evaluation>Adult>Total
Treatment>Adult
Treatment>Adult>Site
Treatment>Adult>Semester
Treatment>Adult>Supervisor
Treatment>Adult>Certification Number
Treatment>Adult>Language
Treatment>Adult>Speech Disorders
Treatment>Adult>Speech Disorders>Articulation
Treatment>Adult>Speech Disorders>Voice
Treatment>Adult>Speech Disorders>Fluency
Treatment>Adult>Speech Disorders>Dysphagia
Treatment>Adult>Related Disorders
Treatment>Adult>Audiology
Treatment>Adult>Total
Observation:
Evaluation:
Evaluation>Child
Evaluation>Child>Site
Evaluation>Child>Semester
Evaluation>Child>Supervisor
Evaluation>Child>Certification Number
Evaluation>Child>Language
Evaluation>Child>Speech Disorders
Evaluation>Child>Speech Disorders>Articulation
Evaluation>Child>Speech Disorders>Voice
Evaluation>Child>Speech Disorders>Fluency
Evaluation>Child>Speech Disorders>Dysphagia
Evaluation>Child>Related Disorders
Evaluation>Child>Audiology
Evaluation>Child>Total
Here is what I have so far and it does not seem to return anything.... this line is where i haveing trouble "AND RequirementName LIKE '%" & subcategory1.value & "%' "
Private Sub Subcategory1_AfterUpdate()
Dim sRequirementName As String
sRequirementName = "SELECT Requirements.RequirementName " & _
"FROM Requirements " & _
"WHERE Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' " & _
"AND RequirementName LIKE '%" & subcategory1.value & "%' "
Me.RequirementName1.RowSource = sRequirementName
Me.RequirementName1.Requery
End Sub
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
I have an unbound combo box that looks up a list of numbers through a query but on the drop down list it does not go all the way down to the bottom of the numbers. For instance the combo box will only go down to the number 27453 but I know there are more numbers than that in the table. I can even type in a number and get the info but it still doesn't show up in the list...
View 1 Replies View RelatedIt wont populate the pertinent fields.. where have I gone wrong. DB attached.
View 8 Replies View RelatedI'm working in Access 2010. I have a simple form that is bound to table "Jobs" in which the key field is job number.
I want to create a search drop down. I need it to display both the job number and the customer name; and I would like the user to be able to start typing the customer name in the dropdown and for the drop down to pull it up as I'm typing it.
I create a simple query for the row source of my drop down:
SELECT Jobs.Job_Number, Customers.Customer_Name
FROM Jobs LEFT JOIN Customers ON Jobs.Job_Number = Customers.Job_Number;
When I run the query, I get both columns. I set the bound column to 1.
Column count = 2.
Column widths =1";1"
Go to run it and it displays only the job number in the drop down after I select. In addition, it does not filter at all.
I'm relatively new to Access 2010. I have an excel spreadsheet that I've converted into an access table for the purposes of creating a user friendly form for the end user to search for relevant criteria to retrieve a list of records that fit the requests. There are two main search functions: unbound keyword search (90+ keywords), and either a series of checkboxes or one list box that lists specific countries/regions matched in a column (columns, as I will get to shortly). The region selection is bound by a selection of 15 countries.
The key is each record can correspond to more than one country. This, one record can have "Belgium, Germany, brazil" and the next record can just have "brazil". Thus if someone selects brazil, the query must retrieve the Belgium Germany brazil record in addition to the record that covers just brazil. I've tried to do this with a concatenated column that list all countries and a text box search, I've tried to do this with individual checkbox columns for each country with checkboxes in the search form...and I can't get it to work. I don't fully understand the criteria to set for checkboxes or list boxes. What would be the best way to build this form, an how?
I have added a combobox to one of the forms in my customer database using the "find a record in my form based on a value in my combobox" section of the combobox wizard.I have a number of columns in the combobox (the primary key is the bound value, and I have unbound columns for first name, surname etc).
I sorted the records in the combobox by column 3 (surname) so I can scroll through the list and choose surname instead of having to memorise customer numbers. Now I have more than 1500 records (with more on the way), scrolling through takes ages, and I was wondering if there is a way to adapt the combobox so I can (for example) type the first letter of a surname into the box and be taken to surnames beginning with that letter, whilst retaining the primary key as my bound column?
My initial thought was to base the combobox values on a query, and make the query prompt for a starting letter (or string of letters) each time it runs, so it only displays the query results in the list, but I was wondering if there is a neater way to do it?
I have a form with a textbox which when users enter a unique number(barcode) I want to run a query which pics up the barcode number, checks against the product id and fills the subform with the name of the product and price.
The basic details of the product table is like:
product id (Autonumber)
prdoduct name (text)
price
barcodeId (number)
The subform where i want the result to go is the order details fields product name and price.
I am trying to figure out how to do this but my mind keeps going blank, its been a long while since i used ms access.
have set up a navigation form which contains 5 unbound subforms that are made visible or not visible thru the on click event of command buttons on the main form, the subforms have a command button with an embedded macro that searches for a record based on what has been keyed to an unbound text box on the subform, this works fine when the sub forms are opened independently of the navigation form but when the search is done from within the main form, the error message is, "....cannot find the referenced from frmAQIFAList" subform, I tried referencing the main form name infront of the subform name but this also doesn't work, perhaps this would be better with code ? or is it because the subforms are unbound ?
View 4 Replies View RelatedI have two unbound text boxes and a search button adjacent each that allows me to search for a user via two methods:
1) Payroll ID
2) Surname
They have the following code:
txtPayrollIDSearch
Code:
Private Sub txtPayrollIDSearch_AfterUpdate()
Dim sWHERE As String
sWHERE = "[PayrollID] = " & Me.txtPayrollIDSearch
sWHERE = "[PayrollID] = '" & Me.txtPayrollIDSearch & "'"
[code]...
Then minor differences between the above and the Surname search.I have two questions:
1) How do I make the search more friendly by allowing it to find partial matches, i.e., a user has a surname of 'Smith' but I want to search for 'Smi'?
2) How can I display further error messages if there aren't any matches?
I am creating an access database to store customer details as well as quote information. I have created a form to input quote details that our office can complete when a customer calls to complete a quote. I need to create a form that opens as a new form every time we click on the form to input a new quotation. How do I do this? At the moment when I click on the form, the form opens but the details of the last quotation are stored on the open form.
I have no training in access at all and am self taught so far so by no means an expert.I also want to create a form that allows you to search for particular quote numbers, so if someone calls and asks about a particular quote number we click on a page that says 'search quotes', input the quote number and the form opens with all the details of that quote.
Hello there.
I Have a Forename & Surname field.
I want to be able to split the the Forename field into Forename & Surname.
Any ideas.
I am tring to seperate a field using a query that has data like this:
02 04 08 18 20 25 31 32 35 39 41 45.... (10 sets of numbers)
into there own fields...
I cant seem to find something along those lines!
i have a form "send mails" which is unbound
it have a combobox field "to" ,i want it to bound to a field "names" in the table "email"
I have a table Client-Details,in which i have a record namely "bharti-chennai".this table is associated with aother table "release-details". bharti-chennai has records in relase details.i want to change the name of bharti-chennai to bharti -mumbai ..how to do it.
View 2 Replies View Relatedin the excel there is just 255 length how and where I can fix it?
View 1 Replies View RelatedHi im having issues runing a query on a short date field in my works database calls [Date Logged] I am actually running the query from PHP/ODBC but have tryed running it in acess also..
I am using this query:
SELECT * FROM Maintenance_Dbf
WHERE [Date Logged] = #$date#
ORDER BY Maintenance_ID ASC
Where $date is i have replaced with all of the following date types..
2007-05-21
2007-21-05
21-05-2007
05-21-2007
07-05-21
07-05-21
21-05-07
05-21-07
2007/05/21
2007/21/05
21/05/2007
05/21/2007
07/05/21
07/05/21
21/05/07
05/21/07
yet i cant get any results returned..
Here is an example date from RAW tables 26/04/2007 12:25:33
Any idea's? even the query builder in access doesn't pull up any records :S
Regards
Liam
I need a litle help to solve a syntax qustion:)
This is my query:
Forfall: Date()+([svarfrist].[svarfrist])
The case is to use this day' date and add the number in the table "svarfrist". The point is that the user are able to change the nomber of pluss days from time to time.
This qery running, but it only give bad number of days...
Hope somone have the time to give med ab advice.
Morten
Another question on how might be the best way to do things with dates ?
I have a simple database which is a fault database.
I get an email request to investigate something and I log it and give it a ticket number. If I email back , I put the comments that I sent in the email in the update field on the database.
When I create the ticket its in an open state with a date and time stamp of now()
When I close (drop down box with open and close as options) the ticket it automatically updates the time in the closed filed to Now()
What I want to do is when I updated my comments field , this sets a date value in a hidden field on the form of Now()
So then I need an expression that looks at the difference between the updated time and the now time
i.e. I update a call on 29/7/2006 at 10:00 am
Its now 30/7/2006 at 10:01
i.e 24hrs and 1 minute later
As its more than 24hrs this flags and output to a filed e.g. 1
I then total all the "1" and therefore I have a ticket count of how many calls have not been updated in 24hrs.
So Im assuming I will need an after update event on my comments field put today's date in this hidden field ,and then maybe some sort of code which is maybe on my switchboard which looks at the difference of these dates and if greater than (or equal too) 24hrs it will output a value of 1.
And If I can do this I can also have a flag if its more than 2 days , then clear the 24hr flag an increment a 24hr flag etc.
My tickets should all be closed with 24 hrs , so this is something to capture anything that is open and hasn't been updated in 24hrs
Id appreciate some guidance on the best way to do this ?
Cheers
Jimmy
How do I get access to auto fill a text (supplier)? I realize that for a new supplier I would have to input the supplier name, however my question is what should I do for Access to enable auto-fill so that the next time I have to input that supplier it would auto fill for me. This is happening in a single column.
For example: In the supplier column, I typed Dell in the first row, Apple in the second row, HP in the third row. Now when I start typing 'Apple' in the fourth row, I want it to autofill 'Apple' because I have already input that once.
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies View RelatedI have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1
1 2
1 3
2 1
2 2
2 3
2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
Example
1 1
2 2
3 3
2 1
2 2
2 3
To make it simple, I have a list of contact names and their email. I want to have a form with no control source and have 2 simple drop down boxes for their Name and their Email. What I would like to happen is the user choose a name from the drop down in field 1 and then the field below auto-populate their email in field 2. I understand I can use conditional IF/THEN and list out each email, but the contact list is ever changing, so I want to first drop down to link to the TblContacts, have the user pick the name from the list and then have the 2nd field autopopulate from that same TblContacts with their email respective to the Name entered in field 1. My form has two field [Traveler] and [Email]...The tblContacts two fields are [Name] and [Email]
Code:
DLookup("[Email]", "tblContact", "[Name] = [frmEmail]![Traveler]")
I am running in to a brick wall with this. I have an unbound text box with the control source set to =IIF([text42]=0,0,[text42]/[text44])*100 and in continues to return a #name? error.
I am not sure how to get this expression to work. I have even tried to put =[text42]/[text44] and I still get the #name? error.
I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zeros and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zeros preceding it identify that person.
For example Joe would be identified below because he had 6 consecutive zeros and then he had 5 weeks of numbers immediately following the string of zeros. Bob would not be identified because he had 5 consecutive zeros and then 5 sets of numbers immediately following the string of zeros so the zero frequency isnt higher than the number frequency immediately following.
ID
Name
Date of Hire
1
2
3
4
5
[code]...