SQL question...

[Atomsk]

I have two queries, SQL is as follows:

SELECT DISTINCT GAPS.GAPID
FROM GAPS
WHERE (((Month([Recieved]))=[Enter month (1-12):]) And ((Year([Recieved]))=[Enter year (yyyy):]) And (([GAPS].[Track])="EDI&quot And (([GAPS].[Status])="Completed&quot);

SELECT DISTINCT GAPS.GAPID
FROM GAPS
WHERE (((Month([Recieved]))=[Enter month (1-12):]) AND ((Year([Recieved]))=[Enter year (yyyy):]) AND ((GAPS.Track)="EDI&quot);

As you can see, they're pretty similar except for the extra criteria in the first one, and prompt for the same two fields. I made a report for each query that counts the number of records returned, respectively, then I added the two reports to another report (as subreports). When I open this last report, it prompts me for the month and the year twice (well, 4 prompts actually), twice for each query, and I have to input the same month/year for each. Is there a way of rewriting these so that I only have to answer once (just two prompts)? Thanks.

 

[skittle123]

create a textbox (make it invisible) called (say rMonth and rYear ) in your last report.
I n both of your queries substitute the part where it asks for user input with rmonth.value and rYear.value respectively, it should just ask you once then.

I also have a question from your post: how did you create a sub report? I am trying to combine a set of 3 reports as 1 report (the 3 reports are 3 different queries) Thanks/

 

[cghoga]

you could accomplish this by grabbing your parameters from a field value on a form.

where you initiate the code ( the form where you have your command button), you could have 2 fields called Begin Date and End Date.

then in the query params you would reference the field values.

instead of [Enter Year] it would be something like
[Forms]![YourFormName]![FieldName].value

remember though, if you're referencing a field, the form the field is housed on cannot be closed.

hope this helps you.

 

[Atomsk]

Thanks you two. Subform/subreport creation can be found in the toolbox (the button is in the rightmost column, second to last row for the default setup). Make sure you have the wizard turned on.

 

[Atomsk]

Ehh, there's a small problem. If there are values defined for each of the text boxes, then the report returns the relevant values perfectly. If they're blank, though, it doesen't prompt me for values and the report just kind of sits there. Any ideas?

 

[cghoga]

in your code write a nested if staement like:

if isnull(yourtextboxname) then
msgbox("You must enter valid criteria.&quot
me.yourtextboxname.setfocus
else

PutYourCodeHere

end if

hope this helps you.