Dynamic query problem

[maceslin]

Been trying to figure this one out for a while in my spare time by
following an example I found in a book but it just isn't working for
me. I figure if I can ge the first query to run I should be able to
figure the rest out (I hope!). I created this basic query in Access
and then cut and paste the SQL into the BuildSQLString function. I
have a total of 6 fields that could populate the query

Public Function BuildSQLString(srSQL As String) As Boolean

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "SELECT tblBasicData.Title,
tblBasicData.HyperlinkToLesson, tblComments.solution,
tblComments.DOTLMPF_ChoiceFK"
srFrom = "FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK;"


If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_ChoiceFk =" & cboDOT
End If

strSQL = "Select" & strSelect
strSQL = strSQL & "From " & strFrom
If strWhere <> "" Then strSQL = strSQL & "WHERE" & Mid$(srtHwere, 6)

BuildSQLString = True
End Function


This routine should return the above SQL string in a MSG box but all I
am getting is a VB Box with OK. No message saying problem creating
string

Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click
Dim strSQL As String
Dim strDocName As String



If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report"
End If

MsgBox strSQL

Exit_cmdViewReport_Click:
Exit Sub

Err_cmdViewReport_Click:
MsgBox err.Description
Resume Exit_cmdViewReport_Click

End Sub

Any thoughts on how to fix is appreciated
Thanks
Dave

 

[pietlinden]

Been trying to figure this one out for a while in my spare time by
following an example I found in a book but it just isn't working for
me. I figure if I can ge the first query to run I should be able to
figure the rest out (I hope!). I created this basic query in Access
and then cut and paste the SQL into the BuildSQLString function. I
have a total of 6 fields that could populate the query

Public Function BuildSQLString(srSQL As String) As Boolean

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "SELECT tblBasicData.Title,
tblBasicData.HyperlinkToLesson, tblComments.solution,
tblComments.DOTLMPF_ChoiceFK"
srFrom = "FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK;"

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_ChoiceFk =" & cboDOT
End If

strSQL = "Select" & strSelect
strSQL = strSQL & "From " & strFrom
If strWhere <> "" Then strSQL = strSQL & "WHERE" & Mid$(srtHwere, 6)

BuildSQLString = True
End Function

This routine should return the above SQL string in a MSG box but all I
am getting is a VB Box with OK. No message saying problem creating
string

Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click
Dim strSQL As String
Dim strDocName As String

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report"
End If

MsgBox strSQL

Exit_cmdViewReport_Click:
Exit Sub

Err_cmdViewReport_Click:
MsgBox err.Description
Resume Exit_cmdViewReport_Click

End Sub

Any thoughts on how to fix is appreciated
Thanks
Dave

First thing I notice is that your BuildSQL function returns a
boolean. It should return a string.

Private Function BuildSQL(Param1 As Type, Param2 As Type...) As
STRING

BuildSQL = "SELECT Field1, Field 2"
BuildSQL = BuildSQL & " " & "FROM ..."

End Function

notice the addition of the space... if you're building the SQL
statement in code, you don't get the checking you get in the query
builder.

 

[Andy Hull]

Hi Dave

See my comments inline below...

Been trying to figure this one out for a while in my spare time by
following an example I found in a book but it just isn't working for
me. I figure if I can ge the first query to run I should be able to
figure the rest out (I hope!). I created this basic query in Access
and then cut and paste the SQL into the BuildSQLString function. I
have a total of 6 fields that could populate the query

Public Function BuildSQLString(srSQL As String) As Boolean

*****
This is fine as Boolean - it doesn't pass a string of SQL back to the
calling procedure. What happens is: the calling procedure declares a variable
named strSQL and this function changes the value of strSQL. When the calling
procedure gets the control back it uses strSQL and its new value.

The error in this line is between the brackets. It says srSQL (missing t) -
change this to strSQL.

Correct line to...
Public Function BuildSQLString(strSQL As String) As Boolean
*****

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "SELECT tblBasicData.Title,
tblBasicData.HyperlinkToLesson, tblComments.solution,
tblComments.DOTLMPF_ChoiceFK"

*****
If you look later in the code, when the SQL is pieced together the word
"select" is added by the code so you must remove it from this statement else
the final SQL will start like "Select select "

Correct line to...
strSelect = "tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution, tblComments.DOTLMPF_ChoiceFK"
*****

srFrom = "FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK;"

*****
Missing another t. Variable should be strFrom
Also, remove the semi-colon from the end as this will not be the end of the
SQL if there is a where clause

Correct line to...
strFrom = "FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK"
*****

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_ChoiceFk =" & cboDOT
End If

*****
Not sure if you need this at all. chkDOT and DOTMLPF_ChoiceFk and cboDOT
don't seem to be declared anywhere.
*****

strSQL = "Select" & strSelect

*****
Add a space after the select

Correct line to...
strSQL = "Select " & strSelect
*****

strSQL = strSQL & "From " & strFrom

*****
Add a space before the From
Correct line to...
strSQL = strSQL & " From " & strFrom
*****

If strWhere <> "" Then strSQL = strSQL & "WHERE" & Mid$(srtHwere, 6)

*****
Put spaces around the WHERE.
Also, between the brackets you have srtHwere !! Correct typo to strWhere.

Correct line to...
If strWhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strWhere, 6)
*****


A lot of the above problems are typing errors. Go to the very top of your
code, before any procedures/functions or variables are declared and put the
following line...

Option Explicit

This forces you to declare all your variables.
When you compile the code it will not continue if it finds a variable that
isn't declared so if you spell a variable incorrectly it will stop and
highlight that variable name - very useful!

hth

Andy Hull

 

[maceslin]

Hi Dave

See my comments inline below...

Been trying to figure this one out for a while in my spare time by
following an example I found in a book but it just isn't working for
me. I figure if I can ge the first query to run I should be able to
figure the rest out (I hope!). I created this basic query in Access
and then cut and paste the SQL into the BuildSQLString function. I
have a total of 6 fields that could populate the query

Public Function BuildSQLString(srSQL As String) As Boolean

*****
This is fine as Boolean - it doesn't pass a string of SQL back to the
calling procedure. What happens is: the calling procedure declares a variable
named strSQL and this function changes the value of strSQL. When the calling
procedure gets the control back it uses strSQL and its new value.

The error in this line is between the brackets. It says srSQL (missing t) -
change this to strSQL.

Correct line to...
Public Function BuildSQLString(strSQL As String) As Boolean
*****

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "SELECT tblBasicData.Title,
tblBasicData.HyperlinkToLesson, tblComments.solution,
tblComments.DOTLMPF_ChoiceFK"

*****
If you look later in the code, when the SQL is pieced together the word
"select" is added by the code so you must remove it from this statement else
the final SQL will start like "Select select "

Correct line to...
strSelect = "tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution, tblComments.DOTLMPF_ChoiceFK"
*****

srFrom = "FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK;"

*****
Missing another t. Variable should be strFrom
Also, remove the semi-colon from the end as this will not be the end of the
SQL if there is a where clause

Correct line to...
strFrom = "FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK"
*****

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_ChoiceFk =" & cboDOT
End If

*****
Not sure if you need this at all. chkDOT and DOTMLPF_ChoiceFk and cboDOT
don't seem to be declared anywhere.
*****

strSQL = "Select" & strSelect

