Replace And Data Types
Jan 26, 2006
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
View Replies
ADVERTISEMENT
Sep 5, 2005
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
View 1 Replies
View Related
Jul 18, 2007
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
View 1 Replies
View Related
Feb 2, 2005
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.
View 4 Replies
View Related
Oct 19, 2005
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
View 1 Replies
View Related
Jan 17, 2006
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
View 2 Replies
View Related
Jan 31, 2008
Hi,
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...
View 4 Replies
View Related
Feb 9, 2006
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
View 5 Replies
View Related
Jun 21, 2005
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?
View 4 Replies
View Related
Dec 11, 2007
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
View 2 Replies
View Related
Aug 3, 2007
Hi,
I have 2 linked tables, I would like to join these tables in access, however the first table's field is a memo and the second table is a number. I have tried to cint() the field in a query but I get an error message "Explicit conversion from datatype text to smallint is not allowed"
Any ideas how to get around this problem?
Thanks
Michael
View 4 Replies
View Related
Jun 8, 2014
I create a table that have one hyperlink data type and i want to open a existing form that i created using this hyperlink.
Can this hyperlink data type open a form in the same access database? or open form in different access database?
Using hyperlink button have the function to open form in same database but the problem is this function is not available in hyperlink data type.
View 2 Replies
View Related
Jun 3, 2005
I am working in MS Access 2000. I have a query that is returning a table with various types of data. The problem is I can't seem to find anywhere in the Access GUI that will show me the data types of columns it has returned so I can't manually create a table to hold the values returned by query. Question is two fold:
1. Is there a way in Access SQL to import data returned from a query into a table that is not yet defined. Some like this:
a. Create a table that has a primary key but doesn't yet know the number of columns and/or data types of those columns.
b. Import the data from a query into this table and have it create the columns and copy the data types and populate the table while also numbering each row by primary key.
or
2. Is there a way to find out what the data types are for a table returned by a query in MS Access. I have checked the properites of the query and have been up and down the gui looking but I can't find a way while looking at the data returned by a query to explicitely see the data type (i.e., number, Date/Time, etc..) used by each column.
Any info would help. Thanks.
View 1 Replies
View Related
Jan 31, 2008
This the first time I post in this forum and I tried looking for an answer to my question before posting, so I apologize if my question has been answered previously.
Is it possible to determine, in Access 2000, the data type in a Make-Table query of a new column with null value so that it does not default to binary.
Here is my sample sql:
SELECT tblAddressBook.Name, Null AS Email INTO tblTestTable
FROM tblAddressBook;
The output is tblTestTable with two columns: Name and Email. The data type of column Email defaults to Binary. How do I make it default to Text.
Thank you.
View 4 Replies
View Related
May 9, 2015
The Data Types match and they are in order. I get a syntax error.
PHP Code:
strSQL = "INSERT INTO tblHour (Hours, WorkDate, SickDay, SickDte) VALUES (" _
Me.AbsenceHrsID & ",#" & Format(Me.AbsenceDteTo, "yyyy-mm-dd") & "#,True,#" _
Format(Me.AbsenceDteTo, "yyyy-mm-dd") & "#)"
CurrentDb.Execute strSQL, dbFailOnError
View 4 Replies
View Related
Jan 26, 2014
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.
View 1 Replies
View Related
Jan 25, 2014
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).
View 1 Replies
View Related
Jul 14, 2014
I've inherited an Access 2000 database that is used to enter and print various types of data. I am using Access 2010 and when I open it I am in a forms view for the main user interface. I want to see and/or edit the objects that make up this database but I can't find a way to see them. The navigation pane is not visible and using F11 doesn't open it. How can I see the database objects?
View 14 Replies
View Related
May 6, 2014
I have this working query:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"
View 6 Replies
View Related
Apr 15, 2007
Hi, i am a new comer. i have a table for students' comment for the first semester. however, in the 2nd semester i want to replace them with import data from excel to this table, but on the same student there are 2 rows of records (1 is 1st semester and 1 is 2nd semester).
how can i replace the new data to the old data? or how can i delete all the old data in the exsiting table so that i can import a new excel file?
thx
View 1 Replies
View Related
Dec 12, 2005
Hi, I'm fairly new with working with Access and programming in VBA, and I was looking for some help. I have a database with many tables, and relationships between the tables. In particular, I export two of these tables to text files from one machine and need to import them back into the database on another machine. I have this working if I delete all the records before useing the transfertext command to import that table. But my problem is that I need to either append a record if it doesn't exist in the promary keys, or if the new record exists in a primary key, I need to replace it. I was thinking about reading the table to a temp table from the text file and checking each record in some sort of query to see if it needs to be appended or replaced in the table. Does anyone have any ideas of how else this could be accomplished a little easier that looping through each record, for this might take a while, as I do not know how many records can be importing each time. All I know is that I need a way of taking data from a text file and either appending or replacing the records in the table in the database. I appreciate any help I can get with this. Thank you.
View 3 Replies
View Related
Oct 3, 2006
friends, a small question.
I have placed a txtbox on my form and via a cmdbutton a can copy data to another text box. Is there a way I can replace data? Will appreciate any help.
View 4 Replies
View Related
Jan 7, 2014
Is it possible to have a Find & Replace in the table using access forms. ( Like the inbuild option in Excel ), I want the user to search some data & replace with the new data which may present in more than one record.
I want to do it with forms, because i dont want the user to use the tables.
View 3 Replies
View Related
Apr 8, 2013
Is it possible for an update query to simply replace all the data in a table's field with the results from a query?
For example, I have Table A with the field Years. This table performs various calculations within based on the field Years.
I would like an update query that simply replaces the Years field data in Table A with a new set of years based on a query's search criteria.
When I run the below query, I get an error saying the query must be updateable- but Im not trying to update the query, rather the Table
I've attached a photo ......
View 1 Replies
View Related
Jul 1, 2015
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
View 1 Replies
View Related
Oct 24, 2006
So, I've recently discovered that I'm more of an Access newbie than I had fancied myself to be. Anyway, I find that I need help with part of an ongoing project.
In our agreements with our clients, we have a set of standard terms which we typically use. Some clients with better bargaining power can occasionally negotiate better terms. One of the things I want our database to do is pull up which clients use the boilerplate terms and which have special situations.
I have two tables:
1) Fields: ClientID (Autonumber - Primary Key) & ClientName (Text)
2) Fields: ClientID (same as above - Primary Key) & Boilerplate (Yes/No)
For the clients that do not use the boilerplate, I would like to input the terms that differ from the original. Now to my question:
Is there anyway in my form(s) to make it so if I answer "No" (the client has special terms), it will then allow me to enter the special terms in another box? Similarly, if I answer "Yes" (that the client has boilerplate terms) no special entry box is triggered?
I'm not sure if I'm making any sense, but I guess I can sort of liken it to a computer survey where you have the choice of 4 options or "other" and if you select "other", an area opens for you to type what "other" is.
Let me know if you have questions. I'm at a loss for how to explain or do this.
View 3 Replies
View Related
