Function within a Function

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
 
D

Douglas J. Steele

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

JWHData

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

Douglas J. Steele

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

JWHData

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

JWHData

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query brings in dups that i don't want 1
type mismatch error in macro in excel 0
Search for maximum set of values 5
counting values 3
Remove rows where specific field contains string value 2
Undefined Function error - Complex 3
Replace or insert characters in text string 6
function based on value in another field 2

Top