how do you code the following? 
Author Message
 how do you code the following?

Hello everyone,

I have a simple form with six textboxes on it, each databound to a database.
The user can choose which textboxes to enter information in.  They could
enter information in all, or in some of the textboxes.  In other words, they
can leave some of the textboxes blank and it wouldn't matter.  I would like
to be able to query the database using only the information that is entered.
Is there a way that I could change the query according to which information
was entered?

Here are the textboxes on my form:  lastname, firstname, school, ss#,
userid, and sku.  If you user enters information on for lastname, firstname,
and userid, I would like the sqlquery to be equal to something like :
select * from mytable where lastname="Smith", firstname="Bob", and
userid="2"

But, if the user enters information for only school and ss#, I would like it
to be like "select * from mytable where school="Hopkinsville",
ss#="###-##-####",

I would like the query to change accordingly.

Thank you very much in advance!  Right now I have around 100 or so querires.

--
Brian Marston



Sun, 25 Mar 2001 03:00:00 GMT  
 how do you code the following?
Brian,

I am quite new to database programming, so there more than likely is a better
solution out there, but here is one that should work...

dim qry as String
qry="select * from mytable where"
if lastname.text = not "" then
    qry=qry & lastname.text & ", "
end if
if firstname.text = not "" then
    qry=qry & firstname.text & ", "
end if
if.......
    qry=qry & .... & ", "
end if
qry=left(qry,len(qry)-2);    '-2 to remove the last ", "

I hope this helps a bit...good luck.  I did not test any of this code
(being as that I am on the job right now and have a db of my own
I have to get coded up).  But a little tweeking and a little looking
into the help files should get it running.  Have a good day.

Just Me

Quote:

> Hello everyone,

> I have a simple form with six textboxes on it, each databound to a database.
> The user can choose which textboxes to enter information in.  They could
> enter information in all, or in some of the textboxes.  In other words, they
> can leave some of the textboxes blank and it wouldn't matter.  I would like
> to be able to query the database using only the information that is entered.
> Is there a way that I could change the query according to which information
> was entered?

> Here are the textboxes on my form:  lastname, firstname, school, ss#,
> userid, and sku.  If you user enters information on for lastname, firstname,
> and userid, I would like the sqlquery to be equal to something like :
> select * from mytable where lastname="Smith", firstname="Bob", and
> userid="2"

> But, if the user enters information for only school and ss#, I would like it
> to be like "select * from mytable where school="Hopkinsville",
> ss#="###-##-####",

> I would like the query to change accordingly.

> Thank you very much in advance!  Right now I have around 100 or so querires.

> --
> Brian Marston




Sun, 25 Mar 2001 03:00:00 GMT  
 how do you code the following?
Set up your 6 textboxes as a control array, say MyText(0) to MyText(5), then try
the following code:

Dim SQLString as String
Dim FirstTextFinished As Boolean

SQLString="SELECT * FROM MyTable WHERE "

For X = 0 to 5
    If MyText(X).Text <> "" Then
        If FirstTextFinished then SQLString = SQLString & " AND "
        SQLString = SQLString & MyText(X).DataField & " = " & Chr(39) &
MyText(X).Text & Chr(39)
        FirstTextFinished = True
    End If
Next

Assuming that your textboxes have a valid DataField entry, this should generate
your SQL string.
BTW, Chr(39) is a single quote
--
Robert R. Thele


[Remove NoSpam_ to reply via E-mail]

Quote:

>Hello everyone,

>I have a simple form with six textboxes on it, each databound to a database.
>The user can choose which textboxes to enter information in.  They could
>enter information in all, or in some of the textboxes.  In other words, they
>can leave some of the textboxes blank and it wouldn't matter.  I would like
>to be able to query the database using only the information that is entered.
>Is there a way that I could change the query according to which information
>was entered?

>Here are the textboxes on my form:  lastname, firstname, school, ss#,
>userid, and sku.  If you user enters information on for lastname, firstname,
>and userid, I would like the sqlquery to be equal to something like :
>select * from mytable where lastname="Smith", firstname="Bob", and
>userid="2"

>But, if the user enters information for only school and ss#, I would like it
>to be like "select * from mytable where school="Hopkinsville",
>ss#="###-##-####",

>I would like the query to change accordingly.

>Thank you very much in advance!  Right now I have around 100 or so querires.

>--
>Brian Marston




Sun, 25 Mar 2001 03:00:00 GMT  
 how do you code the following?
Brian,

Dim sSQL As String

