I am building a table where one of the fields is a yes/no field type. When the yes box is selected, I need to "jump" to a different set of field names than field names for selecting no. I have researched on internet and have done some reading but have found no information. Is it possible to do what I want to do in Access? My skill level is that I can build tables, create forms, create reports and run simple queries. Any help offered is appreciated.
View RepliesI have a database for recording jobs and invoices for our transport company. I was thinking of adding an "Attachment" type field to my invoice table to store pdf's of scanned copies of the delivery notes relating to the invoice. How this will affect the performance of the database? Will adding this field greatly increase the size of the database and slow the whole database down?
View 1 Replies View RelatedHi Everyone,
I have a PO table. Each PO has 3 documents of multiple types (one may be Word, other may be Excel, Acrobat). When I display the PO, the attachments should be shown as Icons, which when double-clicked, will open in its own Application.
I have a pop-up screen where I enter new PO details. In this screen, I have Attach/Detach buttons for each of the Documents.
My Requirement:
When I press 'ATTACH' of Doc1, the File Selection screen should be displayed from where the user will select the file to be attached. This file should be updated into the table field "doc1".
When I press 'DETACH' of Doc1, the file in table field "doc1" must be cleared.
How can I achieve this???:mad:
How to display field types of access tables in vba?
can any one show me how i can display display field types(like : phone integer ....) of
access tables in a text box using vba? I have no idea how i
can obtain that information using vba. Looking forward to your
replys.Thanks
For reasons I need not get into as it generally proves to be a distraction, I need to document the relationships in databases.
I've been playing around with .Relations with some success.
I can easily get the relationship's name, Table Name, Table Field, and ForeignTable.
Code:
For i = 0 To objDestDB.Relations.Count - 1
With rsMigrateRels
.AddNew
!RelNumber = i
!RelAttributes = objDestDB.Relations(i).Attributes
!RelFields = objDestDB.Relations(i).Fields.Count
!RelFieldsName = objDestDB.Relations(i).Fields(0).Name
!RelForeignTable = objDestDB.Relations(i).ForeignTable
!RelName = objDestDB.Relations(i).Name
!RelPartialReplica = objDestDB.Relations(i).PartialReplica
!RelProperties = objDestDB.Relations(i).Properties.Count
!RelTable = objDestDB.Relations(i).Table
.Update
End With
Next i
However, despite lots of digging into the other properties and attributes associated here, I cannot yet figure out how or where to get the:
ForeignTable field
The type of relationship (1:1,1:many,many:many)
I am needing to link two tables but one field is in a "May 2012" format while the other table is in a "053112" format.. Is there a way beyond creating a formula to change "053112" into "May 2012"??
View 2 Replies View RelatedHello everyone
I am in need of assistance with Microsoft Access. In the reporting function of Access I am trying to have a summarization total of indirect time and direct time based off a Auditor Timesheet form developed. On the report I am able to see both the direct time and the indirect time hours seperate per auditor based on the query that is run to seperate direct and indirect hours and each individual auditor stats.
but the problem I am having is trying to summarize on the report the total direct and indirect hours as a whole.
Please let me know if this a simple solution by building an expression in the field for the report or some type of VBA code input which would suffice?
Thank you
Looking for a way to use transfertext to make a table and create a spec file from csv files I am importing. I want all fields in the table to be shorttext.
Otherwise I need another way to bring the files into a table.
I have a generic spec to use that brings in any data as text.
Created the spec from a delimited file with 255 text fields... Field001, Field002, etc.
However the field names need to be changed. My field names are in the first row of a table. I will need to read each fields data in the first row and rename the field.
We have been using an Access (Access 2013) program where we link the program to dataviews created in sql server.
Recently we had to re-link the data views because a change had been made to them. When we relinked the data we noticed that the field type for the field used as the primary key changed from "Short Text" to "Long Text". The programmer who created the dataview from sql server said that nothing was done at that end that would have caused the change
I know how to prevent the problem from recurring. We are changing the sql server spec from VARCHAR(8000) to CHAR(20).
I have an Excel file (sourced externally) linked as a table. All fields are defined as text fields. In some of the Excel cells, data has somehow been entered as numeric (eg phone number). When I query the Excel file thru Access, these fields show up as errors (#num!).
I need to find a way of interogating the field, and if in error (like these ones), then ignore it, or replace with zeros etc.
I cant find a way of doing this. Help :)
Rod
Hi,
I'm a student trying to get to grips with relational databases and Access is a great tool to learn.
Does access support 0..1 and 0..many relationships? I've been playing around with Access for a while and can't seem to model the 0..1 and 0..many relationships.
Thanks for your help
Jack
Hi all :o
Just a quick stupid question, I have a Yes / No data type in my table but I want it to automatically put no unless I change it to Yes, is there an easy way to do this, probably a really simple question but am new to this and any help would be great
Thanks
Jackie
I need to display a drop down list to users.
I know that in a combo box, the user can also type text. However, I want to stop them from doing this and only be able to select values.
Is there a better control I could use? I tried the listbox but this does not seem to "drop down" the data like a combo
I'm trying to append a group of fields to a SQL table using ADO in VBA. One of the fields I'm writing back to is a datatime and in certain situations there won't be a value, however, when I try this it throws in the beginning of time 1/1/1900 or whatever it is. Any ideas?
Thanks.
Is there a way to change the data type for multiple fields at once? Such as 20 text fields to numeric in one step.
Thanks
Hi,
Strange but true.
I am trying to numeric data types on my access 2000 where I could input values in table like 0.3567 or 2.3454
Anyway any values of this kind with 4 decimal places. however somehow the values always get rounded to number + 4 zeros as of example:
if I put 0.756769 I want it to be as 0.7568 but I get 1.0000
I don't understand this. Can anyone help please?
I have not used access for ages and I seemed to be forgeting all this stuff.
Thanks
dfuas
hi everyone, i sort of run into a dead end trying to figure how to structure this. I have three tables for the 3 roles, tblClients, tblDoctors, tblBusinessContacts, each with a Primary key and some unique information about each role. I'm not sure how i can track appointments to each of these roles, i know how to do it if it was one role. I want to be able to later view all appointments for that day with all 3 of these roles.
Also, for clients i have a multiselect listbox describing their reason for the appointment and somewhow would like to associate that to the appointments. Some info i would like about these appointments:
1. date and time
2. details of contact
3. date for next appointment
What would be my best bet?
Thanks!
Joso
Hi,
I am designing a database.
It is going to store data from questionnaires.
The natural way seems to be to have a table - that stores where the data comes from, what is it about, what is the question and what is the answer, like this:
DataProviderID; FocusID; QuestionID; AnswerValue
My problem is that a Focus could be a eg. a Person, a Company or a Job - meaning that somebody may be giving info on an other person, on a Company or on a Job.
Now Persons, Companies and Jobs would have very different properties.
So how should I solve this shold I have a common table for Peoples, Companies and Jobs and use a very complicated structure of properties for them or should I store these entities in separate tables but than how will I store the results?
Thanks for your thoughts.
SWK
Hi all!
I'm experiencing an apparently simple problem, but it's taking me too long to solve :confused:
Two tables with a "text" (100) column on each. I want to select those who are identical. ¡So easy! thats the where clause:
AND a.sDesEntidad = b.sNombreLargo
That works fine. But... i want to "relax" the clause, by not considering dots ( . ) when comparing, and i do like this:
AND (Replace([a]![sDesEntidad],'.','') = Replace([b]![sNombreLargo],'.',''));
and i get a run-time Error 3464, Data type mismatch in criteria expression. Oooops!
My access version is 2003, spanish. I would really appreciate ur help, since this matter shouldn't be keeping me busy for so long :(
Regards and thx in advance!
Alejandro
I have a table with a field for "type". I want a query to run that will show everyone exept "type" A and B. I can get it to work with one of them using Criteria "Not A" and it works. But cannot figure out how to make it not show both A and B
View 2 Replies View RelatedHi,
Wondering if anyone has a solution for my problem.
I have 2 data sources, one which is an ODBC and one which is an import table. I have a 2 queries each querying the 2 data sources seperatly.
My problem is... One of the fields which I need to do a join to a reference table has a different data type in each of the data sources. I have tried changing this in the imported table NO JOY!! You cant change the data type in the ODBC link.
Is the anyway round this?
Any help appreciated...
I am trying to do a comparison between two values and am getting the following error:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
The code that is creating the error is as follows:
sb = Request.Form("storyboardscreen")
if Not Lcase(Request.Form("AllStoryBoard")) = "all" then
whereClause = whereClause + "theStoryboard = " + sb
end if
I am using the statement to build the where clause of my query. The sb value is retrieved from a pull down menu and "theStoryboard" column is the db is of data type "text". The values stored in the field are 12 digit numbers. I have tried doing a cstr to sb but that doesnt work, any tips on what I am doing wrong? I am using asp with an access db....
Thanks
I have a database that, vaguely looks like the following
NAME Value Category A Value Category B Value Category C
Name A -50 0 0
Name A 0 50 0
Name B -100 0 0
Name B 0 75 10
Name B -25 0 0
Name C -100 0 0
Etc. etc.
What I'd like is to find a query that will first, generate a Category D which should be a total of all the values in the three different categories and consolidates the names to one line. So it should spit something like this out.
NAME Value Category A Value Category B Value Category C Total
Name A -50 50 0 0
Name B -125 75 10 -40
Name C -100 0 0 -100
After this, I'd like to have another query that will only display the values that are NOT zero.
NAME Val Cat A Val Cat B Val Cat C Total
Name B -125 75 10 -40
Name C -100 0 0 -100
WorkID is a look-up field, so its Data Type is Number.
In the same table, DailyGrind is a field with the Data Type of text.
I would like to update WorkID with the jobs in DailyGrind.
Surely there must be a way to do it, gurus?
I am struggling with an answer table for multiple type answers. Some questions have yes/no answers, some have option group answers (agree, neutral, disagree), and some require text input.
I have reviewed posts on normalization on survey design. In it's simplest form I would have 3 tables.
tblQuestions
QuestionID
QuestionText
tblAnswerCodes
AnswerID
AnswerText
tblResponses
PersonID
date
QuestionID
AnswerID
If all answers required an option group value then I would use the structure above.
If all the answers were yes/no I would eliminate tbl AnswerCodes and simply have an answer field with data type yes/no in tblResponses.
But if an answer requires a user text comment, I would need either a memo field or a 255 length text field. This is where I get confused. It seems inefficient to use a memo or text field for all answers when most of the questions require a yes/no answer. I feel the answer is somewhere in setting up another table with answer types, but I am struggling with this.
On a further note I am struggling with the response data entry form. For an option group answer I could setup an append query for each new survey response. But how about for multiple answer (check all that apply). I guess I need a form based on a query linking tblQuestions to tbl AnswerCodes. This would be a subform on the master form for tblResponses. I keep getting all the answers for all questions. But handling multiple question types on one continuous form seems awkward.
Thanks.
Hi All,
I have a Union query that brings together several tables into one. This works perfectly well however there is an occassion when a field in one table has its data type changed to text from date. This is to allow N/A to be entered rather than a date. When this occurs the union query changes all the dates from the other tables to text also and therefore other queries I am running based on the Union query now fail.
Is there any way to have the Union Query ignore the data from the table where and when this this occurs as it is not necessary to show in the query and can happen with any of the tables at a users discretion
Thanks Again
IC