Passing value to a query/report - modding existing mdb 1

[Zerububble]

I am trying to modify a database (That works) written by a predecessor, to be more user friendly - I need to pass four variables to a report/query.
It currently uses dialog boxes that I presume are the query asking the user for input. No forms are designed for this input. There is no provision for user input checking as far as I can see.

I am looking to use a calendar control for dates and to automatically pass two other variables.

How do I get the variables into a query ? VBA is still new to me.

Variables are ...
[From Date:]
[To Date:]
[Major Address:]
[Minor Address:]
(Found in the "WHERE" section of the following SQL string)

Current command invoking report (stDocName opens diff reports) is .................
DoCmd.OpenReport stDocName, acPreview

Query (Text is lifted from the SQL view of the design of one query)...............

SELECT Format([Dispense History]![Major],"00-&quot & Format([Dispense History]![Minor],"000 &quot & [CDU].[Name] & " " & [CDU].[City] & ", " & [CDU].[State] AS Location, [Dispense History].Date, [Dispense History].Formula, [Dispense History].Job, [Dispense History].Department, [Dispense History].User, [Dispense History].Quantity, [Raw Value 1]+[Raw Value 2]+[Raw Value 3] AS [Value], IIf([Raw Code 1]=0,"",Format(IIf([Quantity]=0,0,[Raw Quantity 1]/[Quantity]),"0.0%&quot & " of " & [Raw Product 1]![Product Name]) & IIf([Raw Code 2]=0,"",Format(IIf([Quantity]=0,0,[Raw Quantity 2]/[Quantity]),"\and"" 0.0%""&quot & " of " & [Raw Product 2]![Product Name]) & IIf([Raw Code 3]=0,"",Format(IIf([Quantity]=0,0,[Raw Quantity 3]/[Quantity]),"\and"" 0.0%""&quot & " of " & [Raw Product 3]![Product Name]) AS Ingredients, [Dispense History].Result, [Dispense Results].Meaning, [Raw Product 1].[Product Name], [Raw Product 2].[Product Name], [Raw Product 3].[Product Name], [Dispense History].[Raw Quantity 1], [Dispense History].[Raw Quantity 2], [Dispense History].[Raw Quantity 3], [Dispense History].[Raw Code 1], [Dispense History].[Raw Code 2], [Dispense History].[Raw Code 3], [Dispense History].[Raw Value 1], [Dispense History].[Raw Value 2], [Dispense History].[Raw Value 3]
FROM (((([Dispense History] INNER JOIN Products AS [Raw Product 1] ON [Dispense History].[Raw Code 1] = [Raw Product 1].[Product Code]) INNER JOIN Products AS [Raw Product 2] ON [Dispense History].[Raw Code 2] = [Raw Product 2].[Product Code]) INNER JOIN Products AS [Raw Product 3] ON [Dispense History].[Raw Code 3] = [Raw Product 3].[Product Code]) INNER JOIN CDU ON ([Dispense History].Minor = CDU.Minor) AND ([Dispense History].Major = CDU.Major)) INNER JOIN [Dispense Results] ON [Dispense History].Result = [Dispense Results].Result
WHERE ((([Dispense History].Date) Between [From Date:] And [To Date:]) AND (([Dispense History].Result)<=IIf(StrComp(Left([Show exceptions? (Y,N)],1),&quot;Y&quot;,1),0,99)) AND (([Raw Product 1].[Product Name])<>&quot;none&quot AND (([Dispense History].Major)=[Major Address:]) AND (([Dispense History].Minor)=[Minor Address:]));

 

[dhookom]

I would remove the where parameters from the query and add controls to a form for the user to enter. You would then use code like:
[Blue]

    Dim strWhere as String
    strWhere = "1=1 "
    If Not IsNull(Me.txtFromDate ) Then
        strWhere = strWhere & " And [Date]>=#" & _
             Me.txtFromDate & "# "
    End If
    If Not IsNull(Me.txtToDate ) Then
        strWhere = strWhere & &quot; And [Date]<=#&quot; & _
             Me.txtToDate & &quot;# &quot;
    End If
    ' etc for other controls
    DoCmd.OpenReport &quot;rptYourRpt&quot;, acPreview, , strWhere

[/blue]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Zerububble]

I am not too sure if I follow you correctly...but here goes

All I need to do is to truncate the SQL string from &quot;WHERE&quot; to the end then pass a string at the end of the DoCmd line?

&quot;1=1 AND [Database date]>=#Users start date# AND [Database date]<=#Users end date# AND [Database field]=Me.some_other_control_value&quot;

This will be efectively added to the rest of the query and choose the right records?

 

[dhookom]

Zerububble,
I believe you have this correct. This is the method I use for almost all of my DoCmd.OpenForm and DoCmd.OpenReport methods. The Where clause is the stuff that usually follows the WHERE in your SQL syntax.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Zerububble]

So &quot;WHERE&quot; should be left in the SQL I take it

 

[dhookom]

Don't leave the word &quot;WHERE&quot; in your where clause when using the string in the where clause in OpenReport and OpenReport. The string you specify as the where clause becomes the &quot;FILTER&quot; property of the report or form.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bensta]

I have a question a stupid question to ask. What does strWhere= &quot;1=1&quot; in the string express mean and do exactly? I am new to SQL.
Thank you

 

[dhookom]

I only use this since each following addition to the strWhere variable includes &quot; AND &quot;. If there isn't an expresssion like &quot;1=1 &quot; the strWhere would then begin with the &quot; AND &quot; which would break. The &quot;1=1 &quot; could be any expression that evaluates to true. I suppose you could start with
strWhere = &quot;True &quot;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Zerububble]

I am having a problem with the query - I am being asking for &quot;Dispense History.Major&quot; and &quot;Dispense History.Minor&quot;
Both fields do exist in the table - and it appears that Access is finding the table as I am not asked for any date input.

I am out of ideas as to why that is the case - debug.pring comes up with ... (Formatted for easy reading, it is on one line.)
------------------------------------------
(
([Dispense History].Date >= #12/21/2003#)
AND
([Dispense History].Date <= #1/21/2004#)
)
AND (([Raw Product 1].[Product Name])<>&quot;none&quot
AND ([Dispense History].Major =1)
AND ([Dispense History].Minor =100)
--------------------------------------------

Code where the string is built ....

strWhere = &quot;(([Dispense History].Date >= #&quot; & Me.cmbStartDate & &quot;#)
And ([Dispense History].Date <= #&quot; & Me.cmbEndDate & &quot;#)) AND (([Raw Product 1].[Product Name])<>&quot; & Chr(34) & &quot;none&quot; & Chr(34) & &quot
AND (([Dispense History].Major)=&quot; & Me.txtMajor & &quot
AND (([Dispense History].[Minor])=&quot; & Me.txtMinor & &quot&quot;

 

[dhookom]

Are you sure that Dispense History.Major and Dispense History.Minor are included in your report's record source? I would never include the table names in the where clause since a record source shouldn't care about the table.

I expect you have a more than one field in your report's record source named Dat and Product Name. You should only have one of each and then get rid of the table names. BTW: Date is a bad name for a field since Date is the name of a function. Dates should always have some &quot;qualifier&quot; such as ProductionDate or SalesDate or HireDate or WorkDate...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Zerububble]

The reports record source is the SQL query at the start of this thread - it does include [Dispense History]![Major] and [Dispense History]![Minor] in the first few expressions after SELECT - and in the WHERE clause those are referred to as [Dispense History].Major and [Dispense History].Minor

I assigned the text after the WHERE clause from the original query (with chr(34)'s replacing &quot to &quot;strWhere&quot;

When the report is called I now get the normal questions, but I also get asked for &quot;Dispense History.Major&quot; and &quot;Dispense History.Minor&quot;.

It appears that the two methods are slighty different in the required syntax? Even after changing the strWhere reference to match the reports query source I get the questions

strWhere is working well with the dates - point taken on the field names though.

 

[Zerububble]

I deleted occurances of [Dispense history] in strWhere - now it asks for &quot;Major&quot; and &quot;Minor&quot; grrrrrrrrrr

 

[dhookom]

If your code (actually the report opening) asks for Major and Minor then you don't have these fields in the record source of your report. If they were in the record source, you would not be asked for them.
Check your fields list in your report for the names that you can choose to filter on.

BTW: I appreciate your providing the debug.print results and the way you formatted it. Most posters keep us guessing as to what is going on...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Zerububble]

If one beats one's head on a brick wall long enough you'll either break through or come to a realisation ....

The [Dispense History].Major and [Dispense History].Minor were NOT being found due to them not being selected individually! They were referenced but wrapped up by an AS clause - this hides the individual fields in that clause from the rest of the query it seems.

Original query ...
SELECT Format([Dispense History]![Major],&quot;00-&quot & Format([Dispense History]![Minor],&quot;000 &quot & [CDU].[Name] & &quot; &quot; & [CDU].[City] & &quot;, &quot; & [CDU].[State] AS Location ...

New query ...
SELECT Format([Dispense History]![Major],&quot;00-&quot & Format([Dispense History]![Minor],&quot;000 &quot & [CDU].[Name] & &quot; &quot; & [CDU].[City] & &quot;, &quot; & [CDU].[State] AS Location, [Dispense History]![Major], [Dispense History]![Minor]...

 

[dhookom]

Can I assume that you now have this working?

BTW: remember what I said about Date being a bad name? &quot;Name&quot; is probably even worse of a Name since every object has a Name property.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Zerububble]

Yep - its working finally

Thanks for the help.

 

[URunner]

Along these lines...The following line works fine in Access 97, but XP doesn't execute the &quot;and&quot; part. If I truncate the line after the first parameter &quot;2216&quot; it will open the form to the individual in question, but when I add the second parameter, the form open up with no record showing at all. I can't think of any reason why it won't work, and the help files don't show any change in syntax between Access versions. Any ideas?
Thanks!

docmd.OpenForm &quot;indactivitydetail&quot;,,,&quot; [Activity Involvement].[Individual ID] =2216
AND [Activity Involvement].[Activity ID] = 20&quot;

 

[dhookom]

Are you sure you have and IndividualID of 2216 with an ActivityID of 20 in a particular record? I never use the table names, just the field names. However this may be due to never having the same field name listed twice in the form's record source.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[URunner]

Well, the record is there--I can find it using a query with no problem. This is just a sample of the many recores that won't come up.

I have to use the table qualification or I get errors about ambiguous reference.

But like I say, the command line works just fine in Access 97. I was hoping someone had solved a similar problem.

 

[dhookom]

I would get rid of the table names and ambiguous columns/fields. Also, after the report has opened, change to design view and find the filter property. Copy it to the clipboard and the open the report's record source in datasheet view. View the SQL and add the filter string following &quot;WHERE &quot;. What do you find?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.