Excel Data Base?

[REDX]

I've been working with access for about 7 years now, I migrated from the old LOTUS days when you could make a pretty fair data base from a spread sheet, I really miss that.

My question is:

Is there anyway to turn my excel spreadsheet into a one stop shop? I'm tired of having to manipulate formula's thru excel and then import to access to use the data base functions.

I would love to be able to bring up a form that would querry my spreadsheet. The current form option is very limited and will not except my rather larger spreadsheet (37 columns).

 

[REDX]

I tried the YMD option and removed the (/), I can now get a date but all the four digit years show 2020? Hey, that's the closest I've ever been to gettin' a date in access from a txt file!

You say I can do the smae formulas in access? The only places I have formulas are in the querry's, is there another place to use them?

 

[Norie]

Are all the years 4 digit?

Like I said it worked perfectly for me but I only used a small sample.

By the way I'm talking about the formulas you were using in Access to get the date.

They could be used in an update query.

By the way there is another method to get real date values in Excel.

Select the column with the dates.

Goto Data>Text to columns...

On the 3rd step chose Date for Column data format and YDM (or whatever) from the dropdown.

 

[REDX]

Norie Wrote:

By the way there is another method to get real date values in Excel.

Select the column with the dates.

Goto Data>Text to columns...

It worked! Thanks, that saves a bit of formula!

Still trying to get the access thing working, they are all 4 digit dates but come across as the year 2020?

 

[Norie]

REDX

Could you post a sample of the dates?

I really can't see how you would end up with 2020.

By the way if you are getting this 2020 on a consistent basis you could use an update query to change the dates using DateAdd.

Not ideal but it would work.

 

[REDX]

As an example the txt dates look exactly as below:

20051020

this should convert to:

20 October 2005 or 10/20/2005

But for some reason I get random years ending with 2020? Tried every combination of YMD (Year/Month/Date) to no avail.

It seems I'm still stuck using excel and access to do the same job LOTUS used to do?

Thanks for your help Norie.

 

[Norie]

REDX

It might help if you posted more examples.

You've posted 2 so far.

 

[gingerafro]

I'm think that you might be getting 2020 because Access thinks that your date is 2 digit. ie it takes the 20 bit from 2005?

Is the 4 digit year box checked when you go into advanced options?

 

[REDX]

OK...here are a few examples of my txt dates that keep getting skewed, it seems that all of the 2005 year dates keep coming over as 2020.

Yes the four digit year is checked also. the first column is how they look in the TXT format. the second column is how they come over in access in the YMD format. If I format in advanced DMY the date does not come over?

TXT...
20050305
20041204
20050217
20050910
20050124
20041213
20050124
20050920
20050106
20051005
20051005
20050628
20051006
20050816
20050623
20051006
20051006
20050816
20051006
20051006

Access...

03-May-20
12-Apr-20
02-May-20
09-May-20
01-May-20
12-Apr-20
01-May-20
09-May-20
01-May-20
10-May-20
10-May-20
06-May-20
10-May-20
08-May-20
06-May-20
10-May-20
10-May-20
08-May-20
10-May-20
10-May-20

 

[Norie]

REDX

I've been trying to recreate your problem and it seems to be as gingerafro has suggested.

If I don't select 4-digit year in Advanced... I get 2020.

 

[Norie]

Just tried with your latest examples.

Here's the result I got.

04/09/1999
11/09/2003
26/07/2005
21/02/2002
21/02/2002
21/02/2002
21/02/2002
13/10/2005
03/12/1999
28/10/2002
07/06/2002
13/10/2005
13/02/1992
13/04/1990
13/04/1990
13/04/1990
13/04/1990
13/04/1990
13/04/1990
29/07/2003
19/07/2005