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).

 

[gingerafro]

Which version of Access are you on?
Does Access actually put it into that format (dd-mmm-yy), or did you have to select it?

 

[REDX]

Wish I could that to work on my end, I've tried every date format offered with and without four digit year and either get dates that are no good or do not come over at all?

Access if funny like that, tomorrow it'll work and I'll never know why?

 

[REDX]

I have 2003 (11.6355.6360) SP1.

gingerafro wrote:

Which version of Access are you on?
Does Access actually put it into that format (dd-mmm-yy), or did you have to select it?

I've selected every date format available and either get bad dates or no dates at all.

 

[Norie]

Have you tried my other suggestion to use functions in Access to get the dates?

What I would do is import the date as a text field. Then add a date field and update that field based on the other field.

The expression for the update would look something like this.

DateSerial(Left([TextDate], 4), Mid([TextDate],5,2), Right([TextDate],2))

 

[REDX]

I've done a few expressions in the past (very simple mind you) and tried to make a querry using the table that has the txt dates with your example...

No luck so far but at least it has given me some ideas...

 

[Norie]

How did you try my example?

I imported your examples as text into a table called TestImport, I then renamed the field to TextDate, added a new Date/Time field called RealDate and ran this query.

UPDATE TestImport SET TestImport.RealDate = DateSerial(Left([TextDate],4),Mid([TextDate],5,2),Right([TextDate],2));

Here are the results.

TextDate RealDate
19990904 04/09/1999
20030911 11/09/2003
20050726 26/07/2005
20020221 21/02/2002
20020221 21/02/2002
20020221 21/02/2002
20020221 21/02/2002
20051013 13/10/2005
19991203 03/12/1999
20021028 28/10/2002
20020607 07/06/2002
20051013 13/10/2005
19920213 13/02/1992
19900413 13/04/1990
19900413 13/04/1990
19900413 13/04/1990
19900413 13/04/1990
19900413 13/04/1990
19900413 13/04/1990
20030729 29/07/2003
20050719 19/07/2005

 

[REDX]

Your good Norie! Definitely a guru!

Do you think it would be possible to send me your Access data base example?

I think this would solve my problems...

 

[Norie]

REDX

Better edit your post, and remove your email address, unless you like spam.

PM it to me and I'll see what I can do.

 

[REDX]

Thanks Norie...PM sent.

 

[REDX]

It worked!!!!!!!!!!!! Cool!

As usual I was putting the expression in the wrong place