Changing Field Types (1 Viewer)

[accessaspire219]

Hi,
I have a query which works off of objects I do not own. The field that I would like to join is in text format in one source while it is in the numerical format in the other source. I want to convert it into the same format so that I can join the two fields. Here is the SQL I am using

SELECT*
FROM
(SELECT CLng(Input.Material) AS Material, Input.Plant, Sum([Quantity]/[SUMREZ]) AS QTY_SUOM, Input.PurUOM, MARCBES.SOBSL, Input.PurchGrp
FROM Input) AS Input 
INNER JOIN 
(SELECT CLng(MATNR) AS MATERIAL_ID  FROM MARCBES) AS MARCBES
ON Input.Material = MARCBES.MATERIAL_ID;

I get an error saying "You tried to execute a query that does not include CLng(Input.Material) as a part of an aggregate function. Can any help me find what is wrong in my code?
Also, I would like the inner join to be on two fields as follows
Input.Material=MARCBES.MATNR and
Input.Plant=MARCBES.WERKS
How do I incorporate this into the code?
Any help is appreciated.
Thanks!

 

[WayneRyan]

aa219,

I don't know why you included MARCBES.SOBSL in your 1st nested Select statement.

I also don't see the point of the Sum function as you have no Group By clause.

I'm also unclear as to which of (Input.Material & MARCBES.MATNR) is a string,
so I used CLng on them both.

Here's my best shot at it:

SELECT Input.Material AS Material, 
       Input.Plant, 
       Sum([Quantity]/[SUMREZ]) AS QTY_SUOM, 
       Input.PurUOM, 
       MARCBES.SOBSL, 
       Input.PurchGrp, 
       MATNR AS MATERIAL_ID
FROM   Input Inner Join MARCBES On
         CLng(Input.Material) =  CLng(MARCBES.MATNR) and 
         Input.Plan = MARCBES.WERKS

hth,
Wayne

 

[accessaspire219]

Thanks for your reply. I used to have a group by but I took it out to simplify things I will probably add that step in a separate query. I made small changes and used the following code

[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]SELECT Input.Material AS Material, [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]       Input.Plant, [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       ([Quantity]/[SUMREZ]) AS QTY_SUOM, [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Input.PurUOM, [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       MARCBES.SOBSL, [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Input.PurchGrp, [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       MARCBES.MATNR AS MATNR[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]FROM   Input Inner Join MARCBES On[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]         CLng(Input.Material) =  CLng(MARCBES.MATNR) and [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         Input.Plant = MARCBES.WERKS[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]

When I run the query I get an error “Data type mismatch and criteria expression”
Access does not let me open the query in design view saying that the Input.Material to MARCBES.MATNR join could not be represented in design view.
HELP! J

 

[WayneRyan]

AA,

Can you attach a sample DB with dummy data?

Compact/Repair then toss it into a .ZIP file.

Access 2000 please.

Wayne