aggregate function

i import a table with this value as the timestamp (seen as a text data type)

[xact_dat_x]
2005112602465800

i want a query to extract the month..

Date: Format(Left([xact_dat_x],8),"mm") gives an error .

help.

samuel wrote:
i import a table with this value as the timestamp (seen as a text
data type)

[xact_dat_x]
2005112602465800

i want a query to extract the month..

Date: Format(Left([xact_dat_x],8),"mm") gives an error .

help.

The Format function (when fed "mm" as the format argument) expects an Access
DateTime to be fed in as the input. The first 8 characters of your field is not
a DateTime, but rather a String. For your Format function to work you would
first have to convert that String into a DateTime.

Since "mm" only gives the number for the month anyway just use the Mid fiunction
to grab those characters...

=Mid([xact_dat_x], 5, 2)

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Rick Brandt wrote:

samuel wrote:

i import a table with this value as the timestamp (seen as a text
data type)

[xact_dat_x]
2005112602465800

i want a query to extract the month..

Date: Format(Left([xact_dat_x],8),"mm") gives an error .

help.


The Format function (when fed "mm" as the format argument) expects an Access
DateTime to be fed in as the input. The first 8 characters of your field is not
a DateTime, but rather a String. For your Format function to work you would
first have to convert that String into a DateTime.

Since "mm" only gives the number for the month anyway just use the Mid fiunction
to grab those characters...

=Mid([xact_dat_x], 5, 2)

.... which is what I would suggest, too, but if you want a month name you
could use something like

Format(Mid([xact_dat_x],5,2) & "/1/2005","mmmm")

For example:

[Table1] Table Datasheet View:
xact_dat_x
----------------
2005112602465800

[Q_Months] SQL:

SELECT Format(Mid([xact_dat_x],5,2) & "/1/2005","mm")
AS MonthNum,
Format(Mid([xact_dat_x],5,2) & "/1/2005","mmm")
AS MonthAbbr,
Format(Mid([xact_dat_x],5,2) & "/1/2005","mmmm")
AS MonthName
FROM Table1;

[Q_Months] Queary Datasheet View:

MonthNum MonthAbbr MonthName
-------- --------- ---------
11 Nov November


-- Vincent Johns
Please feel free to quote anything I say here.

Access is not recognizing your data as a date.
If you want the month from it use –
Right(Left([xact_dat_x],6),2)


"samuel" wrote:

i import a table with this value as the timestamp (seen as a text data type)

[xact_dat_x]
2005112602465800

i want a query to extract the month..

Date: Format(Left([xact_dat_x],8),"mm") gives an error .

help.

thanks for the help.

would it be possible to display Nov instead of 11 ?

"KARL DEWEY" wrote:

Access is not recognizing your data as a date.
If you want the month from it use –
Right(Left([xact_dat_x],6),2)


"samuel" wrote:

i import a table with this value as the timestamp (seen as a text data type)

[xact_dat_x]
2005112602465800

i want a query to extract the month..

Date: Format(Left([xact_dat_x],8),"mm") gives an error .

help.

PERFECT!!

thanks so much...

"KARL DEWEY" wrote:

Format(DateSerial(Left([xact_dat_x],4),Right(Left([xact_dat_x],6),2),Right(Left([xact_dat_x],8),2)),"mmm")

Or this for full spelling
Format(DateSerial(Left([xact_dat_x],4),Right(Left([xact_dat_x],6),2),Right(Left([xact_dat_x],8),2)),"mmmm")

"samuel" wrote:

thanks for the help.

would it be possible to display Nov instead of 11 ?

"KARL DEWEY" wrote:

Access is not recognizing your data as a date.
If you want the month from it use –
Right(Left([xact_dat_x],6),2)


"samuel" wrote:

i import a table with this value as the timestamp (seen as a text data type)

[xact_dat_x]
2005112602465800

i want a query to extract the month..

Date: Format(Left([xact_dat_x],8),"mm") gives an error .

help.

"Vincent Johns" wrote in message
om...

... which is what I would suggest, too, but if you want a month name you
could use something like

Format(Mid([xact_dat_x],5,2) & "/1/2005","mmmm")


Just a warning. That won't work for users who have their short date form set
(in Regional Setting) to dd/mm/yyyy: it'll return January in all cases.

Far safer is:

Format(DateSerial(2005, Mid([xact_dat_x],5,2) , 1), "mmmm")



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Format(DateSerial(Left([xact_dat_x],4),Right(Left([xact_dat_x],6),2),Right(Left([xact_dat_x],8),2)),"mmm")

Or this for full spelling
Format(DateSerial(Left([xact_dat_x],4),Right(Left([xact_dat_x],6),2),Right(Left([xact_dat_x],8),2)),"mmmm")

"samuel" wrote:

thanks for the help.

would it be possible to display Nov instead of 11 ?

"KARL DEWEY" wrote:

Access is not recognizing your data as a date.
If you want the month from it use –
Right(Left([xact_dat_x],6),2)


"samuel" wrote:

i import a table with this value as the timestamp (seen as a text data type)

[xact_dat_x]
2005112602465800

i want a query to extract the month..

Date: Format(Left([xact_dat_x],8),"mm") gives an error .

help.

KARL DEWEY wrote:
If you want the month from it use –
Right(Left([xact_dat_x],6),2)


But, Karl, that's just the long way around to the Mid
function that everyone else used.

--
Marsh
MVP [MS Access]