Need function to pull out date in middle of field in Access97 query designer

  • Thread starter Thread starter Mike Wright
  • Start date Start date
M

Mike Wright

Hello,

I am very novice in MS Access97.

I have a query that returns the following info in this
format:

Fx 12345 m/dd/ccyy hh:mm:ss PM

So my data looks like the following:

Fx 60321 7/3/2003 12:42:59 PM
Fx 32 8/5/2003 3:23:10 AM

- The 'Fx' is constant
- The number following changes in size
- The date is a constant mm/dd/ccyy format
- The time is a contstant hh:mm:ss format
- The time of day is always AM or PM

I am trying to create a query that will only pull
information from 07/01/2003 to 07/31/2003, but am having
difficulty because of all the other information before and
after the date.

Is there a function that I can use in my query designer to
only return rows in July of 2003?

Sorry if this is such a novice question.
 
Here's one solution:

Create a new field in your query with the following:


Expr2: CDate(Mid([myinfo],InStr(InStr(1,[MyInfo]," ")+1,[myinfo],"
")+1,InStr(InStr(InStr(1,[MyInfo]," ")+1,[myinfo]," ")+1,[myinfo],"
")-InStr(InStr(1,[MyInfo]," ")+1,[myinfo]," ")))


in your criteria, use:

Between #01/07/03# And #31/07/03#

(Be sure to change the above if you are using US style dates)

P
 
Fx 60321 7/3/2003 12:42:59 PM
Fx 32 8/5/2003 3:23:10 AM

- The 'Fx' is constant
- The number following changes in size
- The date is a constant mm/dd/ccyy format
- The time is a contstant hh:mm:ss format
- The time of day is always AM or PM

I am trying to create a query that will only pull
information from 07/01/2003 to 07/31/2003, but am having
difficulty because of all the other information before and
after the date.

Is there a function that I can use in my query designer to
only return rows in July of 2003?
Click to expand...

Add a new column to your query (watch line wrap):

DateSelect: Format(CDate(mid([FxField],instr([FxField],"/")-2)), "m/d/yyyy")

And set the criteria to:

(If hard-coding)

Between #7/1/03# and #7/31/03#

(If you just want the current month - again, watch line wrap)

Between DateSerial(Year(Date()), Month(Date()), 1) And
DateSerial(Year(Date()), Month(Date()) + 1, 0)
 
Well actually I figured it out, in a little different
manner but here is what I came up with.

Expr1: CDate(LTrim(Mid([dbo_Toc]![Name],InStr([dbo_Toc]!
[Name],'/')-2,10)))

Now my problem is that when I do my selection, it will not
let me do any type of lookup.

I.E. if I put:

=#07/14/2003# I get some type of Access error.

Can you not do a selection on an Expression field?
 
Back
Top