SQL problem

[Tony Williams]

I am trying to build a form that the user has a series of Check Boxes and
unbound text boxes to choose which fields from 2 tables to search on and the
criteria for the search. I am following the idea from "Beginning Access 2000
VBA" by Smith and Sussman and trying to adapt their code to fit my form.
Basically the checkbox form builds a query that collects the results.
There is a command button that should execute the query. Here is the OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the text which is
input in the textboxes on the form and loads them into the query with square
brackets as if it's the name of a field. For example if I input "Williams"
in the txtname text box it should find me all records in tblInstitution1
where the txtlastname field is Williams. However when I open the query the
criteria is shown as [williams] and not as "williams" and when I run the
query I get a message that asks me for the value of a field williams.

I'm a novice at VBA and am using the book to try and understand it but when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony

 

[Douglas J Steele]

Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname & Chr$(34)

 

[Tony Williams]

Thanks Douglas that worked fine. Next step is how do I change that line to
accept a wild card like S* to give me everyname starting with S
Thanks this is really helping me to produce a useful form
Tony

Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to build a form that the user has a series of Check Boxes and
unbound text boxes to choose which fields from 2 tables to search on and the
criteria for the search. I am following the idea from "Beginning Access 2000
VBA" by Smith and Sussman and trying to adapt their code to fit my form.
Basically the checkbox form builds a query that collects the results.
There is a command button that should execute the query. Here is the OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the text which
is
input in the textboxes on the form and loads them into the query with square
brackets as if it's the name of a field. For example if I input
"Williams"
in the txtname text box it should find me all records in tblInstitution1
where the txtlastname field is Williams. However when I open the query
the
criteria is shown as [williams] and not as "williams" and when I run the
query I get a message that asks me for the value of a field williams.

I'm a novice at VBA and am using the book to try and understand it but when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony

 

[Douglas J Steele]

strWHERE = " AND i.txtlastname LIKE " & Chr$(34) & txtname & "*" & Chr$(34)

(assuming txtname contains "S")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas that worked fine. Next step is how do I change that line to
accept a wild card like S* to give me everyname starting with S
Thanks this is really helping me to produce a useful form
Tony

Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to build a form that the user has a series of Check Boxes and
unbound text boxes to choose which fields from 2 tables to search on

and
the

criteria for the search. I am following the idea from "Beginning Access 2000
VBA" by Smith and Sussman and trying to adapt their code to fit my form.
Basically the checkbox form builds a query that collects the results.
There is a command button that should execute the query. Here is the OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the text which
is
input in the textboxes on the form and loads them into the query with square
brackets as if it's the name of a field. For example if I input
"Williams"
in the txtname text box it should find me all records in tblInstitution1
where the txtlastname field is Williams. However when I open the query
the
criteria is shown as [williams] and not as "williams" and when I run the
query I get a message that asks me for the value of a field williams.

I'm a novice at VBA and am using the book to try and understand it but when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony

 

[Tony Williams]

That worked great. However I tried the same thing with txtinstitution and
used this
strWHERE = " AND i.txtinstitution = " & Chr$(34) & txtcompany & "*" &
Chr$(34)
But that didn't work? It opened my form but at a new record???
Both are text so why would one work and not the other?
Baffled! One day all this will click into my 61 yearold brain
Tony

strWHERE = " AND i.txtlastname LIKE " & Chr$(34) & txtname & "*" &
Chr$(34)

(assuming txtname contains "S")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas that worked fine. Next step is how do I change that line
to
accept a wild card like S* to give me everyname starting with S
Thanks this is really helping me to produce a useful form
Tony

Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to build a form that the user has a series of Check Boxes and
unbound text boxes to choose which fields from 2 tables to search on and
the
criteria for the search. I am following the idea from "Beginning
Access
2000
VBA" by Smith and Sussman and trying to adapt their code to fit my form.
Basically the checkbox form builds a query that collects the results.
There is a command button that should execute the query. Here is the
OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the text
which
is
input in the textboxes on the form and loads them into the query with
square
brackets as if it's the name of a field. For example if I input
"Williams"
in the txtname text box it should find me all records in tblInstitution1
where the txtlastname field is Williams. However when I open the query
the
criteria is shown as [williams] and not as "williams" and when I run the
query I get a message that asks me for the value of a field williams.

I'm a novice at VBA and am using the book to try and understand it but
when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony

 

[Douglas J Steele]

When using wildcards, you need to use LIKE, not =.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That worked great. However I tried the same thing with txtinstitution and
used this
strWHERE = " AND i.txtinstitution = " & Chr$(34) & txtcompany & "*" &
Chr$(34)
But that didn't work? It opened my form but at a new record???
Both are text so why would one work and not the other?
Baffled! One day all this will click into my 61 yearold brain
Tony

strWHERE = " AND i.txtlastname LIKE " & Chr$(34) & txtname & "*" &
Chr$(34)

