Skipping a field if there is a word “FALSE” in it box or 0

[TJVFree]

I’m trying to get the Standard Deviations but in order for me to do this I need to skip over months that have a “false” or “zero” in the cell. I’m using a VBA Script to get my Standard Deviation, but if there is a better way, please let me know that also.


IIF Standard Deviations then skip that cell

 SELECT Master_Join_tbl.[OMNI#], GetXLStDev([May_Pull_Thru],[June_Pull_thru],[July_Pull_thru],[Aug_Pull_Thru],[Sept_Pull_thru],[Oct_Pull_thru]) AS [Standard Deviations6M], Master_Join_tbl.May_Pull_Thru, Master_Join_tbl.June_Pull_Thru, Master_Join_tbl.July_Pull_Thru, Master_Join_tbl.Aug_Pull_Thru, Master_Join_tbl.Sept_Pull_Thru, Master_Join_tbl.Oct_Pull_Thru
FROM Master_Join_tbl;

VBA Script I’m using:

 Public Function GetXLStDev(No1 As Double, No2 As Double, No3 As Double, No4 As Double, No5 As Double, No6 As Double) As Double
   Dim objExcel As Object
   Set objExcel = CreateObject("Excel.Application")
   
     
   Let GetXLStDev = objExcel.StDev(No1, No2, No3, No4, No5, No6)
   
   objExcel.Quit
   Set objExcel = Nothing
End Function

Public Function Pause(PauseSeconds As Double)

Dim Start
Start = Timer
Do While Timer < Start + PauseSeconds
DoEvents
Loop

End Function

Thanks for taking the time to help
TCB

 

[dhookom]

Corey,
Are you really stuck with this un-normalized table structure?
To get the standard deviation, I would normalize or create a normalizing union query [quniPullThru]:

SELECT [OMNI#], 5 as Mth, [May_Pull_Thru] as Pull_Thru
FROM Master_Join_tbl
WHERE [May_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 6, [June_Pull_Thru]
FROM Master_Join_tbl
WHERE [June_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 7, [July_Pull_Thru]
FROM Master_Join_tbl
WHERE [July_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 8, [Aug_Pull_Thru]
FROM Master_Join_tbl
WHERE [Aug_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 9, [Sept_Pull_Thru]
FROM Master_Join_tbl
WHERE [Sept_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 10, [Oct_Pull_Thru]
FROM Master_Join_tbl
WHERE [Oct_Pull_Thru] <> 0;

Then create a query like:

SELECT [OMNI#], StDev(Pull_Thru) as PT_StdDev
FROM quniPullThru

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Thanks dhookom, I'm not stuck on anything, an will try what ever it takes, but when I try your sql
I'm getting a "Data type mismatch in criteria expression" error.

also, will this skip the fields that have the work "False" in it

SELECT [OMNI#], 5 as Mth, [May_Pull_Thru] as Pull_Thru
FROM Master_Join_tbl
WHERE [May_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 6, [June_Pull_Thru]
FROM Master_Join_tbl
WHERE [June_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 7, [July_Pull_Thru]
FROM Master_Join_tbl
WHERE [July_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 8, [Aug_Pull_Thru]
FROM Master_Join_tbl
WHERE [Aug_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 9, [Sept_Pull_Thru]
FROM Master_Join_tbl
WHERE [Sept_Pull_Thru] <> 0
UNION ALL
SELECT [OMNI#], 10, [Oct_Pull_Thru]
FROM Master_Join_tbl
WHERE [Oct_Pull_Thru] <> 0;


SELECT [OMNI#], StDev(Pull_Thru) as PT_StdDev
FROM quniPullThru

 

[dhookom]

but when I try your sql

I provided sql statements for 2 queries. Which one is generating the error? Are all of your "Pull_Thru" fields numeric?

What is 'the work "False"'?

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

its the first sql, and it looks like they are all text. any ideas?

 

[dhookom]

If they are text then how can you expect to calculate a standard deviation. You would need to change the field(s) to numeric or convert the text to numeric in each section of the union query. You will also need to change the WHERE CLAUSE to match what you mean by that have a "false" or "zero" in the cell.

Is this an Access table or a linked Excel file?

Do you understand normalization?

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

I'm using a VBA Script to get my Standard Deviation, but if there is a better way, please let me know that also.

Instead of opening an instance of Excel on every single record in your query (!!!) I recommend writing your own function. It's not that complicated, here's an example:

http://www.anthony-vba.kefra.com/vba/vba1.htm