HOW TO CREATE A FORM THAT TRANSLATES USER INPUT INTO WHERE STATEMENT!! 3

[luismun]

Hello All,

I would like to get ideas from the experts on how to design a form that allows the user to type values similar as if he/she is typing the values in the criteria section of a query. I want my form to translate the input into SQL statment just like MS Access automatically translets the input of users in queries into SQL statements.

I want the user to type in the values using combo box or list box and be able to type in more than 1 value per field. For instance:
Criteraia Field1 Field2
<>A And <>B = STD
=A Or =C <> STD AND <> HD
I would like to translate these values into:
WHERE ((Field1 <> A and Field1 <> B) AND (Field2=STD)) OR ((Field1 =A or Field1 =c ) and (Field2<>STD and Field2<>HD)

and be able to store the where code instead of the values.
Thanks,

LM

 

[benjamenus]

I've done something similar in VB - it may give you a few ideas.

mySQL is the SQL passed to ORACLE

txtSQL is a text box which displays the SQL

txtDetails is a text box which displays the query in 'human' language so the user can veryify the options selected


__________________________________________

mySQL = &quot;select count(*) &quot; & _
&quot;from CLAIM &quot; & _
&quot;where CLAIM.ACC_NUM = '&quot; & cmbAccount.Text & &quot;' &quot; & _
&quot;and &quot; & sClaimSQL & _
&quot; and CLAIM.EXM_COD &quot; & sHandlerSQL & &quot;;&quot;

___________________________________________

If iHandler = 1 Then
sHandler = &quot;Regardless of who the current handler is&quot;
sHandlerSQL = &quot;<> &quot; & &quot;'&quot; & cmbNewHandler.Text & &quot;'&quot;
ElseIf iHandler = 2 Then
sHandler = &quot;Where current handler is &quot; & cmbCurrentHandler.Text
sHandlerSQL = &quot;= &quot; & &quot;'&quot; & cmbCurrentHandler.Text & &quot;'&quot;
Else
MsgBox (&quot;Error: iHandler = &quot; & iHandler)
End If

txtDetails.Text = &quot;Update &quot; & sClaimStatus & vbCrLf & _
&quot;For Account &quot; & cmbAccount.Text & vbCrLf & _
sHandler & vbCrLf & _
&quot;Assign these claims to handler &quot; & cmbNewHandler.Text & vbCrLf & _
&quot;Assign all diaries for these claims to handler &quot; & cmbNewHandler.Text

___________________________________________

txtSQL.Text = &quot;select count(*) &quot; & vbCrLf & _
&quot;from CLAIM&quot; & vbCrLf & _
&quot;where CLAIM.ACC_NUM = &quot; & &quot;'&quot; & cmbAccount.Text & &quot;'&quot; & vbCrLf & _
&quot;and &quot; & sClaimSQL & vbCrLf & _
&quot;and CLAIM.EXM_COD &quot; & sHandlerSQL & &quot;;&quot;

 

[benjamenus]

Sorry, you may also need the sClaimSQL variable

________________________________

If iClaimStatus = 1 Then
sClaimStatus = &quot;OPEN and REOPENED claims only&quot;
sClaimSQL = &quot;CLAIM.STATUS <> 'C'&quot;
ElseIf iClaimStatus = 2 Then
sClaimStatus = &quot;CLOSED claims only&quot;
sClaimSQL = &quot;CLAIM.STATUS = 'C'&quot;
ElseIf iClaimStatus = 3 Then
sClaimStatus = &quot;ALL claims (OPEN, REOPENED and CLOSED)&quot;
sClaimSQL = &quot;CLAIM.STATUS in ('O','R','C')&quot;
Else
MsgBox (&quot;Error: iClaimStatus = &quot; & iClaimStatus)
End If
________________________________

It's not a very 'neat' solution, but it does work?!

 

[luismun]

Hey Benjamenus,

Thanks for the tip, but I am a little confused on how you generate the where clause. Can you provide you e-mail address so that I can ask you more detailed questions on how your application works.

Regards,

LM.

 

[benjamenus]

Trying to keep this forum based....

The solution posed was based on a VB app with an Access back end, so not quite what the orignal question referred to. You have to use your imagination a bit, but the code is based on user entries in a form

chkbox: open and reopened claims
chkbox: closed claims

cmbbox: account codes

radio: assign only those claims where current handler is...
radio: assign all claims regardless of handler

cmbbox: id of current handler (only available depending on answer above)
cmbbox: id of new handler

Hopefully you have a better idea of how that works.
____________________

Back to the original question: build SQL such as...

SELECT Groupfor.Plant, Groupfor.PkgFamily, Groupfor.LeadFrame, Groupfor.PackSize
FROM Groupfor
WHERE (((Groupfor.LeadFrame)=&quot;STD&quot; Or (Groupfor.LeadFrame)=&quot;HDLF&quot AND ((Groupfor.PackSize)=&quot;28x28&quot) OR (((Groupfor.LeadFrame)=&quot;STD&quot AND ((Groupfor.PackSize)=&quot;10x10&quot);

... based on selections in an Access form.

I would recommend using the Build Expression utility. User values might look like this...

[Forms]![FormName]![ObjectName]

So the SQL might look like this...

SELECT Groupfor.Plant, Groupfor.PkgFamily, Groupfor.LeadFrame, Groupfor.PackSize
FROM Groupfor
WHERE (((Groupfor.LeadFrame)=[Forms]![frmGroupfor]![objLeadFrame1] Or (Groupfor.LeadFrame)=[Forms]![frmGroupfor]![objLeadFrame2]) AND ((Groupfor.PackSize)=[Forms]![frmGroupfor]![objPackSize1])) OR (((Groupfor.LeadFrame)=[Forms]![frmGroupfor]![objLeadFrame3]) AND ((Groupfor.PackSize)=[Forms]![frmGroupfor]![objPackSize2]));

The problem with this is that you need an extra drop-down for every OR option. The only way round is to build the SQL more dynamically (but with certain restrictions) - as per my VB code. Or, you could be really clever and let users build their own query graphically. This is much more involved (and being honest, beyond me) but also raises a MAJOR concern - it relies on users understanding SQL - how to use (), AND, OR, etc. I personally wouldn't risk it!

Hopefully this has given luismun a few ideas, and not been too boring/confusing for the rest of you. Will keep an eye out to see if anyone has any more slick solutions....

 

[luismun]

Hey Benjamenus,

Thanks for your help. I will try to keep this issue forum based. I will post my solution if I find something useful for everyone.

Rgds,

LM.