is this correct? select * from abc where mid(date,4,2) = '12' ???

[Christopher]

hi, i was wondering if the sql above correct or not?

regards,
Chris

 

[Tom Ellison]

Dear Christopher:

If [date] is a text value, then it is fine, assuming what it does is
what you mean to do.

I suspect the value is a date/time datatype. If so, and you are
testing for December, I recommend:

select * from abc where MONTH([date]) = 12

that will extract the number of the month from a date that is, at
least, more intuitive to read.

So, being "correct" could just mean it does not error. Without
knowing what kind of data is stored in [date] or what you mean to do
with it (I was just guessing you were looking for December in
'20031210') I cannot really tell if it is logically correct. Does it
give you an error? Does it not behave the way you expected?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Christopher]

Hi Tom,

Thanks for your reply. Sorry for din provide u enough details.
date is a text data type.
the data in db is '20031210'
there is no compilation or execution error for this sql error.
it din give me correct result. it will direct go to eof.....

any idea?

Dear Christopher:

If [date] is a text value, then it is fine, assuming what it does is
what you mean to do.

I suspect the value is a date/time datatype. If so, and you are
testing for December, I recommend:

select * from abc where MONTH([date]) = 12

that will extract the number of the month from a date that is, at
least, more intuitive to read.

So, being "correct" could just mean it does not error. Without
knowing what kind of data is stored in [date] or what you mean to do
with it (I was just guessing you were looking for December in
'20031210') I cannot really tell if it is logically correct. Does it
give you an error? Does it not behave the way you expected?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

hi, i was wondering if the sql above correct or not?

regards,
Chris

 

[Tom Ellison]

Dear Christopher:

After turning by brain on, what would be the result of this:

mid("20031210", 4, 2)

It would be "31"!!!!

Try mid("20031210", 5, 2). Much better. Sometimes I just can't count
that high.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom,

Thanks for your reply. Sorry for din provide u enough details.
date is a text data type.
the data in db is '20031210'
there is no compilation or execution error for this sql error.
it din give me correct result. it will direct go to eof.....

any idea?

Dear Christopher:

If [date] is a text value, then it is fine, assuming what it does is
what you mean to do.

I suspect the value is a date/time datatype. If so, and you are
testing for December, I recommend:

select * from abc where MONTH([date]) = 12

that will extract the number of the month from a date that is, at
least, more intuitive to read.

So, being "correct" could just mean it does not error. Without
knowing what kind of data is stored in [date] or what you mean to do
with it (I was just guessing you were looking for December in
'20031210') I cannot really tell if it is logically correct. Does it
give you an error? Does it not behave the way you expected?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

hi, i was wondering if the sql above correct or not?

regards,
Chris