If Statement In Query

D

DS

Is there an easier way to code this for a field in a Query for a report.
The field will be in the detail section.
Thanks
DS

If CDTax = 0 And _
CDWhere = "A" And _
CDInclusive = -1 Then
Me.TxtCode = 1
ElseIf CDTax = 0 And _
CDWhere = "A" And _
CDInclusive = 0 Then
Me.TxtCode = 2
ElseIf CDTax = 0 And _
CDWhere = "B" And _
CDInclusive = -1 Then
Me.TxtCode = 3
ElseIf CDTax = 0 And _
CDWhere = "B" And _
CDInclusive = 0 Then
Me.TxtCode = 4
ElseIf CDTax = -1 And _
CDWhere = "A" And _
CDInclusive = -1 Then
Me.TxtCode = 5
ElseIf CDTax = -1 And _
CDWhere = "A" And _
CDInclusive = 0 Then
Me.TxtCode = 6
ElseIf CDTax = -1 And _
CDWhere = "B" And _
CDInclusive = -1 Then
Me.TxtCode = 7
ElseIf CDTax = -1 And _
CDWhere = "B" And _
CDInclusive = 0 Then
Me.TxtCode = 8
Else
Me.TxtCode=0
End If
 
S

strive4peace

Hi DS,

create a table for the codes

Codes
- ynCDtax, yes/no
- CDwhere, text, 1
- ynCDInclusive, yes/no
- Code, integer

with records:
false, "A", true, 1
false, "A", false, 2
false, "B", true, 3
false, "B",false,4
true, "A", true, 5
true, "A", false, 6
true, "B", true, 7
true, "B", false, 8

then, either link to codes table in the RecordSource for the report and
use Code for the ControlSource of a textbox or use this equation in the
ControlSource of a textbox:

=nz(dLookup("Code","Codes","ynCDtax=" & [CDTax] & " AND CDwhere='" &
[CDwhere] & "' AND ynCDInclusive=" & [CDInclusive]),0)

WHERE
CDTax, CDwhere, and CDInclusive are controls on your report (Visible can
be No)


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
D

DS

Wow, Great Thank You. I'll give this a try. Can I also make a function and
call it from the Query such as...

CODE:=FunctionCode()

Thanks
DS
 
J

John Spencer

Not sure this is any simpler but it seems to work for the values 1 to 8

1 + Abs(CDTax * 4) + (Abs(CdWHERE = "B")*2) + (Abs(CdInclusive=-1)* 1)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
S

strive4peace

Hi DS,

you're welcome

yes ... but in order to best help you,we need to know your data
structure. Here is something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub

'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub

'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
E

Evi

Do it in a Function in a Module rather than in a query

without testing it, here is the sort of thing you can put in a module then
in your queries or reports, you can just write stuff like


MyTax: TaxCode ([CDTax], [CDWhere],[CDInclusive])

I'm assuming that CDTax and CDInclusive are Yes/No fields

Public Function TaxCode (intTx, strWhere, intIncl) AS Integer

Select Case strWhere

Case "A"

Select Case intTx
Case False
'CDWhere is A, CDTax is False

If intIncl = True Then
TaxCode = 1
Else
TaxCode = 2
End IF

Case True
'CDWhere is A, CDTax is True
If intIncl = True Then
TaxCode = 5
Else
TaxCode = 6
End IF

End Select

Case "B"

Select Case intTx
Case False
'CDWhere is B, CDTax is False

If intIncl = True Then
TaxCode = 3
Else
TaxCode = 4
End IF

Case True
'CDWhere is B, CDTax is True
If intIncl = True Then
TaxCode = 7
Else
TaxCode = 8
End IF

End Select

Case Else
'anything other than the above scenarios
TaxCode = 0

End Select

End Function



You may have to trap for nulls if that is a possibility

Evi
 
D

DS

This looks very ingenious and simple! I need to take a minute to understand
it though! :)
I'm defintly going to try it.
Thanks
DS
 
D

DS

Very Nice. I now have a number of options to try. And try them I will!
Thank you, I appreciate the input.
DS
 
D

DS

That is very nice. Here are the results..


tblDiscountDetails
==========================
0 CDLineID, 4 (Long), 4
1 CDCheckID, 4 (Long), 4
2 CDGroupID, 4 (Long), 4
3 CDMenuID, 4 (Long), 4
4 CDMenuCatID, 4 (Long), 4
5 CDSent, 1 (Boolean), 1
6 CDQuantity, 6 (Single), 4
7 CDItemID, 4 (Long), 4
8 CDMemo, 12 (Memo), 0
9 CDSeat, 10 (Text), 50
10 CDSub, 4 (Long), 4
11 CDSubGroupID, 4 (Long), 4
12 CDPrice, 5 (Currency), 8
13 CDDiscountID, 4 (Long), 4
14 CDDiscountDP, 4 (Long), 4
15 CDDiscountAmount, 5 (Currency), 8
16 CDDiscountPercent, 20 (20), 16
17 CDDiscountAction, 10 (Text), 50
18 CDDiscountManID, 4 (Long), 4
19 CDDiscountWhere, 10 (Text), 50
20 CDDiscountCheck, 1 (Boolean), 1
21 CDDiscountTime, 8 (Date), 8
22 CDDiscountDate, 8 (Date), 8
23 CDNoDiscount, 1 (Boolean), 1
24 CDInclusive, 1 (Boolean), 1
25 CDTaxCodeID, 4 (Long), 4
26 CDTaxRate, 20 (20), 16
27 CDKillTax, 1 (Boolean), 1
28 CDEntre, 1 (Boolean), 1
29 CDReportID, 4 (Long), 4
30 CDBizDay, 8 (Date), 8
31 CDDriverID, 4 (Long), 4
32 ISDiscount, 1 (Boolean), 1
33 DXID, 10 (Text), 50

Thanks
DS
 
S

strive4peace

Hi DS,

excellent... may I ask, however, why everything is in the same table? Or
did you run the sub to just show fields for one table? What about the
rest of the tables in your database? Like those that define CheckID,
GroupID, MenuID, ...

run the procedure -->
RunShowFieldsForAllTables

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
D

DS

OK John, You gotta tell me why and how this works! You have my complete
attention and curiosity!
Thanks
DS
 
J

John Spencer

It is just a little math.

1 + Abs(CDTax * 4) + (Abs(CdWHERE = "B")*2) + (Abs(CdInclusive=-1)* 1)

CDTax returns minus 1 or 0. So CDTax * 4 is going to return 0 or -4, which
places you in the first 4 or the last 4 values. Abs strips off the minus sign.

CDWhere = "B" is going to return True(-1) or False(0). Then multiply that by 2
to select either the first half of the two sections above or the second half
of the two sections above. Again abs strips off the minus.

The last section does similar things putting you in the first half of the
subsection or the last half of the subsection. Adding 1 simply takes care of
the fact that the calculations above return 0 to 7.

Reviewing the above, I see I could change it to remove most of the abs
functions and simplify it

1 + Abs(CDTax*4 + (CdWHERE="B")*2 + CdInclusive)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
D

DS

John,
Thank you for the reply and the explantion. I always love learning
something new and cool!
DS
 
D

DS

Thanks Crystal. It does work and very well at that. After pondering my
options, I decided to go with John's.
Thank you Crystal for your input and help.
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Applying Code to a Column 0
Union Query Format 6
Counting Criteria Values with For Loop 3
Code Optimization 0
change certain cell colours in VBA 2
long if function 2
Long If Statement 3
Excel Message box called if 1 of 3 options is entered. 4

Top