Re: Dynamic query problem

On Oct 16, 10:39 am, Andy Hull <AndyH...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
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

.

Relevant Pages

  • Re: Using SQL statement to append a recordset to a table
    ... To insert a value into the field Priority: ... > all failed mean while each time my report was working fine. ... Used the query> wizard "Find Unmatched Query Wizard" to create a query between these two> tables, changed to an append query, appending to the ... > table (looking at the sql view along the way). ...
    (microsoft.public.access.formscoding)
  • Re: Creating Access DB
    ... database and in the SQL database that your report needs. ... click the query tab. ...
    (microsoft.public.excel.programming)
  • Re: Criteria prompts
    ... how do I specify a "name" prompt? ... report, which is linked to a query. ... set this criteria to come up as prompts when the report is run? ...
    (microsoft.public.access.reports)
  • Re: Criteria prompts
    ... how do I specify a "name" prompt? ... report, which is linked to a query. ... set this criteria to come up as prompts when the report is run? ...
    (microsoft.public.access.reports)
  • Re: Reporting dates from 2 tables
    ... and from table2 as a prompt for the end ... in SQL? ... I don't know if I should be in the query ... design or the report design. ...
    (microsoft.public.access.gettingstarted)