473,240 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,240 software developers and data experts.

Simple Access 2000 Query won't work in XP

I created a simple link between two tables in a query. TableA has
Social Security numbers stored as Long Integer Data. (I imported this
table).

The Join is between these two fields

Table Field Data Type
------------------------------------------
TableA ClientNumA Long Integer
TableB CliNumB String

When I open an Access 2000 database using the same query it works
fine( even when I open it in AccessXP). When I import the query into
AccessXP I get the message "Type mismatch in expression". Clicking
help tell me I have a 'Type mismatch in JOIN expression. (Error 3615)'
I assume that this is because one filed is Numerical and the other is
STring, even though this always worked before...

I tried recreating the same query in XP with the same failure.
Nevertheless, it still works running the Access 2000 database from
Access XP, which leads me to believe that XP can work the Join. Is
there some default setting I need to change?

I tried two work arounds: Created a new variable in a query for TableA
as follows:

strClientNumA: str$(ClientNumA)

and tried to link the table with the new field. Again it failed. When
I created another variable strCliNumB: str$(CliNumB) from TableB and
linked the two it works fine. This is puzzling and annoying... (I have
a lot of imported tables that I need to do this with...

Any guesses what's going on. I'm guessing I doing something wrong that
will be simple and obvious to someone else...

Thanks!
Nov 13 '05 #1
1 3622
J,

I would only join fields that have the same datatype.
Even if it works in A2000 (which is strange as such) I would not be surprized when the results are
unreliable.
The 'type mismatch'-message is correct and this would urge me to change the datatype in one of the
tables.
If you have to process a lot of tables this way, you can convert the datatype while importing maybe?

--
Hope this helps
Arno R

"j.mandala" <ma*****@rci.rutgers.edu> schreef in bericht
news:6c**************************@posting.google.c om...
I created a simple link between two tables in a query. TableA has
Social Security numbers stored as Long Integer Data. (I imported this
table).

The Join is between these two fields

Table Field Data Type
------------------------------------------
TableA ClientNumA Long Integer
TableB CliNumB String

When I open an Access 2000 database using the same query it works
fine( even when I open it in AccessXP). When I import the query into
AccessXP I get the message "Type mismatch in expression". Clicking
help tell me I have a 'Type mismatch in JOIN expression. (Error 3615)'
I assume that this is because one filed is Numerical and the other is
STring, even though this always worked before...

I tried recreating the same query in XP with the same failure.
Nevertheless, it still works running the Access 2000 database from
Access XP, which leads me to believe that XP can work the Join. Is
there some default setting I need to change?

I tried two work arounds: Created a new variable in a query for TableA
as follows:

strClientNumA: str$(ClientNumA)

and tried to link the table with the new field. Again it failed. When
I created another variable strCliNumB: str$(CliNumB) from TableB and
linked the two it works fine. This is puzzling and annoying... (I have
a lot of imported tables that I need to do this with...

Any guesses what's going on. I'm guessing I doing something wrong that
will be simple and obvious to someone else...

Thanks!

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Bon | last post by:
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to...
10
by: Wayne Aprato | last post by:
I have a database that was written in Access 2003 using the Access 2000 file format. When I run the mdb in Access 2003 everything works fine. If I run it in Access 2000 the timer event won't fire...
6
by: Jean | last post by:
Hi, I am using an Access 2000 front-end to an Oracle 9 backend. I want to write a query that returns all records that are not older than one year for Column "Status_30" (which is a Date). ...
4
by: James | last post by:
I have a VB windows forms application that accesses a Microsoft Access database that has been secured using user-level security. The application is being deployed using No-Touch deployment. The...
17
by: DaveG | last post by:
Hi all I am planning on writing a stock and accounts program for the family business, I understand this is likely to take close to 2 years to accomplish. The stock is likely to run into over a...
9
by: Fish Womper | last post by:
I am at best a part time developer of Access databases. I use Access 2.0, as this is all my employer has on its computers. Even so, to use this ancient version requires a fairly convoluted...
3
by: ChasW | last post by:
Is it possible, within Access, to create an update query that replaces all spaces with underscores for text data in a given field? I am aware that it can be done by going into Edit -> Replace,...
2
by: acw | last post by:
On a SQL Server 2000 db I would like to setup a stored procedure that accesses couple tables and runs the extended stored procedure xp..cmdshell. The goal is to grant users with limited privileges...
12
by: tekctrl | last post by:
Environment; Win2K PC with 1Gb of RAM and plenty of HD space running Access 2002 Issue; Access presents a blank data entry form in the Forms view when the New Record icon is used. However, it...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.