A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Corrupted" query



 
 
Thread Tools Display Modes
  #1  
Old August 4th, 2004, 05:17 PM
Jeff J
external usenet poster
 
Posts: n/a
Default "Corrupted" query

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks
  #2  
Old August 5th, 2004, 01:27 AM
John Spencer (MVP)
external usenet poster
 
Posts: n/a
Default "Corrupted" query

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks

  #3  
Old August 5th, 2004, 05:09 PM
Jeff J
external usenet poster
 
Posts: n/a
Default "Corrupted" query

Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
Thanks

"John Spencer (MVP)" wrote:

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks


  #4  
Old August 5th, 2004, 10:08 PM
John Spencer (MVP)
external usenet poster
 
Posts: n/a
Default "Corrupted" query

Yeah, with seven fields the query once it is reformatted by Access gets to
complex to run.

You might try building the SQL on the fly and then setting that as the record
source for the report. How skillful are you with VBA?

Something you can try,but may be slow is to build a query like

Field: First_Name: [FirstName] & ""
Criteria: "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

You don't need the Is Null criteria, since the calculated field will never be
null. So if [Forms]![frmFilterDialog]![FirstName] is blank (or null) you will
get all records returned.

Another solution is to build your report query without a where clause and then
append one. Something likethe following code for a button on your form.

Private Sub PrintReport_Click()
Dim StrWhere as String

