Text To Column problem while getting data from access in excel

[scorpion99]

Hi all,



I have 2 sheets in Excel 2010:
Sheet 1 where I get data from an access query giving me 2 columns (Date and staff numbers)
Sheet 2 where I have a column of dates entered manually and I am applying Vlookup function to get the staff members from Sheet2 based on the associated date.

The problem is that Vlookup don't give me results unless if I choose the date column got from the query in Sheet2 and convert it using Text To Column button.

Is there a way to fix this, I want to be able to refresh data in Sheet2 and getting results in Sheet1 immediately without having to click the mentioned button above.

[JoeM]

The problem is that Vlookup don't give me results unless if I choose the date column got from the query in Sheet2 and convert it using Text To Column button.

What exactly are you doing to convert it?
Is it a situation where you have to convert Text to Number, or Number to Text (or Date to Text, or Text to Date)?
As you probably know, VLOOKUP only works when comparing values of the same Data Type.

[rpeare]

might also try CDATE in conjunction with the vlookup to ensure you're getting a date value for your field.

[scorpion99]

JoeM ,
In access , it is a date/time so I am using the Format(date,"dd/mm/yyyy") to retrieve it.
In excel in Sheet2 I can connect to the query and get the 2 columns (the above for the date and the other for staff numbers)
Vlookup in Sheet1 search for a short date entered manually by me and look for the match in thoses 2 columns in Sheet2.
I cannot get correct value from it , unless I click Text To Column for the first column in Sheet2 , or edit each cell in it and press Enter.
I attached 2 pictures sheet1 , sheet2 similiar to what i am trying to do.

[scorpion99]

can you give me an example about it, didn't find anything about it

[JoeM]

In access , it is a date/time so I am using the Format(date,"dd/mm/yyyy") to retrieve it.

Get rid of the Format function. It coerces the results to TEXT, as it is a Text function.
So you are comparing a Date to Text, which will not work. That is why you are having to do the Text to Columns.

[rpeare]

Can I ask what you're doing, like where are the dates on the sheet 1 coming from? If they are also housed in access is there a reason you're not doing this with queries in access itself?

The only way I was able to duplicate your problem was by forcing a date to be a string in the query on the ACCESS side, if that is your case (you're storing a date as a text value) you can use the CDATE function on the access query then when you link it into excel it will be properly shown as a DATE/TIME data type.

So let's say your current field is 'MyDate'

In the query you're linking to your excel file do this:

ExportDate: cdate(mydate)

Then your function should work as you intend it to

[scorpion99]

JoeM , I am using the Format because in access it is a date/time (actually i am querying on a table linked to SQL) and if I query without it , I will have many results with same day but different time

[rpeare]

hellooooo use the cdate function in the query you're linking in excel

cdate(format(<date/time field>, "Whatever your format is"))

[scorpion99]

rpeare,
In Sheet1 , the date column is manually entered , I use Vlookup on other sheets (queries from access also) to get values based on the same exact date. They are working fine because simply the date in those queries is as follow :dd/mm/yyyy but the one that I am struggling with is using the following: dd.mm.yyyy hh:mm:ss that's why I was using Format function which is causing the date to be stored as text in Excel. As I mentioned before , if I edit the cell and press Enter ,it works also.

[rpeare]

your format function is on the ACCESS side though, is it not? My point is that you can, after you have converted the field to a date (rather than date and time) it is a string, to get it back to being a valid date you can use the cdate function on the ACCESS side.

So let's say your original field is named

TESTDATE

the value in it is 10/10/2013 09:00:00AM

you, in your access query change it to

FORMAT(TESTDATE, "Short Date")

or something similar

my suggestion is that you modify this formula to be:

CDATE(FORMAT(TESTDATE, "Short Date"))

[scorpion99]

Thanks rpeare ,the latest formula line solved my issue