Re: sort report by year (weird format)
- From: mpjm@xxxxxxxxxxxxx
- Date: 18 Jul 2006 08:33:40 -0700
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"
Marshall Barton wrote:
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]
mpjm@xxxxxxxxxxxxx wrote:
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.
mpjm@xxxxxxxxxxxxx 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]*"
mpjm@xxxxxxxxxxxxx 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
mpjm@xxxxxxxxxxxxx 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)
.
- Follow-Ups:
- Re: sort report by year (weird format)
- From: mpjm
- Re: sort report by year (weird format)
- Prev by Date: Re: Pop-up/Modal Reports in Access 2000
- Next by Date: Re: Report Formatting not Holding
- Previous by thread: Re: beefing up reports and queries
- Next by thread: Re: sort report by year (weird format)
- Index(es):
Relevant Pages
|
