Replacing Data

**kd2017** · 03-19-2021, 12:33 PM

Originally Posted by mikesal57

It worked somehow....but not from the macro I created...

Pictures of what happen when I rAn Macro....and.......pictures of when I RAN it from ALT-11 screen...

Sorry, I'm not familiar with running vba from a macro. I think most of us try to stay away from macros in general. Is this something that will need to be repeated often? If so just call the sub from a command button on a form. Otherwise I'd just run it from the vba editor window.

IF the format of your column names is nPOS01 through nPOS37 I propose a slight code modification:

Code:

Public Sub SetZerosToNull()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim update_qry As String
    Dim i As Integer
    
    Set db = CurrentDb
    
    For i = 1 To 37
        update_qry = "UPDATE [ALL_HX44] SET nPOS" & Format(i, "##") & " = null WHERE nPOS" & Format(i, "##") & " = 0;"
        
        'Debug.Print update_qry
        db.Execute update_qry, dbFailOnError
    Next

ExitHandler:
    Set db = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

**mikesal57** · 03-19-2021, 01:00 PM

Originally Posted by kd2017

Sorry, I'm not familiar with running vba from a macro. I think most of us try to stay away from macros in general. Is this something that will need to be repeated often? If so just call the sub from a command button on a form. Otherwise I'd just run it from the vba editor window.

IF the format of your column names is nPOS01 through nPOS37 I propose a slight code modification:

Code:

Public Sub SetZerosToNull()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim update_qry As String
    Dim i As Integer
    
    Set db = CurrentDb
    
    For i = 1 To 37
        update_qry = "UPDATE [ALL_HX44] SET nPOS" & Format(i, "##") & " = null WHERE nPOS" & Format(i, "##") & " = 0;"
        
        'Debug.Print update_qry
        db.Execute update_qry, dbFailOnError
    Next

ExitHandler:
    Set db = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Sub

Understood...
Thanks KD with the great follow thru to get this working you work BIG OT on this one....

**mike60smart** · 03-19-2021, 01:48 PM

Can you upload a zipped copy of the database?

**mikesal57** · 03-19-2021, 04:16 PM

I'm still somewhere at a loss...
Totals not adding up....

Fields POS1 thru 5 ...have only 1's and 0's

I thought taking out the 0's will take that field out of the equation....
But it seems like it doesnt...

In Pic 1....I'm showing hits on all combo's in fields POS1 thru 5

I select the Line where POS2 and POS3
and ran it without 1-4-5 and I get different totals

Lost on why its happening...

**kd2017** · 03-19-2021, 05:15 PM

Maybe you could show your equation?

Also, this looks like a spreadsheet, this might be a job for excel. Maybe you could give us an overview of what the app is and what it's supposed to achieve.

**mikesal57** · 03-19-2021, 05:44 PM

Originally Posted by kd2017

Maybe you could show your equation?

Also, this looks like a spreadsheet, this might be a job for excel. Maybe you could give us an overview of what the app is and what it's supposed to achieve.

No problem....
SELECT Sum(ALL_HX4.xwin) AS Bets, Count(ALL_HX4.xfin) AS CountOfxfin, Sum(IIf([xwin]>0,1,0)) AS Winners, Sum(IIf([xWIN]>0,1,0))/Count([xwin])*100 AS [Win%], Count([xwin])/[TotalRaces] AS [Bets Per Race], Sum([xWin])/(Count([xwin])*2) AS ROI, [Winners]/[TotalRaces] AS [% of All Races], 2*[ROI]*[BETS]/[WINNERS] AS [Avg Price], Sum(IIf([xpla]>0,1,0)) AS Placers, Sum([xPla])/(Count([xpla])*2) AS PROI, Sum(IIf([xPLA]>0,1,0))/Count([xpla])*100 AS [Place%], Max(ALL_HX4.xwin) AS [Max], ALL_HX44.nPOS01, ALL_HX44.nPOS02, ALL_HX44.nPOS03, ALL_HX44.nPOS04, ALL_HX44.nPOS05
FROM ALL_HX4 INNER JOIN ALL_HX44 ON (ALL_HX4.tTRK = ALL_HX44.tTRK) AND (ALL_HX4.tDATE = ALL_HX44.tDATE) AND (ALL_HX4.nRACE = ALL_HX44.nRACE) AND (ALL_HX4.tPGM = ALL_HX44.tPGM)
GROUP BY ALL_HX44.nPOS01, ALL_HX44.nPOS02, ALL_HX44.nPOS03, ALL_HX44.nPOS04, ALL_HX44.nPOS05;

what I'm trying to do is find out what combo of fields gives out the best ROI....

