Changing the data in a cell

[talia679]

I'm not sure if this can be done with a query or if it needs a macro, so I'll start here.

I have the following information in my database ( | signifies a column separation )

|31|58|15|3|5|102|1|153|

Now this looks like mumbo jumbo to the regulat user, but it's really the date

|second|minute|hour|day|month|year-1900|day of week (starting with 0)|day of year(starting with 0)

Now I want to have a query pull this info an make it readable to the regular viewer. I want them to be able to see that this data is from June, 3, 2002 at 3:58pm

I also have another question. Can you pull strings out of a cell? Say you have a cell that has this:

(compatible; MSIE 5.0; MSNIA; Windows 95; DigExt; MSNIA)

Can I pull each of those peices of info out and seperate them?

If you can't tell this is data from a hit tracking log I have on my site, we need to make the stats viewable to others in the office ... and we need to make it as user friendly and understandable as possible.

I hope I gave you enough information ... I'm using Access 97 btw.

 

[David R]

First part: Look up the DateSerial and TimeSerial functions. They should allow you to build a rational date/time stamp based on the value you get.

Second part: (this will help with the above too): Look up the InStr and text manipulation functions (Left,Right,Mid(), etc).

Post back if you need further help.

 

[talia679]

Okay did the looking up ... even installed the complete help file (cause I didn't have it) but now that I've read the help I realize I don't remember enough VB to do something like that.

This is what I wrote based on the example:

Dim FixTime
FixTime = TimeSerial(Tables![Log]![Hours], Tables![Log]![Minutes], Tables![Log]![Seconds])

But I don't know what to do with this or if it's even the right syntax. I used Access syntax ... maybe it's supposed to look like this:

Dim FixTime
FixTime = TimeSerial(Tables.Log.Hours, Tables.Log.Minutes, Tables.Log.Seconds])

Or maybe I can't do it like that at all ... I have no idea

And this is only me trying the TimeSerial ... I know I still have to look at the others.

Any pointers or help would be greatly appreciated.

[This message has been edited by talia679 (edited 06-05-2002).]

 

[David R]

Try it in a query: TimeCheck: TimeSerial([HrsField],[MinsField],[SecsField])
Base this query on the one that splits all that data into sensible size pieces (you could do it all in one query, but that makes my head hurt).

[This message has been edited by David R (edited 06-05-2002).]

 

[talia679]

THAT IS AMAZING!!! Thanks so much ...

I have but one more problem:

Date: DateSerial([Month]+1,[Day],[Year]+1900)

This is how the date is computed. Right now it shows 0 for January and so on and 102 for the year.

However I can't seem to get it to calculate right ... this makes the date all funny ... it's not calculating right.

Any suggestions?

As far as InStr ... says it returns a # value ... want it to return the String ... and I can't tell it how long the string will be because each input varies based on the browser and computer ... does that make sense?

Thanks again for your amazing help!!

[This message has been edited by talia679 (edited 06-05-2002).]

 

[David R]

The XxxxSerial functions go from large to small: DateSerial requires the Year first, then the Month, then the Day. Look them up in help if you get lost.

InStr gives you the position of a piece of data in a string. InStr([String],";") should give you the position of the first semi-colon, then use Left(), Right(), Mid() separate out the pieces one after another.

 

[talia679]

One more thing

About the date - can I format it so that it shows up mm/dd/yy ... instead of m/d/yy ... I don't see a way to do that in a query ... I know how to do it in a table ... hmmmm

 

[David R]

Right-click on the query field and see if you can set the format in Properties there.