Solved: How to create AND or Query

Concider my last comment only as guidelines. It might be a bit messy. I can explain if something is unclear or write some code dedicated just for Your project.

Llysander

Simple example assuming that each of the five field options has a cboFIELD(FieldName or itemdata pointer to array, etc), cboOPERATOR(=,>,<,Like,etc), cboANDOR, and a txtCRITERIA for field criteria. Minor error trapping to validate a field following an AND/OR on the form would be necessary.

sSelectFields as string
sFrom as string
sWhereClause as string
sSQLString as string

sSelectFields = "SELECT "
sFrom = "FROM TableName "
sWhereClause = "WHERE "

' Each IF statement adds chosen field (if any) to SELECT, then if criteria was selected adds it to the where clause.
if cboFIELD1.ListIndex > -1 then
sSelectFields = sSelectFields & cboFIELD1
if txtCRITERIA1 <> vbnullstring then sWhereClause = sWhereClause & cboFIELD1 & " " & cboOPERATOR1 & " '" & txtCRITERIA1 & "' " & _
iif(cboANDOR1.ListIndex > -1," " & cboANDOR1 & " ",vbnullstring)
end if
end if

if cboFIELD2.ListIndex > -1 then
sSelectFields = sSelectFields & cboFIELD2
if txtCRITERIA2 <> vbnullstring then sWhereClause = sWhereClause & cboFIELD2 & " " & cboOPERATOR2 & " '" & txtCRITERIA2 & "' " & _
iif(cboANDOR2.ListIndex > -1," " & cboANDOR2 & " ",vbnullstring)
end if
end if

if cboFIELD3.ListIndex > -1 then
sSelectFields = sSelectFields & cboFIELD3
if txtCRITERIA3 <> vbnullstring then sWhereClause = sWhereClause & cboFIELD3 & " " & cboOPERATOR3 & " '" & txtCRITERIA3 & "' " & _
iif(cboANDOR3.ListIndex > -1," " & cboANDOR3 & " ",vbnullstring)
end if
end if

if cboFIELD4.ListIndex > -1 then
sSelectFields = sSelectFields & cboFIELD4
if txtCRITERIA4 <> vbnullstring then sWhereClause = sWhereClause & cboFIELD4 & " " & cboOPERATOR4 & " '" & txtCRITERIA4 & "' " & _
iif(cboANDOR4.ListIndex > -1," " & cboANDOR4 & " ",vbnullstring)
end if
end if

if cboFIELD5.ListIndex > -1 then
sSelectFields = sSelectFields & cboFIELD5
if txtCRITERIA5 <> vbnullstring then sWhereClause = sWhereClause & cboFIELD5 & " " & cboOPERATOR5 & " '" & txtCRITERIA5 & "' " & _
iif(cboANDOR5.ListIndex > -1," " & cboANDOR5 & " ",vbnullstring)
end if
end if

sSQLSTRING = sSelectFields & sFrom & sWhereClause

fmufti

ASKER

I am away from my office for 4-5 days so let me evaluate and reply u.

fmufti

ASKER

OK I am back in my office, Now the situation is like that:-
There is a vehicles database, Its fields are
REG NO.
TYPE
DIVSION BELONG TO
UNIT BELONG TO
COST$(LOCAL)
COST$ (FOREIGN)

Now a DIVISION has more than UNITS, If the user want he should be able to see Sum of LOCAL + FOREIGN COST on Vehicle(Group) including REG NO, DIVISION BELONG TO , UNIT BELONG TO, These fields will be on user choice, if he /she want to see detail
1. not inclding REG NO only TYPE and DIVISON,
2. only UNIT and TYPE
3 only UNIT, TYPE and REG NO.
4. etc,etc
All of the above fields with any any OR or AND Combination.
If UNIT and TYPE are selected then the query should or should not include REG NO, becuase teh REG NO are unique and there wont be any grouping so final SQL Statement should cater for it. I Hope u'll now be ablwe to respond me better as I feel more comforatble with my structure, NOTE: the field names have beeen modified to let u understand. I can even increase points if ur ansewer satisfies me. Lookign for response.