*****
Add a space after the select

Correct line to...
strSQL = "Select " & strSelect
*****

strSQL = strSQL & "From " & strFrom

*****
Add a space before the From
Correct line to...
strSQL = strSQL & " From " & strFrom
*****

If strWhere <> "" Then strSQL = strSQL & "WHERE" & Mid$(srtHwere, 6)

*****
Put spaces around the WHERE.
Also, between the brackets you have srtHwere !! Correct typo to strWhere.

Correct line to...
If strWhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strWhere, 6)
*****

A lot of the above problems are typing errors. Go to the very top of your
code, before any procedures/functions or variables are declared and put the
following line...

Option Explicit

This forces you to declare all your variables.
When you compile the code it will not continue if it finds a variable that
isn't declared so if you spell a variable incorrectly it will stop and
highlight that variable name - very useful!

hth

Andy Hull

Modified as suggested and gone one step further still with errors. I
know my MakeQueryDef adn ChangeQueryDef functions work. My
BuildSQLString is not working. When I comment in the MsgBox strSQL
the message box comes up, when I swap in the DoCmd.Open Report for the
MsgBox to view the report I get asked for three parameters:
1. tyblDOTMLPF.DOTMLPF_Choices
2. DOTMLPF_Choices
and 3. cboDOT

Any suggestions, my form has a total of 8 combo boxes that I will
have eto add to the BuildSQLString

Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click
Dim strSQL As String
Dim strDocName As String



If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report"
End If
strDocName = "rptNumberedFleet"
DoCmd.OpenReport strDocName, acViewPreview
'MsgBox strSQL

CurrentDb.QueryDefs("qryParameters").SQL = strSQL

Exit_cmdViewReport_Click:
Exit Sub

Err_cmdViewReport_Click:
MsgBox Err.Description
Resume Exit_cmdViewReport_Click

End Sub

++++++++++++++++++++++++++++++++++++++++

Public Function BuildSQLString(strSQL As String) As Boolean

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution, tblDOTMLPF.DOTMLPF_Choices"
'strFrom = "tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK"
strFrom = "tblDOTMLPF INNER JOIN (tblBasicData INNER JOIN tblComments
ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK) ON tblDOTMLPF.
[DOTMLPF ID PK] = tblComments.DOTLMPF_ChoiceFK"

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices = " & "[cboDOT]"
End If

strSQL = "Select " & strSelect
strSQL = strSQL & " From " & strFrom
If strWhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strWhere, 6)

BuildSQLString = True
End Function

 

[maceslin]

Hi Dave

See my comments inline below...

Been trying to figure this one out for a while in my spare time by
following an example I found in a book but it just isn't working for
me. I figure if I can ge the first query to run I should be able to
figure the rest out (I hope!). I created this basic query in Access
and then cut and paste the SQL into the BuildSQLString function. I
have a total of 6 fields that could populate the query

Public Function BuildSQLString(srSQL As String) As Boolean

*****
This is fine as Boolean - it doesn't pass a string of SQL back to the
calling procedure. What happens is: the calling procedure declares a variable
named strSQL and this function changes the value of strSQL. When the calling
procedure gets the control back it uses strSQL and its new value.

The error in this line is between the brackets. It says srSQL (missing t) -
change this to strSQL.

Correct line to...
Public Function BuildSQLString(strSQL As String) As Boolean
*****

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "SELECT tblBasicData.Title,
tblBasicData.HyperlinkToLesson, tblComments.solution,
tblComments.DOTLMPF_ChoiceFK"

*****
If you look later in the code, when the SQL is pieced together the word
"select" is added by the code so you must remove it from this statement else
the final SQL will start like "Select select "

Correct line to...
strSelect = "tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution, tblComments.DOTLMPF_ChoiceFK"
*****

srFrom = "FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK;"

*****
Missing another t. Variable should be strFrom
Also, remove the semi-colon from the end as this will not be the end of the
SQL if there is a where clause

Correct line to...
strFrom = "FROM tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK"
*****

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_ChoiceFk =" & cboDOT
End If

*****
Not sure if you need this at all. chkDOT and DOTMLPF_ChoiceFk and cboDOT
don't seem to be declared anywhere.
*****

strSQL = "Select" & strSelect

*****
Add a space after the select

Correct line to...
strSQL = "Select " & strSelect
*****

strSQL = strSQL & "From " & strFrom

*****
Add a space before the From
Correct line to...
strSQL = strSQL & " From " & strFrom
*****

If strWhere <> "" Then strSQL = strSQL & "WHERE" & Mid$(srtHwere, 6)

*****
Put spaces around the WHERE.
Also, between the brackets you have srtHwere !! Correct typo to strWhere.

Correct line to...
If strWhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strWhere, 6)
*****

A lot of the above problems are typing errors. Go to the very top of your
code, before any procedures/functions or variables are declared and put the
following line...

Option Explicit

This forces you to declare all your variables.
When you compile the code it will not continue if it finds a variable that
isn't declared so if you spell a variable incorrectly it will stop and
highlight that variable name - very useful!

hth

Andy Hull

Thanks Andy
It look like my MakeQueryDef and ChangeQueryDef work but my
BuildSQLString function is not returning anything in immediate
window. The MsgBox strSQL works but weh I swap out the
DoCmd.OpenReport I get three paramter requests
1. tblDOTMLPF.DOTMLP_Choices
2.DOTMLPF_Choices
3. cboDOT

Any ideas why?

Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click
Dim strSQL As String
Dim strDocName As String



If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report"
End If
strDocName = "rptNumberedFleet"
DoCmd.OpenReport strDocName, acViewPreview
'MsgBox strSQL

CurrentDb.QueryDefs("qryParameters").SQL = strSQL

Exit_cmdViewReport_Click:
Exit Sub

Err_cmdViewReport_Click:
MsgBox Err.Description
Resume Exit_cmdViewReport_Click

End Sub
++++++++++++++++++++++++++++
Public Function BuildSQLString(strSQL As String) As Boolean

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution, tblDOTMLPF.DOTMLPF_Choices"
'strFrom = "tblNumbered INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblNumbered.NumberFleetPK = tblBasicData.NumberedFleetFK"
strFrom = "tblDOTMLPF INNER JOIN (tblBasicData INNER JOIN tblComments
ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK) ON tblDOTMLPF.
[DOTMLPF ID PK] = tblComments.DOTLMPF_ChoiceFK"

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices = " & "[cboDOT]"
End If

strSQL = "Select " & strSelect
strSQL = strSQL & " From " & strFrom
If strWhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strWhere, 6)

BuildSQLString = True
End Function

 

[Andy Hull]

Hi again Dave

Before I get into detail I'm going to give an overview of what is/should be
happening

A button is clicked which runs cmdViewReport_Click
cmdViewReport_Click calls BuildSQLString(strSQL) using this line of code...
If Not BuildSQLString(strSQL) Then

Note that strSQL is blank at this point.
BuildSQLString then builds strSQL

When BuildSQLString is finished it is supposed to return a value of True or
False so as to indicate whether it successfully built the string or not. Note
- very important to understand that it doesn't return the string - it returns
True or False.

If it returns False then a message is displayed - and, I assume, the code
should stop and the report shouldn't be run.

If it returns True then the report can run.

