I need help

[Hydro]

I have 24 bound combo boxes and 24 unbound text boxes. The
combo boxes are named CB1- CB24 the text boxes are named
TB 1- TB24. What I would like to do is SUM the data of TB1-
24 when the corresponding combo box 1-24 is equal to a
certain value ("EV"). AN Iif statement in a query as an
expression is extremeley long and is too long to create in
one field. If there a function or expresion even VB that I
could use to make this process simple? Any help is
appreciated.

 

[Jeff Boyce]

Hydro

Your post describes combo boxes and text boxes -- those happen on forms.

Queries happen on tables, and on other queries. You're working with
spreadsheet data, right? A list of "repeating" fields (CB1, CB2, ... CB24)
is typical and necessary in spreadsheets, but will cause horrible, blinding
headaches in Access, a relational database.

Take a look at the subject of "normalization" before proceeding any
further -- I suspect you need to (and will greatly benefit from) normalizing
your data before querying against it.

Here's a link: ( http://support.microsoft.com ) for KB# 100139.

Good luck

Jeff Boyce
<Access MVP>

 

[Hydro]

Thanks for the advice and I do understand normalization.
The controls are on a form. The form I am creating is
attempting to replace an Excel spreadsheet. I want the DB
to contain the same information and allow for 1 storage
point for data and more reporting options. I have broken
down my tables and data and have normalized them as much
as possible. There are 24 combo boxes and 24 text boxes
because I must separate the information by hour. Each hour
must have it own record. I also need to be able to
summarize the data across hours depending on what value is
in the combo box that corresponds to the text box. I can
use an expression such as Iif([CB1]="EV",[TB1],0)+Iif
([CB1]="EV",[TS1],0)+........in a query but it is tricky
and annoying to replicate. I was just wondering that since
this is the task I have before me if there was a way to
use a function such as DSUM or any kind of code on a
control on a form I might be able to use that might make
it a little less of a headache.

 

[Hydro]

Thanks for the advice and I do understand normalization.
The controls are on a form. The form I am creating is
attempting to replace an Excel spreadsheet. I want the DB
to contain the same information and allow for 1 storage
point for data and more reporting options. I have broken
down my tables and data and have normalized them as much
as possible. There are 24 combo boxes and 24 text boxes
because I must separate the information by hour. Each hour
must have it own record. I also need to be able to
summarize the data across hours depending on what value is
in the combo box that corresponds to the text box. I can
use an expression such as Iif([CB1]="EV",[TB1],0)+Iif
([CB1]="EV",[TS1],0)+........in a query but it is tricky
and annoying to replicate. I was just wondering that since
this is the task I have before me if there was a way to
use a function such as DSUM or any kind of code on a
control on a form I might be able to use that might make
it a little less of a headache.

 

[John Spencer (MVP)]

Well, if you are going to be forced to stick with the design you have. You will
need to write a vba function to do the work for you. I would recommend that you
have three fields to take care of your data.

fldHour (from 1 to 24) (or 0 to 23)
fldEvent (EV, or whatever
fldValue (values to sum)


AIRCODE untested function that could be used on a form

Private Function fSumUp()
Dim intHourCount as Integer
Dim varReturn

For intHourCount = 1 to 24
IF Me("CB" & intHourCount) = "EV" Then
varReturn = varReturn & NZ(Me("TS" & intHourCount),0)
End if
Next intHourCount

fSumup = varReturn

End Function

Thanks for the advice and I do understand normalization.
The controls are on a form. The form I am creating is
attempting to replace an Excel spreadsheet. I want the DB
to contain the same information and allow for 1 storage
point for data and more reporting options. I have broken
down my tables and data and have normalized them as much
as possible. There are 24 combo boxes and 24 text boxes
because I must separate the information by hour. Each hour
must have it own record. I also need to be able to
summarize the data across hours depending on what value is
in the combo box that corresponds to the text box. I can
use an expression such as Iif([CB1]="EV",[TB1],0)+Iif
([CB1]="EV",[TS1],0)+........in a query but it is tricky
and annoying to replicate. I was just wondering that since
this is the task I have before me if there was a way to
use a function such as DSUM or any kind of code on a
control on a form I might be able to use that might make
it a little less of a headache.

-----Original Message-----
Hydro

Your post describes combo boxes and text boxes -- those happen on forms.

Queries happen on tables, and on other queries. You're working with
spreadsheet data, right? A list of "repeating" fields (CB1, CB2, ... CB24)
is typical and necessary in spreadsheets, but will cause horrible, blinding
headaches in Access, a relational database.

Take a look at the subject of "normalization" before proceeding any
further -- I suspect you need to (and will greatly benefit from) normalizing
your data before querying against it.

Here's a link: ( http://support.microsoft.com ) for KB# 100139.

Good luck

Jeff Boyce
<Access MVP>

.