Function within a Function

  • Thread starter Thread starter JWHData
  • Start date Start date
J

JWHData

The current queries (which used to run which seems unbelievable now) utilizes
1 data table (Daily) containing 1 Data Element (F1) which is set to Text 255

Here is a sample data values:
F1
Suncor 103 Edson 10-13-53-15_EMKTC_20080825_DayCost.PDF

Here are the 2 Query values that produce errors (Invalid Procedure Call)

Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)

If I make a slight change to each query I obtain results

Well: Mid([F1],1,(InStr(1,[F1],"2008")))
R_Key: Mid([F1],(InStr(1,[F1],"2008")),5)

Well R_Key
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080

Also if I add the following 2 values to the query I get results

F2: InStr(1,[F1],"2008")-8
F3: InStr(1,[F1],"2008")-6

Well R_Key F2 F3
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080 28 30
 
Did you have a question? There doesn't seem to be one in what you've
posted...
 
My Question is "Why do my 2 Queries fail? and how to reshape them so that
they produce valid results ?"

Here are the 2 Query values that produce errors (Invalid Procedure Call)
Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)
Click to expand...


Douglas J. Steele said:
Did you have a question? There doesn't seem to be one in what you've
posted...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JWHData said:
The current queries (which used to run which seems unbelievable now)
utilizes
1 data table (Daily) containing 1 Data Element (F1) which is set to Text
255

Here is a sample data values:
F1
Suncor 103 Edson 10-13-53-15_EMKTC_20080825_DayCost.PDF

Here are the 2 Query values that produce errors (Invalid Procedure Call)

Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)

If I make a slight change to each query I obtain results

Well: Mid([F1],1,(InStr(1,[F1],"2008")))
R_Key: Mid([F1],(InStr(1,[F1],"2008")),5)

Well R_Key
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080

Also if I add the following 2 values to the query I get results

F2: InStr(1,[F1],"2008")-8
F3: InStr(1,[F1],"2008")-6

Well R_Key F2 F3
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080 28 30
Click to expand...
Click to expand...
 
Does F1 always have a value with 2008 in it? If not, the InStr function is
going to result in 0, so that you'll be using negative values in your Mid
function call.

If it doesn't always have 2008 in, what do you want for those values of F1
that don't have 2008?

Well: IIf(InStr([F1], "2008") > 0, Left([F1],InStr([F1],"2008")-8)), [F1])
R_Key: IIf(InStr([F1], "2008") > 0, Mid([F1],(InStr(1,[F1],"2008")-6),5),
[F1])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JWHData said:
My Question is "Why do my 2 Queries fail? and how to reshape them so that
they produce valid results ?"

Here are the 2 Query values that produce errors (Invalid Procedure Call)
Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)
Click to expand...


Douglas J. Steele said:
Did you have a question? There doesn't seem to be one in what you've
posted...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JWHData said:
The current queries (which used to run which seems unbelievable now)
utilizes
1 data table (Daily) containing 1 Data Element (F1) which is set to
Text
255

Here is a sample data values:
F1
Suncor 103 Edson 10-13-53-15_EMKTC_20080825_DayCost.PDF

Here are the 2 Query values that produce errors (Invalid Procedure
Call)

Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)

If I make a slight change to each query I obtain results

Well: Mid([F1],1,(InStr(1,[F1],"2008")))
R_Key: Mid([F1],(InStr(1,[F1],"2008")),5)

Well R_Key
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080

Also if I add the following 2 values to the query I get results

F2: InStr(1,[F1],"2008")-8
F3: InStr(1,[F1],"2008")-6

Well R_Key F2 F3
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080 28 30
Click to expand...
Click to expand...
Click to expand...
 
F1 always has the current year (in this case 2008) as part of the string

Douglas J. Steele said:
Does F1 always have a value with 2008 in it? If not, the InStr function is
going to result in 0, so that you'll be using negative values in your Mid
function call.

If it doesn't always have 2008 in, what do you want for those values of F1
that don't have 2008?

Well: IIf(InStr([F1], "2008") > 0, Left([F1],InStr([F1],"2008")-8)), [F1])
R_Key: IIf(InStr([F1], "2008") > 0, Mid([F1],(InStr(1,[F1],"2008")-6),5),
[F1])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JWHData said:
My Question is "Why do my 2 Queries fail? and how to reshape them so that
they produce valid results ?"

Here are the 2 Query values that produce errors (Invalid Procedure Call)
Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)
Click to expand...


Douglas J. Steele said:
Did you have a question? There doesn't seem to be one in what you've
posted...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


The current queries (which used to run which seems unbelievable now)
utilizes
1 data table (Daily) containing 1 Data Element (F1) which is set to
Text
255

Here is a sample data values:
F1
Suncor 103 Edson 10-13-53-15_EMKTC_20080825_DayCost.PDF

Here are the 2 Query values that produce errors (Invalid Procedure
Call)

Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)

If I make a slight change to each query I obtain results

Well: Mid([F1],1,(InStr(1,[F1],"2008")))
R_Key: Mid([F1],(InStr(1,[F1],"2008")),5)

Well R_Key
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080

Also if I add the following 2 values to the query I get results

F2: InStr(1,[F1],"2008")-8
F3: InStr(1,[F1],"2008")-6

Well R_Key F2 F3
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080 28 30
Click to expand...
Click to expand...
Click to expand...
 
Whoops Douglas,

My last statement was wrong. You were right - The problem was that 1
database record didn't contain "2008". Once I replaced my statements with
your corrected IF statements everything started to work again.

THANKS

JWHData said:
F1 always has the current year (in this case 2008) as part of the string

Douglas J. Steele said:
Does F1 always have a value with 2008 in it? If not, the InStr function is
going to result in 0, so that you'll be using negative values in your Mid
function call.

If it doesn't always have 2008 in, what do you want for those values of F1
that don't have 2008?

Well: IIf(InStr([F1], "2008") > 0, Left([F1],InStr([F1],"2008")-8)), [F1])
R_Key: IIf(InStr([F1], "2008") > 0, Mid([F1],(InStr(1,[F1],"2008")-6),5),
[F1])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


JWHData said:
My Question is "Why do my 2 Queries fail? and how to reshape them so that
they produce valid results ?"

Here are the 2 Query values that produce errors (Invalid Procedure Call)

Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)


:

Did you have a question? There doesn't seem to be one in what you've
posted...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


The current queries (which used to run which seems unbelievable now)
utilizes
1 data table (Daily) containing 1 Data Element (F1) which is set to
Text
255

Here is a sample data values:
F1
Suncor 103 Edson 10-13-53-15_EMKTC_20080825_DayCost.PDF

Here are the 2 Query values that produce errors (Invalid Procedure
Call)

Well: Mid([F1],1,(InStr(1,[F1],"2008")-8))
R_Key: Mid([F1],(InStr(1,[F1],"2008")-6),5)

If I make a slight change to each query I obtain results

Well: Mid([F1],1,(InStr(1,[F1],"2008")))
R_Key: Mid([F1],(InStr(1,[F1],"2008")),5)

Well R_Key
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080

Also if I add the following 2 values to the query I get results

F2: InStr(1,[F1],"2008")-8
F3: InStr(1,[F1],"2008")-6

Well R_Key F2 F3
Suncor 103 Edson 10-13-53-15_EMKTC_2 20080 28 30
Click to expand...
Click to expand...
Click to expand...
 
Back
Top