Joining a 'long integer' field to a 'short text' number

[peskywinnets]

I have two tables both of which contain 'sales receipt' number information

Table 1 has this information in number format (long integer)

Table 2 has this same information but in 'Short Text' format

I've created a query to convert the table 2 'Short Text' into a number .....but I keep getting data type mismatch when I run another query using the result (the end goal is to link the two tables using the sales receipt number), here's how I'm converting...

SalesReceiptNumber: CLng(Val([Table2]![SalesReceipt]))

...the command works but clearly the result isn't in long integer...which is what I need.

Any ideas?

 

[sneuberg]

I've been trying to replicate your problem but I'm not having any luck. I created Table2 with a field SalesReceipt which is Short Text. The following query runs fine

SELECT CLng(Val([Table2]![SalesReceipt])) AS SalesReceiptNumber
FROM Table2;

In fact it disturbingly works even when there is text data in SaleReceipt, converting the bogus data to 0. I made a make table query from this and that works too.

Could you tell us a little more about how you are using this query?

 

[peskywinnets]

The ultimate goal is to use Access to find any sales receipts missing in a Quickbooks database (due to human errors, import errors etc)

I got an ODBC connection to the quickbooks database working last night (using 'linked table' to the quickbooks database), but the linked QB table has the invoice numbers as short text, whereas in Access (where the invoice numbers where created in the first place), they are in the long integer format. (the plan was to use a mismatched query between the two tables)

I found a post answering a similar question...

http://www.mrexcel.com/forum/microsoft-access/497880-convert-text-number-while-joining.html

& transposing that to my situation would see this SQL code...

(table2 has the short text, table1 has the long integer)

SELECT Table2.*, Table1.* 
FROM Table2 
INNER JOIN Table1 
ON cint(Table2.InvoiceNo) = Table1.InvoiceNo;

