sort report by year (weird format)

[mpjm]

thanks - i'll look into what you mentioned and let you know how it
turned out. there could be errors in data entry because the original DB
was not set up to perform error/validity checking

I don't see how the CInt can cause that error and "work" at
the same time. AFAIK, CInt will only generate that error if
the string it's trying to convert has a non-numeric
character in it. Double check your data in the report's
record source to see if there is an invalid entry. If the
table has a significant number of records, you might want to
use a query to look for invalid entries in this field:

SELECT *
FROM [put name of your table here]
WHERE prop_name LIKE "*[!1-9]*"
--
Marsh
MVP [MS Access]

i get the error "data type mismatch" which could mean that we're using
the wrong function (CInt) - any other one you know of that will work
for text fields?

that piece of code works, just i get a data type mismatch

 

[mpjm]

well i suppose there is one thing i forgot to mention that just dawned
on me as i looked at the query results --- there is a "P" preceding the
dates in prop_num --- this is to signify a proposal. i am going to
work with the expression you gave me and try to get it working on my
own, but can anyone post something here on how to do it?

thanks

I don't see how the CInt can cause that error and "work" at
the same time. AFAIK, CInt will only generate that error if
the string it's trying to convert has a non-numeric
character in it. Double check your data in the report's
record source to see if there is an invalid entry. If the
table has a significant number of records, you might want to
use a query to look for invalid entries in this field:

SELECT *
FROM [put name of your table here]
WHERE prop_name LIKE "*[!1-9]*"
--
Marsh
MVP [MS Access]

i get the error "data type mismatch" which could mean that we're using
the wrong function (CInt) - any other one you know of that will work
for text fields?

that piece of code works, just i get a data type mismatch

 

[Marshall Barton]

That is a really critical bit of information that you
"forgot to mention". Maybe now, it will focus your
attention on why the rules of Database Normalization require
a field in a table to have a single value. If the proposal
indicator was in a field of its own, this latest issue would
not exist.

You will have to eliminate the P in order to sort by the
date field. If you can not rectify the table's design, you
will have to fudge things in the report's record source
query. I look at this as digging your hole deeper ibstead
of fixing the problem, which reminds of the sage advice
"When you find yourself in over your head, Stop Digging"
;-)

The quick and dirty fix using the query consists of adding
two calculated fields to the query.

Proposal: IIf(Left(prop_name, 1) Like "#", Null, "P")
PropDate: IIf(Left(prop_name, 1) Like "#", prop_name,
Mid(prop_name,2)

Then use these fields in the report instead of the prop_name
field.
--
Marsh
MVP [MS Access]

well i suppose there is one thing i forgot to mention that just dawned
on me as i looked at the query results --- there is a "P" preceding the
dates in prop_num --- this is to signify a proposal. i am going to
work with the expression you gave me and try to get it working on my
own, but can anyone post something here on how to do it?

I don't see how the CInt can cause that error and "work" at
the same time. AFAIK, CInt will only generate that error if
the string it's trying to convert has a non-numeric
character in it. Double check your data in the report's
record source to see if there is an invalid entry. If the
table has a significant number of records, you might want to
use a query to look for invalid entries in this field:

SELECT *
FROM [put name of your table here]
WHERE prop_name LIKE "*[!1-9]*"

i get the error "data type mismatch" which could mean that we're using
the wrong function (CInt) - any other one you know of that will work
for text fields?

that piece of code works, just i get a data type mismatch

(e-mail address removed) wrote:
CInt is for integers, but the prop_num field is text
what should i use?
i'm looking on the internet for another one

Marshall Barton wrote:
Arrggghhh, it's one of those things you have to look at 20
times to see the problem. There is a missing ) for the CInt
function.

=IIf(CInt(Left(prop_name,2)) >= 30, "19" & prop_name, "20" &
prop_name)

 

[mpjm]

