After spending the last few hours searching the forum and net for an answer I have finally conceded again that I need advice
I have a field in a table called DOB (date of birth) this is currently set as a Date / Time field
It did orignally have an input mask for short date i.e 11/11/2011 but i have removed this for now.
On my form (Patients) there is also some code in the after update properties box of DOB to calculate a persons age which is entered into another text field on the form.
What I need is a customised message box if the user enters the date incorrectly i.e 11/11/11 instead of 11/11/2011 alternativley is there a way to change what the user puts in i.e 11/11/11 to 11/11/2011 automatically after udating but keeping the code to calculate the age?
how to change Access default error messages with custom messages? Like, if I enter some data in a sub form where the parent form is empty new record, default error message says that 'You must enter a value in the 'column name' field'.
I want to disable this default message and display my own error message. Now I know MessageBox and MsgBox fuctions to display custom messages, but how to disable the default message.
I need to change a fields properties on a form depending on another fields data.
i.e. If a user ticks a Yes/No box saying they've done something they must also complete the date they did it. I can't set the date field in the table to be 'Required' as if ther is no tick then it doesn't need to be.
I have a problem with a single query I have build in Access 2000. Basically, I have a normalised database structure to 3NF. I have a table called Data, a table called Reports and a table called DataReports which cotains the primary keys from both data and reports which are named Data_ID and Report_Id. Basically, what is happening is I am building a query that returns a record from DataReports using the Data_Id field as criteria as this is held on a form. I create a one to many relationship from the Reports table to the DataReports table using a link from one Report_Id to the other. I save the table and it works fine...once only!! The query is used to feed a list box on a form. The form loads correctly but when I go back into design view to make a change to something else on the form and then go back to form view, I get Data Type Mismatch error. When I look at the query, Access has changed the link from the Report_Id --> Report_Id to Report_Id --> Report_Title which is the next field down in the Reports table. Because all ID's in my database are Autonum's it is trying to link a number to text so I understand why this would not work but I dont understand why on earth access is changing my links. It is very frustrating. Could anyone help me please.
I have a form with names of employees, and different skills that employee has.
It is listed in a form as columns with the employee name in rows, and columns for the skills. The form is in fact really great, but I see that the solution might bring problems in the future because of maintainance issues, and field limitations. I add fields frequently, and the table is growing without me beeing able to control it.
I have organized this in only one table, and used the name of the skill as a field. There is many other tables in the DB, but none of them covers this area.
With this solution I get the skillname as a heading, and the name as a column with lots of yes/no values. I love the filter functions, and the form is quite nice...
exampleform:
Employee baking cooking driving juggling employeeeID1 yes no no yes employeeeID2 no yes yes yes employeeeID3 no no yes yes employeeeID4 yes yes yes no
and so on.
Is there a way to organize this so that the skill name comes in the top row, the results get listed as shown above when the skill is placed as a field value, not a field itself?
I have looked for a solution with many-to-many relations with 3 tables, but cant seem to make it work right. It is especially the form I have trouble creating with the new design. I have also tried to make crosstab queries with minor success.
Does anyone have a suggestion on how I can make the relations work. One employee can possess a great number of different skills, and the skill can be possessed by a great number of employees. This is not a difficult issue by itself, the problem is to make the form as shown over.
I need a little help on a little situation. Basically, my client for my school project wants to be able to see graphically if orders sent to their bookstore have been processed or not. So i suggested a change of cololour of the fields.
Now I have created a Form containing the orders as they are stored in a list box, (both already processed and unprocessed orders), now I want to be able to allow each item in the list box to be either Red (if unprocessed) or green (if processed) so the user can see which ones they need to deal with. Im guessing this would take some VB, but if anyone could help me, I would really appreciate it :).
PS, if you do provide VB could you please explain a little of whats going on as i have to annotate all the code I use and im not too profficient in VB
I've asked this question in different forums before, and though some people say it can be done, I have not found anyone who is successful yet.
I have a report that puts down a person's address, however the address information is stored within the database in separate fields ('Address', 'City', 'State', 'ZIP'). The issue I have is with the City/State/ZIP. Some city names are longer than others, is it possible to have the fields adapt to the different word lengths.
Say the address is, Albany, NY 10023. The database would output this alright, but then if the city name is San Francisco, CA 94143, the fields are in disarray.
I have the same issue with names. Last name and first name are stored separately. I would like to display Last, First. My workaround has been to right-align the Last name, and put it adjacent to the left-aligned first name field. This works unless the person's last name is very long, disporportionately from the first name, in which case, it looks very much off center in the overall report.
I have an existing report with a number of fields arranged horizontally in a tabular fashion in the detail section. The first field is an identifying line of text and the remaining fields are numerical calculations. The report is basically a simple profitability estimate report for a number of different products.
Behind the fields of data is another text box that is blank, which stretches across all the horizontal fields, and which changes background color using conditional formatting. When printing, this colors the whole row according to that criteria (it changes color based on the relative profitability of each product in the report).
The problem I'm having is that the first line of identifying text can sometimes overflow.
I've set the Can Grow property to Yes for this and all other controls in the Detail section. But the result is sometimes some funny-looking formatting. The identifier (like "Product XYZ with a really long description") will word-wrap, but every other field in that row maintains their own height. In particular, this makes the coloring of the conditionally formatted row look off, because it provides only one line's worth of color, even though the product description has wrapped to two lines.
How I can resize all the controls in the Detail section based on the height of the tallest control, and keep the whole thing dynamic?
Things I've tried: I've tried putting some VBA in either the OnFormat or the OnPrint events that iterates through the controls in the section and resets their heights. In the "OnFormat" event, nothing happens (no errors and no apparent changes in the appearance of the report). In the "OnPrint" event, I get an error that says the height property cannot be changed during a print or print preview.
The VBA I tried using was as follows:
Code: Dim ctlIt As Control Dim lngMaxHeight As Long For Each ctlIt In Me.Controls If ctlIt.ControlType = acTextBox Then
Hi, Probably this question has been asked before, but I'm new here. Don't know if the question should be asked here and hope that my English is good enough.
I have a database in which I change the content of the records on a regular base. There are two dates in it. One is the day I made/added a new record, one should be the date I made the last change, but I can't get it to work. I have been searching the Help-option in Access as well as Google to find an answer, but so far no results. I don't know anything about Visual Basic, so if you answer please keep it simple.:o
I have this code that a member helped me create in one of my forms. However, the date that is being input into the table is 30 Dec 99. It won't change no matter what the code is changed to.
The date is showing up correctly in the subform, it is just not inputting right into the table.
Private Sub Form_Open(Cancel As Integer) Dim intNQ intNQ = Nz(DMax("QuizNo", "tblQuizzes", "logon='" & Environ("Username") & "'"), 0) + 1
I have searched and found references to converting text to dates. I need to go the other way. I have client# field and a date field. I would like to make an update query to populate a new field by concatenating the two fields minus the "/". I am making a new key field in an existing table. Then in the future when data entry is done, I would like to convert the date and client# as they are entered. I was looking at using the left$ or right$ function, but the dates may be in mm/dd/yyyy format or m/dd/yyyy format or m/d/yyyy and so on, depending on the month and day. Is there an easy way to convert the date to a number? I tried datevalue, but it leaves the "/" in there. I'm not sure how to section off the parts of the date I need, when the month and day vary between 1 and 2 digits. Thanks for any help.
I have a table populated with several thousand records-one field is a date field in the format of 5/5/2006. I have to change the data so that it is 5/05/2006 in order to import it into a SQL table.
I'm sure there has to be an easy way to do it, but I can't come up with it. Changing the imput mask on the field affects future data input but does not change what is currently there.
I have this problem, I have a field that updates by using the now() function. The problem is when I run a date query I have a problem due to the long date format. Is there a way to change all the records with a Long date format to a short date one?
Here is an example of what im working with: Long Date: Januar 1, 2007 2:00 PM Short Date: January 1, 2007
When I run a query I only get the records with the short date even though I have records with the Long Date format don't show up.
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I am working on a french version of Access but in my english forms, I need the current date field to be written in english - is there a way/expression to change the language of the date field on a form (from french to english)?
Is it possible to change the format of a date picker, for instance once i have selected a date, its is visible as 09/11/2006, can this be edited to just the month and the year 11/2006.
I have in a cell in Excel: MM/DD/YYYY...I want to add it to my Access database as: YYYY/MM/DD...This is what I have so far in Excel VBA. It is giving me a date error. I have tried both as MM/DD/YYYY still did not work.
Code: n=1 accDateSub="01/01/2011" Dim conn As New ADODB.Connection conn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:pathfile1.accdb;" thisSQL = "INSERT INTO [Table1] VALUES (" & n & ", #" & Format(accDateSub, "yyyy/mm/dd") & "#);" conn.Execute CommandText:=thisSQL
I need help with an update query that will change records for me based on the date
In the record I have a TEXT record called [GI1] that can have a multitude of data in it. The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”
What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }
Hi, I'm using a web based form to store dates (and other records) in an access database. I use the dd/mm/yyyy date format (europe. go figure) Anyway, the problem is that when submitting an ambiguous date, as in 11/01/2004, access sees it as 01-NOV-2004 rather then 11-JAN-2004, as it should.
Why does this happen, and how can I fix it. I have no problem doing the fixing in the access file itself or in the SQL command that populates it- whatever works best.
My student is doing a project on question paper generation in which he selects questions from each lesson needed and also some of these questions have images associated with them. So, if these questions are selected, the image control in the report displays it.
If there is no image, the space for the control is left in tact. I tried changing the visible property for the image control in the Detail_Format event procedure. But this code is not reached at all and the space is still left. I am not storing the image directly in the field but its path in a text field.
I want that the space occupied by the image control removed. I tried the suppression thing but obviously it did not work.
I have a date/time field on a form that has the default set to "=now()" which puts in the system date and time. I need to change it so that the date changes to the next day any time after 11:00 PM (instead of midnight). This is because our third shift starts their day at 11:00. If they start entering data right away, alot of their work shows up on the wrong (to them anyway) workday date. I'm struggling with the expression builder to get this to work.
One way is changing the time to be an hour ahead but that will affect the entire day when I just want to change the date for one hour per day.
How to get this expression to work? It works by changing the numbers to correct date format, however, if the field is null, I want a blank to appear instead of "type"
