formating a field within a query

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
0
Utf
1/8/2008 10:41:07 PM
access.queries 6343 articles. 1 followers. Follow

11 Replies
1146 Views

Similar Articles

[PageSpeed] 1

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" <jstaley@yvcc.edu> wrote in message 
news:433211E2-1002-47F8-8E5F-3E46C1BB0FBF@microsoft.com...
> 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 


0
Jeff
1/8/2008 11:40:57 PM
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? 
-- 
Jacqueline


"Jeff Boyce" wrote:

> 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" <jstaley@yvcc.edu> wrote in message 
> news:433211E2-1002-47F8-8E5F-3E46C1BB0FBF@microsoft.com...
> > 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 
> 
> 
> 
0
Utf
1/9/2008 12:22:30 AM
On Tue, 8 Jan 2008 14:41:07 -0800, Jacqueline <jstaley@yvcc.edu> wrote:

>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]
0
John
1/9/2008 2:04:04 AM
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


"John W. Vinson" wrote:

> On Tue, 8 Jan 2008 14:41:07 -0800, Jacqueline <jstaley@yvcc.edu> wrote:
> 
> >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]
> 
0
Utf
1/9/2008 5:08:12 PM
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


"Jacqueline" wrote:

> 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
> 
> 
> "John W. Vinson" wrote:
> 
> > On Tue, 8 Jan 2008 14:41:07 -0800, Jacqueline <jstaley@yvcc.edu> wrote:
> > 
> > >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]
> > 
0
Utf
1/9/2008 6:04:06 PM
On Wed, 9 Jan 2008 09:08:12 -0800, Jacqueline <jstaley@yvcc.edu> wrote:

>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]
0
John
1/9/2008 6:04:22 PM
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


"John W. Vinson" wrote:

> On Wed, 9 Jan 2008 09:08:12 -0800, Jacqueline <jstaley@yvcc.edu> wrote:
> 
> >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]
> 
0
Utf
1/9/2008 6:35:02 PM
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
jstaley@yvcc.edu
Thanks for your help
Jacqueline

-- 
Jacqueline


"Jacqueline" wrote:

> 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
> 
> 
> "John W. Vinson" wrote:
> 
> > On Wed, 9 Jan 2008 09:08:12 -0800, Jacqueline <jstaley@yvcc.edu> wrote:
> > 
> > >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]
> > 
0
Utf
1/10/2008 6:06:01 PM
On Thu, 10 Jan 2008 10:06:01 -0800, Jacqueline <jstaley@yvcc.edu> wrote:

>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]

0
John
1/10/2008 8:19:07 PM

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

"Jacqueline" <jstaley@yvcc.edu> wrote in message 
news:CC1347E7-A314-404E-8990-BF8347D06F6F@microsoft.com...
> 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
> jstaley@yvcc.edu
> Thanks for your help
> Jacqueline
>
> -- 
> Jacqueline
>
>
> "Jacqueline" wrote:
>
>> 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
>>
>>
>> "John W. Vinson" wrote:
>>
>> > On Wed, 9 Jan 2008 09:08:12 -0800, Jacqueline <jstaley@yvcc.edu> wrote:
>> >
>> > >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]
>> > 


0
John
1/10/2008 8:19:55 PM
Worked! John you are an angle... thanks so much have a great day.
Jacqueline
-- 
Jacqueline


"John Spencer" wrote:

> 
> 
> 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
> ..
> 
> "Jacqueline" <jstaley@yvcc.edu> wrote in message 
> news:CC1347E7-A314-404E-8990-BF8347D06F6F@microsoft.com...
> > 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
> > jstaley@yvcc.edu
> > Thanks for your help
> > Jacqueline
> >
> > -- 
> > Jacqueline
> >
> >
> > "Jacqueline" wrote:
> >
> >> 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
> >>
> >>
> >> "John W. Vinson" wrote:
> >>
> >> > On Wed, 9 Jan 2008 09:08:12 -0800, Jacqueline <jstaley@yvcc.edu> wrote:
> >> >
> >> > >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]
> >> > 
> 
> 
> 
0
Utf
1/10/2008 9:57:00 PM
Reply:

Similar Artilces: