joining fields of different datatypes

[Audrey1980]

Hey guys

Have you ever had a scenario where you are trying to join fields of
different datatypes? I have 2 tables where I need to join on store and
department and in both tables they are different datatypes (text in one,
number in the other).

Other than changing the datatypes in the underlying tables, any ideas what
can be done?

Thanks.

 

[Douglas J. Steele]

Create your query as normal, then go into the SQL view and change

ON Table1.NumericField = Table2.TextField

to

ON CStr(Table1.NumericField) = Table2.TextField

or

ON Table1.NumericField = CLng(Table2.TextField)

Note that once you make that change, you will not be able to go back to
Design view in your query.

 

[Audrey1980]

Hi Douglas - I am still getting a expression type mismatch from the following
query:

PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
Menu]![Year] Short, [Forms]![Main Menu]![Week] Short;
SELECT VALIDDATABODY.storeNumber, [RSM STORE LISTING].[Store Name],
VALIDDATABODY.week, VALIDDATABODY.year,
[BaseMargin(£)]/[BaseValueAmountExcVAT] AS [BaseMargin(%)],
(([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin], [RSM STORE
LISTING].RSM
FROM [RSM STORE LISTING] INNER JOIN (dbo_Despatch_data_branch_summary INNER
JOIN VALIDDATABODY ON (VALIDDATABODY.storeNumber =
CLng(dbo_Despatch_data_branch_summary.Branch))
AND (CLng(dbo_Despatch_data_branch_summary.Dept) =
VALIDDATABODY.MSRDepartment)) ON [RSM STORE LISTING].Store =
VALIDDATABODY.storeNumber
WHERE (((VALIDDATABODY.Year)=2009) AND
((dbo_Despatch_data_branch_summary.Year)='2008' Or
(dbo_Despatch_data_branch_summary.Year)='2009'))
GROUP BY VALIDDATABODY.storeNumber, [RSM STORE LISTING].[Store Name],
VALIDDATABODY.week, VALIDDATABODY.year, [RSM STORE LISTING].RSM,
dbo_Despatch_data_branch_summary.Branch,
dbo_Despatch_data_branch_summary.Extd_WCEV,
dbo_Despatch_data_branch_summary.Extd_REV;

Do you know what I've done wrong? Thanks

 

[Douglas J. Steele]

What are the data types of VALIDDATABODY.storeNumber,
dbo_Despatch_data_branch_summary.Branch,
dbo_Despatch_data_branch_summary.Dept, VALIDDATABODY.MSRDepartment and [RSM
STORE LISTING].Store?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas - I am still getting a expression type mismatch from the
following
query:

PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
Menu]![Year] Short, [Forms]![Main Menu]![Week] Short;
SELECT VALIDDATABODY.storeNumber, [RSM STORE LISTING].[Store Name],
VALIDDATABODY.week, VALIDDATABODY.year,
[BaseMargin(£)]/[BaseValueAmountExcVAT] AS [BaseMargin(%)],
(([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin], [RSM STORE
LISTING].RSM
FROM [RSM STORE LISTING] INNER JOIN (dbo_Despatch_data_branch_summary
INNER
JOIN VALIDDATABODY ON (VALIDDATABODY.storeNumber =
CLng(dbo_Despatch_data_branch_summary.Branch))
AND (CLng(dbo_Despatch_data_branch_summary.Dept) =
VALIDDATABODY.MSRDepartment)) ON [RSM STORE LISTING].Store =
VALIDDATABODY.storeNumber
WHERE (((VALIDDATABODY.Year)=2009) AND
((dbo_Despatch_data_branch_summary.Year)='2008' Or
(dbo_Despatch_data_branch_summary.Year)='2009'))
GROUP BY VALIDDATABODY.storeNumber, [RSM STORE LISTING].[Store Name],
VALIDDATABODY.week, VALIDDATABODY.year, [RSM STORE LISTING].RSM,
dbo_Despatch_data_branch_summary.Branch,
dbo_Despatch_data_branch_summary.Extd_WCEV,
dbo_Despatch_data_branch_summary.Extd_REV;

Do you know what I've done wrong? Thanks

Create your query as normal, then go into the SQL view and change

ON Table1.NumericField = Table2.TextField

to

ON CStr(Table1.NumericField) = Table2.TextField

or

ON Table1.NumericField = CLng(Table2.TextField)

Note that once you make that change, you will not be able to go back to
Design view in your query.

 

[Audrey1980]

VALIDDATABODY.storeNumber = Number
dbo_Despatch_data_branch_summary.Branch = Text
dbo_Despatch_data_branch_summary.Dept = Text
VALIDDATABODY.MSRDepartment = Text
[RSM STORE LISTING].Store = Number

What are the data types of VALIDDATABODY.storeNumber,
dbo_Despatch_data_branch_summary.Branch,
dbo_Despatch_data_branch_summary.Dept, VALIDDATABODY.MSRDepartment and [RSM
STORE LISTING].Store?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas - I am still getting a expression type mismatch from the
following
query:

PARAMETERS [Forms]![Main Menu]![Store] Text ( 255 ), [Forms]![Main
Menu]![Year] Short, [Forms]![Main Menu]![Week] Short;
SELECT VALIDDATABODY.storeNumber, [RSM STORE LISTING].[Store Name],
VALIDDATABODY.week, VALIDDATABODY.year,
[BaseMargin(£)]/[BaseValueAmountExcVAT] AS [BaseMargin(%)],
(([Extd_REV]-[Extd_WCEV])/[Extd_REV]) AS [Input Margin], [RSM STORE
LISTING].RSM
FROM [RSM STORE LISTING] INNER JOIN (dbo_Despatch_data_branch_summary
INNER
JOIN VALIDDATABODY ON (VALIDDATABODY.storeNumber =
CLng(dbo_Despatch_data_branch_summary.Branch))
AND (CLng(dbo_Despatch_data_branch_summary.Dept) =
VALIDDATABODY.MSRDepartment)) ON [RSM STORE LISTING].Store =
VALIDDATABODY.storeNumber
WHERE (((VALIDDATABODY.Year)=2009) AND
((dbo_Despatch_data_branch_summary.Year)='2008' Or
(dbo_Despatch_data_branch_summary.Year)='2009'))
GROUP BY VALIDDATABODY.storeNumber, [RSM STORE LISTING].[Store Name],
VALIDDATABODY.week, VALIDDATABODY.year, [RSM STORE LISTING].RSM,
dbo_Despatch_data_branch_summary.Branch,
dbo_Despatch_data_branch_summary.Extd_WCEV,
dbo_Despatch_data_branch_summary.Extd_REV;

Do you know what I've done wrong? Thanks

Create your query as normal, then go into the SQL view and change

ON Table1.NumericField = Table2.TextField

to

ON CStr(Table1.NumericField) = Table2.TextField

or

ON Table1.NumericField = CLng(Table2.TextField)

Note that once you make that change, you will not be able to go back to
Design view in your query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hey guys

Have you ever had a scenario where you are trying to join fields of
different datatypes? I have 2 tables where I need to join on store and
department and in both tables they are different datatypes (text in
one,
number in the other).

Other than changing the datatypes in the underlying tables, any ideas
what
can be done?

Thanks.

 

[Hans Up]

VALIDDATABODY.storeNumber = Number
dbo_Despatch_data_branch_summary.Branch = Text
dbo_Despatch_data_branch_summary.Dept = Text
VALIDDATABODY.MSRDepartment = Text
[RSM STORE LISTING].Store = Number

One of your Join expressions converts
dbo_Despatch_data_branch_summary.Dept to a number for matching
against VALIDDATABODY.MSRDepartment, which is text:

AND (CLng(d.Dept) = v.MSRDepartment))

I used aliases for your table names.

 

[samuel]

Is this the only solution to this? Would it be possible to create a query off
the table with the number type field, and allow the query to change to a text
field. Then create the actual query to join the newly created text field with
the originally desired text field?

I know this may seem a bit redundant,but I'm just concerned about not being
able to go into design view again.

 

[John Spencer MVP]

You can use a query as if it were a table, so you can create a saved query
that converts the value(s) to the proper type and then join that query to a table.

If you do that then you can be in design view.

Another option that MIGHT work for you would be to have no join clause (a
cartesian join) and use the where clause to do the joining. Simple example

SELECT *
FROM TableA, TableB
WHERE TableA.NumberField & "" = TableB.TextField

Or

SELECT *
FROM TableA, TableB
WHERE TableA.NumberField = Val(TableB.TextField)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[samuel]

Thanks for the information.

How would I specify the number data type to be a text format in the query?

 

[John Spencer]

Appending a zero-length string to a number forces it to be a string

NumberField & "" forces the number field to be a string.

Another option would be to use the format function. The following would
work for integer numbers.
Format(NumberField,"#")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[samuel]

My field is [Project] The root table has this data type as Number

How do I make this a string or text data type in a query using the Format()
function?

Format([Project],"") ?

 

[John Spencer]

REplace Project in your query with one of the following

Format([Project],"#")

Or

[Project] & ""


The two are slightly different in handling NULL values. Format will
return Null if Project is null.

Concatenating (adding) a zero-length string will return a zero-length
string for nulls.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

My field is [Project] The root table has this data type as Number

How do I make this a string or text data type in a query using the Format()
function?

Format([Project],"") ?

Appending a zero-length string to a number forces it to be a string

NumberField & "" forces the number field to be a string.

Another option would be to use the format function. The following would
work for integer numbers.
Format(NumberField,"#")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================