thanks for the response - honestly i didn't notice the P in the front
of the dates because of how the data shows up on the forms (which are
what i primarily look at) -- i appreciate your response and your help,
please try to bear with me as these databases are very old and "out of
shape" - this is my first experience creating and maintaing a MSaccess
database - if could have designed it on my own from the beginning i
wouldn't have these problems. again thanks for the help!

That is a really critical bit of information that you
"forgot to mention". Maybe now, it will focus your
attention on why the rules of Database Normalization require
a field in a table to have a single value. If the proposal
indicator was in a field of its own, this latest issue would
not exist.

You will have to eliminate the P in order to sort by the
date field. If you can not rectify the table's design, you
will have to fudge things in the report's record source
query. I look at this as digging your hole deeper ibstead
of fixing the problem, which reminds of the sage advice
"When you find yourself in over your head, Stop Digging"
;-)

The quick and dirty fix using the query consists of adding
two calculated fields to the query.

Proposal: IIf(Left(prop_name, 1) Like "#", Null, "P")
PropDate: IIf(Left(prop_name, 1) Like "#", prop_name,
Mid(prop_name,2)

Then use these fields in the report instead of the prop_name
field.
--
Marsh
MVP [MS Access]

well i suppose there is one thing i forgot to mention that just dawned
on me as i looked at the query results --- there is a "P" preceding the
dates in prop_num --- this is to signify a proposal. i am going to
work with the expression you gave me and try to get it working on my
own, but can anyone post something here on how to do it?

I don't see how the CInt can cause that error and "work" at
the same time. AFAIK, CInt will only generate that error if
the string it's trying to convert has a non-numeric
character in it. Double check your data in the report's
record source to see if there is an invalid entry. If the
table has a significant number of records, you might want to
use a query to look for invalid entries in this field:

SELECT *
FROM [put name of your table here]
WHERE prop_name LIKE "*[!1-9]*"


(e-mail address removed) wrote:

i get the error "data type mismatch" which could mean that we're using
the wrong function (CInt) - any other one you know of that will work
for text fields?

that piece of code works, just i get a data type mismatch


(e-mail address removed) wrote:
CInt is for integers, but the prop_num field is text
what should i use?
i'm looking on the internet for another one

Marshall Barton wrote:
Arrggghhh, it's one of those things you have to look at 20
times to see the problem. There is a missing ) for the CInt
function.

=IIf(CInt(Left(prop_name,2)) >= 30, "19" & prop_name, "20" &
prop_name)

 

[Marshall Barton]

Sorry about the chiding tone, I was really trying to point
out the source of the problem and how it could have been
done "correctly".

Sure, I'll stick with it. Where do things stand at this
point?
--
Marsh
MVP [MS Access]

thanks for the response - honestly i didn't notice the P in the front
of the dates because of how the data shows up on the forms (which are
what i primarily look at) -- i appreciate your response and your help,
please try to bear with me as these databases are very old and "out of
shape" - this is my first experience creating and maintaing a MSaccess
database - if could have designed it on my own from the beginning i
wouldn't have these problems. again thanks for the help!

That is a really critical bit of information that you
"forgot to mention". Maybe now, it will focus your
attention on why the rules of Database Normalization require
a field in a table to have a single value. If the proposal
indicator was in a field of its own, this latest issue would
not exist.

You will have to eliminate the P in order to sort by the
date field. If you can not rectify the table's design, you
will have to fudge things in the report's record source
query. I look at this as digging your hole deeper ibstead
of fixing the problem, which reminds of the sage advice
"When you find yourself in over your head, Stop Digging"
;-)

The quick and dirty fix using the query consists of adding
two calculated fields to the query.

