formating a field within a query

[Jacqueline]

In working at a college we must pull our data from the state data management
system. I need to convert the class start time field into a true time stamp
in order to sort it correctly.

example of how the data comes to us: 0400P = 4:00 pm start time. Is there a
way to format this field at a true time within my query? I need to sort in
order of earlies time to latest time. The query runs a report that will go to
our scheduling assistants to determin if a room is available for a class.
Thanks
Jacqueline

 

[Jeff Boyce]

Jacqueline

"Formatting" doesn't alter the value, just changes how it is displayed.

That said, you could still use a query to "parse" your input data into a
data type you could more readily sort.

If you are stuck with "0400P" as your source data, take a look at using the
Left(), Right(), and Mid() functions to see about rearranging the input.
One thought, if you put the "A" or "P" leftmost in the string, could you
sort that in the order you need?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jacqueline]

Jeff,
Yes, this is an idea that might work. Unfortunatly, once the data goes to 12
PM the sort goes haywire again. If they had only made the field a time field
instead of text I would not have these probelms.... AHHHHH!

Ideas?

 

[John W. Vinson]

In working at a college we must pull our data from the state data management
system. I need to convert the class start time field into a true time stamp
in order to sort it correctly.

example of how the data comes to us: 0400P = 4:00 pm start time. Is there a
way to format this field at a true time within my query? I need to sort in
order of earlies time to latest time. The query runs a report that will go to
our scheduling assistants to determin if a room is available for a class.
Thanks
Jacqueline

Try a calculated field