sSQL = AddCriteria(txtFirstName, "fldFirstName", sSQL)
sSQL = AddCriteria(txtLastName, "fldLastName", sSQL)
sSQL = AddCriteria(txtSchool, "fldSchool", sSQL)

If Len(sSQL) > 0 Then sSQL = "SELECT * FROM MyTable WHERE " & sSQL

GetSQL = sSQL

Private Function AddCriteria(ByVal txt As TextBox, _
                             ByVal sFldName As String, _
                             ByVal sSQLCur As String) As String

If Len(txt) > 0 Then
    If Len(sSQLCur) > 0 Then sSQLCur = sSQLCur & " AND "
    sSQLCur = sSQLCur & sFldName & " = '" & txt & "'"
End If

AddCriteria = sSQLCur
End Function

HTH,

Alexander Shirshov

Quote:

>Hello everyone,

>I have a simple form with six textboxes on it, each databound to a
database.
>The user can choose which textboxes to enter information in.  They could
>enter information in all, or in some of the textboxes.  In other words,
they
>can leave some of the textboxes blank and it wouldn't matter.  I would like
>to be able to query the database using only the information that is
entered.
>Is there a way that I could change the query according to which information
>was entered?

>Here are the textboxes on my form:  lastname, firstname, school, ss#,
>userid, and sku.  If you user enters information on for lastname,
firstname,
>and userid, I would like the sqlquery to be equal to something like :
>select * from mytable where lastname="Smith", firstname="Bob", and
>userid="2"

>But, if the user enters information for only school and ss#, I would like
it
>to be like "select * from mytable where school="Hopkinsville",
>ss#="###-##-####",

>I would like the query to change accordingly.

>Thank you very much in advance!  Right now I have around 100 or so
querires.

>--
>Brian Marston




Mon, 26 Mar 2001 03:00:00 GMT  
 how do you code the following?
Aaron,
Try the code before entering it to the newsgroup. Your code fails in more than one
way.
Non-tried code usually don't help at all, specially if your new to the programming
issue

/kjell



Tue, 27 Mar 2001 03:00:00 GMT  
 how do you code the following?
1. In IDE build control array of 6 textboxes (called UserTextBoxes)
2. Store field names in textbox tag property
3. Try this code:

Function BuildSQL ()
Dim tmpSQL$, i%

   tmpSQL = "Select * from [My Table] Where "
   For i = 0 To 5
      If Len(UserTextBox(i)) Then
         tmpSQL = tmpSQL & UserTextBox(i).Tag & "='" & UserTextBox(i) & "' and "
      End If
   Next
   If Len(tmpSQL) > 32 Then BuildSQL = Left$(tmpSQL, Len(tmpSQL) - 4)

End Function

Quote:

> Hello everyone,

> I have a simple form with six textboxes on it, each databound to a database.
> The user can choose which textboxes to enter information in.  They could
> enter information in all, or in some of the textboxes.  In other words, they
> can leave some of the textboxes blank and it wouldn't matter.  I would like
> to be able to query the database using only the information that is entered.
> Is there a way that I could change the query according to which information
> was entered?

> Here are the textboxes on my form:  lastname, firstname, school, ss#,
> userid, and sku.  If you user enters information on for lastname, firstname,
> and userid, I would like the sqlquery to be equal to something like :
> select * from mytable where lastname="Smith", firstname="Bob", and
> userid="2"

> But, if the user enters information for only school and ss#, I would like it
> to be like "select * from mytable where school="Hopkinsville",
> ss#="###-##-####",

> I would like the query to change accordingly.

> Thank you very much in advance!  Right now I have around 100 or so querires.

> --
> Brian Marston




Tue, 27 Mar 2001 03:00:00 GMT  
 how do you code the following?
Sorry about that...I am new to newsgroups also.  : )
Still learning proper etiquette.  Thanks for the advice.

Just Me

Quote:

> Aaron,
> Try the code before entering it to the newsgroup. Your code fails in more than one
> way.
> Non-tried code usually don't help at all, specially if your new to the programming
> issue

> /kjell



Tue, 27 Mar 2001 03:00:00 GMT  
 how do you code the following?

Off the top of my head here is the code that should get you started... You may
have to clean it a little!  Try this:

Dim Cntrl as control, sqlWhere as string

FOR EACH cntrl IN Form.controls
    'make sure the control is a textbox
    IF TYPEOF Cntrl IS Textbox THEN
        'And only do the ones that have data entered
         IF LEN(TRIM(Cntrl.Text)) > 0 THEN
            'now add to the sqlWhere clause all textboxes that information
            sqlWhere=sqlWhere & Cntrl.Datafield & "='" & TRIM(Cntrl.Text) & "'"
        ENDIF
    ENDIF
