format time to [hh]:mm in a query

[Arvin]

Hello,

I would like to format the time to read as [hh]:mm in my query

Note *** I already have a "does not equal" value in the same query on the same colunm/field

How do I do this?

Thank you in advance

 

[Atomic Shrimp]

Add the field to your query again, move your criteria there and uncheck the 'show' box - then you're free to format the original column without interference - the criteria evaluation will be performed on the hidden, unformatted copy of the field.

You want to replace the field name with something like Format([yourfieldname],""hh:nn")

NB: Minutes are 'nn' because 'mm' means month.

 

[Arvin]

I just tried what you suggested and it didn't work

I'm getting "the expr you've entered has an invalid string" error

 

[Arvin]

this is what i have

Expr1: ([C30],"hh:nn")

and Access doesn't like the comma

 

[Brianwarnock]

Access doesn't like the fact that you didn't say Format, read mike's post again

Brian

 

[Arvin]

In FIELD - I entered

Format([C30],""hh:nn")

and i'm getting the same error

Here is a screenshot of my query

http://www.mts.net/~and0215/QueryScreenshot.jpg

 

[Colin@Toyota]

try taking the double quotation out...
so... Format([YourField], "hh:nn")

 

[Arvin]

I'm an Idiot! .... it works now

I should have had this

Expr1: Format([C30],"Short Time")

and NOT

Format([C30],""hh:nn")


One last question ....I hope how would I go about adding/summing the time now .......

I know I know ....it never ends

Thank you in advance

 

[Atomic Shrimp]

Sorry about the double quote typo.

Formatting as 'short time' might be dependent upon Windows regional settings - I'm not aware of any places that use a different short time format, but it's customisable by the user or administrator, so it could happen.

 

[DCrake]

Add a further column that reads

Mins:CInt((Left([C30],2) * 60 ) + Right([C30],2))

Then sum this column to give the total expressed in minutes

David

 

[Arvin]

1st off ....I just want to thank everyone that has and still is helping me with this ...it is really ...really ..really appreicated!

Okay ... I have done as you asked and it seems to work ... but in the MINS fields I'm getting #Error

Here are the screenshots

http://www.mts.net/~and0215/Dataviewscrnshot.jpg
http://www.mts.net/~and0215/QueryScreenshot3.jpg

Also ..... will this is work in a pivot table report ? ...sorry I'm new to this ...

 

[DCrake]

Can you post a sample of you mdb to look at?

 

[Arvin]

this is my ultimate goal .... if possible

http://www.mts.net/~and0215/pivtoltblrpt.jpg

 

[DCrake]

Here is a revised mdb I have built a cross tab query to generate the totals. This should get you on your way.

David

 

[Arvin]

Brilliant!

wow .... Thank you very much David ..... like really man .... and thank you all for assisting me .... this is SO appreciated

Bless you all! ! ! ! ! ! ! ! ! !

 

[Arvin]

I think I spoke to quick .....

Um .... maybe its me ( I think it is ) but ...when I switch to Pivot Table view and drop the fields I want .... it goes a lil crazy .... here is a screenshot

http://www.mts.net/~and0215/crosstab_pivot.jpg

Can someone tell what I'm doing wrong ?

Thank you in advance

 

[DCrake]

Pivot tables are not my specialty, hopefully another member can assist.

David