473,320 Members | 2,177 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

sorting and grouping

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
9 3583
comteck
179 100+
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
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
comteck
179 100+
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
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
comteck
179 100+
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
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
comteck
179 100+
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
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
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

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

Similar topics

1
by: MLH | last post by:
I have a challenge... In a table I call tblStuff4Letters with 3 fields: , and . I have a report called rptOutboundCorrespondence that feeds off this table. Sorting & grouping is turned on in...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
1
by: Jon via AccessMonster.com | last post by:
Hi Guys, My "Sorting and Grouping" in my report looks like this: Field/Expression Sort Order sID Ascending ((= Project Name Ascending User *** ...
4
by: Marie | last post by:
My report has a text field named ItemNum. Most records have a value for ItemNum. I set Grouping And Sorting to sort ascending on the ItemNum field. The records where ItemNum is Null appear at the...
5
by: Mike | last post by:
Hello All, I have a report based upon a query. I have added a control to report footer that calcs the total cost of the inventory: =SUM(). When this total calculation is NOT on the report,...
8
by: nn0410 | last post by:
I have a report whose record source is a query. The query includes an ORDER BY clause that sorts on a particular set of columns. I would like to be able to run the same report with the same input...
6
by: Christoph | last post by:
I'm trying to come up with a stylesheet where, when the rows are displayed, duplicate game names are not shown on subsequent rows. It works but doesn't work properly. If I sort the data using...
3
by: Jimmy | last post by:
Is there a way to sort/group a report based on the second column of a combo box, i.e. the text associated with the primary key number?
3
by: Don | last post by:
I have a "Report" that is created from a "Form". It prints a list of items, you may consider it a shopping list. In any event I use to run this in alphabetical order but have since decided to run...
4
by: kstevens | last post by:
I have a report, and on the report is a min, avg, and max query each with (ok who really cares) 20 or so records. At the footer of the report i would like to have a total. I requery for each...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.