(assuming txtname contains "S")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas that worked fine. Next step is how do I change that line
to
accept a wild card like S* to give me everyname starting with S
Thanks this is really helping me to produce a useful form
Tony
Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to build a form that the user has a series of Check

Boxes
and

unbound text boxes to choose which fields from 2 tables to search on and
the
criteria for the search. I am following the idea from "Beginning
Access
2000
VBA" by Smith and Sussman and trying to adapt their code to fit my form.
Basically the checkbox form builds a query that collects the results.
There is a command button that should execute the query. Here is the
OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the text
which
is
input in the textboxes on the form and loads them into the query with
square
brackets as if it's the name of a field. For example if I input
"Williams"
in the txtname text box it should find me all records in tblInstitution1
where the txtlastname field is Williams. However when I open the query
the
criteria is shown as [williams] and not as "williams" and when I run the
query I get a message that asks me for the value of a field williams.

I'm a novice at VBA and am using the book to try and understand it but
when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony

 

[Tony Williams]

Sorry Douglas I didn't spot that I feel a fool!!!
Tony

When using wildcards, you need to use LIKE, not =.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That worked great. However I tried the same thing with txtinstitution and
used this
strWHERE = " AND i.txtinstitution = " & Chr$(34) & txtcompany & "*" &
Chr$(34)
But that didn't work? It opened my form but at a new record???
Both are text so why would one work and not the other?
Baffled! One day all this will click into my 61 yearold brain
Tony

strWHERE = " AND i.txtlastname LIKE " & Chr$(34) & txtname & "*" &
Chr$(34)

(assuming txtname contains "S")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Douglas that worked fine. Next step is how do I change that
line
to
accept a wild card like S* to give me everyname starting with S
Thanks this is really helping me to produce a useful form
Tony
message
Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname &
Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to build a form that the user has a series of Check Boxes
and
unbound text boxes to choose which fields from 2 tables to search
on
and
the
criteria for the search. I am following the idea from "Beginning
Access
2000
VBA" by Smith and Sussman and trying to adapt their code to fit my
form.
Basically the checkbox form builds a query that collects the results.
There is a command button that should execute the query. Here is
the
OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the text
which
is
input in the textboxes on the form and loads them into the query with
square
brackets as if it's the name of a field. For example if I input
"Williams"
in the txtname text box it should find me all records in
tblInstitution1
where the txtlastname field is Williams. However when I open the query
the
criteria is shown as [williams] and not as "williams" and when I
run
the
query I get a message that asks me for the value of a field williams.

I'm a novice at VBA and am using the book to try and understand it but
when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony

 

[Tony Williams]

Douglas I would like to take this to another stage. Shall I post it here or
as a new post? At the moment the results are shown in a form after the
command button is pressed but I want some help as to how I can change that
to populate a list box on the search form and then select a record from the
list box. I think I can do the latter it's changing the code to populate the
list box instead of opening a form where I might struggle. Would you
recommend posting a new message with all the details or stick with this one?
Thanks
Tony

When using wildcards, you need to use LIKE, not =.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That worked great. However I tried the same thing with txtinstitution and
used this
strWHERE = " AND i.txtinstitution = " & Chr$(34) & txtcompany & "*" &
Chr$(34)
But that didn't work? It opened my form but at a new record???
Both are text so why would one work and not the other?
Baffled! One day all this will click into my 61 yearold brain
Tony

strWHERE = " AND i.txtlastname LIKE " & Chr$(34) & txtname & "*" &
Chr$(34)

(assuming txtname contains "S")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Douglas that worked fine. Next step is how do I change that
line
to
accept a wild card like S* to give me everyname starting with S
Thanks this is really helping me to produce a useful form
Tony
message
Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname &
Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to build a form that the user has a series of Check Boxes
and
unbound text boxes to choose which fields from 2 tables to search
on
and
the
criteria for the search. I am following the idea from "Beginning
Access
2000
VBA" by Smith and Sussman and trying to adapt their code to fit my
form.
Basically the checkbox form builds a query that collects the results.
There is a command button that should execute the query. Here is
the
OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the text
which
is
input in the textboxes on the form and loads them into the query with
square
brackets as if it's the name of a field. For example if I input
"Williams"
in the txtname text box it should find me all records in
tblInstitution1
where the txtlastname field is Williams. However when I open the query
the
criteria is shown as [williams] and not as "williams" and when I
run
the
query I get a message that asks me for the value of a field williams.

I'm a novice at VBA and am using the book to try and understand it but
when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony

 

[Douglas J Steele]

Probably best to start a new post. Often when a thread has multiple
responses in it, it's a little less likely to get attention.