Its not that hard but those damn numbers just dont jive when its done alone without those zero fields

**mikesal57** · 03-20-2021, 07:33 AM

Hi Again...

I'm still at a stop on this ...maybe it just cant be done.....if not , I need to know to stop perusing it...
heres a little more simpler explanation

Pic #1 & #2 is the query and results ......notice the number in red
Pic #3 & #4 new query just adding another field.....notice the amount in red is drastically lower but it the same two fields like above
All the fields are either 1 & zeros

I need a condition or rule that doesnt let fields with a zero to affect the out come...
Can this be done?
thxs
Mike

**mike60smart** · 03-20-2021, 07:49 AM

Can you upload a copy of the database?

**isladogs** · 03-20-2021, 08:13 AM

Going back to post #15, the reason your macro errored is because you can run functions from a macro but cannot run subs.

I'm not going to get further involved as this approach is completely inappropriate for a database as I and many others told you at UA.
It would be courteous to those who responded to your thread at UA if you could state that you have decided to follow another route rather than normalise your data.

**mikesal57** · 03-20-2021, 09:07 AM

Originally Posted by isladogs

Going back to post #15, the reason your macro errored is because you can run functions from a macro but cannot run subs.

I'm not going to get further involved as this approach is completely inappropriate for a database as I and many others told you at UA.
It would be courteous to those who responded to your thread at UA if you could state that you have decided to follow another route rather than normalise your data.

That macro is not an issue any more because if its a blank field or has a zero my queries dont give me what I'm looking for.
I do appreciate all you guys helping , but remember that I just started using Access and do not know much of what I can or cant do...

Normalize is not familiar to me...I did read up but I'm still confused.....
There is nothing repetitive in my fields ...yes it does have 1's and zero's ....but thats just a way to use that field or not.....

I hope no one takes offence to my new status

Mike

**moke123** · 03-20-2021, 11:34 AM

I hope no one takes offence to my new status

No one takes offense at someone who's new to access seeking help. It's the reason many of us are here, as well as several other sites.

Many of us do take offense to cross posting without the poster making note of it. Often we will will spend a lot of time mocking up a test database and trying out code prior to posting an answer.
It can be awfully annoying to find out that a certain solution attempt was made already or the poster already had a solution at another site and you just wasted an hour for nothing.

Done correctly cross posting is not an issue. We have all done it but we do it correctly by including a link to the crosspost.

This is a link that many are commonly refered to about cross posting - https://www.excelguru.ca/content.php?184

**mikesal57** · 03-20-2021, 12:11 PM

Originally Posted by moke123

No one takes offense at someone who's new to access seeking help. It's the reason many of us are here, as well as several other sites.

Many of us do take offense to cross posting without the poster making note of it. Often we will will spend a lot of time mocking up a test database and trying out code prior to posting an answer.
It can be awfully annoying to find out that a certain solution attempt was made already or the poster already had a solution at another site and you just wasted an hour for nothing.

Done correctly cross posting is not an issue. We have all done it but we do it correctly by including a link to the crosspost.

This is a link that many are commonly refered to about cross posting - https://www.excelguru.ca/content.php?184

I hear you and I'm sorry....

I started by Googleing Access Forums .....these 2 came up.......not knowing which one would satisfy me....I did post in both....
But after all the responses , I set my tack here...

Mike

**isladogs** · 03-20-2021, 12:34 PM

Originally Posted by mikesal57

I hear you and I'm sorry....

I started by Googleing Access Forums .....these 2 came up.......not knowing which one would satisfy me....I did post in both....
But after all the responses , I set my tack here...

Mike

Your answers at UA suggested you were using that as your main source of information. As a result, several people continued to respond
....that is until someone else made it clear you were no longer following that advice

Please have the courtesy to post something at UA in order to prevent anyone else wasting their time replying. Thank you.

**mikesal57** · 03-20-2021, 01:27 PM

Originally Posted by isladogs

Your answers at UA suggested you were using that as your main source of information. As a result, several people continued to respond
....that is until someone else made it clear you were no longer following that advice

Please have the courtesy to post something at UA in order to prevent anyone else wasting their time replying. Thank you.

I just did.....they mentioned "Normalizing Data" ...have no clue on that ...will have to read up and understand if thats my solution

My quick read says that the data is duplicated , I dont or maybe cant see it....as my fields are different from each other tho
its all 1's and 0's....

I will try to make sense of it some how

thxs

**isladogs** · 03-20-2021, 01:46 PM

Thank you for doing that

Replacing Data

Thread Tools

Similar Threads

Replacing rows with data from another table

Replacing one value for another

Replacing data in a field.

Compiling Data, Not Replacing

Replacing data in Table

Posting Permissions