A little help!

[lindsayj62]

This forum has been a great help to me in getting started with HTR, especially with the Access syntax questions I've had. Well, here I go again. I've learned how to create my own filter, for instance, finding horses whose closing odds were [nVBET]*2. I've run into something I can't figure out how to do.

I've created a nFR column that adds together the ([rFR1]+[rFR2]). I've created a Ratio column that tells me what 1/2 the field size is, ([nFLD]/2). What I want to do is compare these 2 numbers in some way. If I leave them as is, I get a value for each entrant in each column. However, if in the Ratio column I specify that I want to filter for <=[nFR] I get a box that pops up asking what the value of nFR is.

I also tried it this way. I kept the nFR: column as described above. I then added the nFLD column and tried to filter for <=([nFR]*2), which would essentially be the same comparison. Again I get a popup asking for the value of nFR.

Basically what I want to do is filter for entrants whose ([rFR1]+[rFR2]) value is <= ([nFLD]/2), or (([rFR1]+[rFR2])*2) value is <= [nFLD]



I hope someone can help with this. I'm sure there is any easy way to do it, but I still consider myself an Access novice.

Thanks in advance.

Jay

[Rick]

I am not a novice but not an expert. I am a "seat of my pants" type.

Send me a copy of your query. You can either send a bare mdb file or just use the SQL view and send me a copy of the SQL view.

I have to go hands on to see if I can figure out the problem.

ricks@homebased2.com

[Bernie]

It shouldn't be asking you about nFR if you properly named your column that and have included it in the query. Be sure the field area of the column starts with nFR, then includes a colon, then has the formula you want to use, such as rFR1+fFR2.

[Donnie]

While not 100% sure it appears you may be putting the "["'s in the the criteria box that you are filtering for...hence the program cannot find the field called [nFR] (brackets included).

Start a new comlumn in your query and type this:

MyRating:IIF(nFR<nFLD, "true", "false")

To do a logical comparison between 2 fields you must do an IF statement. In the first part you put the "test" (nFR<nFLD/2). For each record that tests true to that statement, you receive back the second part of the IF statement, the word "true". If that test if false, those numbers added are greater than the field divided by 2, then it will return "false" in your column. Then query for all the "true" horses.

Now I will go and test to ensure I have not typed any typos!

[jnichols]

Create a custom field nFR:[rFr1]+[rFR2]. In the Criteria for that field put <=(.5*[nFLD]). Make sure you don't mix up your brackets "[" with your parentheses "(".

I'm asuming that in plain English you're trying to filter for horses whose combined FR1 and FR2 rankings are less than or equal to half the race field size. Not a bad filter, by the way.

[Donnie]

attached is a picture of the syntax to create the field as a new field, then test for true or false

[Glen]

Nice picture Donnie...How did u do that?

[Donnie]

Pretty simple...anytime you want a picture of something that is on your screen, press your PrintScreen button...this copies your entire screen onto the clipboard. I then opened Frontpage, but this next step could be done in Word also, you hit the paste button, or go to Edit to paste. Once the picture is in Frontpage, I cropped out the parts you don't need to see, and saved it into a web folder. Then the board asks if you want to attach a file, point to that picture in that folder.

Viola!

[hurrikane]

donnie...maybe you should try cropping the lush at the bar? :D

I think the box is popping up because when you run the query it hasn't created a value for that field yet. If you just hit enter it should run fine because it calc the field as it runs.
On the other hand Donnie idea is much better and opens a lot of doors.
(I gotta make one of these friggin access seminars in LV!)

[lindsayj62]

Thanks so much for the help Donnie. Rick was also very helpful. I think I've got it now. HTR member support is the best!!

Jay

[Donnie]

Hurrikane...you mean you've been to one of the seminar's in Vegas? I ALWAYS see your wife there.......:D Can't crop that barfly...that's about all there is to do in Iowa, so we start drinking early...like age 12 or 13!!

On the serious side though, Ken normally goes fishing for the seminar date right after the Super Bowl. For those of you who are "new" to HTR, Ken hosts an annual seminar in Las Vegas that is free to attend. He puts on some fantastic lectures that, without a doubt, I learn something new every year. You get to meet the likes of Ken, Tomcat & Linda, Tommy C, Hurrikane and his lovely wife Sue, Glen, and many of the others who post regularly on this board. We hold an informal Access seminar to help the newer Access users get up to speed with actual classroom "lessons". Last time I talked to Ken, it sounds as if he is leaning towards a July date again this year. Can you tell I'm already looking forward to this year's get together?

[Donnie]

One more cute little picture...kinda sums up my approach to the races.....

[fred4now]

Kane
Like Donnie, was wondering when you were going to make your first seminar:D

[hurrikane]

If I could just stay out of the friggin sports book.
Actually, I'm making LV 3 or 4 times this year so hopefully I won't act like a kid in a candy store.

Donnie GREAT PIC!!!!!! (looks like the Tampa Bay front line. :D)