CDate(Format([textdate], "@@@@\M")

This should convert 0400P to 04:00PM, 1215A to 12:15AM; and the CDate function
will convert that string to a valid date/time.


John W. Vinson [MVP]

 

[Jacqueline]

Hi John,
This makes sense to me, I never liked working with strings... parsing etc.
here is what I have input as my expression

RealStime:CDate(Format([STRT_TIME]),"@@@@\M")

following the syntax you gave me, but I keep getting "the expression you
entered has a function containing the wrong number of arguments"

Help, I would really love for this to work, it will solve a lot of problems
we have delt with for years.
Thanks
Jacqueline


--
Jacqueline

In working at a college we must pull our data from the state data management
system. I need to convert the class start time field into a true time stamp
in order to sort it correctly.

example of how the data comes to us: 0400P = 4:00 pm start time. Is there a
way to format this field at a true time within my query? I need to sort in
order of earlies time to latest time. The query runs a report that will go to
our scheduling assistants to determin if a room is available for a class.
Thanks
Jacqueline

Try a calculated field

CDate(Format([textdate], "@@@@\M")

This should convert 0400P to 04:00PM, 1215A to 12:15AM; and the CDate function
will convert that string to a valid date/time.


John W. Vinson [MVP]

 

[Jacqueline]

John, do I have to get into VB to use the CDate function? I am versed in
expressions in querys but know enough about VB to be really dangerous... and
I cannot find my way back to the VB shell in 2007... augH!
--
Jacqueline

Hi John,
This makes sense to me, I never liked working with strings... parsing etc.
here is what I have input as my expression

RealStime:CDate(Format([STRT_TIME]),"@@@@\M")

following the syntax you gave me, but I keep getting "the expression you
entered has a function containing the wrong number of arguments"

Help, I would really love for this to work, it will solve a lot of problems
we have delt with for years.
Thanks
Jacqueline


--
Jacqueline

In working at a college we must pull our data from the state data management
system. I need to convert the class start time field into a true time stamp
in order to sort it correctly.

example of how the data comes to us: 0400P = 4:00 pm start time. Is there a
way to format this field at a true time within my query? I need to sort in
order of earlies time to latest time. The query runs a report that will go to
our scheduling assistants to determin if a room is available for a class.
Thanks
Jacqueline

Try a calculated field

CDate(Format([textdate], "@@@@\M")

This should convert 0400P to 04:00PM, 1215A to 12:15AM; and the CDate function
will convert that string to a valid date/time.


John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,
This makes sense to me, I never liked working with strings... parsing etc.
here is what I have input as my expression

RealStime:CDate(Format([STRT_TIME]),"@@@@\M")

following the syntax you gave me, but I keep getting "the expression you
entered has a function containing the wrong number of arguments"

Sorry! I left out a closing parenthesis; you guessed wrong where it should
have been. Try

RealStime:CDate(Format([STRT_TIME],"@@@@\M"))


John W. Vinson [MVP]

 

[Jacqueline]

IT WORKED!!!!! You are my hero!!! I cannot tell you how much this will save
us in time messing around with the data.
Thanks so much
--
Jacqueline

Hi John,
This makes sense to me, I never liked working with strings... parsing etc.
here is what I have input as my expression

RealStime:CDate(Format([STRT_TIME]),"@@@@\M")

following the syntax you gave me, but I keep getting "the expression you
entered has a function containing the wrong number of arguments"

Sorry! I left out a closing parenthesis; you guessed wrong where it should
have been. Try

RealStime:CDate(Format([STRT_TIME],"@@@@\M"))


John W. Vinson [MVP]

 

[Jacqueline]

John,
Sorry to bug you, but I ran into another problem. The data I was using for
the first test was clean current data, when trying to apply the expression to
future data it will not work.

The culpert is "ARR" in the time slot. For classes that are in the schedule
but not assigned a time slot yet the schedulers put ARR in the field.

I thought I could convert the ARR to something that the CDate expression
could deal with first by use an IIF statement prior to the conversion, but it
is not working. Can you tell me if I am on the right track or is there a
better way, or just give up and deal with the status quo as they have done
for years!!! Ahhh, so close...

Here is the formula I am working with
RealStime:
IIf(([STRT_TIME])="ARR","000A",CDate(Format(([STRT_TIME]),"@@\@@\M")))

Here is my college email if you would like to send to direct
(e-mail address removed)
Thanks for your help
Jacqueline

--
Jacqueline

IT WORKED!!!!! You are my hero!!! I cannot tell you how much this will save
us in time messing around with the data.
Thanks so much
--
Jacqueline

Hi John,
This makes sense to me, I never liked working with strings... parsing etc.
here is what I have input as my expression

RealStime:CDate(Format([STRT_TIME]),"@@@@\M")

following the syntax you gave me, but I keep getting "the expression you
entered has a function containing the wrong number of arguments"

Sorry! I left out a closing parenthesis; you guessed wrong where it should
have been. Try

RealStime:CDate(Format([STRT_TIME],"@@@@\M"))


John W. Vinson [MVP]

 

[John W. Vinson]

John,
Sorry to bug you, but I ran into another problem. The data I was using for
the first test was clean current data, when trying to apply the expression to
future data it will not work.

The culpert is "ARR" in the time slot. For classes that are in the schedule
but not assigned a time slot yet the schedulers put ARR in the field.

I thought I could convert the ARR to something that the CDate expression
could deal with first by use an IIF statement prior to the conversion, but it
is not working. Can you tell me if I am on the right track or is there a
better way, or just give up and deal with the status quo as they have done
for years!!! Ahhh, so close...

Here is the formula I am working with
RealStime:
IIf(([STRT_TIME])="ARR","000A",CDate(Format(([STRT_TIME]),"@@\@@\M")))

I suspect that you want the IIF statement to return an actual date/time value
rather than a text string if it's "ARR". Try:

IIf(([STRT_TIME])="ARR",#12:00AM#,CDate(Format(([STRT_TIME]),"@@\@@\M")))

That way both possible return values from the IIF will have the same datatype.

Here is my college email if you would like to send to direct
<omitted>

Well, I'm a self-employed consultant donating (too much) time on the
newsgroups. As such, private email support is limited to paying customers.

It's also A Bad Idea to post your email address in clear on these newsgroups.
Too late now, but... spammers can and will harvest your address from these
public-domain groups, and you'll start getting a lot of junk mail.

John W. Vinson [MVP]

 

[John Spencer]

TRY

CDate(Format(IIF([STRT_TIME = "ARR","0001A",[STRT_TIME]),"@@@@"))

That should generate 00:01:00 AM when STRT_TIME is "ARR"
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,
Sorry to bug you, but I ran into another problem. The data I was using for
the first test was clean current data, when trying to apply the expression
to
future data it will not work.

The culpert is "ARR" in the time slot. For classes that are in the
schedule
but not assigned a time slot yet the schedulers put ARR in the field.

I thought I could convert the ARR to something that the CDate expression
could deal with first by use an IIF statement prior to the conversion, but
it
is not working. Can you tell me if I am on the right track or is there a
better way, or just give up and deal with the status quo as they have done
for years!!! Ahhh, so close...

Here is the formula I am working with
RealStime:
IIf(([STRT_TIME])="ARR","000A",CDate(Format(([STRT_TIME]),"@@\@@\M")))

Here is my college email if you would like to send to direct
(e-mail address removed)
Thanks for your help
Jacqueline

--
Jacqueline

IT WORKED!!!!! You are my hero!!! I cannot tell you how much this will
save
us in time messing around with the data.
Thanks so much
--
Jacqueline

Hi John,
This makes sense to me, I never liked working with strings... parsing
etc.
here is what I have input as my expression

RealStime:CDate(Format([STRT_TIME]),"@@@@\M")

following the syntax you gave me, but I keep getting "the expression
you
entered has a function containing the wrong number of arguments"

Sorry! I left out a closing parenthesis; you guessed wrong where it
should
have been. Try

RealStime:CDate(Format([STRT_TIME],"@@@@\M"))


John W. Vinson [MVP]

 

[Jacqueline]

Worked! John you are an angle... thanks so much have a great day.
Jacqueline
--
Jacqueline

TRY

CDate(Format(IIF([STRT_TIME = "ARR","0001A",[STRT_TIME]),"@@@@"))

That should generate 00:01:00 AM when STRT_TIME is "ARR"
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,
Sorry to bug you, but I ran into another problem. The data I was using for
the first test was clean current data, when trying to apply the expression
to
future data it will not work.

The culpert is "ARR" in the time slot. For classes that are in the
schedule
but not assigned a time slot yet the schedulers put ARR in the field.

I thought I could convert the ARR to something that the CDate expression
could deal with first by use an IIF statement prior to the conversion, but
it
is not working. Can you tell me if I am on the right track or is there a
better way, or just give up and deal with the status quo as they have done
for years!!! Ahhh, so close...

Here is the formula I am working with
RealStime:
IIf(([STRT_TIME])="ARR","000A",CDate(Format(([STRT_TIME]),"@@\@@\M")))

Here is my college email if you would like to send to direct
(e-mail address removed)
Thanks for your help
Jacqueline

--
Jacqueline

IT WORKED!!!!! You are my hero!!! I cannot tell you how much this will
save
us in time messing around with the data.
Thanks so much
--
Jacqueline


:


Hi John,
This makes sense to me, I never liked working with strings... parsing
etc.
here is what I have input as my expression

RealStime:CDate(Format([STRT_TIME]),"@@@@\M")

following the syntax you gave me, but I keep getting "the expression
you
entered has a function containing the wrong number of arguments"

Sorry! I left out a closing parenthesis; you guessed wrong where it
should
have been. Try

RealStime:CDate(Format([STRT_TIME],"@@@@\M"))


John W. Vinson [MVP]