Query Help

**ncurran217** · 02-09-2012, 04:48 PM

Ok I do not know if my title is ok for what I am asking about. I am trying to take a list of mileages and mark them as a certain category. So here is some examples:

These mileages
35833
39861
35107
59933
48913
20197

Have to fall into these categories:
24000
36000
50000
60000

So the mileages are each a separate record and in the column next to it I want to have one of the categories. So for the first one the record is 35833, the column next to it should show 36000.

Let me know if this is possible or should be through a Query.

**alansidman** · 02-09-2012, 05:00 PM

Will there be only four categories?

**ncurran217** · 02-09-2012, 05:52 PM

Well in the end there will be alot of different ones depending on brand and a type of that brand. If that makes sense

**alansidman** · 02-09-2012, 06:30 PM

This could be done in a query, but would require nested iif statements. I think it would be better to do it in a form with a Select Case Statement in VBA.

Here are links to both types of tutorials.

Nested IIF http://office.microsoft.com/en-us/ac...001228853.aspx

Select Case http://www.techonthenet.com/access/f...anced/case.php

**ncurran217** · 02-10-2012, 10:22 AM

Ok so want to make sure I can do this as well, And statements within either the IIF statements or the select case statement? So in excel it would look like this:

IF(AND(A2="AADIAM",B2<24001),24000,IF(AND(A2="AADIAM",B2>24000,B2<36001),36000, and so on...

Is using that and statement possible still in the IIF statements in access and or the select case statement?

**ncurran217** · 02-10-2012, 10:35 AM

Ok, so I got the Nested IIF statement to work. Found the EVAL formula.

**ncurran217** · 02-10-2012, 10:50 AM

Well my IIF statement is more than 1024 letters, so can the select case statement do the same Eval with AND statements?

Yea I am lost with the select case statement.

How do I reference the different columns? Let me know if you want some examples of what I would be doing with this. Thanks again for the help!

**alansidman** · 02-10-2012, 11:05 AM

You have to use the select case in VBA. Which means you will need to use a form to get this information. This also then opens up the possibility of using IF-THEN--ELSE statements in VBA which resemble in concept the IIF statement, but has to be written in code. There is no limitation on length or number of IF statements in either of these.

Just thought of another angle that you might be able to do in a query. Try using the Choose function. Here is a link on how it works.

http://www.techonthenet.com/access/f...ced/choose.php

This may be the easiest of all to do.

Alan

**ncurran217** · 02-10-2012, 11:15 AM

I do not know if how that will work with what I am doing...here is the Excel version of what I am doing:

I can do it either this way:

IF(AND(A2="AADIAM",B2<24001),24000,IF(AND(A2="AADIAM",B2>24000,B2<36001),36000, and so on...

or

I have setup and INDEX and MATCH formula to look up off a list. Which that formula looks like this:

{=INDEX(maxpurchmiles,MATCH(1,INDEX((pmcoverages=AI2)*(maxpurchmiles>=N2),0),0))}

Let me know what you think!

**alansidman** · 02-10-2012, 03:12 PM

I've built a solution for you. You will need to create a form with just a button on it. Then you will need to put this code in the on-click event for the button. It will then cycle through your table and add the category according to the first posting. You will need to add a field to your table. I used the term Category

I've attached a database with all the information so you can see how it works.

**ncurran217** · 02-10-2012, 04:18 PM

Ok I see that, now what if there are the mileages between a certain point, but then there is a brand that is associated with as well. So here is what I guess I am trying to explain:

CCPLAT will have the categories of 24000,36000,50000 & 60000
CCDIAM will have the categories of 50000,75000,100000,125000, & 150000

How in the case select statement make it look for the brand and the mileage category?

**alansidman** · 02-10-2012, 04:28 PM

You would have to combine an IF-Then Statement with the Select Case, ie.

Please Login or Register  to view this content.

You can have as many of these as you like. It will take a little bit of work to set them up. I would do it with a test db first. Once you have it in place, then the code runs very quickly.

**ncurran217** · 02-10-2012, 04:30 PM

Ok I will try that, and I would have to use the button? Can't just have it in the query field or anything?

**ncurran217** · 02-10-2012, 04:37 PM

Here is what I have and it keeps saying, "Compile Error: Loop Without Do":

Private Sub Command4_Click()
Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim mycategory As String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("Table1")
Do Until myset.EOF

myset.Edit
If Coverage = "CCPLAT" Then
Select Case myset!Mileage
Case 0 To 24000
[mycategory] = "24,000"
Case 24001 To 36000
[mycategory] = "36,000"
Case 36001 To 50000
[mycategory] = "50,000"
Case 50001 To 60000
[mycategory] = "60,000"
Case Else
[mycategory] = "N/A"
End Select
Else
If Coverage = "CCDIAM" Then
Select Case myset!Mileage
Case 0 To 50000
[mycategory] = "50,000"
Case 50001 To 75000
[mycategory] = "75,000"
Case 75001 To 100000
[mycategory] = "100,000"
Case 100001 To 125000
[mycategory] = "125,000"
Case 125001 To 150000
[mycategory] = "150,000"
Case Else
[mycategory] = "N/A"
End Select
End If
myset("Category").Value = mycategory
myset.Update

myset.MoveNext
Loop
End Sub

**alansidman** · 02-10-2012, 08:10 PM

Code looks ok. Usually that error pops when there is no End If statement, but there is. So now, open your form in design view. Click on Alt +F11 to open the VBA window. Click on Tools. Now References. See if there are any references missing. It will tell you. If so, then add them and try again.

**ncurran217** · 02-10-2012, 09:45 PM

These are the ones that are checked:

Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library

**alansidman** · 02-10-2012, 11:23 PM

None indicated as missing?

**ncurran217** · 02-11-2012, 12:39 AM

nope nothin coming up saying anything is missing

**alansidman** · 02-11-2012, 03:14 PM

A little bit of tweaking and I got it to work.

Please Login or Register  to view this content.

**ncurran217** · 02-11-2012, 04:15 PM

Awesome that's great! Thanks for the help!

Query Help

LinkBack

Thread Tools

Rate This Thread

Display

Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Re: Query Help

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions