Use of Val Function and DoCmd.TransferSpreadsheet Method together (1 Viewer)

[azlan]

Hi There,
Is there a way to use Val Function and DoCmd.TransferSpreadsheet Method together.
I have one field on excel that contains numbers and text together but I only want to get number part.

Thank You...

 

[CJ_London]

the short answer is no. The ways to do it are

1. add a column to excel with the calculated value
2. link to excel the use a query (using val) to import from the linked table
3. use transferspreadsheet to import the worksheet to a temporary table then use a query (usng val) to append to your table

 

[azlan]

the short answer is no. The ways to do it are

1. add a column to excel with the calculated value
2. link to excel the use a query (using val) to import from the linked table
3. use transferspreadsheet to import the worksheet to a temporary table then use a query (usng val) to append to your table

Ok, Here is what I do, I scan barcode to a excel file with my android phone and email it to myself and import excel to temp table and append this temp table to my Invetory Cycle Count Table than I compare this table to my main Inventory table. The thing is one of the fields that I import is combo takes from enother connected table ( No need to say I know ıt now that using of connected field combo box was bad idea but it is too late now).
So what I need is to for example "4#Main Location" needs to go in my Invetory Cycle Count Table as "4". If you can help me doing this, It would be greatly appreciated.

 

[CJ_London]

you need to use one of the suggestions I have made. Suggest option 2

One refinement is if your excel file always has the same name then you only need to link the once - then copy each new excel file to the same name/location

Think of the linked table being a microscope and the excel file as being a slide-'slide out' the old excel file and 'slide in' the new

However you seem to be importing to the temp table so you can create a query along the following lines

SELECT * 
FROM temptable inner join maintable on val(temptable.location)=maintable.location

You need to write this a sql since the querybuilder cannot show this type of join

alternatively

SELECT * 
FROM temptable inner join maintable
WHERE val(temptable.location)=maintable.location

 

[azlan]

CJ_London Thank You for your help,
Can you please help with Third option, I mean how can I use Val function when in Append Query. Do you have any example access template. I understand better with example.

 

[CJ_London]

your code would be something like this

INSERT INTO mainTable (Location) SELECT val(location)
FROM temptable

 

[smig]

try to set your field as number and import the Spreadsheet.
It should take only the numbers out of the string.

 

[azlan]

Wohooo, it was very simple with Append query.
For others with same problem see attached.
Thank you London for your help.