By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

sorting and grouping

P: 65
i have a piece of code to use in the sorting and grouping box as an expression...just not sure why i'm getting a certain error on it. any ideas?

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

i get the error "the expression you entered has a function containing the wrong number of arguments."


HELP!
Jul 12 '06 #1
Share this Question
Share on Google+
9 Replies


100+
P: 179
It looks like you just left out a closing bracket. You should have gotten a syntax error though. I added the following code to the OnClick event of a button and it worked:

Private Sub Command3_Click()
Dim T, prop_num As Integer
prop_num = 25
Test = IIf(CInt(Left(prop_num, 2)) >= 30, "19" & prop_num, "20" & prop_num)
Me.Check1 = Test
End Sub

I'm assuming that you do realize that IIf doesn't work quite the same as "If Then". IIf is normally used in an expression, like the one above.

comteck
Jul 12 '06 #2

P: 65
i'm not putting this in a button, but in the sorting and grouping box under "field/expression" in a report.

the report should just have to be opened and be sorted the way the expression intends to.
Jul 12 '06 #3

100+
P: 179
Sorry.. I should have read that from the beginning. But, I went and entered the expression into the sorting and grouping box as well, and I got the same error message:

"the expression you entered has a function containing the wrong number of arguments."

Again, I found the problem to be the extra closing bracket was missing. I added the bracket, and received no error message. I added the closing bracket at the end of (Left(prop_num, 2), so the final expression is:

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

comteck
Jul 13 '06 #4

P: 65
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

thanks!
Jul 13 '06 #5

100+
P: 179
You didn't really indicate how or what it's being used in. What is the data type? And what is this formula being used for? Could you please provide more details.

comteck
Jul 13 '06 #6

P: 65
the IIF expression is supposed to convert the first two characters in my "date" string to a number so we can compare it to to the number 30. This whole exercise should end up converting my text field from something like:

990102 to 19990102
051130 to 20051130


what i have neglected to tell you all (and it is my own fault) is that there is a preceding "P" before any numbers - how can i work around this??

it is a TEXT field
Jul 17 '06 #7

100+
P: 179
I'm sorry, but I'm a little confused. It seems like you want to convert the date from 2 digit year to a 4 digit year (990103 to 19990103). Please let me know if I read this wrong.

If this is thew case, I don't see why you're comparing the 2 left most characters to 30.

Also, you said there's a P in front of any numbers. Does that mean it would read:

P990103
P050104
and so on.

If there is a letter character in your string, I don't think CINT will work because CINT takes a number (i.e 201.34) and converts it to an integer (201).

Let me know if I'm way off track. If I'm not, then we may have to look at an alternate formula. You may have to remove the "P" and then convert the number that remains.

comteck
Jul 17 '06 #8

P: 65
yeah, i had an access guroo look at what was wrong and they gave me that expression to try, only i did not notice the "P" preceding the dates (bc of how it appears in the forms - my primary viewing source).

the expression should work to convert the date, its just getting the "P" off - ideas? i'm going to try a query or something
Jul 18 '06 #9

P: 65
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!
Jul 18 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.