aggregate function

[Guest]

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.

 

[Rick Brandt]

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)

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

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

 

[Guest]

thanks for the help.

would it be possible to display Nov instead of 11 ?

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

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.

 

[Guest]

PERFECT!!

thanks so much...

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

thanks for the help.

would it be possible to display Nov instead of 11 ?

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


:

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.

 

[Douglas J. Steele]

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

 

[Guest]

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 spellin
Format(DateSerial(Left([xact_dat_x],4),Right(Left([xact_dat_x],6),2),Right(Left([xact_dat_x],8),2)),"mmmm")

thanks for the help.

would it be possible to display Nov instead of 11 ?

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

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.

 

[Marshall Barton]

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.