So that's the plan.
However, your BuildSQLString funtion always returns True so I don't see why
the function is needed at all. I don't know whether you have changed it a lot
from what was in the book you said you got it from. Personally, I wouldn't
use the function and I would build the strSQL inside the sub
cmdViewReport_Click.

Having said that, if I just comment on the syntax of the code in
BuildSQLString it all now looks ok. The only thing I noticed is the variable
chkDOT is declared anywhere. What is this?

You said that BuildSQLString showed nothing in the immediate window. I tried
this also and got the same so typed the following (in immediate)...

msgbox buildsqlstring("")

and this gave a message of True. So you can see it's working.

Now let's move on to the sub cmdViewReport_Click.
I have pasted this whole below, and made comments in line...

Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click

Dim strSQL As String
Dim strDocName As String

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report"
End If

***************
As said above, it never returns False so this message is never displayed.
BUT!!! If it did then I assume you would want to stop the code here but the
code doesn't stop. So, even if BuildSQLString returned False and the message
was displayed it would still carry on and try to run the report.

The code should probably be...

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report"
Exit Sub
End If
***************

strDocName = "rptNumberedFleet"
DoCmd.OpenReport strDocName, acViewPreview
***************
So this is where the report is opened and then you get the prompts.
I will talk about the prompts lower down.
The big point to be made here is that all effort up to this point has been
to build the variable strSQL which I believe was so it could be used in the
final step when the report was run.
However, strSQL is NOT used by this report rptNumberedFleet.
If you go into the design of the report itself you will see what its source
query is.
Go into that query and you will be able to see the SQL it is using - and it
is this SQL that is running - NOT the SQL built into strSQL - but see next
comments...
***************

'MsgBox strSQL

CurrentDb.QueryDefs("qryParameters").SQL = strSQL
***************
This looks promising! I am guessing that the report report rptNumberedFleet
uses the query qryParameters in some way so this is how we can get the report
to use the strSQL we have built.
However, shouldn't this command come before we open the report?
(It probably seems to work anyway because the SQL in the query has already
been set from previous runs of the code).
***************

Exit_cmdViewReport_Click:
Exit Sub


Hope I've made sense so far. Now the prompts.
These prompts are all to do with the query that the report is running.
It means that Access doesn't know what they are.
If we look at strSQL we can see where they appear.

(1) tblDOTMLPF.DOTMLP_Choices

Check there is a table tblDOTMLPF (check spelling carefully!)
And, does this table have a column called DOTMLP_Choices
To me, it looks like a letter F is missing from just before the _Choices.

(2) DOTMLPF_Choices
This adds a bit more confusion. It helps to confirm that item (1) has a
missing letter F (because this item does have an F). But, if it was right it
wouldn't be prompted for so I can only get you to check the column names of
all the tables in the query. I notice that this reference doesn't use a table
name so if this column is spelt correctly but appears in more than 1 table it
will give an error. So, check it is spelt correctly and also check which
table you want to use it from and add the table name to your code.

(3) cboDOT
As you have used the prefix cbo I am assuming this is the name of a combo box.
When a query uses a combo box value it has to use the following syntax...

Forms![MyForm]![cboDOT]

You will replace MyForm with the name of the form the combo box is on.

The line of code affected is...
If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices = " & "[cboDOT]"
End If

If cboDOT is a number, change it to...
strWhere = strWhere & " AND DOTMLPF_Choices = " & Forms![MyForm]![cboDOT]