but I still get a data type mismatch :-(

 

[sneuberg]

I tested that solution with Clng

SELECT Table2.*, Table1.*
FROM Table2 INNER JOIN Table1 ON Clng(Table2.InvoiceNo) = Table1.InvoiceNo;

and it work as long as the data in InvoiceNo are numbers. In this case if they're not I get the mismatch error. I suggest you test table2 with a query something like

SELECT IsNumeric([Table2]![InvoiceNo]) AS Expr1, Table2.InvoiceNo
FROM Table2
WHERE (((IsNumeric([Table2]![InvoiceNo]))=False));

to make sure they are all really numeric

 

[sneuberg]

Maybe you could do it the other way. Convert the field in table1 to text, CStr()

 

[peskywinnets]

Good idea...I'll give that a go later.

 

[peskywinnets]

Still can't get it to work....I'll have to kick this can down the road!

 

[sneuberg]

I wonder if it has something to do with the ODBC connection. How about trying a make table query on that linked table to make a temporary table. If you can do that then retry these joins with conversions with that table.

 

[gemma-the-husky]

I presume the problem is what sneuberg said in #4

ie. some of the text values are not numbers.

[edit - note that val picks up leading numbers. if the "test" starts with letters you will get zero]

[further edit
you could just change the data type of the short text field to number. If the data is invalid, access won't allow the change

 

[arnelgp]

convert both fields to string, i guess:

SELECT Table2.*, Table1.*
FROM Table2 INNER JOIN Table1 ON (Table2.InvoiceNo & "") = (Table1.InvoiceNo & "");

 

[sneuberg]

I presume the problem is what sneuberg said in #4

ie. some of the text values are not numbers.

[edit - note that val picks up leading numbers. if the "test" starts with letters you will get zero]

[further edit
you could just change the data type of the short text field to number. If the data is invalid, access won't allow the change

But it must be more than that because if you do it the other way like

SELECT Table2.*, Table1.*
FROM Table2 INNER JOIN Table1 ON Table2.InvoiceNo = CStr(Table1.InvoiceNo);

this works without error even if there is nonnumeric data in Table2, but he seemed to say this did not work for him.

 

[peskywinnets]

Ok, the 'join' works ...in other words after making the join I don't get a data type mismatch, here's the SQL I'm using...

(Sales.InvoiceNo is the long integer, SalesReceipt.RefNumber is the short text)

SELECT Sales.InvoiceNo, SalesReceipt.RefNumber
FROM Sales INNER JOIN SalesReceipt ON Sales.InvoiceNo = cint(SalesReceipt.RefNumber);

*BUT*

when I try to add some basic criteria to the query, like this...

SELECT Sales.InvoiceNo, SalesReceipt.RefNumber, Sales.OrderDateTime, Sales.ContactName
FROM Sales INNER JOIN SalesReceipt ON Sales.InvoiceNo = cint(SalesReceipt.RefNumber)
WHERE (((Sales.OrderDateTime)>#1/1/2016#));

So my apologies....it seems the join was always working, but it's my criteria that's throwing my query into 'data mismatch' condition.

Now Sales.OrderDateTime *is* definitely a date/time field....so any ideas why the mismatch?

 

[sneuberg]

I replicated your table structure or at least enough to copy and paste your SQL into, but you can see in the attached database that the query works fine.

Maybe you can look at what I attached and compare it with what you have to see what the differences might be.

 

[sneuberg]

Try adding CDate as below

SELECT Sales.InvoiceNo, SalesReceipt.RefNumber, Sales.OrderDateTime, Sales.ContactName
FROM Sales INNER JOIN SalesReceipt ON Sales.InvoiceNo = cint(SalesReceipt.RefNumber)
WHERE (((CDate(Sales.OrderDateTime))>#1/1/2016#));

and see if it makes any difference

 

[peskywinnets]

Wow thanks....you went above & beyond the call doing that .

You are correct....your runs fine.

the only difference between yours & mine is that in mine, the table called
SalesReceipt is an 'ODBC linked table'.

So rather than link, I imported the data into Access in its own table. I got the same problem (data mismatch) ....even without using the date criteria.

So then I thought perhaps there's a problem with the data in the table that has short text for the InvoiceNo.....I therefore trimmed that data right back (from 3 years worth of data to one year's worth). The data mismatch dsiappeared.

progress (perhaps there's some corrupt data in the ODBC original database?)

So then I thought let's use the date criteria as per your code.....

SELECT Sales.InvoiceNo, SalesReceipt.RefNumber, Sales.OrderDateTime, Sales.ContactName
FROM Sales INNER JOIN SalesReceipt ON Sales.InvoiceNo = cint(SalesReceipt.RefNumber)
WHERE (((CDate(Sales.OrderDateTime))>#1/1/2016#));

Now I get an 'overflow' message?!!!

Talk about getting the runaround!!!

 

[peskywinnets]

Ok, it seems the 'join' between the two tables is a misnomer!!!

There's clearly something awry about the data coming from the ODBC linked table, here's what I did (just the one table in play - using linked ODBC data).....

SELECT SalesReceipt.RefNumber, Int([SalesReceipt]![RefNumber]) AS Converted
FROM SalesReceipt;

That works, but if I then try for example sorting on the 'Converted' data (criteria on the number e.g. >1000) , I get a data type mismatch......quite odd

 

[sneuberg]

Now I get an 'overflow' message?!!!!

What's overflowing. Would you post a screen shot of the message?

 

[peskywinnets]

It didn't say anything other than 'overflow' when I ran the earlier query with crieria.

Ok, I've got rid of the mismatch when trying to use criteria...

SELECT SalesReceipt.RefNumber, CLng(Val([SalesReceipt]![RefNumber])) AS Converted
FROM SalesReceipt;

So I applied that to the earlier SQL...

SELECT Sales.InvoiceNo, SalesReceipt.RefNumber, Sales.OrderDateTime, Sales.ContactName
FROM Sales INNER JOIN SalesReceipt ON Sales.InvoiceNo = CLng(Val(SalesReceipt.RefNumber))
WHERE (((CDate(Sales.OrderDateTime))>#1/1/2016#));

& it works!! (no data mismatch now). So it was all in the conversion of the short text to an integer that was causing problems.

Phew

Many thanks for all your help ....it really was appreciated.

P.S. I'm not so hot with SQL & the above SQL does not allow me to use the standard Access GUI, so how would I add a Data Mismatch


I want the same query to show any Sales.invoiceNo entries that aren't in the converted CLng(Val(SalesReceipt.RefNumber)) ??!!!

 

[peskywinnets]

Ok, whilst I've got everything 'joined' & no data mismatch error, the end goal (Find Unmatched records) eludes me.

this code just doesn't find the unmatched records...

SELECT Sales.InvoiceNo, SalesReceipt.RefNumber, Sales.OrderDateTime, Sales.ContactName
FROM Sales INNER JOIN SalesReceipt ON Sales.InvoiceNo = CLng(Val(SalesReceipt.RefNumber))
WHERE (CLng(Val(SalesReceipt.RefNumber)) is Null );

the (very small) database if anyone fancies the challenge! Basically, there's one mismatch between the two tables, but I can't get access to show it :-(

 

[sneuberg]

You need to use an outer join in a unmatched query. You have an inner join. Check out http://blogannath.blogspot.com/2010/03/microsoft-access-tips-tricks-find.html for starters, but if you google "unmatched query" you can find other sources.

This can also be done with a WHERE NOT IN clause.