fmufti

ASKER

Where is every one!!!!!

HDE226868

There are two things to be done here. First a user interface that lets user create expressions strong enough and still is not confusing and second a querry based on this user input.

Personally I think querry is simple as soon as user interface is defined good enough. So I think You are on line at the moment and we can make things clear about what user can do and what he may not.

Do You already have any idea how user would enter his criteria or should we do this from the scratch?

Sorry for not responding on Your last comment (it seems like there were some problems at EE - I didn't get the message about it)

fmufti

ASKER

I think if we have come so long so why not to do it from the start and do good???

HDE226868

There are two basic approaches I see at the moment. As first user can select what he wants from predefined options (there are few combo or list boxes and user just clicks what he wants). The other is, that user types down some logical expression and engine executes it.
There of course can be combinations.
For first option You need to know what are possible values for each field in database. At least type and maybe division and unit might be sensible to select the first way.

HDE226868

I mean, perhaps You alread have in Your database a table where all possible types are stored, and maybe the same for some other field.

fmufti

ASKER

As u can see my last detail commnet in which I write about the fields and req, can u specifically guide me taking that into account.

HDE226868

Here is a simple form outfit. No base connection yet. Also it will probably have much different looks. I'm sending it just to make it easier to talk what You to be done (what possibilities users have).

Copy this code and paste it into notepad. Than save it as selector.frm. You can give it any name (only .frm is important).

Here is the code:

VERSION 5.00
Begin VB.Form Selector
Caption = "Form1"
ClientHeight = 5835
ClientLeft = 60
ClientTop = 345
ClientWidth = 4665
LinkTopic = "Form1"
ScaleHeight = 5835
ScaleWidth = 4665
StartUpPosition = 3 'Windows Default
Begin VB.CheckBox Check1
Height = 495
Index = 3
Left = 1200
TabIndex = 27
Top = 5040
Width = 375
End
Begin VB.CheckBox Check1
Height = 495
Index = 2
Left = 1200
TabIndex = 26
Top = 4680
Width = 375
End
Begin VB.CheckBox Check1
Height = 495
Index = 1
Left = 1200
TabIndex = 25
Top = 4320
Width = 375
End
Begin VB.CheckBox Check1
Height = 495
Index = 0
Left = 1200
TabIndex = 24
Top = 3960
Width = 375
End
Begin VB.TextBox Text1
Height = 375
Index = 7
Left = 2040
TabIndex = 17
Top = 3120
Width = 1815
End
Begin VB.TextBox Text1
Height = 375
Index = 6
Left = 2040
TabIndex = 16
Top = 2760
Width = 1815
End
Begin VB.TextBox Text1
Height = 375
Index = 5
Left = 2040
TabIndex = 15
Top = 2400
Width = 1815
End
Begin VB.TextBox Text1
Height = 375
Index = 4
Left = 2040
TabIndex = 14
Top = 2040
Width = 1815
End
Begin VB.TextBox Text1
Height = 375
Index = 3
Left = 2040
TabIndex = 13
Top = 1680
Width = 1815
End
Begin VB.TextBox Text1
Height = 375
Index = 2
Left = 2040
TabIndex = 12
Top = 1320
Width = 1815
End
Begin VB.TextBox Text1
Height = 375
Index = 1
Left = 2040
TabIndex = 11
Top = 960
Width = 1815
End
Begin VB.TextBox Text1
Height = 375
Index = 0
Left = 2040
TabIndex = 10
Top = 600
Width = 1815
End
Begin VB.Label Label1
Caption = "Unit"
Height = 255
Index = 13
Left = 360
TabIndex = 23
Top = 5160
Width = 1575
End
Begin VB.Label Label1
Caption = "Division"
Height = 255
Index = 12
Left = 360
TabIndex = 22
Top = 4800
Width = 1575
End
Begin VB.Label Label1
Caption = "Type"
Height = 255
Index = 11
Left = 360
TabIndex = 21
Top = 4440
Width = 1575
End
Begin VB.Label Label1
Caption = "Reg no."
Height = 255
Index = 10
Left = 360
TabIndex = 20
Top = 4080
Width = 1575
End
Begin VB.Label Label2
Caption = "Show fields"
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 238
Weight = 700
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 375
Index = 1
Left = 360
TabIndex = 19
Top = 3720
Width = 2655
End
Begin VB.Label Label2
Caption = "Filter settings"
BeginProperty Font
Name = "MS Sans Serif"
Size = 9.75
Charset = 238
Weight = 700
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 495
Index = 0
Left = 360
TabIndex = 18
Top = 240
Width = 2655
End
Begin VB.Label Label1
Caption = "Max"
Height = 255
Index = 9
Left = 1560
TabIndex = 9
Top = 3240
Width = 1575
End
Begin VB.Label Label1
Caption = "Min"
Height = 255
Index = 8
Left = 1560
TabIndex = 8
Top = 2880
Width = 1575
End
Begin VB.Label Label1
Caption = "Max"
Height = 255
Index = 7
Left = 1560
TabIndex = 7
Top = 2520
Width = 1575
End
Begin VB.Label Label1
Caption = "Min"
Height = 255
Index = 6
Left = 1560
TabIndex = 6
Top = 2160
Width = 1575
End
Begin VB.Label Label1
Caption = "Cost$ (foreign)"
Height = 255
Index = 5
Left = 360
TabIndex = 5
Top = 2880
Width = 1575
End
Begin VB.Label Label1
Caption = "Cost$ (local)"
Height = 255
Index = 4
Left = 360
TabIndex = 4
Top = 2160
Width = 1575
End
Begin VB.Label Label1
Caption = "Unit"
Height = 255
Index = 3
Left = 360
TabIndex = 3
Top = 1800
Width = 1575
End
Begin VB.Label Label1
Caption = "Division"
Height = 255
Index = 2
Left = 360
TabIndex = 2
Top = 1440
Width = 1575
End
Begin VB.Label Label1
Caption = "Type"
Height = 255
Index = 1
Left = 360
TabIndex = 1
Top = 1080
Width = 1575
End
Begin VB.Label Label1
Caption = "Reg no."
Height = 255
Index = 0
Left = 360
TabIndex = 0
Top = 720
Width = 1575
End
End
Attribute VB_Name = "Selector"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

fmufti

ASKER

I dont know why I did not receive the alert from EE, any way I have copied and the form is ready , now what to do. Now I 'll be relpying u daily. Sorry for the delay. I am waiting for response.

fmufti

ASKER

Hi
Waiting for Reply

fmufti

ASKER

Where is every one. Two days for reply???

HDE226868

Sorry I've been away for quite some time now, and coul'd not often get to the net. Now I'm back. I guess the last thing we did was a form I've posted to discuss possible functions of the program.

HDE226868

fmufti

ASKER

Yes , It will work fine, but the REG No is in one table and Rest of the fields are in table no 2.

HDE226868

If there are two tables, are there any relations between them?

fmufti

ASKER

Yes REG NO is Primary in one table where each registration no has details like Type, fuel type, cylinders, etc and itr foeign in the 2nd table where
DIVSION BELONG TO (text)
UNIT BELONG TO (text)
COST$(LOCAL) (currency)
COST$ (FOREIGN) (currency)

Are the fields, u dont have to do any thing with table ones field except REG NO and all the fields of Table 2
SO the net answer is YES there is Relationshiop of REG No between tables

Éric Moreau

You need to repeat the key (regno) in the second table to establish a relation!

Lunchy

Force accepted HDE226868's comment

fmufti

ASKER

This is cheating.......
HDE226868 did not answer my Q :((((((

Lunchy

Did not mean to step on your toes fmufti. I was cleaning up a long list of questions submitted by a member. I have refunded your points to allow you to repost your question.

Lunchy
Friendly Neighbourhood Community Support Moderator