I know access!

[Carl]

Well, not that good, as I am finding out trying to manipulate NBA files.

Help guys!

I did manage to get the point differentials for basketball with the line built in with this expression
pt dif: (([PTS]+[LINE-C])-[V-PTS])

So now I have a number (sometimes minus, sometimes plus) in a new field.

How can I build a new field that will show a "W" for positive numbers, "T" for 0, and "L" for negative numbers? I have already done a make table on my "pt dif" calculated field, so it is fixed like I want it to be.

[MikeDee]

Ok Carl I finally figured out how to make vba run in a query. Here is how you can do this.

Go to the module tab of your db make a new module.

In the new module click on insert.

In the name box type carl_df(t)

On your screen you should have the following

Public Function carl_df(t)


End Function

take the following and copy it from this message and paste it between the 2 lines above:

If IsNull(t) Then
carl_df = Null
ElseIf (t) > 0 Then
carl_df = "W"
ElseIf (t) = 0 Then
carl_df = "T"
ElseIf (t) < 0 Then
carl_df = "L"
Else
End If

now save the function and save the module. Access will want to save the module as module1 but change it to My Functions. You should now have a module named my functions with one function in it named carl_df.


Now go to a query of your data table where you have the value pt dif. In the field name line of a blank column type:

result: carl_df([pt dif])

and run the query.

If we have done this right you should have a column of Ws, Ts and Ls based on the value of pt dif +, - or 0

[Carl]

But I'll try it step by step in the a.m.

It's great that you know all this stuff.........

[MikeDee]

Lets not celebrate until it works:)

[hurrikane]

oh...MikeD..this could be really cool. Have you tried converting any of your other vba modules this way?

[MikeDee]

HK - no not really but it should work with anything, where you want to do a query and then do some operation based on the value of another field. I have used it to pull numbers and or text out of strings.

[Carl]

I found an easy cheat for stupid people.

I just pull out the negative numbers in a query, join it with a db called "L" that has an "L" field in it. Likewise for the postive numbers/"W"'s. Only takes me about five minutes and I didn't have to learn anything.

New question though.
1) I want to know the amount of days between games as a field.
2) Can I find out the difference between two days as a calculation if they are consecutive lines of data?

For example, two lines:

4/18/2001,Home,Charlotte,L,92,97,L,-1,U,192,
4/15/2001,Home,NewJersey,W,95,88,L,-9,U,204,

Can I get access to create a field and calculate "3" for number of days between games?



BTW, access assigned primary key numbers have been useful in combining/recombing databases. Knew they were there for something, just not HTR.

[MikeDee]

There is a date diff function, you can look it up in help for detailed info you can put it in a query like the following:
DaysElapsed: DateDiff("y", [OrderDate], [ShippedDate])

For your example you would need the 2 dates to be on the same line so you need to figure a way to get the NJ date on the same line as the Charlotte date

It can be done with vba of course, but since you are VBA intolerant we won't go there.

[Carl]

Thanks Mike, I'll try it.

"VBA intolerant".

Waiting till I am sixty to learn VBA, gives me something to look forward to hehe.

[Carl]

Originally posted by MikeDee
There is a date diff function, you can look it up in help for detailed info you can put it in a query like the following:
DaysElapsed: DateDiff("y", [OrderDate], [ShippedDate])

For your example you would need the 2 dates to be on the same line so you need to figure a way to get the NJ date on the same line as the Charlotte date

It can be done with vba of course, but since you are VBA intolerant we won't go there.

Mikey, YOU'RE THE BEST!

I have had to learn Excel alot to do this stuff, but I was just able to do the difference in dates between 2378 games ALL AT ONCE using the little expression you gave me.

Hope you had a great Christmas and all the best in the New Year.

[MikeDee]

Thanks Carl Happy New Year to you to.
After I blow all of my retirement money on the horses, I'll just become an Access consutant:)