Summarize the basic details of what you already have, and be explicit about
what you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas I would like to take this to another stage. Shall I post it here or
as a new post? At the moment the results are shown in a form after the
command button is pressed but I want some help as to how I can change that
to populate a list box on the search form and then select a record from the
list box. I think I can do the latter it's changing the code to populate the
list box instead of opening a form where I might struggle. Would you
recommend posting a new message with all the details or stick with this one?
Thanks
Tony

When using wildcards, you need to use LIKE, not =.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That worked great. However I tried the same thing with txtinstitution and
used this
strWHERE = " AND i.txtinstitution = " & Chr$(34) & txtcompany & "*" &
Chr$(34)
But that didn't work? It opened my form but at a new record???
Both are text so why would one work and not the other?
Baffled! One day all this will click into my 61 yearold brain
Tony
strWHERE = " AND i.txtlastname LIKE " & Chr$(34) & txtname & "*" &
Chr$(34)

(assuming txtname contains "S")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Douglas that worked fine. Next step is how do I change that
line
to
accept a wild card like S* to give me everyname starting with S
Thanks this is really helping me to produce a useful form
Tony
message
Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname &
Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to build a form that the user has a series of Check Boxes
and
unbound text boxes to choose which fields from 2 tables to search
on
and
the
criteria for the search. I am following the idea from "Beginning
Access
2000
VBA" by Smith and Sussman and trying to adapt their code to fit my
form.
Basically the checkbox form builds a query that collects the results.
There is a command button that should execute the query. Here is
the
OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " &

Mid$(strWHERE,
6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the text
which
is
input in the textboxes on the form and loads them into the query with
square
brackets as if it's the name of a field. For example if I input
"Williams"
in the txtname text box it should find me all records in
tblInstitution1
where the txtlastname field is Williams. However when I open the query
the
criteria is shown as [williams] and not as "williams" and when I
run
the
query I get a message that asks me for the value of a field williams.

I'm a novice at VBA and am using the book to try and understand

it
but

when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony

 

[Tony Williams]

Thanks Douglas I'll do that and thank you again for getting me this far
Tony

Probably best to start a new post. Often when a thread has multiple
responses in it, it's a little less likely to get attention.

Summarize the basic details of what you already have, and be explicit
about
what you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas I would like to take this to another stage. Shall I post it here or
as a new post? At the moment the results are shown in a form after the
command button is pressed but I want some help as to how I can change that
to populate a list box on the search form and then select a record from the
list box. I think I can do the latter it's changing the code to populate the
list box instead of opening a form where I might struggle. Would you
recommend posting a new message with all the details or stick with this one?
Thanks
Tony

When using wildcards, you need to use LIKE, not =.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That worked great. However I tried the same thing with txtinstitution and
used this
strWHERE = " AND i.txtinstitution = " & Chr$(34) & txtcompany & "*" &
Chr$(34)
But that didn't work? It opened my form but at a new record???
Both are text so why would one work and not the other?
Baffled! One day all this will click into my 61 yearold brain
Tony
message
strWHERE = " AND i.txtlastname LIKE " & Chr$(34) & txtname & "*" &
Chr$(34)

(assuming txtname contains "S")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Douglas that worked fine. Next step is how do I change that
line
to
accept a wild card like S* to give me everyname starting with S
Thanks this is really helping me to produce a useful form
Tony
message
Since txtname is text, it needs to be enclosed in quotes:

strWHERE = " AND i.txtlastname = " & Chr$(34) & txtname &
Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to build a form that the user has a series of Check
Boxes
and
unbound text boxes to choose which fields from 2 tables to
search
on
and
the
criteria for the search. I am following the idea from "Beginning
Access
2000
VBA" by Smith and Sussman and trying to adapt their code to fit my
form.
Basically the checkbox form builds a query that collects the
results.
There is a command button that should execute the query. Here is
the
OnClick
procedure of the button

Private Sub cmdFind_Click()

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL
End Sub

Here is my code for building the SQL

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT = "s.* "
strFROM = "tbldocument s "

If chkCompanyID Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtinstitution = " & txtcompany
End If

If chklastname Then
strFROM = strFROM & " INNER JOIN tblInstitution1 i " & _
"ON s.txtrefnbr = i.txtrefnbr "
strWHERE = " AND i.txtlastname = " & txtname
End If

If chkDaterange Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND s.txtExpiryDate >= " & _
"#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND s.DateOrdered <= " & _
"#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE,
6)

BuildSQLString = True

End Function

This seems to work except that the query it creates uses the
text
which
is
input in the textboxes on the form and loads them into the query
with
square
brackets as if it's the name of a field. For example if I input
"Williams"
in the txtname text box it should find me all records in
tblInstitution1
where the txtlastname field is Williams. However when I open the
query
the
criteria is shown as [williams] and not as "williams" and when I
run
the
query I get a message that asks me for the value of a field
williams.

I'm a novice at VBA and am using the book to try and understand it
but
when
things go wrong I'm not yet clever enough to work out why!!!

Can anyone help me here?
Thanks
Tony