ALTER TABLE and SEPARATE DATA SQL STATEMENTS

[Starscream]

Helo…please really need your help.

I designed a small desktop database to automatically import some Log files. A sample of a transmit log file (emails sent from our rural email stations) looks like this when imported in access.

Date Time Direction Sender MessageID
03062005 133501 To Internet emailX@ab.com Blablabla
03062005 125001 To Internet emailQ@ab.com Blablabla
03062005 125001 To Internet emailZ@ab.com Blablabla
03062005 125001 To Internet emailA@ab.com Blablabla

I would like to be able to query all emails sent between one date and another. However, the date here is recorded has a string or text. I would like to automatically add a field with the date as Date/Time datatype in the Table. I think I should be able to do this with a few SQL statement like ALTER TABLE myTable ADD COLUMN NewDate AS datetime…and then another SQL statement to separate data and put it into this new field.

Then I could query for BETWEEN Date1 and Date 2 easily.

I’m pretty close but been trying for a while now and always error messages as results. I think I really NEED help this time.

Thank you,

Ghislain Bob Hachey

 

[Pat Hartman]

You don't actually have to store the date field (unless you need to add an index on it to speed up processing). You can calculate it in the query.

Where CDate(.....whatever formating) Between [enter from date] AND [enter through date];

 

[Starscream]

Thank you for your help I REALLY appreciate people like you who take time to help others. I'm getting closer to the solution now.

However, what do I write in the CDate(??????). How will access know that the date is only the first 8 characters. How will access know that 01062005 is DDMMYYYY? When Access prompts me for the parameters do I enter the date it its original format (ddmmyyyy)?

Any kind of help is really appreciated.

By the way I'm from Canada but I working in the Solomon Islands poor country in the pacific...pretty far from home where I have lots of support...hehehe.

Cheers,

Ghislain Bob HAchey

 

[Pat Hartman]

I didn't fill in the expression because you didn't give us the format of the string.
CDate(Mid(StringDate,3,2) & "/" & Left(StringDate,2) & "/" & Right(StringDate,4)) As RealDate

WARNING - experiment with this because you may have to swap the first two expressions if your system settings are day, month, year rather than month, day, year as in the US.

 

[Starscream]

Filter by date on the fly

Hey me again. I've been trying with the tips you gave me for a while now and I think I'm getting closer to the solution.

However, I just can't seem to get it to work.

Is there a way I could attach my database or send it to you by email or something.

That is if you don't mind taking a look at it quickly. Maybe it's just a minor thing that I'm missing and you would see it right away.

Thanks again for for support

Ghislain Bob Hachey

 

[Starscream]

I attached the database to the thread

Cheers,

Ghislain Bob Hachey