NEXT Cntrl
'Now strip the final comma so we can add it to the SQL string further on
sqlWhere=sqlWhere & LEFT(sqlWhere,LEN(sqlWhere)-1)

Kevin Sehl, MCP,MCT,MCSD,MCSE

Quote:

> Hello everyone,

> I have a simple form with six textboxes on it, each databound to a database.
> The user can choose which textboxes to enter information in.  They could
> enter information in all, or in some of the textboxes.  In other words, they
> can leave some of the textboxes blank and it wouldn't matter.  I would like
> to be able to query the database using only the information that is entered.
> Is there a way that I could change the query according to which information
> was entered?

> Here are the textboxes on my form:  lastname, firstname, school, ss#,
> userid, and sku.  If you user enters information on for lastname, firstname,
> and userid, I would like the sqlquery to be equal to something like :
> select * from mytable where lastname="Smith", firstname="Bob", and
> userid="2"

> But, if the user enters information for only school and ss#, I would like it
> to be like "select * from mytable where school="Hopkinsville",
> ss#="###-##-####",

> I would like the query to change accordingly.

> Thank you very much in advance!  Right now I have around 100 or so querires.

> --
> Brian Marston


  vcard.vcf
< 1K Download


Sun, 01 Apr 2001 03:00:00 GMT  
 how do you code the following?

Larry,

Please see my response to this problem... the problem with your method is that since
the controls ARE bound, we can use the datafield property.  Use of the tag property
adds to the maintenance nightmare... if the fieldname changes later, we have to
change it in 2 places rather than one.  And you are assuming that the controls have
no code that distinguishes them from the others, a necessity if you are going to
place them into a control array.

Kevin Sehl, MCP, MCT, MCSD, MCSE

Quote:

> 1. In IDE build control array of 6 textboxes (called UserTextBoxes)
> 2. Store field names in textbox tag property
> 3. Try this code:

> Function BuildSQL ()
> Dim tmpSQL$, i%

>    tmpSQL = "Select * from [My Table] Where "
>    For i = 0 To 5
>       If Len(UserTextBox(i)) Then
>          tmpSQL = tmpSQL & UserTextBox(i).Tag & "='" & UserTextBox(i) & "' and "
>       End If
>    Next
>    If Len(tmpSQL) > 32 Then BuildSQL = Left$(tmpSQL, Len(tmpSQL) - 4)

> End Function


> > Hello everyone,

> > I have a simple form with six textboxes on it, each databound to a database.
> > The user can choose which textboxes to enter information in.  They could
> > enter information in all, or in some of the textboxes.  In other words, they
> > can leave some of the textboxes blank and it wouldn't matter.  I would like
> > to be able to query the database using only the information that is entered.
> > Is there a way that I could change the query according to which information
> > was entered?

> > Here are the textboxes on my form:  lastname, firstname, school, ss#,
> > userid, and sku.  If you user enters information on for lastname, firstname,
> > and userid, I would like the sqlquery to be equal to something like :
> > select * from mytable where lastname="Smith", firstname="Bob", and
> > userid="2"

> > But, if the user enters information for only school and ss#, I would like it
> > to be like "select * from mytable where school="Hopkinsville",
> > ss#="###-##-####",

> > I would like the query to change accordingly.

> > Thank you very much in advance!  Right now I have around 100 or so querires.

> > --
> > Brian Marston


  vcard.vcf
< 1K Download


Sun, 01 Apr 2001 03:00:00 GMT  
 how do you code the following?
It is amazing how some people place themselves so far above others that the
fail to remember that the responsiblity of teaching lies with those that
have learned. kjell were u born knowing  VB ? You said the code will
fail.... tell them how....Teach don't preach. off my soap box.
Quote:

>Aaron,
>Try the code before entering it to the newsgroup. Your code fails in more
than one
>way.
>Non-tried code usually don't help at all, specially if your new to the
programming
>issue

>/kjell



Mon, 02 Apr 2001 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Why does the following code not work?

2. Please help on the following code fragment

3. Problems with the following mail merge code

4. Need to Change the Font and SIZE in the following VB code

5. code to follow hyperlinks

6. Utility to add the following code to .dot files

7. Convert the following C# code to VB please

8. How can I perform the following codes in VB.NET

9. Help: Whats wrong with following code??

10. Coding conventions: Stand Strong or Follow Along?

11. Help: Whats wrong with following code??

12. Can anyone port the following C code?

 

 
Powered by phpBB® Forum Software