Proposal: IIf(Left(prop_name, 1) Like "#", Null, "P")
PropDate: IIf(Left(prop_name, 1) Like "#", prop_name,
Mid(prop_name,2)

Then use these fields in the report instead of the prop_name
field.

well i suppose there is one thing i forgot to mention that just dawned
on me as i looked at the query results --- there is a "P" preceding the
dates in prop_num --- this is to signify a proposal. i am going to
work with the expression you gave me and try to get it working on my
own, but can anyone post something here on how to do it?


Marshall Barton wrote:
I don't see how the CInt can cause that error and "work" at
the same time. AFAIK, CInt will only generate that error if
the string it's trying to convert has a non-numeric
character in it. Double check your data in the report's
record source to see if there is an invalid entry. If the
table has a significant number of records, you might want to
use a query to look for invalid entries in this field:

SELECT *
FROM [put name of your table here]
WHERE prop_name LIKE "*[!1-9]*"


(e-mail address removed) wrote:

i get the error "data type mismatch" which could mean that we're using
the wrong function (CInt) - any other one you know of that will work
for text fields?

that piece of code works, just i get a data type mismatch


(e-mail address removed) wrote:
CInt is for integers, but the prop_num field is text
what should i use?
i'm looking on the internet for another one

Marshall Barton wrote:
Arrggghhh, it's one of those things you have to look at 20
times to see the problem. There is a missing ) for the CInt
function.

=IIf(CInt(Left(prop_name,2)) >= 30, "19" & prop_name, "20" &
prop_name)

 

[mpjm]

its okay - i get the error "data type mismatch in criteria expression"
(i think because of the "P")

i am going to try and get the "P" parsed off (any ideas of how to do
this? can i use a query?) and just have the dates so i can perform the
sorting operation, then just have a "P" in a text box on the form...but
the fun part here is of course parsing off the preceding "P"

Sorry about the chiding tone, I was really trying to point
out the source of the problem and how it could have been
done "correctly".

Sure, I'll stick with it. Where do things stand at this
point?
--
Marsh
MVP [MS Access]

thanks for the response - honestly i didn't notice the P in the front
of the dates because of how the data shows up on the forms (which are
what i primarily look at) -- i appreciate your response and your help,
please try to bear with me as these databases are very old and "out of
shape" - this is my first experience creating and maintaing a MSaccess
database - if could have designed it on my own from the beginning i
wouldn't have these problems. again thanks for the help!

That is a really critical bit of information that you
"forgot to mention". Maybe now, it will focus your
attention on why the rules of Database Normalization require
a field in a table to have a single value. If the proposal
indicator was in a field of its own, this latest issue would
not exist.

You will have to eliminate the P in order to sort by the
date field. If you can not rectify the table's design, you
will have to fudge things in the report's record source
query. I look at this as digging your hole deeper ibstead
of fixing the problem, which reminds of the sage advice
"When you find yourself in over your head, Stop Digging"
;-)

The quick and dirty fix using the query consists of adding
two calculated fields to the query.

Proposal: IIf(Left(prop_name, 1) Like "#", Null, "P")
PropDate: IIf(Left(prop_name, 1) Like "#", prop_name,
Mid(prop_name,2)

Then use these fields in the report instead of the prop_name
field.


(e-mail address removed) wrote:
well i suppose there is one thing i forgot to mention that just dawned
on me as i looked at the query results --- there is a "P" preceding the
dates in prop_num --- this is to signify a proposal. i am going to
work with the expression you gave me and try to get it working on my
own, but can anyone post something here on how to do it?


Marshall Barton wrote:
I don't see how the CInt can cause that error and "work" at
the same time. AFAIK, CInt will only generate that error if
the string it's trying to convert has a non-numeric
character in it. Double check your data in the report's
record source to see if there is an invalid entry. If the
table has a significant number of records, you might want to
use a query to look for invalid entries in this field:

SELECT *
FROM [put name of your table here]
WHERE prop_name LIKE "*[!1-9]*"


(e-mail address removed) wrote:

i get the error "data type mismatch" which could mean that we're using
the wrong function (CInt) - any other one you know of that will work
for text fields?

that piece of code works, just i get a data type mismatch


(e-mail address removed) wrote:
CInt is for integers, but the prop_num field is text
what should i use?
i'm looking on the internet for another one

Marshall Barton wrote:
Arrggghhh, it's one of those things you have to look at 20
times to see the problem. There is a missing ) for the CInt
function.