If cboDOT is text, change it to...
strWhere = strWhere & " AND DOTMLPF_Choices = """ &
"Forms![MyForm]![cboDOT]" & """"

(Every quote character is a double quote. Where they are repeated there are
3 before the first & and 4 at the end)

As regards your 8 combo boxes my approach would be to get it working with
one of them first. Then when you know you have the right syntax you can add
the others into the code.

A question that might change your approach...
Is it only the Where clause that is dynamic?
If so, you don't need to build the SQL every time. You can go into the
query design and put the combo box references directly in there.


hth

Andy Hull

 

[pietlinden]

Assuming that your report requires at least a fixed set of fields, why
not just build the report with NO filter, and then just build the
filter elsewhere? One option is to remove the parameters from the
query you're using as the recordsource for the report, and then
passing a filter when you open the report. To me it sounds like you
may be making this more difficult than it needs to be. If you remove
all the filters from the query (anything after the WHERE statement),
you can just pass those in the open event of the report. If you're
referencing controls on a form, you could just build the filter in the
Click event of the button that opens the report...

I'm not Kent Brockman, but that's my 2 cents...

Pieter

 

[maceslin]

Hi again Dave

Before I get into detail I'm going to give an overview of what is/should be
happening

A button is clicked which runs cmdViewReport_Click
cmdViewReport_Click calls BuildSQLString(strSQL) using this line of code...
If Not BuildSQLString(strSQL) Then

Note that strSQL is blank at this point.
BuildSQLString then builds strSQL

When BuildSQLString is finished it is supposed to return a value of True or
False so as to indicate whether it successfully built the string or not. Note
- very important to understand that it doesn't return the string - it returns
True or False.

If it returns False then a message is displayed - and, I assume, the code
should stop and the report shouldn't be run.

If it returns True then the report can run.

So that's the plan.
However, your BuildSQLString funtion always returns True so I don't see why
the function is needed at all. I don't know whether you have changed it a lot
from what was in the book you said you got it from. Personally, I wouldn't
use the function and I would build the strSQL inside the sub
cmdViewReport_Click.

Having said that, if I just comment on the syntax of the code in
BuildSQLString it all now looks ok. The only thing I noticed is the variable
chkDOT is declared anywhere. What is this?

You said that BuildSQLString showed nothing in the immediate window. I tried
this also and got the same so typed the following (in immediate)...

msgbox buildsqlstring("")

and this gave a message of True. So you can see it's working.

Now let's move on to the sub cmdViewReport_Click.
I have pasted this whole below, and made comments in line...

Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click

Dim strSQL As String
Dim strDocName As String

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report"
End If

***************
As said above, it never returns False so this message is never displayed.
BUT!!! If it did then I assume you would want to stop the code here but the
code doesn't stop. So, even if BuildSQLString returned False and the message
was displayed it would still carry on and try to run the report.

The code should probably be...

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report"
Exit Sub
End If
***************

strDocName = "rptNumberedFleet"
DoCmd.OpenReport strDocName, acViewPreview
***************
So this is where the report is opened and then you get the prompts.
I will talk about the prompts lower down.
The big point to be made here is that all effort up to this point has been
to build the variable strSQL which I believe was so it could be used in the
final step when the report was run.
However, strSQL is NOT used by this report rptNumberedFleet.
If you go into the design of the report itself you will see what its source
query is.
Go into that query and you will be able to see the SQL it is using - and it
is this SQL that is running - NOT the SQL built into strSQL - but see next
comments...
***************

'MsgBox strSQL

CurrentDb.QueryDefs("qryParameters").SQL = strSQL
***************
This looks promising! I am guessing that the report report rptNumberedFleet
uses the query qryParameters in some way so this is how we can get the report
to use the strSQL we have built.
However, shouldn't this command come before we open the report?
(It probably seems to work anyway because the SQL in the query has already
been set from previous runs of the code).
***************

Exit_cmdViewReport_Click:
Exit Sub

Hope I've made sense so far. Now the prompts.
These prompts are all to do with the query that the report is running.
It means that Access doesn't know what they are.
If we look at strSQL we can see where they appear.

(1) tblDOTMLPF.DOTMLP_Choices

Check there is a table tblDOTMLPF (check spelling carefully!)
And, does this table have a column called DOTMLP_Choices
To me, it looks like a letter F is missing from just before the _Choices.

(2) DOTMLPF_Choices
This adds a bit more confusion. It helps to confirm that item (1) has a
missing letter F (because this item does have an F). But, if it was right it
wouldn't be prompted for so I can only get you to check the column names of
all the tables in the query. I notice that this reference doesn't use a table
name so if this column is spelt correctly but appears in more than 1 table it
will give an error. So, check it is spelt correctly and also check which
table you want to use it from and add the table name to your code.

(3) cboDOT
As you have used the prefix cbo I am assuming this is the name of a combo box.
When a query uses a combo box value it has to use the following syntax...

Forms![MyForm]![cboDOT]

You will replace MyForm with the name of the form the combo box is on.

The line of code affected is...
If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices = " & "[cboDOT]"
End If

If cboDOT is a number, change it to...
strWhere = strWhere & " AND DOTMLPF_Choices = " & Forms![MyForm]![cboDOT]

If cboDOT is text, change it to...
strWhere = strWhere & " AND DOTMLPF_Choices = """ &
"Forms![MyForm]![cboDOT]" & """"

(Every quote character is a double quote. Where they are repeated there are
3 before the first & and 4 at the end)

As regards your 8 combo boxes my approach would be to get it working with
one of them first. Then when you know you have the right syntax you can add
the others into the code.

A question that might change your approach...
Is it only the Where clause that is dynamic?
If so, you don't need to build the SQL every time. You can go into the
query design and put the combo box references directly in there.

hth

Andy Hull

Andy
First I appreciate your help. I am doing this as a collateral duty for
work with the Navy as I have time in the evenings because I am one of
the main users of the Dbase for research purposes and can not get the
data in a usable format.

I have frmParameters with several check boxes (like chkDOT) that when
chosen enables a ComboBox (cboDOT) that is used to build string to use
to create a report. There are multiple combo boxes that can set
criteria for the query and I will use more than one at a time. They
do not all use the same tables or fields so I do need to keep the
"Select" and "From" in the function. THe report will be standard for
all queries. I also will have to be able to email a form to users. I
need to use a form because there are hyperlinks tha I require to be
actiove whcih you can not do in a report unless you export it to some
other format and I might end up doing that but first I need to get my
query figured out.

I have created and tested MakeQueryDef and ChangeQueryDef Module as
follows:

Option Compare Database

Function MakeQueryDef(strSQL As String) As Boolean
Dim qdf As QueryDef
If strSQL = "" Then Exit Function

Set qdf = CurrentDb.CreateQueryDef("qryParameters")
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow

MakeQueryDef = True

End Function
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++
Function ChangeQueryDef(strQuery As String, strSQL As String) As
Boolean
If strQuery = "" Or strSQL = "" Then Exit Function
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs(strQuery)
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow

ChangeQueryDef = True

End Function

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++=

I thought these functions would set the record source for my
rptParameters as qryParameters

I have not modified the routine much except for the "Select" and
"from" clauses as they were consistent in the example I am following.
tblBasicData and tblComments are common throughout the queries but the
third table will change based on which chkBox is chosen

I thought that if the BuildSQLString function could not put together
an understandable query (would this be False return) it would return
"There was a problem...." from the If Not BuildSQLString(strSQL).

I fixed all the parameter request problems that came up but the report
does not populate


when I enter ?BuildSQLString("") in immediate window I am getting
"mismatch error" but I can not see a type mismatch

Here is the code as it currently sits:

Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click
Dim strSQL As String
Dim strDocName As String

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building query for the report" never
seen this and when I comment in MsgBox strSQL a message box appears
witht he correct SQL string
Exit Sub
End If

strDocName = "rptNumberedFleet"

CurrentDb.QueryDefs("qryParameters").SQL = strSQL

DoCmd.OpenReport strDocName, acViewPreview
' MsgBox strSQL

Exit_cmdViewReport_Click:
Exit Sub

Err_cmdViewReport_Click:
MsgBox Err.Description
Resume Exit_cmdViewReport_Click

End Sub

Public Function BuildSQLString(strSQL As String) As Boolean

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution, tblDOTMLPF.DOTMLPF_Choices"
strFrom = "tblDOTMLPF INNER JOIN (tblBasicData INNER JOIN tblComments
ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK) ON tblDOTMLPF.
[DOTMLPF ID PK] = tblComments.DOTLMPF_ChoiceFK"

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices =""" & "Forms!
[frmParameters]![cboDOT]" & """"
End If

'IF chkPersonnel Then
'strWhere =strWhere & "...........
' End If

strSQL = "Select " & strSelect
strSQL = strSQL & " From " & strFrom
If strWhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strWhere, 6)

BuildSQLString = True
End Function

If I can get one (or should I say "we" becasue I really do appreciate
and need your help) I hope I can get the others until the last one
which is bsed on a query in a query. I am learning a alot

Thanks
Dave in Chesapeake VA

 

[Andy Hull]

Hi again

Thanks for the background. It always helps. We seem to be making progress so
I'm sure we'll get it working as you want.

I can not see anywhere that the BuildSQLString function tests to see if the
SQL is valid. However, I don't think we need to worry about that at this
stage. You know your table structures and options that the user can pick so
when we have the code right it will always build a valid SQL statement. As
you have said, the function is needed to check all the options and build the
relevant SQL.

Here are my comments on some of your last post...

You said...
when I enter ?BuildSQLString("") in immediate window I am getting "mismatch
error" but I can not see a type mismatch

***************
Try with MsgBox at the start of the line like...

MsgBox BuildSQLString("")

But don't worry too much if this still doesn't seem to work. We can see from
the results you get when you run the code that we're going in the right
direction.
***************

Does checking chkDOT work correctly?
I ask this because I would normally use the syntax...

Me.chkDOT

The Me. refers to the current form. Without this Access could think chkDOT
is a variable.

You said...
I fixed all the parameter request problems that came up but the report
does not populate

***************
I assume that no prompts come up at all now - good.
I was going to say that if the report does not populate then we now need to
turn our attention to the query and tables and their data.
But first we need to change the following bit of code (in Function
BuildSQLString)...

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices =""" &
"Forms![frmParameters]![cboDOT]" & """"
End If

If you look at the resulting SQL you will see it says...

WHERE DOTMLPF_Choices = "Forms![frmParameters]![cboDOT]"

So it literally wants DOTMLPF_Choices to be "Forms![frmParameters]![cboDOT]"
which is why it won't yet populate.

Whereas we want it to be whatever the value of
Forms![frmParameters]![cboDOT] is.

So the code should be...

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices =""" &
Forms![frmParameters]![cboDOT] & """"
End If

Note I've removed the quotes just before Forms! and just after [cboDOT]

This will insert the actual value of cboDOT into the SQL.

hth

Andy Hull

 

[maceslin]

Andy- we are making progress, I got hte report to populate with the
correct data. So I toke it the next step with the following code,
notice I moved the strSelect and strFrom inside the If Then claus as
it will change each time as you can see in the code:

If chkDOT Then
strSelect = "tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution, tblDOTMLPF.DOTMLPF_Choices"
strFrom = "tblDOTMLPF INNER JOIN (tblBasicData INNER JOIN
tblComments ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK)
ON tblDOTMLPF.[DOTMLPF ID PK] = tblComments.DOTLMPF_ChoiceFK"
strWhere = strWhere & " AND DOTMLPF_Choices =""" & Forms!
[frmParameters]![cboDOT] & """"
End If

If chkCore Then
strSelect = "tblBasicData.Title, tblComments.solution,
tblBasicData.HyperlinkToLesson, tblCore.CoreCapability"
strFrom = "tblCore INNER JOIN (tblBasicData INNER JOIN tblComments
ON tblBasicData.[Lesson IDPK] = tblComments.Lesson_IDFK) ON tblCore.
[Core Capability IDPK] = tblBasicData.CoreCapabilityFK"
strWhere = strWhere & " AND CoreCapability = """ & Forms!
[frmParameters]![cboCore] & """"
End If

Each of the statments produces the report as expected when run
individually but when I try to run a report for both of them together
which is the whole purpose of the form I now get a parameter box popup
asking for "DOTMLPF_Choices" I enter a selection and it creates a
report based on cboCore and ignores what I typed in the query box.

I see several problems here
1. Why parameter box
2. Why ignoring selelction when typed in
3. New problem- how do I avid duplication in results between the two
"searches" as some records may fill both combo box choices

I guess I was wrong in saying I could figure it out if IO got one of
htem to work! maybe with two
Standing by

Thanks

Hi again

Thanks for the background. It always helps. We seem to be making progress so
I'm sure we'll get it working as you want.

I can not see anywhere that the BuildSQLString function tests to see if the
SQL is valid. However, I don't think we need to worry about that at this
stage. You know your table structures and options that the user can pick so
when we have the code right it will always build a valid SQL statement. As
you have said, the function is needed to check all the options and build the
relevant SQL.

Here are my comments on some of your last post...

You said...
when I enter ?BuildSQLString("") in immediate window I am getting "mismatch
error" but I can not see a type mismatch

***************
Try with MsgBox at the start of the line like...

MsgBox BuildSQLString("")

But don't worry too much if this still doesn't seem to work. We can see from
the results you get when you run the code that we're going in the right
direction.
***************

Does checking chkDOT work correctly?
I ask this because I would normally use the syntax...

Me.chkDOT

The Me. refers to the current form. Without this Access could think chkDOT
is a variable.

You said...
I fixed all the parameter request problems that came up but the report
does not populate

***************
I assume that no prompts come up at all now - good.
I was going to say that if the report does not populate then we now need to
turn our attention to the query and tables and their data.
But first we need to change the following bit of code (in Function
BuildSQLString)...

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices =""" &
"Forms![frmParameters]![cboDOT]" & """"
End If

If you look at the resulting SQL you will see it says...

WHERE DOTMLPF_Choices = "Forms![frmParameters]![cboDOT]"

So it literally wants DOTMLPF_Choices to be "Forms![frmParameters]![cboDOT]"
which is why it won't yet populate.

Whereas we want it to be whatever the value of
Forms![frmParameters]![cboDOT] is.

So the code should be...

If chkDOT Then
strWhere = strWhere & " AND DOTMLPF_Choices =""" &
Forms![frmParameters]![cboDOT] & """"
End If

Note I've removed the quotes just before Forms! and just after [cboDOT]

This will insert the actual value of cboDOT into the SQL.

hth

Andy Hull

 

[Andy Hull]

Hi. Sorry for delay - couldn't get onto the site Friday afternoon and haven't
been near a pc all weekend!

Here is what I tried to post on Friday...

OK, this is where we need to be very methodical.
Problems 1 & 2 are probably related and I'll discuss those first.

By the way, one thing I'm not sure of in your setup is...
Do you open different reports based on the user's selections or is there
just one report that is meant to cater for all situations?

I think what would be useful is to comment out opening the report so you can
test the SQL being generated.
Comment it out then run your form as normal trying out each option.
At the point where it should open the report it will just stop.
Now, go to the queries screen, highlight the generated query and click design.
Then go to SQL view.

Examine the SQL closely. Is it what you expect?
Also, run the query. Does it run? Is the data correct? Does is display any
prompts before running?

If you do this I think you will see where any problems lie.
You'll probably see straight away if the SQL isn't what you expected.
Also, if it displays prompts, you should see why it doesn't know what they
are.

I'm not totally sure, but one possibility is the query may run but then the
report might refer to something that the query doesn't select and so the
report will then display a prompt.
If you test as described above and never get prompted then we will know that
the problem is when the report runs.


Here's what could be happening... (This is my guesswork!)
You have set up a query based on chkDOT
You then created a report based on this query

The query selects tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution and tblDOTMLPF.DOTMLPF_Choices
You presumably used some or all of these items in the report

Note: The report sees them as Title, HyperlinkToLesson, solution and
DOTMLPF_Choices

Bit by bit we got it to work.

Then you added the options for chkCore
When the chkCore query runs it selects tblBasicData.Title,
tblComments.solution, tblBasicData.HyperlinkToLesson and
tblCore.CoreCapability

The report sees these as Title, solution, HyperlinkToLesson and CoreCapability

It knows the first 3 but may not know CoreCapability
Also, it is expecting to see something called DOTMLPF_Choices but it isn't
there so this may be why it prompts you for it


Ultimately, the final generated query must provide the same column headings
to the report every time.
Maybe, some of these columns will be blank depending on the options chosen
but the column must be there else the report will fail.


To help more I am going to need to know more about the data and what the
different options are for.
Consider the following...

Scenario 1
User selects chkDOT only
chkDOT IF statement is true and strSQL is set to be chkDOT query
chkCore IF statement is false - no action
Runs OK

Or Scenario 2
User selects chkCOre only
chkDOT IF statement is false - no action
chkCore IF statement is true and strSQL is set to be chkCore query
Runs OK

Or Scenario 3
User selects chkDOT and chkCore
chkDOT IF statement is true and strSQL is set to be chkDOT query
chkCore IF statement is true and strSQL is set to be chkCore query

Note: strSQL was chkDOT query but has now been overwritten to be the chkCore
query
So if both are selected it is the chkCore query that gets the data and
explains why when you were prompted for DOTMLPF_Choices it wasn't used
(because it isn't in the chkCore query)


Because I don't know the data it's quite difficult to know what advice to
give but if I was doing this I would want to design a single query that
selects all columns from all tables that might be needed.

This query would be the same for all options.
I would then use code to build a WHERE clause to add to this base query that
restricted the data based on the user's choices.


The WHERE code might look like...

if chkDOT then
if strWhere = "" then
strWhere = "WHERE "
end if
strWhere = strWhere & "DOTMLPF_Choices = """ &
Forms![frmParameters]![cboDOT] & """"
end if

if chkCore Then
if strWhere = "" then
strWhere = "WHERE "
else
strWhere = strWhere & " AND "
end if
strWhere = strWhere & "CoreCapability = """ &
Forms![frmParameters]![cboCore] & """"
end if


So this adds each criteria if needed without overwriting the previous.

There is even more to consider!
The above method will select records that satisfy both criteria and will not
select records that satisfy only one of the criteria.
If you want the data that satisifes either criteria then we would have to
use an OR between them instead of and AND so it isn't a major issue.
Ulitmately, you may want to allow the user to choose - but I think there's
enough to be doing before we get that far!

There's a lot of info above so I hope it isn't confusing.

Hope it helps

Andy Hull

 

[maceslin]

Hi. Sorry for delay - couldn't get onto the site Friday afternoon and haven't
been near a pc all weekend!

Here is what I tried to post on Friday...

OK, this is where we need to be very methodical.
Problems 1 & 2 are probably related and I'll discuss those first.

By the way, one thing I'm not sure of in your setup is...
Do you open different reports based on the user's selections or is there
just one report that is meant to cater for all situations?

I think what would be useful is to comment out opening the report so you can
test the SQL being generated.
Comment it out then run your form as normal trying out each option.
At the point where it should open the report it will just stop.
Now, go to the queries screen, highlight the generated query and click design.
Then go to SQL view.

Examine the SQL closely. Is it what you expect?
Also, run the query. Does it run? Is the data correct? Does is display any
prompts before running?

If you do this I think you will see where any problems lie.
You'll probably see straight away if the SQL isn't what you expected.
Also, if it displays prompts, you should see why it doesn't know what they
are.

I'm not totally sure, but one possibility is the query may run but then the
report might refer to something that the query doesn't select and so the
report will then display a prompt.
If you test as described above and never get prompted then we will know that
the problem is when the report runs.

Here's what could be happening... (This is my guesswork!)
You have set up a query based on chkDOT
You then created a report based on this query

The query selects tblBasicData.Title, tblBasicData.HyperlinkToLesson,
tblComments.solution and tblDOTMLPF.DOTMLPF_Choices
You presumably used some or all of these items in the report

Note: The report sees them as Title, HyperlinkToLesson, solution and
DOTMLPF_Choices

Bit by bit we got it to work.

Then you added the options for chkCore
When the chkCore query runs it selects tblBasicData.Title,
tblComments.solution, tblBasicData.HyperlinkToLesson and
tblCore.CoreCapability

The report sees these as Title, solution, HyperlinkToLesson and CoreCapability

It knows the first 3 but may not know CoreCapability
Also, it is expecting to see something called DOTMLPF_Choices but it isn't
there so this may be why it prompts you for it

Ultimately, the final generated query must provide the same column headings
to the report every time.
Maybe, some of these columns will be blank depending on the options chosen
but the column must be there else the report will fail.

To help more I am going to need to know more about the data and what the
different options are for.
Consider the following...

Scenario 1
User selects chkDOT only
chkDOT IF statement is true and strSQL is set to be chkDOT query
chkCore IF statement is false - no action
Runs OK

Or Scenario 2
User selects chkCOre only
chkDOT IF statement is false - no action
chkCore IF statement is true and strSQL is set to be chkCore query
Runs OK

Or Scenario 3
User selects chkDOT and chkCore
chkDOT IF statement is true and strSQL is set to be chkDOT query
chkCore IF statement is true and strSQL is set to be chkCore query

Note: strSQL was chkDOT query but has now been overwritten to be the chkCore
query
So if both are selected it is the chkCore query that gets the data and
explains why when you were prompted for DOTMLPF_Choices it wasn't used
(because it isn't in the chkCore query)

Because I don't know the data it's quite difficult to know what advice to
give but if I was doing this I would want to design a single query that
selects all columns from all tables that might be needed.

This query would be the same for all options.
I would then use code to build a WHERE clause to add to this base query that
restricted the data based on the user's choices.

The WHERE code might look like...

if chkDOT then
if strWhere = "" then
strWhere = "WHERE "
end if
strWhere = strWhere & "DOTMLPF_Choices = """ &
Forms![frmParameters]![cboDOT] & """"
end if

if chkCore Then
if strWhere = "" then
strWhere = "WHERE "
else
strWhere = strWhere & " AND "
end if
strWhere = strWhere & "CoreCapability = """ &
Forms![frmParameters]![cboCore] & """"
end if

So this adds each criteria if needed without overwriting the previous.

There is even more to consider!
The above method will select records that satisfy both criteria and will not
select records that satisfy only one of the criteria.
If you want the data that satisifes either criteria then we would have to
use an OR between them instead of and AND so it isn't a major issue.
Ulitmately, you may want to allow the user to choose - but I think there's
enough to be doing before we get that far!

There's a lot of info above so I hope it isn't confusing.

Hope it helps

Andy Hull

I was also not near a computer over the weekend as I was working on
the honey do list. Am currently on travel so should have plenty of
time to play with the challenge.

The report will be a standard report for all selections but will show
the "where" selections in the header (not fully designed)

Comment out the report and added back in MsqBoxstrSQL to see the SQL
string as it is created, it is not merging the multiple query's as I
thought it should.

Your scenario 1 and two are correct- I get good query statements in
MsgBoxstrSQL

Added cboNumbered to the mix and have determined that the last If...
Then claus for which a chk is selected is used for the "select" and
"from" statements in MsgBoxstrSQL but the "where" statement shows what
is expected. Confirmed this by moving order of If...then statements
around

Then commented out MsgBoxstrSQL and commented in report command with
following results:
It always asks for Numbered_Fleet but returns the correct answer for
what ever chk was selected when return is entered. Tried closing DB
and it still asks for Numbered_Fleet

In an attempt to follow your recommendation I created a query with all
the fields from tblBasicData and tblComments (did not include all the
other table that feed the foreign keys. Used this qryBasicData as
basis for frmPara1. Entered code you recommended for cboDOT and
cboCore using "OR" and they both return expected values individually
and in combination when MsgBoxstrSQl is shown.

Public Function BuildSQLString(strSQL As String) As Boolean

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "tblBasicData.*, tblComments.*"
strFrom = "tblBasicData INNER JOIN tblComments ON tblBasicData.[Lesson
IDPK] = tblComments.Lesson_IDFK"

If chkDOT Then
If strWhere = "" Then
strWhere = "Where"
End If
strWhere = strWhere & "DOTMLPF_Choices =""" & Forms![frmPara1]!
[cboDOT] & """"
End If

If chkCore Then
If strWhere = "" Then
strWhere = "Where"
Else
strWhere = strWhere & " OR "
End If
strWhere = strWhere & "CoreCapability=""" & Forms![frmPara1]!
[cboCore] & """"
End If



strSQL = "Select " & strSelect
strSQL = strSQL & " From " & strFrom
If strWhere <> "" Then strSQL = strSQL & " WHERE " & Mid$(strWhere, 6)

BuildSQLString = True
End Function


Comment in report and it now asks for DOTMLPF_Choices, CoreCapability
and Numbered_Fleet, even though Numbered Fleet is not part of
BuildSQLString. It looks to me like the problem might be in the
MakeQueryDef and/or ChangeQueryDef and/or the report routine but I can
not see anything.

I hope all this makes sense as I am getting tired and am ready to
relax a little

Thanks for continued support. I have mentioned that the most
difficult chk will be the last one. Details are as follows:
frmComment is subform on frmBasicData. frmComment contains cboDOTMLPF
and contains multiple record for tracking status of each DOTMLPF
(doctrine, organization,training,material,leadership,personnel,
facilites (aren't you sorry you asked)) pertaining to frmBasicData. I
will have to be able to call out the most recent action for each
DOTMLPF on each record of frmBasicData. I have created a query of a
query that does this but not sure how adding it to frmParameters
(frmpara1) will work

Dave (now in Newport RI until Thursday evening)

 

[Andy Hull]

Hi Dave

You've included all fields from tblBasicData and tblComments - good.
But, note that you don't need to include every single field. There is no
point in making a query gather more data than it needs as it will take
longer. But I do know that it is far easier to type tblBasicData.* instead of
listing the required fields individually!!!

You don't have to change it as using the *'s will work fine but bear in mind
it might be worth using field names once we get it fully working later on.

You also said the strSQL is displayed correctly by the MsgBox - good again.

However, you think that maybe the make and/or change querydef isn't working.
I actually think it is working because the strSQL is correct and it is this
that defines the querydef.

You can verify this.
Imagine you want to desing a query. You would go into the query window in
access. Press new, add your tables etc and when you've finished you save it.
Then, when you look in the query window you can see your query listed. And,
you can highlight it and press design and then you can go into the SQL view
to see the SQL.

Well, you can do all of this with the query that your code creates.
So, comment out the report and comment in the msgbox.

Run your form, try out some options.
The SQL will be displayed by the msgbox - check it and press ok.

Now, go to the queries window.
Find the query that your code creates - highlight it - press design - go to
SQL view.
You will see the SQL is exactly the same as was displayed by the msgbox so
we know the querydef bit is ok.

Now onto the issues of being prompted for parameters...

One thing I still need you to verify first. When you look at the SQL in the
query as described above - run the query.
It should run withut prompting for anything. This confirms the query is ok.
You already said the SQL didn't refer to things being prompted for but just
do this as a double check and let me know what happens.

So, this step has 2 outcomes...
1) Doesn't prompt so is ok
2) Does prompt - needs further investigation - see bit below asterisked *****

For now, I'll assume outcome 1 - no prompt so is ok

This says to me that it is the report that is looking for a field that
doesn't exist in the query. What I think you need to do is add in the other
required columns from the other tables like you did for tblBasicData and
tblComments.

So, do the same for tblDOTMLPF and tblCore (you can use tblDOTMLPF.* and
tblCore.* for now).
As you know, you will add these to the "select" part of the SQL.
And you will need to add the tables and the relevant joins to the "from"
part of the SQL.

Having done this you can test the whole thing again.
Check it step by step - it's so easy to make typing errors when adding /
changing code.
Does the SQL look ok?
Does the query run on its own? and without prompting?
Now run the report - does that run? Does it prompt?


***** Now what if we get outcome 2 when we test the query *****
Double check the query SQL to make sure it really doesn't contain a field
that isn't in any of the chosen tables. Check very carefully for spelling
mistakes.

What is the query prompting for?
If this really can't be seen in the SQL then there might be some sort of
corruption but we must be careful not to assume this too soon. I have seen
something similar when a query is run and filtered or sorted when in its
results view and then saved.

To solve, try the following...

Rename the query
Go into the SQL view of the renamed query - highlight all the SQL and copy
Close the query
Create a new query without adding any tables - go straight into SQL view and
paste the copied SQL
Close and save the query with the correct name
Test - it shouldn't prompt.

Hope this gets us closer

Good luck!

Andy

 

[maceslin]

Hi Dave

You've included all fields from tblBasicData and tblComments - good.
But, note that you don't need to include every single field. There is no
point in making a query gather more data than it needs as it will take
longer. But I do know that it is far easier to type tblBasicData.* instead of
listing the required fields individually!!!

You don't have to change it as using the *'s will work fine but bear in mind
it might be worth using field names once we get it fully working later on.

You also said the strSQL is displayed correctly by the MsgBox - good again.

However, you think that maybe the make and/or change querydef isn't working.
I actually think it is working because the strSQL is correct and it is this
that defines the querydef.

You can verify this.
Imagine you want to desing a query. You would go into the query window in
access. Press new, add your tables etc and when you've finished you save it.
Then, when you look in the query window you can see your query listed. And,
you can highlight it and press design and then you can go into the SQL view
to see the SQL.

Well, you can do all of this with the query that your code creates.
So, comment out the report and comment in the msgbox.

Run your form, try out some options.
The SQL will be displayed by the msgbox - check it and press ok.

Now, go to the queries window.
Find the query that your code creates - highlight it - press design - go to
SQL view.
You will see the SQL is exactly the same as was displayed by the msgbox so
we know the querydef bit is ok.

Now onto the issues of being prompted for parameters...

One thing I still need you to verify first. When you look at the SQL in the
query as described above - run the query.
It should run withut prompting for anything. This confirms the query is ok.
You already said the SQL didn't refer to things being prompted for but just
do this as a double check and let me know what happens.

So, this step has 2 outcomes...
1) Doesn't prompt so is ok
2) Does prompt - needs further investigation - see bit below asterisked *****

For now, I'll assume outcome 1 - no prompt so is ok

This says to me that it is the report that is looking for a field that
doesn't exist in the query. What I think you need to do is add in the other
required columns from the other tables like you did for tblBasicData and
tblComments.

So, do the same for tblDOTMLPF and tblCore (you can use tblDOTMLPF.* and
tblCore.* for now).
As you know, you will add these to the "select" part of the SQL.
And you will need to add the tables and the relevant joins to the "from"
part of the SQL.

Having done this you can test the whole thing again.
Check it step by step - it's so easy to make typing errors when adding /
changing code.
Does the SQL look ok?
Does the query run on its own? and without prompting?
Now run the report - does that run? Does it prompt?

***** Now what if we get outcome 2 when we test the query *****
Double check the query SQL to make sure it really doesn't contain a field
that isn't in any of the chosen tables. Check very carefully for spelling
mistakes.

What is the query prompting for?
If this really can't be seen in the SQL then there might be some sort of
corruption but we must be careful not to assume this too soon. I have seen
something similar when a query is run and filtered or sorted when in its
results view and then saved.

To solve, try the following...

Rename the query
Go into the SQL view of the renamed query - highlight all the SQL and copy
Close the query
Create a new query without adding any tables - go straight into SQL view and
paste the copied SQL
Close and save the query with the correct name
Test - it shouldn't prompt.

Hope this gets us closer

Good luck!

Andy

:> Some success- Ireally appreciate your guidance and have been
learning a a lot

I added all the other fields and query runs from SQL view without
prompts. Commented out MsgBox strSQL and modified report to show all
fields brought in report, seems to work.

Continuing to add If..Then statements can not figure out how to finish
the following or even if what I have put in so far is correct. I am
entering a begin date and end date based on field Date_Entered of
tblComments. Do I need to have a format statement for the dates?

If chkDateRange Then
If strWhere = "" Then
strWhere = "Where"
Else
If Not IsNull(txtBeginDate) Then
strWhere = strWhere & " AND Date_Entered >= # "
End If

If Not IsNull(txtEndDate) Then
strWhere = strWhere & " AND Date_Entered <= # "
End If
Else
strWhere = strWhere & " OR "
End If
strWhere =strWhere & ............ how do I finish this?
End If


A question for later in addition to the tough one I mentioned
yesterday:
How do we give the user the choice of and/or as he chooses each chk?

Thanks
Dave

 

[Andy Hull]

Hi again

This post is getting pretty lengthy now and is gradually moving away from
the original topic so, if you are happy for me to do so I will send my next
post to your email.

Let me know know if this is ok.
I'm assuming it's maceslin followed by at gmail dot com
I've typed it like that so that email harvesting programs can't extract it
from this post and start sending you unsolicited mail.

For that reason, you should think about changing your options for this
newsgroup so that your email doesn't show.

Here is the code I would consider using to process the dates...

If (chkDateRange) and Not (IsNull(txtBeginDate) and IsNull(txtEndDate)) Then
If strWhere = "" Then
strWhere = "Where"
Else
strWhere = strWhere & " OR "
End If

strWhere = strWhere & "(Date_Entered between " &
format(nz(txtBeginDate,#01/01/1900#),"\#mm\/dd\/yyyy\#") & " and " &
format(nz(txtEndDate,#12/31/9999#),"\#mm\/dd\/yyyy\#") & ")"

End If

Be careful with the long line as it will be wrapped in this window.
I don't know how familiar you are with using dates and formatting so it
might look very complicated at first glance.

Take the time to go through it carefully though with the goal of
understanding it all fully. If you just use the code as is (as many people
probably do in the newsgroup) you will get stuck in the future with similar
but slightly different problems. Whereas taking the time to understand it
will mean you can then apply that knowledge to solve all similar future
problems.

To come up with the above code I decided it was too troublesome to have to
test which date, if either, was null as this affects where the brackets and
the "and" can be placed. So I decided that if the begin date was null I'd use
01/01/1900 and if the end data was null I'd use 12/31/9999. I've assumed that
you won't be searching for dates outside this range. You can make the 1900
date earlier if you really nedd to.

Also, rather than using if then statements to test if they are null I used
the Nz function.
This works in the following way...

If txtBeginDate is null then nz(txtBeginDate,#01/01/1900#) = #01/01/1900#

But, if txtBeginDate isn't null then nz(txtBeginDate,#01/01/1900#) =
txtBeginDate (ie whatever the user entered)


You've asked about choosing AND's and OR's...

This is a tricky situation.
Not because it's hard to code necessarily but because combining AND's and
OR's is a confusing subject for lots of people so providing the option can
lead to more users making mistakes.

If we were to provide a fully featured dynamic query generator we would have
to let the user, not only choose AND's or OR's, but put their criteria in any
order and possibly choose where to put brackets.

This is a lot of coding effort and for many users unnecessary and confusing.

My opinion is, if a user needs that type of functionality and understands it
then they are better off being...

1) Provided with already built queries which they can edit
and/or 2) Allowed to build their own queries from scratch


To start answering your question about the subform...
I would design the query and/or report in full that you would want to run if
this option was chosen.
Having done that you will know what the SQL is going to be so look at that
SQL and see where it is the same as for the other queries and where it
differs.
Then you will design your if then statements to piece together the SQL as
required.
You may find that there is a chunk of SQL needed in the select and from
parts that isn't required in the other queries.
In that case you will need to add if then functionality to build the select
and from parts just like you currently do to build the where part.

Another long post - hope it makes sense

Remember to say if it would be better to email you direct

Regards

Andy Hull

 

[maceslin]

Hi again

This post is getting pretty lengthy now and is gradually moving away from
the original topic so, if you are happy for me to do so I will send my next
post to your email.

Let me know know if this is ok.
I'm assuming it'smaceslinfollowed by at gmail dot com
I've typed it like that so that email harvesting programs can't extract it
from this post and start sending you unsolicited mail.

For that reason, you should think about changing your options for this
newsgroup so that your email doesn't show.

Here is the code I would consider using to process the dates...

If (chkDateRange) and Not (IsNull(txtBeginDate) and IsNull(txtEndDate)) Then
If strWhere = "" Then
strWhere = "Where"
Else
strWhere = strWhere & " OR "
End If

strWhere = strWhere & "(Date_Entered between " &
format(nz(txtBeginDate,#01/01/1900#),"\#mm\/dd\/yyyy\#") & " and " &
format(nz(txtEndDate,#12/31/9999#),"\#mm\/dd\/yyyy\#") & ")"

End If

Be careful with the long line as it will be wrapped in this window.
I don't know how familiar you are with using dates and formatting so it
might look very complicated at first glance.

Take the time to go through it carefully though with the goal of
understanding it all fully. If you just use the code as is (as many people
probably do in the newsgroup) you will get stuck in the future with similar
but slightly different problems. Whereas taking the time to understand it
will mean you can then apply that knowledge to solve all similar future
problems.

To come up with the above code I decided it was too troublesome to have to
test which date, if either, was null as this affects where the brackets and
the "and" can be placed. So I decided that if the begin date was null I'd use
01/01/1900 and if the end data was null I'd use 12/31/9999. I've assumed that
you won't be searching for dates outside this range. You can make the 1900
date earlier if you really nedd to.

Also, rather than using if then statements to test if they are null I used
the Nz function.
This works in the following way...

If txtBeginDate is null then nz(txtBeginDate,#01/01/1900#) = #01/01/1900#

But, if txtBeginDate isn't null then nz(txtBeginDate,#01/01/1900#) =
txtBeginDate (ie whatever the user entered)

You've asked about choosing AND's and OR's...

This is a tricky situation.
Not because it's hard to code necessarily but because combining AND's and
OR's is a confusing subject for lots of people so providing the option can
lead to more users making mistakes.

If we were to provide a fully featured dynamic query generator we would have
to let the user, not only choose AND's or OR's, but put their criteria in any
order and possibly choose where to put brackets.

This is a lot of coding effort and for many users unnecessary and confusing.

My opinion is, if a user needs that type of functionality and understands it
then they are better off being...

1) Provided with already built queries which they can edit
and/or 2) Allowed to build their own queries from scratch

To start answering your question about the subform...
I would design the query and/or report in full that you would want to run if
this option was chosen.
Having done that you will know what the SQL is going to be so look at that
SQL and see where it is the same as for the other queries and where it
differs.
Then you will design your if then statements to piece together the SQL as
required.
You may find that there is a chunk of SQL needed in the select and from
parts that isn't required in the other queries.
In that case you will need to add if then functionality to build the select
and from parts just like you currently do to build the where part.

Another long post - hope it makes sense

Remember to say if it would be better to email you direct

Regards

Andy Hull

If you are still willing to teach me (I am learning a lot not just
blindly copying your inputs) please continue to contact me at the
email address you listed or same name @juno.com

I understand the nZ statement but do not understand the purpose of
the"\" in addition to the"/"before everything in the user entered date
and can not find anyhting on the web that show it when formating dates

When nothing entered in the txt boxes I get "missing operator in query
statement" when I would expect to see everything since all records are
between the default values

I have spent this evening changing the "select" and "from" statments
to show only what I need. I have also rebuilt and renamed the report
rptInternal. I am using the same query (qryParamters) to populate a
form so that I can send it out with active links in it. Have started
to develop cmdEmail to do so

Am still trouble shooting chkStatus and cboStatus- the report does not
populate

May be out of loop until Monday evening as I am returning home late
tomm and then on road for USNA vs Wake Forest football game, will be
able to check email but not have much time to work problems

As always thanks for the instruction
Dave