'For each control build a
IF Len([Forms]![frmFilterDialog]![FirstName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [FirstName]=""" &
[Forms]![frmFilterDialog]![FirstName] & """"
End IF

IF Len([Forms]![frmFilterDialog]![LastName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [LastName]=""" &
[Forms]![frmFilterDialog]![LastName] & """"
End IF

'repeat as necessary for your other fields, using the proper field delimiters.
Then call the report

DoCmd.OpenReport "YourReportName", acPreview, , strWhere

'The strWhere applies the criteria to query.

End Sub

Jeff J wrote:

Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
Thanks

"John Spencer (MVP)" wrote:

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks


  #5  
Old August 6th, 2004, 06:29 PM
Jeff J
external usenet poster
 
Posts: n/a
Default "Corrupted" query

Thank you so much for the If statements below. It works very well except it
is too strict in filtering. Could you tell me how to modify the strWhere =
strWhere & statements so that they build a string more like [LastName] =
"*John*" or some other way to produce a "looser" filtering? Thanks again.

"John Spencer (MVP)" wrote:

Yeah, with seven fields the query once it is reformatted by Access gets to
complex to run.

You might try building the SQL on the fly and then setting that as the record
source for the report. How skillful are you with VBA?

Something you can try,but may be slow is to build a query like

Field: First_Name: [FirstName] & ""
Criteria: "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

You don't need the Is Null criteria, since the calculated field will never be
null. So if [Forms]![frmFilterDialog]![FirstName] is blank (or null) you will
get all records returned.

Another solution is to build your report query without a where clause and then
append one. Something likethe following code for a button on your form.

Private Sub PrintReport_Click()
Dim StrWhere as String

'For each control build a
IF Len([Forms]![frmFilterDialog]![FirstName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [FirstName]=""" &
[Forms]![frmFilterDialog]![FirstName] & """"
End IF

IF Len([Forms]![frmFilterDialog]![LastName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [LastName]=""" &
[Forms]![frmFilterDialog]![LastName] & """"
End IF

'repeat as necessary for your other fields, using the proper field delimiters.
Then call the report

DoCmd.OpenReport "YourReportName", acPreview, , strWhere

'The strWhere applies the criteria to query.

End Sub

Jeff J wrote:

Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
Thanks

"John Spencer (MVP)" wrote:

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks


  #6  
Old August 7th, 2004, 01:05 AM
John Spencer (MVP)
external usenet poster
 
Posts: n/a
Default "Corrupted" query


Instead of " [Firstname]=""" & [Forms]![frmFilterDialog]![FirstName] & """"

Use

" [Firstname] Like ""*"" & " & [Forms]![frmFilterDialog]![FirstName] & " & ""*"""

That looks complicated, but in order to get one quote mark inside a string, you
need to have two quote marks.

If you run into problems, try using a debug.print statement or a message box to
show the string before you actually use it.

Msgbox strWhere



Jeff J wrote:

Thank you so much for the If statements below. It works very well except it
is too strict in filtering. Could you tell me how to modify the strWhere =
strWhere & statements so that they build a string more like [LastName] =
"*John*" or some other way to produce a "looser" filtering? Thanks again.

"John Spencer (MVP)" wrote:

Yeah, with seven fields the query once it is reformatted by Access gets to
complex to run.

You might try building the SQL on the fly and then setting that as the record
source for the report. How skillful are you with VBA?

Something you can try,but may be slow is to build a query like

Field: First_Name: [FirstName] & ""
Criteria: "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

You don't need the Is Null criteria, since the calculated field will never be
null. So if [Forms]![frmFilterDialog]![FirstName] is blank (or null) you will
get all records returned.

Another solution is to build your report query without a where clause and then
append one. Something likethe following code for a button on your form.

Private Sub PrintReport_Click()
Dim StrWhere as String

'For each control build a
IF Len([Forms]![frmFilterDialog]![FirstName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [FirstName]=""" &
[Forms]![frmFilterDialog]![FirstName] & """"
End IF

IF Len([Forms]![frmFilterDialog]![LastName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [LastName]=""" &
[Forms]![frmFilterDialog]![LastName] & """"
End IF

'repeat as necessary for your other fields, using the proper field delimiters.
Then call the report

DoCmd.OpenReport "YourReportName", acPreview, , strWhere

'The strWhere applies the criteria to query.

End Sub

Jeff J wrote:

Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
Thanks

"John Spencer (MVP)" wrote:

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks


  #7  
Old August 9th, 2004, 06:07 PM
Jeff J
external usenet poster
 
Posts: n/a
Default "Corrupted" query

Thanks again for the new code. Using your MsgBox idea I can see that it
creates a valid where statement but now the OpenReport statement displays a
dialog entitled "Enter Parameter Value", with the string entered on the form
just below that title, and a text box to enter a string below that, for each
Like statement. If I enter the string in the text box(s) the report is
correct. Any ideas?

"John Spencer (MVP)" wrote:


Instead of " [Firstname]=""" & [Forms]![frmFilterDialog]![FirstName] & """"

Use

" [Firstname] Like ""*"" & " & [Forms]![frmFilterDialog]![FirstName] & " & ""*"""

That looks complicated, but in order to get one quote mark inside a string, you
need to have two quote marks.

If you run into problems, try using a debug.print statement or a message box to
show the string before you actually use it.

Msgbox strWhere



Jeff J wrote:

Thank you so much for the If statements below. It works very well except it
is too strict in filtering. Could you tell me how to modify the strWhere =
strWhere & statements so that they build a string more like [LastName] =
"*John*" or some other way to produce a "looser" filtering? Thanks again.

"John Spencer (MVP)" wrote:

Yeah, with seven fields the query once it is reformatted by Access gets to
complex to run.

You might try building the SQL on the fly and then setting that as the record
source for the report. How skillful are you with VBA?

Something you can try,but may be slow is to build a query like

Field: First_Name: [FirstName] & ""
Criteria: "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

You don't need the Is Null criteria, since the calculated field will never be
null. So if [Forms]![frmFilterDialog]![FirstName] is blank (or null) you will
get all records returned.

Another solution is to build your report query without a where clause and then
append one. Something likethe following code for a button on your form.

Private Sub PrintReport_Click()
Dim StrWhere as String

'For each control build a
IF Len([Forms]![frmFilterDialog]![FirstName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [FirstName]=""" &
[Forms]![frmFilterDialog]![FirstName] & """"
End IF

IF Len([Forms]![frmFilterDialog]![LastName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [LastName]=""" &
[Forms]![frmFilterDialog]![LastName] & """"
End IF

'repeat as necessary for your other fields, using the proper field delimiters.
Then call the report

DoCmd.OpenReport "YourReportName", acPreview, , strWhere

'The strWhere applies the criteria to query.

End Sub

Jeff J wrote:

Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
Thanks

"John Spencer (MVP)" wrote:

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks



  #8  
Old August 9th, 2004, 08:31 PM
John Spencer (MVP)
external usenet poster
 
Posts: n/a
Default "Corrupted" query

Then we've mucked up the where statement somehow and Access thinks we are
passing it a field name that it does not recognize.

What you should see for example would be something like following (assuming
FirstName control is "John"

[FirstName] Like "*John*"

Of course, with your field name. AUUUGH! and now that I look at it I stuck in
some very unneeded ampersands.

" [FirstName] Like ""*" & [Forms]![frmFilterDialog]![FirstName] & "*"""

It might be clearer if you use the Chr function - Chr(34) is a quote mark if I
recall correctly.

" [FirstName] Like " & Chr(34) & "*" & [Forms]![frmFilterDialog]![FirstName]
& "*" & chr(34)

Play with those and see if you can get the expected string.

I am going offline for a few days, so if this doesn't work try starting a new
thread with details as to where you are.

Jeff J wrote:

Thanks again for the new code. Using your MsgBox idea I can see that it
creates a valid where statement but now the OpenReport statement displays a
dialog entitled "Enter Parameter Value", with the string entered on the form
just below that title, and a text box to enter a string below that, for each
Like statement. If I enter the string in the text box(s) the report is
correct. Any ideas?

"John Spencer (MVP)" wrote:


Instead of " [Firstname]=""" & [Forms]![frmFilterDialog]![FirstName] & """"

Use

" [Firstname] Like ""*"" & " & [Forms]![frmFilterDialog]![FirstName] & " & ""*"""

That looks complicated, but in order to get one quote mark inside a string, you
need to have two quote marks.

If you run into problems, try using a debug.print statement or a message box to
show the string before you actually use it.

Msgbox strWhere



Jeff J wrote:

Thank you so much for the If statements below. It works very well except it
is too strict in filtering. Could you tell me how to modify the strWhere =
strWhere & statements so that they build a string more like [LastName] =
"*John*" or some other way to produce a "looser" filtering? Thanks again.

"John Spencer (MVP)" wrote:

Yeah, with seven fields the query once it is reformatted by Access gets to
complex to run.

You might try building the SQL on the fly and then setting that as the record
source for the report. How skillful are you with VBA?

Something you can try,but may be slow is to build a query like

Field: First_Name: [FirstName] & ""
Criteria: "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

You don't need the Is Null criteria, since the calculated field will never be
null. So if [Forms]![frmFilterDialog]![FirstName] is blank (or null) you will
get all records returned.

Another solution is to build your report query without a where clause and then
append one. Something likethe following code for a button on your form.

Private Sub PrintReport_Click()
Dim StrWhere as String

'For each control build a
IF Len([Forms]![frmFilterDialog]![FirstName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [FirstName]=""" &
[Forms]![frmFilterDialog]![FirstName] & """"
End IF

IF Len([Forms]![frmFilterDialog]![LastName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [LastName]=""" &
[Forms]![frmFilterDialog]![LastName] & """"
End IF

'repeat as necessary for your other fields, using the proper field delimiters.
Then call the report

DoCmd.OpenReport "YourReportName", acPreview, , strWhere

'The strWhere applies the criteria to query.

End Sub

Jeff J wrote:

Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
Thanks

"John Spencer (MVP)" wrote:

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks



  #9  
Old August 12th, 2004, 07:51 PM
Jeff J
external usenet poster
 
Posts: n/a
Default "Corrupted" query

Thanks so much John. After looking a bit closer I saw that the statement was
producing a string like [FirstName] Like "*&John&*" so I used your line with
the Chr(34) and it works perfectly. Thanks for the VB lesson.

"John Spencer (MVP)" wrote:

Then we've mucked up the where statement somehow and Access thinks we are
passing it a field name that it does not recognize.

What you should see for example would be something like following (assuming
FirstName control is "John"

[FirstName] Like "*John*"

Of course, with your field name. AUUUGH! and now that I look at it I stuck in
some very unneeded ampersands.

" [FirstName] Like ""*" & [Forms]![frmFilterDialog]![FirstName] & "*"""

It might be clearer if you use the Chr function - Chr(34) is a quote mark if I
recall correctly.

" [FirstName] Like " & Chr(34) & "*" & [Forms]![frmFilterDialog]![FirstName]
& "*" & chr(34)

Play with those and see if you can get the expected string.

I am going offline for a few days, so if this doesn't work try starting a new
thread with details as to where you are.

Jeff J wrote:

Thanks again for the new code. Using your MsgBox idea I can see that it
creates a valid where statement but now the OpenReport statement displays a
dialog entitled "Enter Parameter Value", with the string entered on the form
just below that title, and a text box to enter a string below that, for each
Like statement. If I enter the string in the text box(s) the report is
correct. Any ideas?

"John Spencer (MVP)" wrote:


Instead of " [Firstname]=""" & [Forms]![frmFilterDialog]![FirstName] & """"

Use

" [Firstname] Like ""*"" & " & [Forms]![frmFilterDialog]![FirstName] & " & ""*"""

That looks complicated, but in order to get one quote mark inside a string, you
need to have two quote marks.

If you run into problems, try using a debug.print statement or a message box to
show the string before you actually use it.

Msgbox strWhere



Jeff J wrote:

Thank you so much for the If statements below. It works very well except it
is too strict in filtering. Could you tell me how to modify the strWhere =
strWhere & statements so that they build a string more like [LastName] =
"*John*" or some other way to produce a "looser" filtering? Thanks again.

"John Spencer (MVP)" wrote:

Yeah, with seven fields the query once it is reformatted by Access gets to
complex to run.

You might try building the SQL on the fly and then setting that as the record
source for the report. How skillful are you with VBA?

Something you can try,but may be slow is to build a query like

Field: First_Name: [FirstName] & ""
Criteria: "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

You don't need the Is Null criteria, since the calculated field will never be
null. So if [Forms]![frmFilterDialog]![FirstName] is blank (or null) you will
get all records returned.

Another solution is to build your report query without a where clause and then
append one. Something likethe following code for a button on your form.

Private Sub PrintReport_Click()
Dim StrWhere as String

'For each control build a
IF Len([Forms]![frmFilterDialog]![FirstName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [FirstName]=""" &
[Forms]![frmFilterDialog]![FirstName] & """"
End IF

IF Len([Forms]![frmFilterDialog]![LastName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [LastName]=""" &
[Forms]![frmFilterDialog]![LastName] & """"
End IF

'repeat as necessary for your other fields, using the proper field delimiters.
Then call the report

DoCmd.OpenReport "YourReportName", acPreview, , strWhere

'The strWhere applies the criteria to query.

End Sub

Jeff J wrote:

Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
Thanks

"John Spencer (MVP)" wrote:

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
query wizard error Christen General Discussion 12 August 13th, 2004 08:37 PM
Display Parameter from Form on Report sara Setting Up & Running Reports 10 July 19th, 2004 04:54 PM
Hidden files in Ms-Query cause ODBC connect errors or Query is wac needyourhelp General Discussion 4 July 12th, 2004 09:38 PM
SELECT function in Query alexparks Running & Setting Up Queries 9 July 5th, 2004 11:31 AM
query field reference help -dch Running & Setting Up Queries 4 June 2nd, 2004 07:30 PM


All times are GMT +1. The time now is 07:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.