MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 280|Reply: 8

Copy and paste linked table structure only, then change dat

[Copy link]

11

Topics

27

Posts

81

Integral

Member

Rank: 2

Integral
81
Post on 1-27-2017 15:02:40 | All posts |Read mode
Hi all,
I need to change the data type for a field in order to make some outer joins work, but the field in question comes from a linked table. There is too much data to change the data type in the actual table that is linked so I need a workaround.
So far I do it like this:
Copy and paste linked table as structure onlyChange the field data type in the new tableAppend from the linked table
This way my queries that have outer joins will work. I don't want to do this manually though so is there a way to put this into a macro somehow? Or maybe a better workaround than what I'm doing here?
Thanks!




Reply

Props Report

3

Topics

561

Posts

1225

Integral

King

Rank: 6Rank: 6

Integral
1225
Post on 1-27-2017 15:22:23 | All posts



I don't know what the data type in question, but have you tried using cint or clng (or cstr) functions to make your joins?
for example if the table a field is a string and table b integer:
ON tablea.field1 = cstr(tableb.field1)
Reply Support Opposition

Props Report

3

Topics

561

Posts

1225

Integral

King

Rank: 6Rank: 6

Integral
1225
Post on 1-27-2017 18:31:00 | All posts



I don't know what the data type in question, but have you tried using cint or clng (or cstr) functions to make your joins?
for example if the table a field is a string and table b integer:
ON tablea.field1 = cstr(tableb.field1)












Reply Support Opposition

Props Report

11

Topics

27

Posts

81

Integral

Member

Rank: 2

Integral
81
 Author| Post on 1-27-2017 19:27:20 | All posts



I need to convert a text to a number, so would that formula work in my case?




Reply Support Opposition

Props Report

3

Topics

561

Posts

1225

Integral

King

Rank: 6Rank: 6

Integral
1225
Post on 1-27-2017 20:49:38 | All posts



Cstr is for conversion into a string... Clng to convert to long integer
Reply Support Opposition

Props Report

3

Topics

561

Posts

1225

Integral

King

Rank: 6Rank: 6

Integral
1225
Post on 1-29-2017 21:56:05 | All posts



Cstr is for conversion into a string... Clng to convert to long integer









Reply Support Opposition

Props Report

0

Topics

2

Posts

6

Integral

Newbie

Rank: 1

Integral
6
Post on 1-29-2017 23:15:52 | All posts



Hi there - the issue is not the structure of the query, but the data. It seems to me that I cannot edit/update the data when accessed through a query. Or perhaps I am just doing something wrong?
Reply Support Opposition

Props Report

0

Topics

2

Posts

6

Integral

Newbie

Rank: 1

Integral
6
Post on 1-30-2017 01:56:31 | All posts



Hi there - the issue is not the structure of the query, but the data. It seems to me that I cannot edit/update the data when accessed through a query. Or perhaps I am just doing something wrong?











Reply Support Opposition

Props Report

3

Topics

561

Posts

1225

Integral

King

Rank: 6Rank: 6

Integral
1225
Post on 1-30-2017 02:22:28 | All posts



You said it needed to be converted for the join.... But now it sounds like maybe it is when you are passing data to the query? What is the code with the isue?
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 9-24-2017 21:27 , Processed in 0.222880 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!