=IIf(CInt(Left(prop_name,2)) >= 30, "19" & prop_name, "20" &
prop_name)

 

[mpjm]

thanks to everyone who helped me - i finally got it working and here's
how...

i created an update query to test first to see if i could properly
parse off the "P" that precedes the date and it worked leaving me with
the new date format

then i added the expression
=IIf(CInt(Left(prop_name,2)) >= 30, "19" & prop_name, "20" &
to my sorting and grouping in my report and it works.

thanks a lot guys!

prop_name)

its okay - i get the error "data type mismatch in criteria expression"
(i think because of the "P")

i am going to try and get the "P" parsed off (any ideas of how to do
this? can i use a query?) and just have the dates so i can perform the
sorting operation, then just have a "P" in a text box on the form...but
the fun part here is of course parsing off the preceding "P"

Sorry about the chiding tone, I was really trying to point
out the source of the problem and how it could have been
done "correctly".

Sure, I'll stick with it. Where do things stand at this
point?
--
Marsh
MVP [MS Access]

thanks for the response - honestly i didn't notice the P in the front
of the dates because of how the data shows up on the forms (which are
what i primarily look at) -- i appreciate your response and your help,
please try to bear with me as these databases are very old and "out of
shape" - this is my first experience creating and maintaing a MSaccess
database - if could have designed it on my own from the beginning i
wouldn't have these problems. again thanks for the help!


Marshall Barton wrote:
That is a really critical bit of information that you
"forgot to mention". Maybe now, it will focus your
attention on why the rules of Database Normalization require
a field in a table to have a single value. If the proposal
indicator was in a field of its own, this latest issue would
not exist.

You will have to eliminate the P in order to sort by the
date field. If you can not rectify the table's design, you
will have to fudge things in the report's record source
query. I look at this as digging your hole deeper ibstead
of fixing the problem, which reminds of the sage advice
"When you find yourself in over your head, Stop Digging"
;-)

The quick and dirty fix using the query consists of adding
two calculated fields to the query.

Proposal: IIf(Left(prop_name, 1) Like "#", Null, "P")
PropDate: IIf(Left(prop_name, 1) Like "#", prop_name,
Mid(prop_name,2)

Then use these fields in the report instead of the prop_name
field.


(e-mail address removed) wrote:
well i suppose there is one thing i forgot to mention that just dawned
on me as i looked at the query results --- there is a "P" preceding the
dates in prop_num --- this is to signify a proposal. i am going to
work with the expression you gave me and try to get it working on my
own, but can anyone post something here on how to do it?


Marshall Barton wrote:
I don't see how the CInt can cause that error and "work" at
the same time. AFAIK, CInt will only generate that error if
the string it's trying to convert has a non-numeric
character in it. Double check your data in the report's
record source to see if there is an invalid entry. If the
table has a significant number of records, you might want to
use a query to look for invalid entries in this field:

SELECT *
FROM [put name of your table here]
WHERE prop_name LIKE "*[!1-9]*"


(e-mail address removed) wrote:

i get the error "data type mismatch" which could mean that we're using
the wrong function (CInt) - any other one you know of that will work
for text fields?

that piece of code works, just i get a data type mismatch


(e-mail address removed) wrote:
CInt is for integers, but the prop_num field is text
what should i use?
i'm looking on the internet for another one

Marshall Barton wrote:
Arrggghhh, it's one of those things you have to look at 20
times to see the problem. There is a missing ) for the CInt
function.

=IIf(CInt(Left(prop_name,2)) >= 30, "19" & prop_name, "20" &
prop_name)