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.
I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.
Hi, I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
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
I use a Make-Table Action query to import data from a linked table into my database. The linked table is on a network server that is automatically updated.
One column of the linked table is named "QTY/PARTIAL" and approximately 10% of the 500 records have a "P" after a number i.e. 1000 P. I would like to separate the number and the P into separate columns in the new table to faciliate being able to compute the total number "QTY".
I am using Access 2003.I have created a Makle Table Query which gives me the invalid argument error because it makes the database balloon to over 2gb. I have two questions:
1. Is it possible to get the make table query to save data in one database until it reaches say 1.9 gb and then begin saving the rest of the data in another database?
2. Is there a wey to pause or stop a make table query to see the results and then let it run again?
Hi there, I have a simple bit of VBcode in my database which reads the password a user enters and 'encrypts' it for storing in the user table. It's more obfuscation than encryption to be honest, and I feel limited by the 'text' data type assigned to the password field.
At the moment it works by taking the password entered, and converting the whole lot into a byte array, then for each 'character' in the array, changing its binary value. At the moment it's then converted back into a string and dumped into the 'password' field, as garbage characters. But they are still characters, and if I go out of range of the character set it results in horrible errors :D.
I feel I may be missing the blindingly obvious and could just not convert it back to string, and write the binary into a text field instead, but I'm not sure how I'd get intelligible data back.
The reason it's not exactly encryption is because the key is the same for all characters on all passwords (I just multiply the binary value by 2!) what I want is to have a key as well, stored in another field alongside, but again can't implement this due to character-set range issues.
So my question is - is there a 'raw' data field hidden somewhere amongst the features of microsoft access, or should I be thinking about just storing the numbers themselves? For the moment I'll investigate the latter, but I'll appreciate any help! Thanks :)
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.
I have a query that selects invdate, status and type from a table, I want to be able to search for invdate=today status between 50 and 70 and to count types 30, 31+32, 33+34 and 35.
I'm relatively new to access. I have a database i created for my school project everything works fine until I try running a query with 2 or more tables, the queries return a datasheet with all the table headings but no data content, I ensured that there is data in the tables that I'm trying to join for the query.
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
This is used for a report for a 28-day period (the query selecting the Start Date and the End Date), and the intention is to show the information grouped by Supervisor, showing the information for as many days as they worked.However, I would like to have the overall 28-day averages of the six fields EExpect thru to IntLossEN shown underneath the detail for each individual. I know I could do this in Access Totals in the page footer if the report was confined to one supervisor, but with up to 20 supervisors I am unsure how to get these totals after the detail for each?
I would also like to have this summary information for each individual presented on a separate page at the end of the report (almost like a league table, if you like), so there is a single page that shows the data for each supervisor in comparison to all their colleagues.
I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:
Code: SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC FROM [1733_All Print Orders] WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013# GROUP BY [1733_All Print Orders];[Application]
[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.
I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records
To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.
I have a table providing a list of all members of a local bowling club, with the following fields - MemID, MemName (table is tblMembers)
I also have two more tables providing details of matches we play in a local bowling league. The first table holds the basic match information (called tblMatches) - MatchID, Date, Opponents, ScoreFor, ScoreOpps. I've used a MatchID field as more than 1 match can be played on a single date.
The other table provides details of who played in what match and on what bowling rink and is called tblMatchDetails, with the following fields - MatchID, MemID, Rink (numeric), Result (numeric - 1 for win, 0 for loss). Each match uses different rinks, and not all rinks are used in a match, however, each rink can only be used once in a match.
We have 10 rinks, and I was looking to see if I could get a table to show me the match details along with the rinks in numerical order with who played on them in what match and their result.
For example:
Field Row: Date - Opponents - ScoreFor - ScoreAgainst - Rink 1 Player - Rink 1 Result - Rink 2 player - Rink 2 result - [and son on upto] - Rink 10 player - Rink 10 result. Row 1: 01/01/2005 - Example BC - 4 - 3 - [blank] - [blank] - Fred - 1, etc Row 2: 08/01/2005 - Other BC - 5 - 2 - John - 0 - [blank] - [blank], etc
I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):
MatchID - PlayerID with a criteria field of rink = "1" or whatever number rink it is for.
I then produced a second query providing a link of the matches (tblMatches) and the rink queries (qryRinkOneDets, etc), with the joins between the MatchID fields in tblMatches and the individual queries to provide every record from tblMatches and only those records from qryRinkOneDets where MatchID was equal.
However, the results I got only showed the match details and no info from the Rink query. If I change the join from RIGHT to LEFT, then I get the records from the Rink query but not from the Matches table. If I then do a join where only the MatchID's from the table and query are equal (an INNER join) then I get no records at all.
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.
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
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?
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.
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
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.
